Part 5.4SQL injection

SQL injection is a major problem with the original design of SQL and web sites.

What SQL injection is

SQL injection involves an input is given by a user it changes the meaning of the SQL statement.

Assume the following statement is used:

SELECT * FROM `users` WHERE username = "input1" AND password = "input2"
		

This statement would take two inputs, input1 and input2. The first input would be a username and the second input would be a password.

Assume the user inserts a username of tester and password as test" OR password != "test.

If this password is inserted the statement becomes:

SELECT * FROM `users` WHERE username = "tester" AND password = "test" OR password != "test"
		

Now the statement means that a password can be the word test or not the word test. This will return all passwords. This would mean that the user could login with a password like that.

Protecting against SQL injection

SQL can prevent injection but only if the input is first sanitised. This is often done using escaping of characters.

PHP provides many ways of doing this, but perhaps the most powerful way to prevent this is using prepared statements.

Proof of injection

Assume the following table exists called users:

Test injection within a statement using the following form (the two initial values work perfectly for this):

The query would like:

SELECT * FROM `users` WHERE username = "" AND password = ""