SQL Book

SQL Injection Attacks - are your databases secured?

Introduction

SQL Injection Attacks result when a hacker uses an interface to your database (such as a webpage form or windows form applicaiton) to execute SQL statements that can be used to delete, update or view your data. On more powerful RDBMS such as SQL Server the hacker can even execute system stored procedures for creating new system logins etc. This may mean that your data is being watched / updated without you knowing it thus comprimising your organisations security.

In this 2 part series we will demonstrate what SQL injection is, how the hacker can use it against you and how you can protect yourself by employing a number of different strategies in your database and the design of the applications that provide users with an interface to your database.

How does SQL Injection work?

Lets assume we have a web page with a login form on it that has a textbox for username and textbox for password. When the form is submitted we might have a script that creates two variables called Username and Password and assigns them the value from the textboxes on the login form. The script then dynamically builds a SQL query similar to the following:

strSQL = "SELECT * FROM t_User " & _
 " WHERE Username = '" & UserName & "' AND Password = '" & Password & "'" 

The hacker can exploit this by entering the following into the username textbox:

' OR 1=1 --

This will result in a WHERE clause that reads

WHERE UserName = '' OR 1 = 1 -- AND Password = 'whatever'

We can see where SQL Injection attack gets its name from, the hacker has 'injected' some of his own SQL code into the query. This query will now return every row in the t_User table as the OR operator means that the WHERE condition will always return true as 1 always equals 1. The requirement for a matching password has been removed as the hacker has utilised the universal SQL syntax for a line comment, the double hyphen: -- to comment out the rest of the SQL query that filters by password.

Notice how the hacker does not need to know what your tables are called or what the column names are to achieve this attack.

Oh no, he knows my table and column names

Although many SQL injection attacks occur on an applications logon scripts they can also appear anywhere else in the application. Consider a e-commerce website that has a 'Search' page that enables a customer to search the list of products in the sites product catalogue. The search form has a textbox on it that lets the customer enter the name of the product they are searching for. The code behind the form to dynamically generate the SQL query might look something similar to:

SELECT p.Title As Product, p.Description, c.Title As Category, p.NumInStock
FROM Product p INNER JOIN Category c
 ON p.CategoryID = c.CategoryID
WHERE p.Title LIKE '%" & SearchKeyphrase & "%' 

This search page might display an HTML table similar to the following for a search on 'widget':

Product Description Category No. in stock
Red Widget This widget is red Red stuff 12
Expensive Widget Solid Gold widget Over $1000 3

Armed with the knowledge of the INFORMATION_SCHEMA tables which are system tables that store the databases MetaData data such as table and column information the hacker can append the list of table names and or columns to the search query by using the UNION clause. The UNION clause appends one query resultset onto the end of the previous query resultset.

An example of this would be for the hacker to type the following into the search box:

' UNION SELECT TABLE_CATALOG, TABLE_SCHEMA, 
TABLE_NAME, 1 FROM INFORMATION_SCHEMA.Tables --

This would give the hacker the name of the database (TABLE_CATALOG), the owner of the table, i.e. dbo - (TABLE_SCHEMA) and the name of the table (TABLE_NAME). There is also a INFORMATION_SCHEMA.Columns table which the hacker can query to find information about the columns (names, data types, length etc).

Because the INFORMATION_SCHEMA tables are a SQL-92 standard it means that they are available in a large number of databases and so again the hacker doesn't need to worry about what database platform the website is using. If the hacker wanted they could also try using system tables that were specific to different database vendors, i.e. they could try targetting the sysobjects table that SQL Server uses to store information about database objects or the MSysObjects table in Microsoft Access.

This injection attack would give the hacker a returned HTML table similar to:

Product Description Category No. in stock
MyDatabase dbo Customers 1
MyDatabase dbo Products 1
      etc

Your data is compromised

Now the hacker has found out your table names etc they can perform devestating operations such as DROP TABLE or TRUNCATE TABLE. They can therefore wipe out your whole database in a matter of minutes.

They can do this by using the semi-colon which is used in SQL to mark the end of a query and enables a new query to be started on the same line.

Let us take our product search form example from above. The hacker now know the table names of the database so they can run a TRUNCATE TABLE statement by typing the following into the search keyword box:

' ; TRUNCATE TABLE Customers --

This means the search form script would dynamically create a SQL query similar to:

SELECT p.Title As Product, p.Description, c.Title As Category, p.NumInStock
FROM Product p INNER JOIN Category c
 ON p.CategoryID = c.CategoryID
WHERE p.Title LIKE ''; TRUNCATE TABLE Customers -- 

If the above happened then it could be devastating (have you backed up?) but at lease you may suspect that a SQL Injection attack was the cause of the loss of data and check your application and database to see how it could be prevented. What if the hacker does not do anything immediately obvious. They may run an update statement to change the price of a product from £1000.00 to £0.01, go their friends to order the product and then ran another update statement to change the price back again. You would never know the database had been comprimised until the accounts department complained and even then you would not be able to pinpoint SQL injection as the cause of the discrepancy.

SQL Injection is not fussy

We can see in the above examples that 5 of the main points a SQL Injection hacker can exploit are:

  • The single quote apostrophe that is used as a string delimiter
  • The line comment identifier of the double hyphen.
  • The semi-colon that is used to delimit seperate SQL queries on one line
  • The UNION operator to append data from other tables to the originally intended resultset
  • Ability to find out database settings, table names and column information by querying SQL 92 compliant tables such as INFORMATION_SCHEMA or vendor specific tables such as SQL Servers's sysobjects.

It is important to note that the first 4 of these areas of attack are common to all SQL database systems including SQL Server, Oracle, MySQL and Microsoft Access. This means that the hacker doesn't have to know what database you are using to launch an attack and so s/he sees every target as potentially hackable. Do not think just because you are using a less well known database you will be less likely to be attacked.

It is also important to note that because the attack is using aspects of the SQL language it does not matter what scripting language your web / windows application is written in. It also doesn't matter what operating system you are using, Windows, Unix etc. SQL injection will work with web pages written in PHP, ASP, ASP.NET, Perl etc just as it will work with windows / unix applications written in VB, Java, C++, Pascal etc.

Conclusion

In the first part of this series we have demonstrated what SQL Injection is and what effects it can have on our database. We have also seen how the attacks may be carried out without the knowledge of the DBA. We have also shown that the techniques used by the hacker can apply to all SQL database systems including the big names such as Oracle, DB2, Informix, SQL Server etc. In the next part of this series we will see how to defend against SQL Injection attacks.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.