Resolve vulnerability: Improper neutralization of special elements used in an SQL Command ('SQL Injection')

Placeholder Adam Zwickeyrequested to merge
remediate/improper-neutralization-of-special-elements-used-in-an-sql-comm-D20251121T144721 into main

MR created from vulnerability: Improper neutralization of special elements used in an SQL Command ('SQL Injection')

AI GENERATED FIX

The suggested code changes were generated by GitLab Duo Vulnerability Resolution, an AI feature. Use this feature with caution. Before you run a pipeline or apply the code changes, carefully review and test them, to ensure that they solve the vulnerability.

The large language model that generated the suggested code changes was provided with the entire file that contains the vulnerable lines of code. It is not aware of any functionality outside of this context.

Please see our documentation for more information about this feature.

Description:

SQL Injection represents a severe vulnerability that can result in compromising data or the entire system. Through dynamically constructing SQL query strings, user input might manipulate the logic of the SQL statement. Consequently, attackers could gain unauthorized access to sensitive information or even execute OS commands or code.

To mitigate this risk, it's crucial to replace all dynamically generated SQL queries with parameterized queries. In scenarios where dynamic queries are necessary, never directly incorporate user input. Instead, utilize a map or dictionary containing valid values and resolve them using a user-supplied key.

For instance, certain database drivers don't support parameterized queries for comparison operators like > or <. In such cases, refrain from using user-provided > or < values; rather, have users provide gt or lt values. These alphabetical values can then be used to retrieve the appropriate > and < values for constructing the dynamic query. The same principle applies to other scenarios where column or table names are required but cannot be parameterized.

Here's an example using PreparedStatement queries:

import sqlite3

# Create a new database (in memory)
con = sqlite3.connect(":memory:")
# Get a cursor from the connection
cur = con.cursor()
# Create a tuple of the value to be used in the parameterized query
params = ('user-input',)
# execute the statement, passing in the params for the value
cur.execute("select name from sqlite_master where name = ?", params)
# work with the result
result = cur.fetchall()

For more information on SQL Injection see OWASP: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Summary:

SQL Injection Vulnerability Analysis and Fix

Vulnerability

The reported vulnerability is an SQL Injection (CWE-89) in the demo_search function. The code directly concatenates user input (search_term) into an SQL query string without proper sanitization, allowing attackers to manipulate the query's logic and potentially access or modify unauthorized data.

Fix Implementation

The fix replaces the vulnerable string concatenation with a parameterized query:

# Before (vulnerable):
vulnerable_query = f"SELECT * FROM demo_products WHERE name LIKE '%{search_term}%' OR description LIKE '%{search_term}%'"
cursor.execute(vulnerable_query)

# After (secure):
safe_query = "SELECT * FROM demo_products WHERE name LIKE ? OR description LIKE ?"
search_pattern = f"%{search_term}%"
cursor.execute(safe_query, (search_pattern, search_pattern))

Security Improvement

The fix uses SQLite's parameterized query feature, which:

  1. Separates the SQL command structure from the data
  2. Properly escapes special characters in user input
  3. Prevents attackers from injecting malicious SQL code
  4. Maintains the original functionality of searching products by name or description

This implementation follows the OWASP recommendation to use parameterized queries instead of dynamically constructed SQL strings, effectively mitigating the SQL injection risk while preserving the application's intended behavior.

Identifiers:

  • A1:2017 - Injection
  • Bandit Test ID bandit.B608
  • python-lang-sqli-hardcoded-sql-expression-taint
  • A03:2021 - Injection
  • CWE-89
  • SAST Rules ID - python_sql_rule-hardcoded-sql-expression

Merge request reports