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. For document databases such as MongoDB, reject objects where you expect a string, so {"$ne": null} cannot pass as a password. Give the app's database user only the rights it needs.

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 with psycopg: the driver sends the value separately
cur.execute("SELECT * FROM orders WHERE email = %s", (email,))

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

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

How to check

Send a quote, ' OR '1'='1 and {"$ne": null} 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.