Parameterized Queries

One-liner: A secure coding technique that separates SQL commands from user input, preventing SQL Injection by treating data as data, not executable code.

🎯 What Is It?

Parameterized queries (also called prepared statements) are the primary defense against SQL Injection. Instead of concatenating user input directly into SQL commands, parameterized queries use placeholders (?, @param, :param) that the database engine treats strictly as data values, never as SQL code.

This separation ensures the SQL interpreter cannot be confused—it knows exactly what is command and what is data.

🤔 Why It Matters

• Eliminates SQL Injection at its root — The #1 defense recommended by OWASP
• Works regardless of input — Even malicious payloads like ' OR 1=1-- are treated as literal strings
• Required for compliance — PCI-DSS, OWASP Top 10, secure coding standards
• Performance benefits — Prepared statements can be cached and reused by the database

Misconceptions About Defenses

❌ Input validation alone — Blocklists and allowlists can be bypassed
❌ String escaping — Complex, error-prone, and insufficient
❌ WAF only — Can be bypassed; doesn't fix the root cause
✅ Parameterized queries — Address the vulnerability at its core

🔬 How It Works

Vulnerable Code (String Concatenation)

Python Example (Vulnerable)

# ❌ VULNERABLE: User input concatenated directly into query
username = request.form['username']
password = request.form['password']

query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(query)

# Attack: username = "admin'--" bypasses password check!
# Resulting query: SELECT * FROM users WHERE username = 'admin'--' AND password = ''

C# Example (Vulnerable)

// ❌ VULNERABLE: String concatenation
string username = Request.Form["username"];
string password = Request.Form["password"];

string query = "SELECT * FROM Users WHERE Username = '" + username + "' AND Password = '" + password + "'";
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();

Secure Code (Parameterized Queries)

Python Example (Secure)

# ✅ SECURE: Parameterized query
username = request.form['username']
password = request.form['password']

query = "SELECT * FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))

# Attack payload: username = "admin'--"
# Database treats entire string as username literal, no SQL injection possible

C# / .NET with MS SQL (Secure)

// ✅ SECURE: Parameterized query
string username = Request.Form["username"];
string password = Request.Form["password"];

string query = "SELECT * FROM Users WHERE Username = @username AND Password = @password";
SqlCommand command = new SqlCommand(query, connection);

// Add parameters - user input never touches the query string
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", password);

SqlDataReader reader = command.ExecuteReader();

PHP with PDO (Secure)

// ✅ SECURE: Prepared statement with PDO
$username = $_POST['username'];
$password = $_POST['password'];

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

$user = $stmt->fetch();

Java with JDBC (Secure)

// ✅ SECURE: PreparedStatement
String username = request.getParameter("username");
String password = request.getParameter("password");

String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(query);

stmt.setString(1, username);
stmt.setString(2, password);

ResultSet rs = stmt.executeQuery();

📊 Parameterization by Language/Framework

🛡️ Detection & Prevention

How to Detect (Code Review)

• Look for string concatenation in SQL queries
• Search for +, f-strings, template literals near SQL keywords
• Flag any direct use of user input in query strings
• Check for ORM misuse (raw SQL strings)

How to Detect (Dynamic Testing)

• Inject SQL payloads: ', ", ' OR 1=1--, '; DROP TABLE--
• Use Burp Suite Intruder or sqlmap
• Monitor for error messages revealing SQL syntax
• Time-based blind SQLi: '; WAITFOR DELAY '00:00:05'--

How to Prevent

• Always use parameterized queries — No exceptions for user input
• Use ORMs correctly — Most support parameterization by default
• Code review — Enforce parameterization in secure coding standards
• Static analysis — Tools like SonarQube, Semgrep detect SQL injection patterns
• Training — Educate developers on secure database access

🎤 Interview Angles

Common Questions

• "What's the best defense against SQL Injection?"
• "Explain how parameterized queries prevent SQL Injection"
• "What's the difference between parameterized queries and input validation?"
• "Can you still get SQL Injection with an ORM?"

STAR Story

Situation: During a penetration test, discovered SQL Injection in a login form that bypassed authentication using admin'--.
Task: Demonstrate the vulnerability and provide actionable remediation guidance.
Action: Showed the bypass in the pentest report with HTTP request/response evidence. Provided a code example converting the vulnerable Python code from string concatenation to parameterized queries using cursor.execute(query, (username, password)). Explained that input validation is defense-in-depth but parameterization is the root fix.
Result: Development team implemented parameterized queries across all database interactions. Follow-up testing found zero SQL injection vulnerabilities. Code examples were added to their secure coding standards.

✅ Best Practices

• Use parameterization for all dynamic queries, even internal tools
• Never concatenate user input into SQL, even after "sanitization"
• Use ORM query builders when possible (they parameterize automatically)
• Include parameterization examples in code review checklists
• Test with SQL injection payloads in QA/staging

❌ Common Misconceptions

• "Input validation is enough" → Blocklists can be bypassed; parameterization is the primary control
• "Stored procedures prevent SQL Injection" → Only if they use parameterization internally
• "ORMs are always safe" → Model.raw(), execute_raw() can still be vulnerable
• "Only public-facing apps need parameterization" → Internal apps are targets too

🔗 Related Concepts

• SQL Injection
• Injection
• OWASP
• Penetration Testing Report
• Secure Coding
• Defense in Depth

📚 References

• OWASP SQL Injection Prevention Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
• OWASP Query Parameterization: https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html
• CWE-89: SQL Injection: https://cwe.mitre.org/data/definitions/89.html
• Bobby Tables: https://bobby-tables.com/