My Software Notes

Useful things I discover

SQL Injection Attacks

with one comment

I’ve never had to deal with a SQL Injection attack before, mainly because in all the sites I’ve ever created I pass any input from the site to the database in parametrized form.  I have never built SQL statements on the fly by adding user supplied values in strings.

But this week a new client  received such an attack which brought down the corporate web site.  So I had to get involved and figure out what to do.

The intention of the attack was to insert a call to some JavaScript on a remote server into the html of the site, but all they managed to do was make every page fail.  They would have succeeded if it were not for the ineptitude of the web site builder.  He had the content of each page in a table with an integer id, but he used a varchar as the data type of the id.  The text that the attacker injected into all varchar fields made the ids invalid and the select statement trying to get the page failed and return a null. (What do you call that?  Double Ineptity?)

When I looked into the database I found all character fields were filled with “”.  (BTW, if you Google that string you will find it all over the place, so the incompetent programmer who created my client’s site is not alone.)

The attacker managed to inject the script because:
1. The guy who created the site put inline sql all over the place.  Here is a sample:

sql = "select * from content where id = '" & Request.QueryString("id") & "'"

 

2. He never checked any user input or any query string for dangerous content.
3. When he set up the database he gave admin privileges to the database userid the web site used.

That was his side of the story. The attackers side went like this.  They used the query string to inject a huge sql statement that they hid in a hex encoded number.

page.aspx?id=29;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44 ... 200%20AS%20NVARCHAR(4000));EXEC(@S);--

I cut out the full number because it was almost 2k digits long. This huge string of hex digits was cast into a very clever set of sql statements that opened a cursor on the sysobjects and syscolumns tables and updated every character type column by adding that script tag to it.

One clever thing about this is that all of the articles I read when I was researching what to do mentioned looking out for strings such as “select”, “drop”, “insert” and “update” but not one mentioned “declare”, “set” or “cast”.  Of course catching the “;” would have prevented the attack and most articles did mention that.

So, how were the attacks handled?

I created a function that handled the single quote issue and nuked the naughty words(like “declare”, “select”, etc.).  Then me and two other developers went through the 500+ locations in the code where the !@#%$^ who created the site had built his sql statements by concatenating strings with variables and we wrapped every variable and every Request.QueryString(“paramname”) in a call to the function.  Tedious in the extreme, but necessary.

And it worked.  I know that because they have tried the attack several times since (robots never give up) and it has not succeeded.  So when you do a Google search for that script tag you won’t find my client’s web site in the results.

So, how do you avoid SQL Injection Attacks?

  • Never build sql statements by concatenating strings and variables.
  • Use parameters (such as SQLParameter or OLEDBParameter or whatever the equivalent is in your language of choice) in your database calls.
  • Validate user input and URL query strings so you detect and reject potentially dangerous entries.
  • Limit how much a user can enter in text boxes so they can’t add on a bunch of sql statements.
  • If you are stuck with an existing site that is wide open to attack, then wrap every variable and every use of the URL query string in a call to a sanitizing method.
  • Get educated on SQL Injection Attacks.  Read the resources below and anything else you need to until you get the idea.

Some resources on SQL Injection Attacks:

Advertisements

Written by gsdwriter

July 2, 2009 at 12:21 pm

Posted in Database, Design, Web Development

Tagged with , ,

One Response

Subscribe to comments with RSS.

  1. nice info thx

    sql injection

    July 6, 2010 at 4:00 pm


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: