SQL Injection

How does SQL Injection work?
Often web developers want to retrieve, add, delete or update records in a database based on a visitors input.  Such systems include login systems whereby a user enters their username and password and this is then checked against a database, web pages that are stored in a database so they can be easily edited, or even prices lists and shopping baskets.

In these scenarios, the web developer may provide a FORM allowing users to enter text, click radio buttons, etc.  This form data is then submitted for processing and this is where the SQL injection attack takes place.

In ASP a web developer will often get the values submitted by the FORM by using the REQUEST, REQUEST.FORM or REQUEST.QUERYSTRING command.  This then returns the information entered by the user, however if this information is used unchecked in an SQL statement then the website is vunerable to SQL Injection.

A typical SQL statement might be:
conn.execute("SELECT * FROM Users WHERE (Username='Robert')")

To make this use the data from a form it may be changed to:
conn.execute("SELECT * FROM Users WHERE (Username='" & REQUEST("Username") & "')")

Now if the user enters Robert on the form page, when the code is compiled the sql statement will be:
conn.execute("SELECT * FROM Users WHERE (Username='Robert')")

NOTE: A hacker can also submit values to your processing page directly even if you use REQUEST.FORM .  This will of course bypasses any protection you think you may have by using the form e.g On your form page if you have radio buttons you define the value that is passed and you might therefore assume it will always be safe - however a hacker will submit their own values for the radio button.

 

BASIC SQL INJECTION
If the user enters ' OR 1=1 OR 'ABC'=' in the form, when the code is compiled the sql statement will be:
conn.execute("SELECT * FROM Users WHERE (Username='' OR 1=1 OR 'ABC'='')")

So records where Username='' or 1=1 or 'ABC'='' will be selected from the database, and as 1 always equals 1 then every record matches and the first record will be returned, and the code will continue to run as if the First User in the database had entered their username.

Numeric Inputs:
It is a common misconception that if you are simply selecting records using a number sql injection is not a problem, because the numeric fields do not require apostrophes around them in the sql statement as below.
conn.execute("SELECT * FROM Users WHERE (NumericField=1)

Wrong! To do sql injection the user would simply add the SQL Injection Code after the number.  e.g  inputting 1 or 1=1 will result in:
conn.execute("SELECT * FROM Users WHERE (NumericField=1 OR 1=1)")

 

MORE ADVANCED SQL INJECTION

Union
It is also possible to perform UNION commands with SQL injection - thus making the sql statement query itself or reveal vital information about the database and even your website as a whole.

Append Commands, etc...
Finally, you can also use SQL injections attacks to execute your very own SQL statement by using append commands.  Once a user has closed the existing query with an apostrophe, they can then append statements to the SQL command, including DROP TABLE, INSERT INTO, etc...
*The jet driver for Access does not allow commands to be appended, so if you are using an access database you can rest easy!

If a user enters, '"):("INSERT INTO Users (Username) VALUES('NewName'"):("SELECT * FROM Users WHERE Username='NewName then when complied the SQL statement becomes

conn.execute("SELECT * FROM Users WHERE (Username=''"):("INSERT INTO Users (Username) VALUES('NewName'"):("SELECT * FROM Users WHERE Username='NewName')")

this is now 3 sql statement that will be execute, the first will select from users where username='', the 2nd will insert a new record into users with the username=Newname and the final statement will select from users where username='NewName' - which is of course the record we created in the 2nd statement.