SQL Injection Prevention: Why Parameterized Queries Beat Escaping Every Time

Why I take SQL injection personally

I've seen SQL injection destroy projects. About six years ago, I inherited a codebase at Šikulovi s.r.o. where someone had built a custom CMS with zero parameterization. Just raw string concatenation everywhere. Within two months of launch, the client's entire user database got dumped. Passwords, emails, addresses - everything. That experience changed how I approach database security forever.

There are two ways to prevent SQL injection: escaping and parameterization. Both can work, but after cleaning up that mess, I have very strong opinions about which one you should use. Spoiler: it's parameterization, always.

The anatomy of an attack

If you don't understand how SQL injection works, you can't defend against it properly. The core problem is mixing code (your SQL) with data (user input) in the same string.

• Vulnerable code looks like: "SELECT * FROM users WHERE id = " + userId
• Normal input: userId = "5" gives you: SELECT * FROM users WHERE id = 5
• Malicious input: userId = "5 OR 1=1" gives you: SELECT * FROM users WHERE id = 5 OR 1=1
• That OR 1=1 is always true - suddenly you get ALL users instead of one
• Nastier payloads can drop tables, extract password hashes, or even get shell access
• Numeric fields are just as vulnerable when you concatenate them as strings

The escaping approach (and why I avoid it)

Escaping transforms dangerous characters so they're treated as literal text, not SQL syntax. Single quotes get backslashed, that sort of thing. Sounds reasonable, right?

• Single quote becomes backslash-quote
• Backslashes get doubled up
• Some functions handle null bytes, newlines, control characters
• Every database has different escaping rules
• PHP has mysqli_real_escape_string(), Postgres has pg_escape_string()

Why escaping keeps me up at night

I don't use escaping for SQL injection prevention anymore. Here's why it's a ticking time bomb:

• Database-specific: MySQL, PostgreSQL, SQL Server all have different rules. Switch databases? Rewrite everything.
• Character encoding exploits: Multi-byte character sets can bypass escaping entirely. I've seen it happen.
• Human error: ONE missed escape in a 50,000 line codebase = vulnerability. Good luck never making that mistake.
• Can't protect identifiers: Table and column names? Escaping doesn't help there.
• Mental overhead: Every single query, every single time, you have to remember to escape.
• False confidence: Partial escaping looks like it works... until it doesn't.

Parameterization: the right way

Parameterized queries (or prepared statements - same thing) completely separate your SQL code from your data. You write your query with placeholders, then bind the data separately. The database driver handles escaping internally, correctly, every time.

• Query template: SELECT * FROM users WHERE id = ?
• Bind the value: statement.bind(1, userId)
• Database knows the parameter is ALWAYS data, never code
• No manual escaping - the driver does it right
• Works the same across all databases
• Sometimes called prepared statements, bound parameters, or query placeholders

Why I exclusively use parameterization

Every project at Šikulovi s.r.o. uses parameterized queries. Period. Here's why it's not even a debate:

• Structural separation: Code and data never mix. Ever.
• Zero manual work: No escaping to forget
• Database agnostic: Same approach for MySQL, Postgres, SQLite, whatever
• Better performance: Prepared statements get cached and reused
• Type safety: Parameters have explicit types
• Bulletproof: Even if data looks like SQL, it can't execute as SQL

How I do it in PHP

I use PDO for everything in PHP. It's database-agnostic and the API is clean. Here's what it looks like in practice:

• NEVER concatenate. prepare() and execute() always go together.
• Pro tip: Set ATTR_EMULATE_PREPARES = false for true server-side preparation

// PDO with positional parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$userId]);

// MySQLi
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);

// Named parameters (my preference)
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(["id" => $userId]);

Node.js implementations

Every decent Node.js database library supports parameterization. The syntax varies a bit, but the concept is the same.

• NEVER do this: `SELECT * FROM users WHERE id = ${userId}` - template literals are NOT parameterization

// mysql2
connection.execute("SELECT * FROM users WHERE id = ?", [userId])

// pg (PostgreSQL)
client.query("SELECT * FROM users WHERE id = $1", [userId])

// better-sqlite3
db.prepare("SELECT * FROM users WHERE id = ?").get(userId)

// Sequelize ORM
User.findOne({ where: { id: userId } })

// Prisma ORM
prisma.user.findUnique({ where: { id: userId } })

Python examples

Python's DB-API 2.0 spec standardizes this across libraries. The %s placeholder threw me off at first - it's NOT string formatting.

• That %s is a parameter placeholder, NOT Python string formatting. Don't confuse them.

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

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

# sqlite3
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# SQLAlchemy
session.execute(text("SELECT * FROM users WHERE id = :id"), {"id": user_id})

# Django ORM
User.objects.get(id=user_id)

Java implementations

Java's JDBC has solid PreparedStatement support. Most frameworks build on top of this.

• The type-specific setters (setInt, setString) add another layer of safety

// JDBC PreparedStatement
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?");
ps.setInt(1, userId);

// JPA/Hibernate
entityManager.createQuery("SELECT u FROM User u WHERE u.id = :id")
  .setParameter("id", userId)

// Spring Data JPA
userRepository.findById(userId)

// jOOQ
dsl.selectFrom(USERS).where(USERS.ID.eq(userId))

The stuff you can't parameterize

Here's something that trips people up: you can only parameterize DATA values, not structural SQL elements. This requires different handling.

• Table names: SELECT * FROM ? doesn't work
• Column names: SELECT ? FROM users doesn't work
• SQL keywords: Can't parameterize ASC/DESC
• Solution: Allowlists (whitelists)
• Example: if (allowedTables.includes(tableName)) { query += tableName; }
• NEVER accept raw user input for table or column names. Never.

My allowlist pattern

When I need dynamic table names, column names, or SQL keywords, I use allowlists. Validate against known-good values, reject everything else.

• Define what's allowed: const allowedColumns = ["name", "email", "created_at"]
• Validate: if (!allowedColumns.includes(sortColumn)) throw new Error("Invalid column")
• In TypeScript, I use enums for compile-time safety
• Never build identifiers from user input directly
• Ask yourself: do I actually need dynamic SQL? Usually the answer is no.
• Log invalid input attempts - someone might be probing your app

Mistakes I still catch in code reviews

Even with parameterized queries, people make mistakes. I see these constantly:

• Concatenating THEN parameterizing: query = "SELECT * FROM " + table + " WHERE id = ?" - the table is still vulnerable!
• Using f-strings instead of binding: f"SELECT * FROM users WHERE id = {user_id}" - that's just concatenation with extra steps
• Forgetting LIKE patterns: Use "name LIKE ?" with "%" + searchTerm + "%" as the bound value
• Trusting internal data: Data from your own database still needs parameterizing if it goes into another query
• Screwing up IN clauses: Use repeated placeholders, not string joining
• Disabling prepared statement emulation without testing: Can break things

Building IN clauses safely

IN clauses are tricky. You need as many placeholders as you have values. Here's how I handle them:

• WRONG: "SELECT * FROM users WHERE id IN (" + ids.join(",") + ")"
• PHP: $placeholders = implode(",", array_fill(0, count($ids), "?")); "SELECT * FROM users WHERE id IN ($placeholders)"
• JS: const placeholders = ids.map(() => "?").join(","); `SELECT * FROM users WHERE id IN (${placeholders})`
• Then bind all values: statement.execute(ids)
• Most ORMs handle this automatically: whereIn("id", ids)
• Watch out for database limits on IN clause size

ORMs: mostly safe, with caveats

ORMs use parameterization internally, so they're generally safe. But you can still shoot yourself in the foot.

• Safe: User.find({ where: { id: userId } })
• Vulnerable: sequelize.query("SELECT * FROM users WHERE id = " + id) - raw queries bypass ORM protection
• If you need raw queries, use the ORM's parameterization: sequelize.query("SELECT * FROM users WHERE id = ?", [id])
• Dynamic column selection can be vulnerable in some ORMs
• Read your ORM's security docs
• Remember: ORMs don't protect against authorization bugs

Stored procedures aren't magic

I've had clients tell me "we use stored procedures so we're safe from SQL injection." That's not how it works.

• Stored procedures can be vulnerable if they use dynamic SQL internally
• EXECUTE IMMEDIATE with concatenation? Still vulnerable.
• sp_executesql with concatenation? Still vulnerable.
• Stored procedure parameters still need validation
• They add complexity without guaranteed security benefits
• My recommendation: Use parameterized queries. Stored procedures are optional.

Defense in depth

Parameterization should be your primary defense. But I never rely on just one layer. Security is about multiple barriers.

• Least privilege: Your app's database user shouldn't have DROP TABLE permissions
• Input validation: Reject garbage before it even gets to the database layer
• WAF: Can catch common attack patterns at the network level
• Some databases let you enforce parameterization
• Monitor and alert: Log suspicious query patterns
• Regular audits: Code review plus pen testing

How I test for SQL injection

Trust but verify. Every project at Šikulovi s.r.o. gets SQL injection testing.

• Manual testing: Try payloads like ' OR 1=1--, " OR ""=", UNION SELECT
• Automated tools: SQLMap, Burp Suite, OWASP ZAP
• Code review: Search for string concatenation near query building
• Static analysis: Linters and SAST tools catch concatenation patterns
• Unit tests: Verify queries handle special characters correctly
• External pen tests: For critical apps, bring in professionals

Migrating legacy code

Got a codebase full of mysqli_real_escape_string? I've been there. Here's how I migrate:

• Audit: Find every database query in the codebase
• Prioritize: Start with authentication and sensitive data queries
• Refactor incrementally: One query at a time, with tests
• grep for: mysqli_real_escape_string, addslashes, string concatenation with SELECT/INSERT/UPDATE
• Test with special characters after each migration
• Update coding standards: Make parameterization mandatory in code review

The bottom line

The escaping vs parameterization debate is over. Parameterization wins. It's more reliable, more maintainable, and every security organization recommends it. I don't even consider escaping for new projects anymore.

If you're still using escaping, start migrating. If you're building something new, use parameterized queries from day one. Future you (and your users' data) will thank you.