SQL Injection Prevention Across Languages and Frameworks

TL;DR

• Bottom line: ALWAYS use parameterized queries (prepared statements) — they separate SQL structure from data, making injection structurally impossible.
• Key tool/command: cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,)) — parameterized query pattern (syntax varies by language).
• Watch out for: Dynamic table/column names and ORDER BY clauses cannot be parameterized — use strict server-side allowlists for these.
• Works with: Every major language and database — Python, Node.js, Java, Go, PHP, C#; PostgreSQL, MySQL, SQL Server, SQLite, Oracle.

Constraints

• NEVER concatenate or interpolate user input directly into SQL strings — this is the root cause of all SQL injection
• Dynamic table names, column names, and ORDER BY clauses cannot be parameterized — use strict server-side allowlists
• Stored procedures are NOT automatically safe — internal EXEC with string concatenation is still vulnerable
• ORMs reduce but do not eliminate risk — raw query escape hatches (.raw(), @Query, execute()) bypass parameterization
• Input validation and WAFs are defense-in-depth only — never the sole protection
• Escaping special characters is fragile and database-specific — parameterization is always preferred

Quick Reference

Decision Tree

START
|-- What language/framework?
|   |-- Python?
|   |   |-- Using SQLAlchemy/Django ORM? --> Use ORM query API (avoid .raw()/.text())
|   |   |-- Using psycopg2/sqlite3?     --> Use %s or ? placeholders with params tuple
|   |   +-- Using asyncpg?              --> Use $1, $2 numbered placeholders
|   |-- Node.js?
|   |   |-- Using Prisma/Sequelize?     --> Use ORM query builders (avoid $queryRaw)
|   |   |-- Using pg (node-postgres)?   --> Use $1, $2 parameterized queries
|   |   +-- Using mysql2?              --> Use ? placeholders with params array
|   |-- Java?
|   |   |-- Using Hibernate/JPA?       --> Use named parameters (:param) or Criteria API
|   |   +-- Using JDBC directly?       --> Use PreparedStatement with ? placeholders
|   |-- Go?
|   |   |-- Using GORM/sqlx?           --> Use struct binding or named params
|   |   +-- Using database/sql?        --> Use $1 or ? placeholders with Query/Exec
|   |-- PHP?
|   |   |-- Using Laravel Eloquent?    --> Use query builder; avoid DB::raw()
|   |   +-- Using PDO directly?        --> Use prepare() + execute() with :param or ?
|   |-- C#/.NET?
|   |   |-- Using Entity Framework?    --> Use LINQ queries; avoid FromSqlRaw with interpolation
|   |   +-- Using ADO.NET?            --> Use SqlCommand with SqlParameter objects
|   +-- Other? --> Find language-specific prepared statement API; same principle applies
|
|-- Need dynamic table/column names?
|   +-- YES --> Use server-side allowlist, NEVER parameterize identifiers
|
|-- Need LIKE pattern matching?
|   +-- YES --> Parameterize the value, escape % and _ in user input
|
+-- Need ORDER BY user input?
    +-- YES --> Map user input to allowlisted column names server-side

Step-by-Step Guide

1. Identify all SQL query construction points

Audit your codebase for any location where SQL strings are built with string concatenation, interpolation, or formatting near SQL keywords. [src1]

# Search for common vulnerable patterns in Python
grep -rn "execute.*\".*+\|execute.*f\"" --include="*.py" .
# Search in JavaScript/TypeScript
grep -rn "query.*\`.*\${\|query.*\".*+" --include="*.js" --include="*.ts" .

Verify: Every result from these searches should be reviewed and remediated.

2. Replace string concatenation with parameterized queries

For each vulnerable query, refactor to use the language-appropriate parameterized query syntax. [src1] [src4]

# BEFORE (vulnerable)
cursor.execute("SELECT * FROM users WHERE email = '" + email + "'")

# AFTER (secure)
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

Verify: Refactored query should produce identical results with normal input and reject ' OR 1=1 -- payloads.

3. Handle dynamic identifiers with allowlists

Table names, column names, and ORDER BY fields cannot be parameterized. Build a strict allowlist. [src2]

ALLOWED_SORT_COLUMNS = {'name', 'created_at', 'email', 'id'}

def safe_order_by(column, direction='ASC'):
    if column not in ALLOWED_SORT_COLUMNS:
        raise ValueError(f"Invalid sort column: {column}")
    if direction.upper() not in ('ASC', 'DESC'):
        raise ValueError(f"Invalid sort direction: {direction}")
    return f"ORDER BY {column} {direction.upper()}"

Verify: Passing "name; DROP TABLE users" should raise ValueError.

4. Secure LIKE clauses

Escape wildcard characters in user input before incorporating into LIKE patterns. [src1]

import re

def safe_like_search(cursor, search_term):
    escaped = re.sub(r'([%_\\])', r'\\\1', search_term)
    cursor.execute(
        "SELECT * FROM products WHERE name LIKE %s",
        (f"%{escaped}%",)
    )

Verify: Input %admin% should search for the literal string, not match everything.

5. Configure defense-in-depth measures

Apply the principle of least privilege and enable additional safeguards. [src3] [src7]

-- Create a restricted application database user
CREATE USER app_reader WITH PASSWORD 'strong_password';
GRANT SELECT ON users, products, orders TO app_reader;
-- Do NOT grant DELETE, DROP, ALTER, or EXECUTE to application accounts

Verify: Application user should fail on DROP TABLE attempts with a permissions error.

6. Test with automated SQL injection scanners

Run sqlmap or OWASP ZAP against your application to verify no injection points remain. [src5]

# Test a specific URL parameter with sqlmap
sqlmap -u "http://localhost:8080/api/users?id=1" --batch --level=3 --risk=2

# Test with OWASP ZAP (CLI mode)
zap-cli quick-scan -s all http://localhost:8080

Verify: Both tools should report zero SQL injection vulnerabilities.

Code Examples

Python / psycopg2: Parameterized Query

# Input:  user-supplied email string
# Output: matching user record or None
import psycopg2

conn = psycopg2.connect("dbname=myapp user=app_reader")
cur = conn.cursor()

email = request.args.get('email')
cur.execute(
    "SELECT id, name, email FROM users WHERE email = %s",
    (email,)
)
user = cur.fetchone()
cur.close()
conn.close()

Python / SQLAlchemy ORM: Safe Query

# Input:  user-supplied search term
# Output: list of matching users
from sqlalchemy import select
from models import User, Session

session = Session()
search = request.args.get('q')

stmt = select(User).where(User.name.ilike(f"%{search}%"))
results = session.execute(stmt).scalars().all()

Node.js / pg: Parameterized Query

// Input:  user-supplied id from request
// Output: user record object
const { Pool } = require('pg');
const pool = new Pool();

async function getUser(userId) {
  const result = await pool.query(
    'SELECT id, name, email FROM users WHERE id = $1',
    [userId]
  );
  return result.rows[0];
}

Java / JDBC: PreparedStatement

// Input:  user-supplied username from HTTP request
// Output: matching user or null
String username = request.getParameter("username");

String sql = "SELECT id, name, email FROM users WHERE username = ?";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, username);
    ResultSet rs = stmt.executeQuery();
    if (rs.next()) {
        return new User(rs.getInt("id"), rs.getString("name"));
    }
}

Go / database/sql: Parameterized Query

// Input:  user-supplied email from query string
// Output: user struct or error
func getUser(db *sql.DB, email string) (*User, error) {
    var u User
    err := db.QueryRow(
        "SELECT id, name, email FROM users WHERE email = $1",
        email,
    ).Scan(&u.ID, &u.Name, &u.Email)
    if err != nil {
        return nil, err
    }
    return &u, nil
}

PHP / PDO: Prepared Statement

// Input:  user-supplied product ID
// Output: product record array
$pdo = new PDO('mysql:host=localhost;dbname=shop', 'app_reader', $pass);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$productId = $_GET['id'];
$stmt = $pdo->prepare('SELECT id, name, price FROM products WHERE id = :id');
$stmt->execute(['id' => $productId]);
$product = $stmt->fetch(PDO::FETCH_ASSOC);

C# / ADO.NET: SqlParameter

// Input:  user-supplied search term
// Output: list of matching records
string search = Request.Query["q"];
string sql = "SELECT Id, Name FROM Users WHERE Name LIKE @search";

using var cmd = new SqlCommand(sql, connection);
cmd.Parameters.Add(new SqlParameter("@search", $"%{search}%"));

using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) {
    results.Add(new User(reader.GetInt32(0), reader.GetString(1)));
}

Anti-Patterns

Wrong: String concatenation in Python

# BAD -- direct string concatenation allows injection
email = request.args.get('email')
cursor.execute("SELECT * FROM users WHERE email = '" + email + "'")
# Input: ' OR '1'='1' --   => returns ALL users

Correct: Parameterized query in Python

# GOOD -- parameterized query separates structure from data
email = request.args.get('email')
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
# Input: ' OR '1'='1' --   => searches for literal string, finds nothing

Wrong: Stored procedure with EXEC concatenation

-- BAD -- dynamic SQL inside stored procedure
CREATE PROCEDURE GetUser @name NVARCHAR(100)
AS
  EXEC('SELECT * FROM users WHERE name = ''' + @name + '''')

Correct: Stored procedure with sp_executesql

-- GOOD -- parameterized dynamic SQL
CREATE PROCEDURE GetUser @name NVARCHAR(100)
AS
  EXEC sp_executesql
    N'SELECT * FROM users WHERE name = @n',
    N'@n NVARCHAR(100)',
    @n = @name

Wrong: ORM raw query with unsafe interpolation

// BAD -- $queryRawUnsafe with template string interpolation
const name = req.query.name;
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE name = '${name}'`  // VULNERABLE
);

Correct: ORM query builder or safe tagged template

// GOOD -- Use Prisma's query builder
const users = await prisma.user.findMany({
  where: { name: req.query.name }
});
// Or safe tagged template (Prisma auto-parameterizes)
const users2 = await prisma.$queryRaw`
  SELECT * FROM users WHERE name = ${req.query.name}
`;

Wrong: Relying only on WAF or blocklist

# BAD -- blocklist approach is always bypassable
def sanitize(input):
    blocked = ['SELECT', 'DROP', 'INSERT', '--', ';']
    for word in blocked:
        input = input.replace(word, '')
    return input
# Bypass: "SeLeCt", "SEL/**/ECT", URL encoding, Unicode tricks

Correct: Parameterize first, validate second

# GOOD -- parameterization as primary defense, validation as depth
import re

def validate_email(email):
    if not re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$', email):
        raise ValueError("Invalid email format")
    return email

email = validate_email(request.args.get('email'))
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

Common Pitfalls

• PHP PDO emulated prepares: By default, PDO uses emulated prepared statements which are vulnerable. Fix: $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false). [src1]
• ORM raw query escape hatches: Every ORM has a .raw() or .execute() method that bypasses parameterization. Fix: Audit all raw query usage; parameterize within raw queries. [src2]
• Integer values left unparameterized: Developers skip parameterization for numeric inputs. Fix: Parameterize ALL values — WHERE id = ? not WHERE id = + id. [src3]
• Second-order injection overlooked: Data stored safely can be dangerous when retrieved and used in a later query without parameterization. Fix: Treat ALL data as untrusted, even from your own database. [src6]
• LIKE wildcards not escaped: User input containing % or _ changes LIKE query behavior. Fix: Escape %, _, and \ in user input before adding wildcards. [src1]
• Multi-statement execution enabled: Some drivers allow semicolons to stack queries. Fix: Disable multi-statement mode (MySQL: allowMultiQueries: false). [src4]
• Error messages expose schema: Detailed SQL error messages help attackers map your database. Fix: Return generic error messages to clients; log details server-side. [src5]
• Testing only login forms: SQL injection can occur in any parameter — search, sort, filters, headers, cookies. Fix: Test all input vectors with sqlmap or OWASP ZAP. [src5]

Diagnostic Commands

# Scan a URL for SQL injection with sqlmap
sqlmap -u "http://target.com/page?id=1" --batch --level=3 --risk=2

# Scan with specific parameter and POST data
sqlmap -u "http://target.com/login" --data="user=admin&pass=test" --batch

# Run OWASP ZAP active scan from CLI
zap-cli active-scan http://target.com

# Check PostgreSQL for dangerous dynamic SQL in functions
psql -c "SELECT proname, prosrc FROM pg_proc WHERE prosrc LIKE '%EXECUTE%' AND prosrc LIKE '%||%';"

# Search codebase for vulnerable patterns (Python)
grep -rn --include="*.py" "execute.*f\"|execute.*%.*%" .

# Search codebase for vulnerable patterns (JavaScript)
grep -rn --include="*.js" "query.*\`.*\${|query.*+.*req\." .

When to Use / When Not to Use

Important Caveats

• Parameterized queries protect VALUES only — they cannot parameterize identifiers (table names, column names, schema names). Always use allowlists for dynamic identifiers.
• ORM protection varies by method — User.objects.filter(name=x) in Django is safe, but User.objects.raw(f"SELECT * FROM users WHERE name = '{x}'") is not. Audit every raw query method.
• Placeholder syntax varies by driver: ? (SQLite, MySQL JDBC), %s (psycopg2), $1 (node-postgres, asyncpg), @param (ADO.NET), :param (cx_Oracle, Hibernate).
• Multi-database applications need consistent parameterization across all connections — a single unprotected query path is sufficient for compromise.
• SQL injection is ranked #3 in OWASP Top 10 (2021) and CWE-89 remains one of the most exploited vulnerabilities in production as of 2026.