SQL Injection – What to Do?

SQL Injection – What to Do?
Lots of good responses regarding what to do in the short term for SQL Injection. Before I list some of the great triage answers today, let me make a simple reminder of how to stop SQL Injection.

The easiest answer is simple…use strongly typed parameters whenever you interact with your database. Most data access libraries support the use of parameters. Using parameters, any SQL Injection attempts are converted to data. I’m providing this short sample to be sure you understand what parameterized queries are.

Here are two short C# examples, one for inline parameterized text, and the other using a stored procedure. I have fully qualified the libraries being called in case you don’t use ADO.Net. By the way, the inline SQL with parameters method is the method used most often with Object Relational Mapping frameworks such as Entity Framework.

Examples of Values retrieved from controls in the user interface

String Notes = "A Whole Bunch of text where injection code can be entered’;DROP DATABASE @@DATABASE";
int NoteID = 23;

Inline SQL with Parameters

string CommandText = "Update SomeTable Notes = @Notes WHERE NoteID = @NoteID";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(CommandText);
cmd.CommandType = CommandType.Text;
System.Data.SqlClient.SqlParameter NoteParm = cmd.Parameters.AddWithValue("@Notes", Notes);
cmd.Parameters.Add(NoteParm);
System.Data.SqlClient.SqlParameter NoteIDParm = cmd.Parameters.AddWithValue("@NoteID", NoteID);
cmd.Parameters.Add(NoteIDParm);
cmd.ExecuteNonQuery();

Stored Procedure With Parameters

string CommandText = "usp_UpdateNotes";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(CommandText);
cmd.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter NoteParm = cmd.Parameters.AddWithValue("@Notes", Notes);
cmd.Parameters.Add(NoteParm);
System.Data.SqlClient.SqlParameter NoteIDParm = cmd.Parameters.AddWithValue("@NoteID", NoteID);
cmd.Parameters.Add(NoteIDParm);
cmd.ExecuteNonQuery();

I have some helper classes that simplify the whole process here, removing a lot of the redundancy, allowing me to use either of these methods with four lines of code.

Regardless, using these two methods of code grant you a lot of good nights of sleep. With SQL Server 2008, on a busy server, this will even let your SQL Server perform more efficiently.

Clearly there are other ways to address SQL Injection. However, those methods simply restrict how much an attacker can do once they have broken down your front door. They are all something you should do anyway, because there are other methods of attack. You’ll pick up some of those ideas from reader comments that follow.

John:
How about changing access rights on the web user to allow only read access to objects that do not need to be changed, read/write on the others and absolutely no access to create/drop etc.

David:
On your SQL injection through a stored procedure, it’s just a demonstration that any time you execute code in which some of it has come from a non-trusted source, you’re vulnerable to injection attacks. This is the whole reason why Javascript validation is only used as a convenience for “well-behaving” users, since the entire source could be altered to do whatever the user wants.

As far as what to do in this situation, that depends on the exact technologies involved and how quick of a turn-around time is desired. (And whether higher-ups will consider a temporary patch “good enough” and cancel any serious reworking, but that’s another topic.)

Ideally, I’d use the following steps:
1) Some back-end languages such as PHP have their own built-in sanitizers to prevent SQL injection on inputs (primarily because of how long it took for MySQL to get stored procs). If your language has it, wrap the user input in these as the first line of defense without needing to really refactor the code at all (find/replace or regular expressions [eg: sed] are good ideas for this, if these blocks of code are relatively uniform in construction)
2) Migrate all queries using this “dynamic stored procedure” on public-facing pages to real stored procedures first, each time removing access to the tables none of the remaining queries use anymore.
3) Migrate the logged-in pages, and remove access to the respective tables.
4) Migrate administrative pages, then kill the “dynamic stored procedure”, and to be on the safe side double-check that the database permissions don’t allow access by any means other than through stored procedures (especially if this is a large enough company where DB and programming responsibilities fall to different groups.
5) Chastise whoever came up with this idea, and whoever approved it, but also try to find out the root cause of this choice (ignorance, or something deeper, such as DBAs that make adding stored procedures for new applications difficult for the development team).
6) In a perfect world, the DBAs would make it hard to get new stored procs in, but not slow down developers, by providing the developers a development database that they can go wild in, get their code working, and then pass it off to an SQL expert to validate and approve for inclusion. This would need management support, though.

There’s no way to do such dynamic queries safely, period. Even the built-in sanitizers are subject to failure, while a stored proc will not execute on malformed data (excepting strings, but string escaping is a solved problem and nothing in the string will be treated as code [unless EXEC’d, of course]).

I was hoping this entry was something more clever, though.

There’s still the possibility that even properly designed stored procs could produce an injection attack. How? Many websites wear their underlying technology on their sleeve. (.php, .aspx, .cfm, etc) For the older, frankly crappier languages such as PHP, ColdFusion, and the original ASP (saying this as a CF developer by day), code can and sometimes is expected to reside on the presentation pages, and is dynamically interpreted.

So, someone familiar with the language could write into an innocuous, stored-proc-protected entry point some, say, PHP code that does a raw query on the database, then view what they entered and see what it actually produces. (This is especially problematic if the field allows user-generated HTML to be stored in the database, for instance.)

This is what I was expecting when I saw the beginning of this feature.

John:
You don’t say anything about the complexity of the statements being run. Do they follow a predictable pattern? What is the datatype for @SQLStatement?

CREATE PROCEDURE spUpdateTable (
@SQLStatement nvarchar(4000)
)
--Version 1 - some naive programmer. Date = ??. Original version.
--Version 2 - interested reader. Date = 20 Jun 2011. Added in checking to mitigate (somewhat) against sql injection.
set @SQLStatement = ltrim(rtrim(@SQLStatement))
--get rid of double spaces and carraige returns to make pattern matching easier
set @SQLStatement = replace(@SQLStatement, char(10), ' ')
while charindex(' ', @SQLStatement) > 0 begin
set @SQLStatement = replace(@SQLStatement, ' ', ' ')
end
--We only want to allow update statements. Based on careful analysis of sql traces, we have determined that
--the front-end app
-- * only updates one table at a time
-- * has no joins to other tables as part of the update
-- * etc.
if @SQLStatement not like 'update%' begin
select 'missing update directive: ' + @SQLStatement
return -1
end
if @SQLStatement not like 'update % set %' begin
select 'missing set directive: ' + @SQLStatement
return -1
end
--etc etc
go

This approach could go on to extract the table name and ensure that it exists. More tricky, but still doable, would be to extract the column names.

* Monitor this sql user *very closely*
* Review its permissions. Is it db_owner? (much worse) sysadmin? If so, immediately find a dev server and restrict its permissions to what it requires. Ideally to the table and column level. Realistically get it to at least db_datawriter and db_datareader
* If the user does have sysadmin privileges, then ensure its windows accounts are severely restricted. Ideally you just need a local windows user with just read/write access to sql binaries, folders for backups (see bol for full details). Again, if nothing else, ensure the sql account does not have ability to create or modify local windows accounts. Finally note that disabling xp_cmdshell is of no use whatsover as anyone with syadmin can reenable it.

There are a lot of other tips we’ll be sure to share tomorrow.

You can always send your comments, experience or issues to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured Article(s)
Five New DBA Interview Questions
It is important to keep up with the skills that you use daily when you work with SQL Server, but also the basics with features that you don’t regularly use.

Featured White Paper(s)
SharePoint Migration
Microsoft SharePoint Server 2010 is already transforming the way organizations are connecting their knowledge workers, busin… (read more)

Featured Script
Asynchronous SMTP alternative to SQLMail
Use these proceures in conjunction with the xp_smpt_sendmail extended stored procedure avaialable from www.sqldev.net to crea… (read more)