What is SQL injection
SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application (like queries). It happens from using poorly designed query language interpreters. SQL Injection Attack, abbreviated SQLIA, is a web attacking vector.
Incorrectly filtered escape characters: occurs when user input is not filtered for escape characters and is then passed into an SQL statement.
SELECT * FROM users WHERE name = <username_variable>
user enters: " OR "1"="1 for username_variableSELECT * FROM users WHERE name = "" OR "1"="1"this cause to force the selection of a valid username because the evaluation of "1"="1" is always true
Incorrect type handling: occurs when a user supplied field is not strongly typed or is not checked for type constraints.
SELECT * FROM userinfo WHERE id = <id_variable> id_variable must be integer but not checked properly, so the user can enter a string
user enters: 1;DROP TABLE usersSELECT * FROM userinfo WHERE id=1;DROP TABLE userswill delete the "users" table from the database
How can we prevent SQL injection
1) Escaping: to escape characters that have a special meaning in SQL.
These charcters are ", AND, OR, WHERE, =, LIKE, ... .
Many functions exist for this purpose including HtmlEncode() in ASP.Net and mysql_real_escape_string() in PHP.
2) Limiting max size: number of characters allowed for variable input
3) Using two levels for checking: for example you can use one query for checking the username variable entered by user
then if there was a matching record, check the password in the matched record with the password variable entered by user.
SELECT * from table1 WHERE userName = <username_variable>
If there was a matched record:
If table1.userPassword = <userpassword_variable> then
continue program ...
It is obvious that use of multiple techniques is more secure than using only one of them