SQL Parameters & Prepared Statements

Prepared Statements (also known as Parameterized Queries) are the primary defense against SQL Injection (SQLi) vulnerabilities. They enforce a fundamental structural boundary between the executable SQL code and user-supplied data parameters.

When an application uses prepared statements, the database engine compiles and optimizes the SQL command structure before combining it with user input. As a result, user inputs are strictly treated as scalar literal values—never as executable SQL commands.

How Prepared Statements Work

In dynamic string concatenation, the application sends a combined SQL string directly to the database interpreter:

[ Application ] -- Raw Text String with Unfiltered Input --> [ Database SQL Interpreter ]

With prepared statements, query execution is split into two distinct phases:

               PREPARED STATEMENT EXECUTION FLOW
               
 Phase 1: PREPARE (Code Template Sent)
 Application  ------------ SELECT * FROM Users WHERE ID = ? ------------> Database Optimizer
                                                                          |
                                                                  (Compiles & Caches
                                                                   Execution Plan)
 
 Phase 2: EXECUTE (Bound Parameters Sent)
 Application  -------------------- Parameter 1: "101" -------------------> Execution Engine
                                                                          |
                                                                  (Executes plan safely)

1. Compilation Phase (PREPARE): The application sends an SQL query template containing parameter placeholders (?, $1, or @param) to the database. The database engine parses, validates, and compiles an execution plan for that template structure.
2. Execution Phase (EXECUTE): The application sends the parameter values separately. The database inserts these values directly into the compiled execution plan's slots.

Why Injections Fail: Even if a parameter value contains SQL syntax elements like ' OR '1'='1 or ; DROP TABLE Users;, the database engine treats the entire string purely as literal character data inside the parameter slot.

Code Examples Across Languages

1. Python (using psycopg2 / PostgreSQL)

import psycopg2

# ❌ VULNERABLE: Direct string interpolation (DO NOT USE)
user_input = "admin' --"
cursor.execute(f"SELECT * FROM users WHERE username = '{user_input}'")

# ✅ SECURE: Parameterized Query
user_input = "admin' --"
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (user_input,))  # Parameters passed as a tuple

2. Node.js (using pg / PostgreSQL)

// ❌ VULNERABLE
const query = `SELECT * FROM users WHERE email = '${req.body.email}'`;
await client.query(query);

// ✅ SECURE: Parameterized Query with Positional Placeholders ($1, $2)
const query = 'SELECT * FROM users WHERE email = $1 AND status = $2';
const values = [req.body.email, 'Active'];
const result = await client.query(query, values);

3. Java (JDBC)

// ❌ VULNERABLE
String query = "SELECT * FROM accounts WHERE acc_number = '" + reqAccount + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

// ✅ SECURE: PreparedStatement
String query = "SELECT * FROM accounts WHERE acc_number = ? AND pin = ?";
PreparedStatement pstmt = connection.prepareStatement(query);

// Bind variables strictly by index and data type
pstmt.setString(1, reqAccount);
pstmt.setInt(2, reqPin);

ResultSet rs = pstmt.executeQuery();

4. C# (.NET / ADO.NET)

// ✅ SECURE: SqlCommand with Named Parameters
string query = "SELECT * FROM Employees WHERE LastName = @LastName AND DepartmentID = @DeptID";

using (SqlCommand cmd = new SqlCommand(query, connection))
{
    // Explicitly add typed parameters
    cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = inputLastName;
    cmd.Parameters.Add("@DeptID", SqlDbType.Int).Value = inputDeptId;

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // Process results
    }
}

Parameter Placeholder Syntax Variations

Different database engines and drivers use varying syntax conventions for placeholders:

Limitations: What Parameterized Queries CANNOT Do

Prepared statements can only parameterize scalar data values (WHERE values, INSERT values, UPDATE set values). They cannot accept parameter placeholders for structural database identifiers:

• Table names (FROM ?)
• Column names (SELECT ? FROM Users)
• Sort order directives (ORDER BY Column ?)
• SQL keywords or operators (SELECT * FROM Users WHERE Age ? 18)

Safe Handling of Dynamic Identifiers (Allow-Listing)

When applications must allow users to choose sort columns or filtering fields, use strict allow-listing in code:

# Safe dynamic column sorting using allow-list validation
ALLOWED_SORT_COLUMNS = {
    "name": "LastName",
    "date": "CreatedDate",
    "salary": "Salary"
}

user_sort_choice = request.args.get("sort", "name")

# Fallback to default if user choice is not explicitly allowed
selected_column = ALLOWED_SORT_COLUMNS.get(user_sort_choice, "LastName")

# Identifier is safe to concatenate because it comes from a trusted hardcoded map
query = f"SELECT EmployeeID, FirstName, LastName FROM Employees ORDER BY {selected_column} ASC"
cursor.execute(query)