SQL Injection Attacks
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.
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:
- SQL Injection Attacks – Are you safe?
- SQL Injection Attacks By Example
- SQL Injection Attacks and Some Tips on How to Prevent Them
- SQL Injection
- SQL Injection and Cross-Site Scripting