A parameterized query keeps SQL structure separate from untrusted values. Write placeholders into the command, then pass the values separately to the database driver. The driver binds them safely, so quote marks, comments, or extra SQL in an input cannot change what the command means. section 12.5 section 4.10
String concatenation and interpolation blur code and data. In a login query, a username containing a quote and SQL comment can cause the database to ignore the password check; injected clauses or chained commands can also read, alter, or delete data. section 12.4 section 4.10
Bind login values separately
Use a fixed query such as `SELECT * FROM users WHERE username = %s AND password_hash = %s`, and supply `username` and the password hash as a separate parameter tuple to `execute`. Placeholder syntax differs by driver: Java prepared statements use `?`, while some drivers use `%s` or named parameters. section 12.5 section 4.10
Treat parameterization as the default, with limits to remember:
- Use it for database commands that include client or other untrusted values; it is an essential defense against SQL injection. section 4.16 section 12.16
- Allow-list inputs when query structure itself must vary, such as dynamic `ORDER BY` columns, because values cannot safely stand in for SQL identifiers or clauses. section 12.5
- Do not assume an ORM removes every risk: ORMs generally parameterize ordinary operations, but manually interpolated SQL fragments remain vulnerable. section 12.6
- Layer defenses with input validation, limited database permissions, expected-response checks, and logging and monitoring of database activity. section 4.14
@app.route('/books', methods=['GET'])
def get_books():
order = request.args.get('order') or "" #1
columns = order.lower().split(",") #2
permitted = [ "title", "author", "isbn" ] #3
sanitized = [ c for c in columns
if c in permitted ] #4
if not sanitized :
sanitized = [ 'isbn '] #5
order_by = ",".join(sanitized ) #6
sql = f"SELECT * FROM books ORDER BY {order_by}" #7
books = db.execute(sql).fetchall()
return jsonify(books)
This example shows the exception case: it allow-lists requested sort columns before constructing an `ORDER BY` clause dynamically. section 12.5
Parameterized queries are widely available across mainstream languages and database drivers. They also apply beyond conventional SQL databases: SQL++ queries in Couchbase can bind named or positional values. When using an ORM or another abstraction, verify its parameter-binding behavior and use its safe placeholder API for any explicit conditions. section 12.5 section 12.6 section 12.9
