Insecure Use of SQL Queries

Fixing Insecure Use of SQL Queries

About SQL Injection (SQLi)

What is SQL injection (SQLi)?

SQL injection (SQLi) is a type of security vulnerability that allows attackers to exploit an application's interaction with a SQL database.

This occurs when an application fails to properly sanitize user input and includes untrusted data in an SQL query, allowing attackers to inject malicious code or modify the intended behavior of the query.

SQL injection can affect various types of applications that interact with SQL databases, such as web applications, content management systems, and e-commerce platforms.

Attackers can use various techniques, such as crafted queries or filter values, to inject malicious code or entities into the application.

Check out this video for a high-level explanation:

What is the impact of SQL injections?

SQL injection can lead to various security threats and risks, such as:

• Information disclosure: SQL injection can expose sensitive information, such as user data, login credentials, or other types of confidential information, to unauthorized parties.
• Unauthorized access: SQL injection can allow attackers to gain unauthorized access to databases, perform unauthorized actions, or modify data.
• Denial-of-service: SQL injection can cause denial-of-service (DoS) attacks by injecting malicious queries or values that consume system resources, such as CPU or memory, leading to system crashes or slowdowns.
• Arbitrary code execution: SQL injection can execute arbitrary code on the database server by injecting malicious queries or values that exploit vulnerabilities in the application or the underlying operating system.

SQL injections are very common and have been the cause of many high-profile breaches.

How to prevent SQL injections?

To prevent SQL injection, it is important to follow security best practices and implement appropriate security measures, such as:

• Use parameterized queries: Use parameterized queries and other secure coding practices to ensure that user input is properly sanitized and not used to construct SQL queries directly.
• Use input validation: Validate user input to ensure that it is safe and does not contain malicious code or characters that can be used for injection attacks.
• Limit user access: Limit user access to databases to authorized users and restrict access to sensitive information and actions.
• Use secure coding practices: Follow secure coding practices, such as code reviews, vulnerability scanning and testing, and threat modeling, to ensure that the source code is free of vulnerabilities and that SQL injection vulnerabilities are addressed.
• Use a web application firewall: Use a web application firewall (WAF) that can help prevent SQL injection by filtering and blocking malicious traffic.

References

Taxonomies

• OWASP Top 10 - A03 Injection
• SANS TOP 25 - #3
• CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

Explanation & Prevention

• OWASP: SQL Injection Attacks
• OWASP: SQL Injection Prevention Cheat Sheet
• OWASP: Query Parameterization Cheat Sheet
• CAPEC-66: SQL Injection
• NoSQL Injection explanation video
• Holistic Info-Sec: Injection risks
• Holistic Info-Sec: Injection countermeasures

Related CVEs

• CVE Details: CWE-89

Training

• Secure Code Warrior: SQL Injection

Tainted user input passed to cursor.execute() (Django)

Django's ORM uses parameterized statements everywhere and thus prevents most SQL injection vulnerabilities out of the box. However, some APIs allow executing raw SQL queries and as such can be susceptible to SQL injections if user input is passed to them.

This rule detects user input flowing into the first parameter of the cursor.execute() function, which results in a SQL injection vulnerability.

Rule-specific references:

• Django - Executing custom SQL directly

Option A: Use cursor.execute() with parameterized statements

Preferably, instead of cursor.execute() the secure Django ORM functionality is used. However, if that is not possible, then using parameterized statements with cursor.execute() will prevent SQL injection vulnerabilities.

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for usages of cursor.execute() that have user input in the SQL query and replace them with the examples shown below.

   from django.db import connection
   # ...
   # Insecure example:
   # User input is passed into the first argument of cursor.execute()
   def insecure(request):
     with connection.cursor() as cursor:
       baz = request.GET.get('baz')
       cursor.execute(f"SELECT foo FROM bar WHERE baz = {baz}")
       row = cursor.fetchone()
       # ...

   from django.db import connection
   # ...
   # Secure example:
   # User input is passed as prepared statement to cursor.execute()
   def secure(request):
     with connection.cursor() as cursor:
       baz = request.GET.get('baz')
       cursor.execute("SELECT foo FROM bar WHERE baz = %s", [baz])
       row = cursor.fetchone()
     # ...

3. Test it

4. Ship it 🚢 and relax 🌴

Tainted user input passed to extra() (Django)

Django's ORM uses parameterized statements everywhere and thus prevents most SQL injection vulnerabilities out of the box. However, some APIs allow executing raw SQL queries and as such can be susceptible to SQL injections if user input is passed to them.

This rule detects user input flowing into the $MODEL.objects.extra() function, which results in a SQL injection vulnerability. Furthermore, as per the Django documentation, this is an old API that will be deprecated and its use should be avoided.

Rule-specific references:

• Django - extra() documentation
• Django - QuerySet.extra

Option A: Use extra() with parameterized statements

Preferably, instead of extra() the secure Django ORM functionality is used. However, if that is not possible, then using parameterized statements with extra() will prevent SQL injection vulnerabilities.

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for usages of extra() that have user input in the SQL query and replace them with the examples shown below.

   from django.db import connection
   # ...
   class User(models.Model):
     # ...
   # Insecure example:
   # User input is passed into the first argument of raw()
   def insecure(request):
     baz = request.GET.get('baz')
     Entry.objects.extra(where=["headline=%s" % baz])
     # ...

   from django.db import connection
   # ...
   class User(models.Model):
     # ...
   
   # Secure example:
   # User input is passed as prepared statement to raw()
   def secure(request):
     baz = request.GET.get('baz')
     Entry.objects.extra(where=['headline=%s'], params=[baz])
     # ...

3. Test it

4. Ship it 🚢 and relax 🌴

Tainted user input passed to raw() (Django)

Django's ORM uses parameterized statements everywhere and thus prevents most SQL injection vulnerabilities out of the box. However, some APIs allow executing raw SQL queries and as such can be susceptible to SQL injections if user input is passed to them.

This rule detects user input flowing into the first parameter of the $MODEL.objects.raw() function, which results in a SQL injection vulnerability.

Rule-specific references:

• Django - Performing raw queries

Option A: Use raw() with parameterized statements

Preferably, instead of raw() the secure Django ORM functionality is used. However, if that is not possible, then using parameterized statements with raw() will prevent SQL injection vulnerabilities.

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for usages of raw() that have user input in the SQL query and replace them with the examples shown below.

   from django.db import connection
   # ...
   class User(models.Model):
     # ...
   # Insecure example:
   # User input is passed into the first argument of raw()
   def insecure(request):
     baz = request.GET.get('baz')
     User.objects.raw("SELECT foo FROM bar WHERE baz = %s" % baz)
     # ...

   from django.db import connection
   # ...
   class User(models.Model):
     # ...
   
   # Secure example:
   # User input is passed as prepared statement to raw()
   def secure(request):
     baz = request.GET.get('baz')
     User.objects.raw("SELECT foo FROM bar WHERE baz = %s", (baz,))
     # ...

3. Test it

4. Ship it 🚢 and relax 🌴

Tainted user input passed to RawSQL() (Django)

Django's ORM uses parameterized statements everywhere and thus prevents most SQL injection vulnerabilities out of the box. However, some APIs allow executing raw SQL queries and as such can be susceptible to SQL injections if user input is passed to them.

This rule detects user input flowing into the first parameter of the RawSQL() function, which results in a SQL injection vulnerability.

Rule-specific references:

• Django - Raw SQL expressions
• Django - RawSQL

Option A: Use RawSQL() with parameterized statements

Preferably, instead of RawSQL() the secure Django ORM functionality is used. However, if that is not possible, then using parameterized statements with RawSQL() will prevent SQL injection vulnerabilities.

1. Go through the issues that GuardRails identified in the PR/MR.

2. Look for usages of RawSQL() that have user input in the SQL query and replace them with the examples shown below.

   from django.db.models.expressions import RawSQL
   # ...
   # Insecure example:
   # User input is passed into the first argument of RawSQL()
   def insecure(request):
     baz = request.GET.get('baz')
     RawSQL("select foo from bar where baz = %s" % baz)
     # ...

   from django.db.models.expressions import RawSQL
   # ...
   # Secure example:
   # User input is passed as prepared statement to RawSQL()
   def secure(request):
     baz = request.GET.get('baz')
     RawSQL("select foo from bar where baz = %s", (baz,))
     # ...

3. Test it

4. Ship it 🚢 and relax 🌴