January 3, 2010 How to avoid SQL Injection attacks?
What is SQL Injection?
SQL injection is an attack to the database of dynamic websites and not only. A very pervasive and unsecured database can be exposed to these attacks on the application level.
A simple example of this kind of an attack is the login form. One can inject an SQL code that can alter data in the database or extract private and precious information.
If we try to log on to an account with the username "vasile", but instead of "vasile" we write "vasile';DROP TABLE users; SELECT * FROM data WHERE 't' = 't", how will the SQL command form itself in the database?
SELECT * FROM users WHERE name = 'vasile';DROP TABLE utilizatori; SELECT * FROM DATA WHERE 'x' = 'x';
The consequence? Where is that Users table? Inevitably the answer is: Gone!
How do we protect the database?
- It is advisable to use users with limited privileges. We should not connect as super-users or any other user that can manipulate more databases that the one we are interested in.
- You should user validation functions for forms, from the most simple type of validation, like variables, to regular expressions.
- For the numeric input we should call/use the function "is numeric()".
- For number type values we can use the "string escape" function, as it is used in a certain SGBD.
- You must not display information that is typical for the database, like the scheme.
- You should avoid the possibility of introducing characters like: colon, semi-colon, slash, backslash, dot or comma, characters like NULL or new line etc. in any of the input strings for users, URL parameters and cookie values.
