SQL injection

Description

SQL injection occurs when untrusted data becomes part of executable SQL text instead of being passed as a separate value. An attacker can change the query structure to bypass conditions or read, modify or delete data accessible to the application's database account.

Using an API such as execute() or writing raw SQL is not inherently vulnerable. The problem arises when untrusted values are not separated from SQL structure at execution.

Potential impact

Data exposure: Bypassed conditions or injected SQL can reveal information the application account can read.
Data modification or deletion: An account with write permissions can be used to undermine data integrity.
Authentication or authorization bypass: Queries used to make access decisions may have their protective conditions bypassed.
Further effects: Denial of service or operating-system command execution depends on the database, enabled features and account permissions.

Remediation

Pass values separately using the API's documented placeholders and parameter argument. Drivers use different forms, including ?, %s, %(name)s and :name. Follow the relevant API documentation and do not quote placeholders.
Parameters cannot represent SQL structure such as table names, column names or sort directions. Prefer query builders, use identifier-composition APIs such as psycopg.sql.Identifier, or map a small set of allowed choices to developer-controlled SQL fragments.
Prefer SQLAlchemy Core or ORM expressions. If textual SQL is needed, keep the text() template fixed and pass values as bound parameters. For exec_driver_sql(), use the underlying driver's placeholder syntax.
Prefer Django's ORM. When using Manager.raw() or a cursor, pass values through params.
Pass values through params with Peewee's execute_sql() and pandas' read_sql_query().
Even constrained input, such as numbers, should be bound after type conversion where possible. Pattern checks, escaping helpers, helper names or client-controlled allow-lists do not replace parameterization.
Give the database account only the permissions it needs to limit the impact of exploitation.

Examples

Before

python

import sqlite3

from flask import request

def find_users():
    keyword = request.args.get("q", "")
    sort_column = request.args.get("sort", "id")

    with sqlite3.connect("app.db") as connection:
        cursor = connection.cursor()
        # Both the value and identifier are inserted directly into SQL text.
        sql = (
            "SELECT id, name, created_at FROM users "
            f"WHERE name LIKE '%{keyword}%' ORDER BY {sort_column} DESC"
        )
        return cursor.execute(sql).fetchall()

After

python

import sqlite3

from flask import request

def find_users():
    keyword = request.args.get("q", "")
    choice = request.args.get("sort", "id")

    # Input selects a branch; SQL identifiers come from fixed code literals.
    if choice == "name":
        sort_column = "name"
    elif choice == "created_at":
        sort_column = "created_at"
    else:
        sort_column = "id"

    # SQLite's ? placeholders are for values only.
    sql = (
        "SELECT id, name, created_at FROM users "
        "WHERE name LIKE ? "
        f"ORDER BY {sort_column} DESC"
    )

    with sqlite3.connect("app.db") as connection:
        cursor = connection.cursor()
        return cursor.execute(sql, (f"%{keyword}%",)).fetchall()

Explanation:

Before: Both the search value and sort column are inserted into SQL text and can be interpreted as SQL syntax.
After: The search value is passed as the second argument to execute(). The sort column, which cannot be bound as a value, comes from fixed literals in server code. Unknown choices fall back to id.
Building the allow-list itself from request parameters, a body or cookies lets the attacker control the validation policy.

Implementation considerations

For database wrappers or queries passed through several functions, keep SQL text and values separate through the final execution call. If an API accepts either a table name or a query, distinguish those uses and bind dynamic query values with the relevant driver's syntax.