첫 시도는 아래와 같이
makeQuery()로 만든 쿼리(string 타입)를 cursor.execute(sql, [params])에서 [params] 파라미터 중 하나로 보내면
쿼리문으로 인식해 작동할 것이라 예상했으나, 오직 string으로만 인식하여 원하는 결과물을 얻지 못했다.
# BAD CASE
def makeQuery(exam_content_id):
from exam.models import ExamField
sections = ExamField.objects.filter(exam_content_id=exam_content_id).order_by('id')
return ''.join(
["""
(select count(*) from exam_examuseranswer
inner join exam_examquestion on exam_examquestion.id = exam_examuseranswer.exam_question_id
inner join exam_question on exam_question.id = exam_examquestion.question_id
inner join exam_examfield on exam_examfield.id = exam_examquestion.exam_field_id
where exam_examuseranswer.user_exam_id = exam_userexamcontent.id and exam_examfield.id = %s and exam_question.answer = exam_examuseranswer.answer
),
""" % (section.id) for section in sections])
with connection.cursor() as cursor:
cursor.execute(
"""
select distinct exam_userexamcontent.created_at, exam_userexamcontent.id, temp_account."name", temp_account.phone_number,
auth_user.username, auth_user.date_joined, %s
from exam_examuseranswer as eua
right join exam_userexamcontent on exam_userexamcontent.id = eua.user_exam_id
inner join exam_examtime on exam_examtime.id = exam_userexamcontent.exam_time_id
inner join payment_usercontent on payment_usercontent.id = exam_userexamcontent.user_content_id
inner join payment_order on payment_order.id = payment_usercontent.order_id
inner join temp_account on temp_account.id = payment_order.temp_account_id
left join user_userinfo on user_userinfo.id = temp_account.user_id
left join auth_user on auth_user.id = user_userinfo.user_id
inner join exam_examcontent on exam_examcontent.id = exam_examtime.exam_content_id
where exam_examcontent.id = %s order by exam_userexamcontent.id desc;
""", [makeQuery(exam_content_id), exam_content_id]
)
result = cursor.fetchall()중간 과정
cursor.execute(sql, [params])의 파라미터가 아닌 사전에 포맷팅하여 완성된 쿼리(string)를 execute()의 sql에 때려박으니, 원하는 결과값은 얻는다.
query = """
select distinct exam_userexamcontent.created_at, exam_userexamcontent.id, temp_account."name", temp_account.phone_number,
auth_user.username, auth_user.date_joined, %s
from exam_examuseranswer as eua
right join exam_userexamcontent on exam_userexamcontent.id = eua.user_exam_id
inner join exam_examtime on exam_examtime.id = exam_userexamcontent.exam_time_id
inner join payment_usercontent on payment_usercontent.id = exam_userexamcontent.user_content_id
inner join payment_order on payment_order.id = payment_usercontent.order_id
inner join temp_account on temp_account.id = payment_order.temp_account_id
left join user_userinfo on user_userinfo.id = temp_account.user_id
left join auth_user on auth_user.id = user_userinfo.user_id
inner join exam_examcontent on exam_examcontent.id = exam_examtime.exam_content_id
where exam_examcontent.id = %s order by exam_userexamcontent.id desc;
""" % (makeQuery(exam_content_id), exam_content_id)
from django.db import connection
with connection.cursor() as cursor:
cursor.execute(query)
result = cursor.fetchall()근데 문제는 ‘SQL injection’이라는 것
장고 공식 문서를 보면 (https://docs.djangoproject.com/en/3.0/topics/db/sql/#passing-parameters-into-raw)
Warning : Do not use string formatting on raw queries or quote placeholders in your SQL strings! As discussed in SQL injection protection, using the params argument and leaving the placeholders unquoted protects you from SQL injection attacks, a common exploit where attackers inject arbitrary SQL into your database. If you use string interpolation or quote the placeholder, you’re at risk for SQL injection.
위와 같은 경고가 있다. ‘SQL injection attack’에 노출될 수 있으니, 포맷팅된 쿼리를 execute()에 때려박지 말라는 듯 하다.
그래서 아래와 같이 바꿔본다
def makeQuery(sections):
scores_query = ''.join(
["""
(select count(*) from exam_examuseranswer
inner join exam_examquestion on exam_examquestion.id = exam_examuseranswer.exam_question_id
inner join exam_question on exam_question.id = exam_examquestion.question_id
inner join exam_examfield on exam_examfield.id = exam_examquestion.exam_field_id
where exam_examuseranswer.user_exam_id = exam_userexamcontent.id and exam_examfield.id = %s and exam_question.answer = exam_examuseranswer.answer
),
""" for section in sections])
return """
select distinct exam_userexamcontent.id, temp_account."name", temp_account.phone_number,
auth_user.username, auth_user.date_joined, {scores_query} exam_userexamcontent.created_at
from exam_examuseranswer as eua
right join exam_userexamcontent on exam_userexamcontent.id = eua.user_exam_id
inner join exam_examtime on exam_examtime.id = exam_userexamcontent.exam_time_id
inner join payment_usercontent on payment_usercontent.id = exam_userexamcontent.user_content_id
inner join payment_order on payment_order.id = payment_usercontent.order_id
inner join temp_account on temp_account.id = payment_order.temp_account_id
left join user_userinfo on user_userinfo.id = temp_account.user_id
left join auth_user on auth_user.id = user_userinfo.user_id
inner join exam_examcontent on exam_examcontent.id = exam_examtime.exam_content_id
where exam_userexamcontent.created_at >= %s at time zone 'utc' at time zone 'kst'
and exam_userexamcontent.created_at <= %s at time zone 'utc' at time zone 'kst'
and exam_examcontent.id = %s order by exam_userexamcontent.id desc;
""".format(scores_query=scores_query)
def makeParams(sections, start_date, end_date, exam_content_id):
return [section.id for section in sections] + [start_date, end_date, exam_content_id]
with connections[using_db_name].cursor() as cursor:
cursor.execute(makeQuery(sections), makeParams(sections, start_date, end_date, exam_content_id))
results = cursor.fetchall()makeQuery 함수에선 query를, makeParams 함수에선 params만을 반환하게 역할을 명확히 분리시켰다.
makeQuery()에서[params](파라미터)로 채워질%s까지 포함한 문자열 타입의 쿼리를 리턴한다.makeParams()에서 파라미터 값들을 가진 배열을 리턴한다.
이러면 ‘SQL injection’ 위험도 피하고, 원하는 대로 쿼리와 파라미터를 만들어 원하는 결과를 얻을 수 있었다.