org.sonar.l10n.py.rules.python.S2077.html Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of python-checks Show documentation
The newest version!
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 {rule:python: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

   Use parameterized queries, prepared
  statements, or stored procedures and bind variables to SQL query parameters. 
   Consider using ORM frameworks if there is a need to have an abstract layer to access data. 

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

   OWASP - Top 10 2021 Category A3 - Injection 
   OWASP - Top 10 2017 Category A1 - Injection 
   CWE - CWE-20 - Improper Input Validation 
   CWE - CWE-89 - Improper Neutralization of Special Elements used in an SQL Command 
   Derived from FindSecBugs rules Potential SQL/JPQL Injection
  (JPA), Potential SQL/JDOQL Injection (JDO), Potential SQL/HQL Injection (Hibernate)