Formatted SQL queries can be difficult to maintain, debug and can increase the risk of SQL injection when concatenating untrusted values into the
query. However, this rule doesn’t detect SQL injections (unlike rule S3649), the goal is only to highlight complex/formatted
queries.
Ask Yourself Whether
- Some parts of the query come from untrusted values (like user inputs).
- The query is repeated/duplicated in other parts of the code.
- The application must support different types of relational databases.
There is a risk if you answered yes to any of those questions.
Recommended Secure Coding Practices
Sensitive Code Example
from django.db import models
from django.db import connection
from django.db import connections
from django.db.models.expressions import RawSQL
value = input()
class MyUser(models.Model):
name = models.CharField(max_length=200)
def query_my_user(request, params, value):
with connection.cursor() as cursor:
cursor.execute("{0}".format(value)) # Sensitive
# https://docs.djangoproject.com/en/2.1/ref/models/expressions/#raw-sql-expressions
RawSQL("select col from %s where mycol = %s and othercol = " + value, ("test",)) # Sensitive
# https://docs.djangoproject.com/en/2.1/ref/models/querysets/#extra
MyUser.objects.extra(
select={
'mycol': "select col from sometable here mycol = %s and othercol = " + value}, # Sensitive
select_params=(someparam,),
},
)
Compliant Solution
cursor = connection.cursor(prepared=True)
sql_insert_query = """ select col from sometable here mycol = %s and othercol = %s """
select_tuple = (1, value)
cursor.execute(sql_insert_query, select_tuple) # Compliant, the query is parameterized
connection.commit()
See
