← mohdarif.com · All notes
Web Security · Secure Code Review

SQL Injection in Practice: Values, Column Names, Stored Data and Stored Procedures

Most people learn "use placeholders" and stop there. This post covers the cases where that advice is not enough, with code you can read and test yourself.

By Mohammad Arif · 14 min read

This post is for developers and reviewers who have heard "use parameterized queries" and want to know what that does and does not cover. We start from the basics and build up. Each section adds one idea that the next one needs: first how injection works, then why values and column names need different fixes, then data that is stored and used later, and finally database functions. The examples use Python and PostgreSQL, but the ideas apply to any language and database.

1. How SQL injection works

A SQL query is a text command sent to the database. If you build that text by joining strings, user input becomes part of the command:

query = f"SELECT * FROM employees WHERE department = '{department}'"

With a normal value, Finance, the database receives:

SELECT * FROM employees WHERE department = 'Finance'

But the database cannot tell which part of that text is your code and which part came from the user. If the user sends x' OR '1'='1, the database receives:

SELECT * FROM employees WHERE department = 'x' OR '1'='1'

The quote in the input closed the string early, and the rest became SQL. The condition is now always true, so every row comes back. That is SQL injection: input that was meant to be data is read as part of the command.

The fix idea is to send the command and the data separately, so the database never has to guess. That is what a parameterized query (also called a prepared statement) does:

cursor.execute("SELECT * FROM employees WHERE department = %s", (department,))

The %s is a placeholder. The value travels separately and is always treated as a value. The same hostile text now just searches for a department with a strange name and finds nothing.

2. The code we will review

Here is a reporting endpoint for an internal analytics tool:

@app.get("/api/reports")
def get_report(request):
    department = request.args.get("department")
    sort_by = request.args.get("sort_by", "created_at")

    query = f"""
        SELECT id, employee_name, salary, department
        FROM employees
        WHERE department = '{department}'
        ORDER BY {sort_by}
    """
    return database.execute(query)

The developer says: "This API is internal and needs SSO. The department comes from a dropdown. We also block words like UNION and DROP." Each of these three claims fails:

3. Two injection points, two different fixes

This query has two places where input is pasted in, and they are not the same kind of thing.

Both are vulnerable, but the fixes differ. This is the most common gap in code reviews, so we take them one at a time.

Fix for the value: a placeholder

cursor.execute(
    "SELECT id, employee_name, salary, department FROM employees WHERE department = %s",
    (department,),
)

The value is passed separately, so it cannot change the shape of the query.

Fix for the identifier: an allowlist

A user cannot be trusted to name a column, because there is no safe way to quote their text into the structure of the query. Instead, you decide which columns are allowed, and let the user pick from your list:

ALLOWED_SORT_COLUMNS = {
    "name": "employee_name",
    "department": "department",
    "date": "created_at",
}

sort_by = request.args.get("sort_by", "date")
column = ALLOWED_SORT_COLUMNS.get(sort_by)
if column is None:
    raise ValueError("Invalid sort column")

query = f"""
    SELECT id, employee_name, salary, department
    FROM employees
    WHERE department = %s
    ORDER BY {column}
"""
cursor.execute(query, (department,))

The only text pasted into the SQL is column, which comes from a dictionary you wrote, never from the request. A sort_by that is not in the dictionary is rejected. The same approach works for sort direction: map the user's choice to a literal ASC or DESC.

Some libraries can quote identifiers for you, for example psycopg2.sql.Identifier. That is safe from injection, but it will happily sort by any column the user names, including ones you did not mean to expose. An allowlist also decides which columns are allowed, so it is the better default for user-chosen sorting.

4. A fix that looks right but breaks the feature

A well-meaning developer sends a pull request that puts placeholders everywhere:

query = """
    SELECT id, employee_name, salary, department
    FROM employees
    WHERE department = %s
    ORDER BY %s
"""
cursor.execute(query, (department, sort_by))

They say: "Both inputs are placeholders, so the query is fully parameterized and SQL injection is fixed." Should you approve it?

First, is it still injectable? No. The value is bound as data, so it cannot change the SQL structure. Second, does it work? Also no. A placeholder carries a value. When you pass the text "salary", you get a text value, not the salary column. What happens next depends on the driver and the database:

So the fix is safe but the feature is broken, and one of the outcomes is worse than an error because it fails silently. This matters in practice: a security fix that breaks a feature often gets reverted, and the revert goes back to string concatenation. Reject the pull request, explain the value versus identifier difference, and offer the allowlist version from section 3.

5. Second-order SQL injection

So far the input went straight from the request into the query. Second-order injection is different: the attacker's text is stored first and used later.

Say users can name their reports, and the app stores the name safely with placeholders:

def create_report(name, owner_id):
    cursor.execute(
        "INSERT INTO reports (name, owner_id) VALUES (%s, %s)",
        (name, owner_id),
    )

Days later, a background job reads that name and builds another query:

def run_saved_report(report_id):
    cursor.execute("SELECT name FROM reports WHERE id = %s", (report_id,))
    report_name = cursor.fetchone()[0]

    query = f"""
        SELECT * FROM analytics
        WHERE report_name = '{report_name}'
    """
    cursor.execute(query)

The developer's reasoning: "We used placeholders when saving, and the job only reads from our own database, so it is trusted." That reasoning is the trap.

1. StoreAttacker saves a reportwith SQL text in its nameINSERT uses placeholders: safe2. WaitThe text sits in the databaseIt looks like any other row3. UseA job reads the name andpastes it into a new queryThe SQL runs
Figure 1. The first query is safe. The second query is where the stored text turns into SQL.

Data does not become trustworthy because it passed through your database, a queue, a cache or an internal API. What matters is how each query uses it.

Do not try to fix this by cleaning the name when it is saved. A report named Director's Quarterly Report is perfectly valid, and the apostrophe is not an attack. Keep the data as it is, and make sure every query that uses it treats it as a value:

cursor.execute(
    "SELECT * FROM analytics WHERE report_name = %s",
    (report_name,),
)

The stored text can now contain any characters and it stays data.

6. Stored procedures are not automatically safe

A stored procedure (or database function) is code that runs inside the database. Teams sometimes move queries there and say "the app calls a procedure with a placeholder, so injection is fixed". Look at what the function does with the value:

CREATE FUNCTION search_employees(p_department TEXT)
RETURNS SETOF employees AS $$
DECLARE
    query TEXT;
BEGIN
    query := 'SELECT * FROM employees WHERE department = ''' || p_department || '''';
    RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;
cursor.execute("SELECT * FROM search_employees(%s)", (department,))

The Python call is safe, so the value reaches the function intact. But inside the function, || joins strings together and EXECUTE runs the result as a new query. That is the same string-building mistake from section 1, moved one layer down. Binding the arguments of the call protects the call. It does not protect dynamic SQL built inside the function.

Fixing the function

Best: do not use dynamic SQL when you do not need it.

CREATE FUNCTION search_employees(p_department TEXT)
RETURNS SETOF employees AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM employees e WHERE e.department = p_department;
END;
$$ LANGUAGE plpgsql;

If you really need dynamic SQL, pass values with EXECUTE ... USING so they stay values:

RETURN QUERY EXECUTE
    'SELECT * FROM employees WHERE department = $1'
    USING p_department;

For a dynamic column or table name inside a function, use PostgreSQL's format() with %I, which quotes an identifier safely (and %L for a literal). Even then, prefer picking from a fixed list.

Why SECURITY DEFINER raises the stakes

By default a function runs with the caller's database permissions (SECURITY INVOKER). A function declared SECURITY DEFINER runs with the permissions of the role that owns it. This is useful when you want to give controlled access to data the caller cannot read directly. It also means that if the function is injectable, the attacker's SQL runs with the owner's permissions, which are often much higher than the app's.

ModeRuns withIf the function is injectable
SECURITY INVOKER (default)The caller's permissionsDamage is limited to what the app account can already do
SECURITY DEFINERThe function owner's permissionsThe attacker may reach what the owner can reach

A fixed function can still be risky

Suppose we make the function static (no dynamic SQL) and mark it SECURITY DEFINER. Is it still vulnerable to SQL injection through p_department? No, because the value is never part of the SQL text. But other risks remain, and they come from the elevated permissions:

BEGIN;
CREATE FUNCTION search_employees(p_department TEXT)
RETURNS SETOF employees AS $$ ... $$
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public, pg_temp;
REVOKE ALL ON FUNCTION search_employees(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION search_employees(TEXT) TO report_app;
COMMIT;

7. Fixing injection does not fix access control

A perfectly safe query can still return data the user should not see. In our example the report returns salary for a department the caller picks. If the user is not allowed to see salaries, parameterized queries do not help. Injection controls answer "can the user change my query?". Authorization answers "should this user get this data?". You need both, and the access control post covers the second one.

Three different identities

It helps to keep three questions apart, because teams often merge them:

ConceptQuestion it answersExample
AuthenticationWho is making the request?Alice, from her login or JWT
AuthorizationWhat is that person allowed to access?Alice may see her own record, not Bob's
Database privilegesWhat may the database role do?The role app_service may SELECT from employees

Here is where they come apart. Most web apps connect to PostgreSQL with one shared role, say app_service, no matter which user is logged in. The database never hears the name "Alice". If app_service can read the whole employees table and nothing in the app or in PostgreSQL (for example row-level security) filters rows, then Alice's request can return Bob's salary. The database allowed it because app_service is allowed. Alice's login was never part of that decision.

This also explains what "runs with the caller's permissions" means in section 6. For a normal function (SECURITY INVOKER), the caller is the database role the app connected as, which is app_service, not the person at the keyboard. The app has to add the "is this user allowed?" rule itself.

8. How to test it safely

Only test systems you own or have permission to test, and use a staging database filled with fake data, never real salaries.

Summary

References