preventing sql injection

I’ve known for a while that one of the ways to prevent SQL injection attacks is to use parameterized queries - but I never really understood why that worked.

I found this explanation from HackEDU, a web security education firm, particularly useful / easy to grasp:

A parameterized query (also known as a prepared statement with variable binding) is a SQL query that contains a placeholder instead of the actual values provided by the user. When executed, a parameterized query is first pre-compiled, so the user input data is always interpreted as a simple string and not as part of the statement. This enables better performance and safe execution of the SQL command since data won’t be “executed”.

HackEDU expanded on this in a blog post ”How to prevent SQL Injection vulnerabilities: How Prepared Statements Work” which was extremely informative.

In the blog post, they expand on the differences between standard query execution and query execution for prepared statements. They provided these two (simplified) views to compare/contrast:

While simplified, two distinct differences are highlighted:

1. In standard execution, during the binding phase the query is converted into byte code. The same thing happens with prepared statements, however the placeholders are noted and will be replaced later.
2. This replacement happens in the extra step (inserted between cache and execution). As the query has already been compiled (and it doesn’t make sense to recompile it), the user-provided data will always be interpreted as simple strings and not part of the query.

It is by virtue of the fact that compilation occurs before the user data is inserted into the query (and is consequently treated as a string) that prepared statements are immune from SQL Injection vulnerabilities.

Other Approaches

HackEDU does touch on how prepared statements differ from other approaches for avoiding SQL Injection. The high level is:

1. Stored procedures work the same as prepared statements. The difference is that they live in the database rather than the application.
2. Allowlist is a feature that can limit what users can select, however, it’s easy to mess up the implementation.
3. Input sanitization is also an option, but similarly easy to mess up.

Bottom line: where possible, use prepared statements.