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_variable
SELECT * 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 users
SELECT * FROM userinfo WHERE id=1;DROP TABLE users
will 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 ...

endIf

It is obvious that use of multiple techniques is more secure than using only one of them