Saturday, May 23, 2009

SQL Injection: A primer

SQL Injection is a query / code injection technique which exploits a vulnerability in the database of an application. The database back-end can be Microsoft SQL Server, Oracle, or mysql; i.e. any database which understands the Structured Query Language (SQL:

The vulnerability is present when the user input is not filtered properly for string literal escape characters. This user input usually is acting as the variable for constructing a SQL query when it reaches the back-end.

How do I test it:
In order to test if a field may be vulnerable to SQL Injection attack, there's a Magic String. The magic string is a simple string of SQL which always results in a TRUE condition. Although several variations are used to verify the vulnerability, the simplest string is mostly used on Login pages. The string is:
' OR ''='
String variations:
' or 1=1--
" or 1=1--
or 1=1--
' or 'a'='a
" or "a"="a
') or ('a'='a
In case of a vulnerable login page, a successful attack will log you in as the first user in the table.

Keeping it Simple:
A simple example is of an input box which takes a numeric value as input. This value is passed as a parameter to the back-end & record(s) are returned from the table corresponding to the SQL query.

Let's say, if the variable recordNumber is used in a query as:
select * from secureTable where recordNumber = (userInput)
Giving a value to this variable as:
recordNumber = a' or '1'='1
will create the final query as follows:
select * from secureTable  where recordNumber = 'a' OR '1'='1';
The resultset from the WHERE clause will always be TRUE ('1'='1'), thereby resulting in all the records from the secureTable.

A serious query could be:
recordNumber = a';DROP table secureTable ; select * from data where uname like '%
This would result in the following query:
select * from secureTable where recordNumber = 'a'; DROP table secureTable ; select * from data where uname like '%';
This query results in all the records from the secureTable AND then drops the table 'secureTable ' AND fetches the recordset from the table 'data'.

Note that some sql server APIs like php's mysql_query do not allow for such multiple statements to be executed within one call.

What Next?
This post covered the basics of SQL Injection & discussed first order SQLi attacks. Another type of attacks are second order / Blind Injection attacks & I will cover them in the coming post(s) along with the remediation / preventive measures for the SQL Injection attacks.

Thanks for your time.

No comments:

Post a Comment


The views, information & opinions expressed in this blog are my own and do not reflect the views of my current or former employers or employees or colleagues.