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.

Foreign Keys - a quick recap.

What are foreign keys for? A foreign key is used to allow data in one table to be checked against another (reference) table.

A foreign key will also prevent the referenced (master) table from being dropped. Consider this code snippet:

IF EXISTS (SELECT object_id(N'dbo.mst'))

DROP TABLE dbo.mst

CREATE TABLE dbo.mst (

id INT IDENTITY(1,1) PRIMARY KEY

,txt varchar(10))

INSERT dbo.mst (txt) VALUES ('foo'),('bar')

CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY

,name varchar(100)

,mstid INT)

ALTER TABLE [dbo].child  WITH CHECK ADD  CONSTRAINT [FK_CHILD_SINGLE] FOREIGN KEY(mstid)

REFERENCES [dbo].mst ([id])

This code works fine once, as dbo.mst does not exist, so the drop table is not run. However once the check constraint is in place, the run will fail:

Msg 3726, Level 16, State 1, Line 2

Could not drop object 'dbo.mst' because it is referenced by a FOREIGN KEY constraint.

Msg 2714, Level 16, State 6, Line 3

There is already an object named 'mst' in the database.

Foreign key behaviour

Having created the child table, let’s put some data in it:

INSERT dbo.child

        ( name, mstid )

    VALUES

        ( 'Barney', 1 )

(1 row(s) affected)

Great! We can verify that the data has been inserted by selecting records from the child table.

Now,  what if we try to insert a record where the mstid doesn’t exist in the mst table?

INSERT dbo.child

        ( name, mstid )

    VALUES

        ( 'Fred', 0 )

We would not expect this to work, and sure enough:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CHILD_SINGLE". The conflict occurred in database "Test_DB", table "dbo.mst", column 'id'.

The statement has been terminated.

Now, lets say we have the situation where we would like to have a foreign key on a non-mandatory value.

INSERT dbo.child

        ( name, mstid )

    VALUES

        ( 'Wilma', null )

(1 row(s) affected)

So if the field is null, then it isn’t considered to break the foreign key.

Thus, a Foreign Key can be summarised as a check on whether a value that exists is actually part of the referenced set.

What about composite foreign key behaviour?

A quick search of Books Online produces the following (from http://msdn.microsoft.com/en-us/library/ms175464.aspx) :

“A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.”

So, that’s an interesting nugget. Let’s test this. A different table structure is called for:

IF EXISTS (SELECT object_id(N'dbo.child'))

DROP TABLE dbo.child

IF EXISTS (SELECT object_id(N'dbo.mst'))

DROP TABLE dbo.mst

CREATE TABLE dbo.mst (

id INT

,idtwo INT

,txt varchar(10)

,Primary key (id, idtwo)

)

CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY

,name varchar(100)

,mstid INT

,mstidtwo INT

)

ALTER TABLE [dbo].child  WITH CHECK ADD  CONSTRAINT [FK_CHILD_DOUBLE] FOREIGN KEY(mstid,mstidtwo)

REFERENCES [dbo].mst ([id],idtwo)

And some data:

INSERT dbo.mst (id,idtwo,txt) VALUES (1,1,'foo'),(1,2,'bar')

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test both valid', 1, 1 )

         

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test not valid', 1, 3 ) 

         

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test one valid other null', 1, null )        

         

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test both null', null, null )  

INSERT dbo.child

        ( name, mstid, mstidtwo )

    VALUES

        ( 'test one valid other null 2', null, 2 )                    

So, what results?

SELECT * FROM dbo.mst

id	idtwo	txt
1	1	foo
1	2	bar

SELECT * FROM dbo.child

id	name	mstid	mstidtwo
1	test both valid	1	1
3	test one valid other null	1	NULL
4	test both null	NULL	NULL
5	test one valid other null 2	NULL	2

That’s a little odd..  The only record that failed was the one that had known wrong (as opposed to null) values.

This is the natural extension of the single-field version, but still a bit of a surprise, and one that could trip you up if you didn’t expect it.

What about if we follow the advice from Books Online, and make the columns non-nullable?

Well, the first thing to note, is that it isn’t the columns in mst that must be non-nullable, it’s the columns in the child table. This is key (if you want to prove it to yourself, try altering the above code to match!)

So, if we change the child table to be

CREATE TABLE dbo.child (id INT IDENTITY(1,1) PRIMARY KEY

,name varchar(100)

,mstid INT NOT NULL

,mstidtwo INT NOT NULL

)

And then re-run the insert statements from above, what do we get?

As might be predicted, the output looks like :

 

id	name	mstid	mstidtwo
1	test both valid	1	1

However, let's look at the messages returned:

(2 row(s) affected)

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 11

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CHILD_DOUBLE". The conflict occurred in database "Test_DB", table "dbo.mst".

The statement has been terminated.

Msg 515, Level 16, State 2, Line 19

Cannot insert the value NULL into column 'mstidtwo', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Msg 515, Level 16, State 2, Line 27

Cannot insert the value NULL into column 'mstid', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Msg 515, Level 16, State 2, Line 34

Cannot insert the value NULL into column 'mstid', table 'Test_DB.dbo.child'; column does not allow nulls. INSERT fails.

The statement has been terminated.

(2 row(s) affected)

(1 row(s) affected)

We note that the insert wasn’t refused by the Foreign Key if there were nulls in the table – the ‘nullability’ of the column takes precident over the Foreign Key. This makes sense as it’s quicker to validate when only looking at one table, but still a point of note.

This means our conclusion of “a Foreign Key can be summarised as a check on whether a value that exists is actually part of the referenced set” is still true, but it’s worth being aware of where the model can break down if you allow null values in the set.

My first SQLBits - how I got on, and why you should give it a go

I'm a Database Developer (primarily using Microsoft SQL) in my day job, and have been going along to the SQL South West User Group since it was formed back in April. Following some gentle encouragement from a colleague, and a discussion at the group, I thought that this "SQLBits" stuff seemed like something worth investigating - as previously I'd thought (wrongly) that SQL Conferences were all held in America, which is a bit far for me to go for a day trip.

Going to the SQL Bits website, I looked at some of the previous sessions (which are available as videos online - a great training resource in themselves), and the Saturday was listed as a "Community Day" (i.e. free!), I thought I would give it a try. I have to say my employer was very supportive - and even helped with my costs in terms of accommodation etc.

So it was that when the Friday before the conference rolled round, I found myself driving up from the west country to Liverpool, with a mounting sense of anticipation about the conference, and a mounting sense of frustration at Friday afternoon traffic. I don't think this was helped by the forecast of the hottest October day on record for the Saturday, with the accompanying rush to "get away for the weekend"!

I was pleasantly surprised by how many SQL Server users there are who will get up and be at a conference by 8am on a Saturday. Apparently, it simply requires the offer of a good breakfast - and an urn of coffee. It was great to see people engaging and chatting as they awoke - and the speakers and helpers engaging with the rest of us in a helpful and friendly manner.

So on to the sessions. With six sessions running concurrently, spread over 7 time slots, there was a lot of option as to what to see. Clearly, as these were spread over 5 subject areas (BI, DBA, Denali, Dev, and Sponsor), there were going to be clashes of what people wanted to see - in my case I'll be watching at least two sessions (which I couldn't attend due to a clash) on the SQLBits website as soon as the videos go up. I may also "revise" some of the bits where I can't quite read my notes from the sessions!

The sessions are all listed with a "difficulty rating" which follows the same pattern as the standard levels that Microsoft uses for training sessions (100 being not very technical, up to 400 which is really very detailed) - so you can pick a more or less technical session depending upon your ability and awakeness levels. The agenda is published online before you sign up, so you know what you are going to attend, and can discuss with colleagues before you go. I chose to start with a level 200 session on Database Unit Testing in Visual Studio 2010.  The talk was very helpful and identified several areas where I can incorporate changes into my daily work. It's worth noting that whilst this was a level 200 session, there was a question from the audience which highlighted a feature which couldn't be used via the default use of the tool, but another audience member was able to identify how the problem could be solved - at a slightly more technical content level. This shows that the speakers aren't afraid to tailor the sessions should the audience be interested in knowing a bit more detail.

The breaks gave me an opportunity (after a quick coffee) to engage with some of the events sponsors. It's great to put a face to the names of the organisations upon whose tools you rely every day, and to find others who you can use in the future. Of course, there were a number of times that specific tools were mentioned in the sessions - but this was always on merit, and often were completely unrelated to the sponsors.

I was pleasantly surprised that I found myself talking to all sorts of people - some of which I'd heard of from blogs and community websites, and some of whom were first time attendees like myself. It's great to get a sense of involvement in the community, and to realise that these people are so down to earth. It also means that you worry less about asking "silly" questions online if you've at least met the people who answer you!

I saw a selection of talks from across the variety of "specialisms" - it was great to be able to pick on a talk-by-talk basis which I was attending next. Generally the rooms were sufficiently sized that there was enough space to go round - although some of the sessions clearly appealed to more people than the organisers expected. I only missed one session - I'd gotten talking to a vendor, and quite forgotten the time - which was unfortunate, but resulted in a somewhat longer lunch break than expected! This wasn't a problem, and it was actually nice to be able to take a bit of a break as the day can get quite intense if you engage in as much as you can.

The only slight niggle came from the skies - the aforementioned weather. Whilst most rooms were well ventilated, there were a couple of rooms which were quite warm - those of you who attended the sessions in the "boardroom" will know what I mean! Unfortunately there's little which the organisers could have done to foresee this - and I really don't regard this as a problem.

All in all, I really enjoyed my first SQLBits conference, and I'll be looking forward to the next one. In the mean time, I'm going to continue to engage with my local User Group - you can find yours at http://www.sqlserverfaq.com/.

If you're thinking of attending your first SQLBits conference, and you have any questions, do get in touch - either with me or the SQLBits organisers directly - they're very friendly!

Dave.