9. SQLi prevention
How to prevent SQLi
We can prevent most instances of SQLi using parameterized queries instead of string concatenation within the query. These parameterized queries are also known as prepared statements. The following code is vulnerable to SQLi because the user input is concatenated directly into the query:
We can rewrite this code in a way that prevents the user input from interfering with the query structure:
We can use parameterized queries for any situation where untrusted input appears as data within the query, including the WHERE clause and values in an INSERT or UPDATE statement. They can’t be used to handle untrusted input in other parts of the query, such as table or column names, or the ORDER BY clause. App functionality that places untrusted data into these parts of the query needs to take a different approach, such as:
- Whitelisting permitted input values.
- Using different logic to deliver the required behaviour.
For a parameterized query to be effective in preventing SQLi, the string that is used in the query must always be a hard-coded constant. It must never contain any variable data from any origin. We should not be tempted to decide case-by-case whether an item of data is trusted, and continue using string concatenation within the query for cases that are considered safe. It’s easy to make a mistake about the possible origin of data, or for changes in other code to taint trusted data.