Use parameterised queries

How to do it

Use the placeholders of your database driver or query builder. Placeholders cannot hold table names, column names or sort order. Pick those from a fixed allow-list in code. Document databases need their own guard, in Keep query operators out of document database input. Give the app's database user only the rights it needs.

Examples

JavaScript example

// Bad: input becomes part of the query
db.query(`SELECT * FROM orders WHERE email = '${email}'`);
// Good: input is a separate value
db.query('SELECT * FROM orders WHERE email = $1', [email]);
// Sort columns come from an allow-list, never from the request
const SORT_COLUMNS = { newest: 'created_at', total: 'total_cents' };
const column = SORT_COLUMNS[req.query.sort] ?? 'created_at';

Python example

# Python with psycopg: the driver sends the value separately
cur.execute("SELECT * FROM orders WHERE email = %s", (email,))

PHP example

// PHP with PDO
$stmt = $pdo->prepare('SELECT * FROM orders WHERE email = ?');
$stmt->execute([$email]);

Java example

// Java with JDBC
PreparedStatement st = conn.prepareStatement("SELECT * FROM orders WHERE email = ?");
st.setString(1, email);

How to check

Send a quote and ' OR '1'='1 in every search field, filter, sort parameter and login field. The answers match normal input, with no database errors and no extra rows. A search of the code finds no query built with string joins or template strings.

Sources

• OWASP SQL Injection Prevention Cheat Sheet

Related rules

• DAT-8 Pass values as parameters in Data and databases
• STK-14 Learn SQL and write it with parameters in Software choices
• APS-97 Keep query operators out of document database input in Application security
• APS-98 Escape input in directory queries in Application security