SQL Injection

At the November meeting of the SQL South West user group I gave a presentation on SQL Injection attacks. My main reason for giving the presentation was that I have been surprised by the number of SQL Developers (and DBAs) who don't know what this is - or have the ability to justify why they should care about it to thier management. As it's one of the most used attacks (http://cwe.mitre.org/top25/#CWE-89) it clearly isn't as well prevented as it should be, and it can be quite powerful. It's also been around a long time - hence the famous XKCD comic:




Explots of a Mom - http://xkcd.com/327/

I aimed this talk / post at those who haven't heard of, or don't know much about SQL Injection. I'm going to run through the highlights of the talk - and you can download the PDF file.

I'm going to run through the demonstration I gave below - please do read the PDF file to get more details on the presentation itself. I've ignored the danger of code which is in the application and does the same sort of thing - but clearly this is susceptible to a few more tricks, as well as those below.

Some prerequisites - I'm using the AdventureWorksLT2008 database which is available for download.

I'm using a couple of stored procedures, which I will call from a purpose built web page. The first one is :

CREATE proc [dbo].[Concatenated] (@CustomerLastName Varchar(500))
as

/* SP to demonstrate SQL Injection Attacks - http://d-a-green.blogspot.com/  (an example of what NOT to do!)*/

insert tbl (msg)
select 'select CustomerID,Title,FirstName,MiddleName,LastName,EmailAddress,''Concatenated''
as SP from SalesLT.Customer where LastName = '''+@CustomerLastName+''''
exec ('select CustomerID,Title,FirstName,MiddleName,LastName,EmailAddress,''Concatenated''
as SP from SalesLT.Customer where LastName = '''+@CustomerLastName+'''')

This is a stylised example of something that is often used - and shouldn't be.
The second SP I created was :

ALTER proc [dbo].[Parameterised] (@CustomerLastName Varchar(500)) as
/* SP to demonstrate SQL Injection Attacks - http://d-a-green.blogspot.com/  A better solution*/
select CustomerID,
Title,FirstName,MiddleName,LastName,EmailAddress,'Parameterised' as SP
from SalesLT.Customer where LastName = @CustomerLastName

Now, clearly these aren't doing anything tricky, and as you can see they accomplish the same thing. The difference is in how they work. The first SP, 'Concatenated', "trusts" the input from the client. Whoever that may be. In normal circumstances we would expect this to be the designed application, but this assumes that nothing else is passing in the data.

Anyway, let's see the application we're calling, with a typical output:

You can see that the screen is split into two return grids - these are to show the respective outputs of the two stored procedures. Notice also that seven columns of data are returned - and these look like a table.

So, one way of indicating that SQL Injection would work, is if you get an error, or unexpected data (or lack thereof) if you put a single appostrophie in the input. So, noting that we had seven columns before, let's see what we can do.

We can type the following into the text box to show us all customer records

Harris' or 1=1 --

This could be sold to a competitor, used to embarass the company concerned, or to send targeted "phishing" emails to the users (particularly to those with recent orders).  We can also combine this with use of the
UNIONcommand to get information from other tables or views.
The -- on the end stops any further clauses executing, and prevents the closing apostrophie in the stored procedure causing an error.

We can see what tables are in the database (this can be adapted to list SPs, too):

Harris' union select 1,'a',TABLE_SCHEMA,TABLE_NAME,'a','a','a' FROM INFORMATION_SCHEMA.TABLES -- 

We can also see who can log into the server (note, I've used a name at the beginning that won't return a result so I only get the answers I want returned to me. This is more convenient!):

fHarris' union all select 1,'a',name COLLATE DATABASE_DEFAULT ,'a','a','a','a' from master..syslogins where isntname = 1 --

Clearly this application isn't now doing what it should be - it has been subverted and can do as the attacker wishes.
Other possiblities include (depending on what user the system is running as)

• Command shell - with all it's possibilities.
• Linked Server - Am I running as the same user as another machine?
• Do I have email enabled? Can I enable it? This is a much more convenient way of obtaining data.
• Can I create a trigger to use email to send me updates in the future, potentially after this method of getting in is fixed?

 I've not demonstrated it in this post, but you can also use encoded statements to get round checking for key words (delete, update, drop, etc).

Note, I've used SQL Server here, but these points are valid on most RDBMS' - you just tailor them to the environment - the errors you get or characters/methods that work can even help you to determine what the server at the other end is running, and from that what you can do.

I hope I've demonstrated what a simple thing an SQL Injection attack is, and how easy it is to prevent - note that the parameterised proc prevented these. If you must use dynamic SQL in your SPs, please use sp_executesql with parameters. Please also restrict the permissions of the web application to the bare minimum needed.

There's some more details of walk-throughs on the slides, and what some of the potential ramifications are of the attack - do take a look.