SQL injection

How SQL injection happens

The bug appears when your code builds a query by pasting user input into a string. The database receives one block of text and cannot tell which part you meant as data. This is typical of code that was written quickly or generated without review:

# vulnerable: never build SQL from user input
email = request.args["email"]
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")

If a visitor submits x' OR '1'='1 as the email, the condition becomes true for every row and the query returns all users. Depending on the query and the database account's permissions, an attacker can also read other tables, change data or delete it. The OWASP SQL Injection Prevention Cheat Sheet describes the defences, and the same idea inspired the name of prompt injection.

The fix: parameterised queries

Send the SQL text and the values separately. The database then treats the values purely as data, whatever characters they contain. OWASP puts prepared statements with parameterised queries first among the defences, and warns that joining untrusted values into the query before preparing it defeats the protection.

# Python (psycopg)
cur.execute("SELECT * FROM users WHERE email = %s", (email,))

// Node.js (node-postgres)
const res = await client.query('SELECT * FROM users WHERE email = $1', [email]);

The psycopg documentation says never to use % or + to merge values into queries, and the node-postgres documentation warns that concatenating parameters into the query text can lead to SQL injection. ORMs are safe when you pass values as parameters. The usual trap is their raw query option, where OWASP says to bind untrusted values instead of concatenating them.

What parameters cannot cover

Placeholders stand for values, not for table names, column names or keywords such as ASC and DESC. When a user picks a sort order, map the choice to fixed text in your code and reject anything unknown:

const orders = { newest: 'created_at DESC', oldest: 'created_at ASC' };
const order = orders[req.query.sort] ?? orders.newest;

OWASP calls escaping all dynamic input strongly discouraged and recommends parameterised queries instead. It also stresses that binding values does not decide whether the caller may access the matching record, so you still need access rules such as row-level security.

Finally, limit the damage. OWASP warns against giving application accounts DBA or admin access and advises a separate database user per application with only the rights it needs. That does not prevent injection but makes a successful one far less costly. See least privilege.

Checking an AI-written app

Assistants can write correct parameterised code, but they can also produce string-built queries when asked to make something work quickly. A short review:

1. Search the code for SQL built from variables. A rough first pass:

grep -rniE 'f"(select|insert|update|delete)|`(select|insert|update|delete)' src/

2. Ask the assistant to list every query that uses string formatting, concatenation or template literals, and convert each to parameters.
3. Type a single quote into your own forms and URL parameters. A database error or changed behaviour is a warning sign. Test only apps you own.
4. Connect with a restricted database user, not the owner or an admin.
5. Never pass model output straight into SQL; see the vibe coding security checklist.

A WAF can block some obvious attack patterns, but treat it as a safety net, not as a replacement for fixing the query.

Related terms: WAF, Least privilege, Row-level security (RLS), Prompt injection, Vibe coding.