In the last few years I’ve learned a lot in this area and I thought I’d share my knowledge with the world. I realise there are other, possibly better techniques than the ones I list here, and I’d love to hear about them in the comments.
1 – Lather, rinse, repeat
Rule one - Sanitise your inputs. This is probably the most important thing you can do! Anywhere text from a form is used in an SQL query, your database is vulnerable, this includes Log in forms, search boxes, even some contact forms (assuming you save the message to a database as a backup). It is vital that you ensure the text being entered by users cannot have an adverse affect on your database, to do this you must sanitise the text before adding it to your query.
Most programming languages have built in functions to take care of input sanitisation, always use a purpose built function whenever it’s available – I’ve seen a few PHP scripts that use function stripslahes() to sanitise inputs, while this will deal with some if not most forms of SQL injection, it is no where near as good as mysql_real_escape_string(), which is unsurprising really, as mysql_real_escape_string() is part of the PHP MySQL API, and has the sole purpose of sanitising your input.
2 – The doorman
You may think that just by using the purpose build sanitise function that you’re safe, unfortunately that’s not the case. Cunning use of character encoding and other tricks can get round those functions, so another technique that can be deployed is the banned words list. This will filter suspect SQL keywords such as ‘SELECT’, ‘DROP’, ‘INSERT’, ‘DELETE’, ‘–’, ‘;’ etc from the users input.
Unfortunately there will be times where the user quite innocently needed to use the word drop, so just filtering these words out wont do. A good alternative is to splice an HTML zero width space (​) into the middle of the word, so SELECT becomes SEL​ECT. When viewed in a browser it still appears as just SELECT.
For something like a user name or email address, this will not do. These are covered in point 4 – The right tool for the job
3 – Size does matter
Where possible restrict the length of an input field. If you only allow usernames to be 30 characters long, truncate input to 30 characters, as this will hopefully just a potential injection attack off mid flow.
4 – The right tool for the job
For certain field types using the banned words approach described in point 2 may not work, eg: in email addresses and user names, as an email address could quite reasonably contain the word delete, and you can’t go sticking HTML codes like ​ into peoples email addresses, unless you want everything you send them to bounce back.
Instead you should used field specific validation where possible, so for email addresses use regular expressions to check it looks like a valid email address. This in itself is a huge topic, I tend to just check it’s got an ‘@’, and a valid domain name directly after the @. You can even go one step further and check the associated mail server to check the email address in in fact real.
For usernames, simply don’t allow users to create user names using any of the banned words in the first place. Of course if it’s too late to do that, use what ever rules you can, eg: no spaces, min and max length, etc.
5 – Counting chickens after they hatch
Most injection attacks will be with the aim of gaining entry to a closed system, as opposed to just trying to wipe your database clean (although those attacks will happen as well). When trying to gain entry with an injection, a SELECT * is usually used. This means your log in script will return all the users, as opposed to just one authenticating user. A lot of systems just check to see if a user was found when running log in script, and if so let them in. By simply checking that the number of matching users is exactly 1, you can prevent some attacks that may have gotten through the first layers of defence.
6- The double check
This is my personal favourite, because it’s so simple, but so effective. Like number 5 it only protects against people trying to use SQL injection against your log in form to gain access, but I can’t see anyway around it (challenge extended…)
Lets assume an attacker constructed an injection that got past the first 4 layers of defence, they even stuck a LIMIT 1 on the end and foiled step 5. It should be safe to say their injection went in the user name or email field, as unless you store passwords as plain text, the passwords hashing algorithm will render injection attacks useless.
So this SQL injection made it into your data base and returned a user as authenticated, even thought the attacker didn’t have the username or password. All you need to do is check the username they entered, against the user name returned from the database, if they don’t match, don’t let them in.
For example, the user returned from the injected query has the user name of “mattbearman”, but the attacker doesn’t know that, or the password for that matter, so they will have entered something along the lines of “x’; SELECT * FROM users LIMIT 1; –”. Obviously this doesn’t match “mattbearman”, so something clearly isn’t right and your system knows not to grant them access.
So those are my tips, they may be a bit over the top depending on how high profile your site is, but I hope they can help some people. I’d love to hear some other techniques as well.