This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Query Parameterization: Bind Parameters, Prepared Statements, and SQL Injection

Parameterized queries are simultaneously the most important security practice and a significant performance optimization. This article explains how bind parameters work, how PostgreSQL caches query plans, and why parameterization is non-negotiable.

SQL Injection: The Problem

Without parameterization, user input is concatenated into SQL strings:

DANGEROUS: Never do this

user_id = request.args.get("id")

cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

An attacker provides 1; DROP TABLE users; as the id parameter, and the query becomes:

SELECT * FROM users WHERE id = 1; DROP TABLE users;

Parameterization prevents this by separating SQL code from data:

SAFE: Use parameterized query

cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

The database receives the SQL structure and the parameter values separately. The parameter value 1; DROP TABLE users; is treated as a literal string, not executable SQL.

Bind Parameters

PostgreSQL supports two parameter syntaxes depending on the driver:

psycopg2 (%s):

cursor.execute(

"INSERT INTO users (email, name, age) VALUES (%s, %s, %s)",

("[e-mail]", "Alice", 30)

)

asyncpg ($1, $2):

await conn.execute(

"INSERT INTO users (email, name, age) VALUES ($1, $2, $3)",

"[e-mail]", "Alice", 30

)

JDBC (?):

PreparedStatement stmt = conn.prepareStatement(

"SELECT * FROM users WHERE email = ?"

);

stmt.setString(1, "[e-mail]");

ResultSet rs = stmt.executeQuery();

Prepared Statements

PostgreSQL separates prepared statements into two phases:

• PREPARE : The database parses, analyzes, and plans the query.

2\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\. EXECUTE : The database runs the plan with specific parameter values.

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Explicit prepared statement

PREPARE find_user(INTEGER) AS

SELECT * FROM users WHERE id = $1;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Execute with parameter

EXECUTE find_user(42);

EXECUTE find_user(99);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Deallocate when done

DEALLOCATE find_user;

Automatic Prepared Statements in Drivers

Most PostgreSQL drivers implement automatic prepared statement caching:

import psycopg2

from psycopg2 import pool

psycopg2 automatically caches prepared statements per connection

conn = psycopg2.connect("dbname=mydb")

cursor = conn.cursor()

First call: prepares and executes

cursor.execute("SELECT * FROM users WHERE id = %s", (42,))

Second call: reuses cached prepared statement

cursor.execute("SELECT * FROM users WHERE id = %s", (99,))

Generic Query Plans

For prepared statements with bind parameter

---

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.