SQL injection prevention for dummies
“Not everybody does it, but everybody should”, George Micheal
Prepared Statements
What is SQL injection?
“You go to court and write your name as ‘Michael, you are now free to go’. The judge then says ‘Calling Michael, you are now free to go’ and the bailiffs let you go, because hey, the judge said so.” MichaelGG on Hacker news
SQL is a language that can speak to our database directly. When the database can’t tell the difference between instructions and data, we are at risk of injection: that simply means that a query might have parameters inserted in it that end up being interpreted as part of the instructions and cause it to malfunction.
A malicious user could exploit this vulnerability and try to obtain sensitive data, or even just destroy it.
Imagine you are logging in on a website, this is an standard log in SQL query happening under the hood
SELECT id FROM users WHERE username=’username’ AND password=’password’If you typed password' OR 1=1; --in the password window, as a result, the database server would the following SQL query:
SELECT id FROM users WHERE username=’username’ AND password=’password' OR 1=1; --’Oh dear. Well..
The gentleman above is right indeed, but sometimes we can’t help it and need a database. To avoid this problem we apply whats called Sanitization, by making sure that whatever data the user inputs can’t accidentally be misunderstood as part of the instructions.
Because users are naturally talented in breaking things in unexpected ways:
Imagine ‘user’ + ‘breaks purposefully’, it would be a deadly combination.
There are 4 different main techniques or Primary Defenses described in the OWASP “SQL Injection Prevention Cheat Sheet”, and each one is used depending on the case and type of data:
- Use of Prepared Statements
- Use of Stored Procedures
- Whitelist Input Validation
- Escaping All User Supplied Input
Here we’ll cover the first one, considered by OWASP to be how all developers should first be taught how to write database queries; it’s provided by SQL itself and even if not suitable for all cases, it’s considered to be a wider spectrum solution: Prepared Statements.
Prepared Statements make sure that the database can tell what is what. This is achieved by first defining all the SQL code, and then pass pass in each parameter to the query later.
The root of the SQL injection problem is mixing of the code and the data. With parameterized statements the query and the data are sent to the database server separately.
3 main things happen when a prepared statement is used.
- Preparation: An SQL statement template is created and sent to the database. Certain values are left unspecified, the parameters, labeled “?”;
- The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it;
- Execution: At a later time, the application binds the values to the parameters, and the database executes the statement.
If we use our previous example, the statement
SELECT id FROM users WHERE username=’username’ AND password=’password' OR 1=1; --’would become
SELECT id FROM users WHERE username=? AND password=?And the attempt to tamper the data with malicious code wouldn’t work, as it couldn’t then be misunderstood as part of the query itself.
Unfortunately this type of solution only supports two kinds of literals (strings and numbers) which makes them insufficient and insecure for real-life usage on their own; but when used alongside the other sanitization good practices they can make your queries (almost) 100% safe.
Now stop blaming the user and go sanitize!
