SQL Injection Prevention: Prepared Statements and ORM

SQL Injection Prevention: Prepared Statements and ORM

SQL injection is the classic OWASP Top 10 Injection flaw that still surfaces in production in 2026. A single ' OR '1'='1 in a login field can bypass authentication, exfiltrate an entire database, or land remote code execution via UDFs. This post covers SQLi types, proper defence with prepared statements, the role of ORMs, least-privilege DB users, and WAFs as a compensating layer.

How SQL injection works

The canonical flaw is concatenating user input into an SQL string:

// Vulnerable
$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '{$email}'";

// Attacker sends: ' OR '1'='1
// Result: SELECT * FROM users WHERE email = '' OR '1'='1' — returns everyone

Harsher payloads: '; DROP TABLE users; --, UNION SELECT password, NULL FROM admin_users, blind SQLi via timing.

SQL injection flavours

• Classic (in-band) — response exposes the attacker's data
• Blind — no visible output; attacker differentiates true/false via HTTP status or timing
• Out-of-band — exfiltration via DNS/HTTP from the DB (xp_cmdshell, UTL_HTTP)
• Second-order — value is stored safely but later used in an unsafe query

The primary defence: prepared statements

Prepared statements separate SQL code and data at the protocol level. The driver sends a template and parameters independently; no parameter value can become SQL.

PHP PDO

// Safe
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND is_active = ?');
$stmt->execute([$email, 1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// Named parameters
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute([':email' => $email]);

Critical: disable emulated prepares so the driver performs real parameterisation:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Node.js

// mysql2
const [rows] = await pool.execute(
  'SELECT * FROM users WHERE email = ? AND active = ?',
  [email, 1]
);

// pg
const { rows } = await client.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

Python

# psycopg2
cur.execute('SELECT * FROM users WHERE email = %s', (email,))

# SQLAlchemy Core
stmt = text('SELECT * FROM users WHERE email = :email')
result = conn.execute(stmt, {'email': email})

What cannot be parameterised

Table names, column names, and sort direction (ASC/DESC) cannot be bound. Use a whitelist:

$allowed = ['name', 'created_at', 'email'];
$sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'created_at';
$sql = "SELECT * FROM users ORDER BY {$sort}";

ORMs as a defence

Modern ORMs (Prisma, Doctrine, Sequelize, SQLAlchemy ORM) use prepared statements by default. The danger is raw-query API документацию:

// Prisma — unsafe raw with interpolation
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);

// Safe parameterised tagged template
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;

Least privilege for the DB user

Your app should not connect as root. Minimum grants:

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'localhost';
-- never grant: DROP, CREATE, ALTER, FILE, SUPER, GRANT

Use a separate read-only user for GET endpoints. This turns a successful SQLi into a data disclosure instead of destruction.

WAF and monitoring

A WAF (ModSecurity + OWASP CRS, Cloudflare) blocks standard payloads — see the WAF guide. Alert on:

• Unusually long query strings (>1 KB)
• Keywords UNION, SELECT, DROP in input fields that aren't search
• Spikes of DB 500 errors (SQLi probing signature)

Testing

• Static analysis: Semgrep, SonarQube — catch concatenation in SQL
• Dynamic: sqlmap, Burp Suite Active Scan
• Code review — every query with variables

FAQ

Is mysql_real_escape_string enough? Weak. Bypassable via multibyte sets (CVE-2006-7243) and useless in numeric contexts. Use prepared statements.

Is a regex blacklist safe? No — blacklists lose. Whitelist input plus parameterisation is the only workable approach.

Does an ORM fully protect me? Yes, as long as you avoid raw APIs. Audit for $queryRawUnsafe, db.raw(), Connection.execute().

What about NoSQL (MongoDB)? Equivalent threat — NoSQL injection via $ne/$gt in JSON. Enforce types: typeof user.email === 'string'.

Conclusion

Three layers: 1) prepared statements everywhere; 2) least privilege in the DB; 3) a WAF as alarm system. Monitor endpoints with enterno monitors and audit headers via the Security Scanner. Related: XSS, rate limiting, WAF.