CrunchGear

First off there is no SQL Server 2003. Its either SQL 2000 or SQL 2005.

Second - change your web app to call a SQL stored procedure rather than dynamically creating a query. Simple, effective and clean.

Third - why are you still running Windows 2000? If you must run your web app on Windows at least upgrade to Windows Server 2003 SP2 and make sure its patched.

John,

Just saying “use stored procedures” isn’t the answer. The problem occurs when you concatenate _any_ user-entered data into a string that you send off to the database server. This can happen when you use stored procedures just as easily as when you use direct queries. You can do something like

// BAD
cmd.CommandText = “exec MyProcedure ‘” + UserName.Value + “‘”;

and still have a problem. The solution is to never concatenate together a string and send it along. So, if you are not sending along arguments, using a text command type is safe from SQL injection.

// GOOD
cmd.CommandText = “SELECT * FROM MyTable”;

If you are sending along arguments, though, it is recommended to use the stored procedure command type and pass arguments along using the parameters options. So, instead of the first example above, do something like this:

// BEST
cmd.CommandText = “MyProcedure”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(”@UserName”, UserName.Value);

The examples are in C#, but the general idea is the same in all .NET languages. ;-) The parameters are automatically scrubbed and cannot be used to inject unwanted SQL into the command.

Hope this helps. Good luck!

A few more steps beyond the “Best” solution -

1. Type your parameters - i.e most SQL injection attacks happen because the code written on the server side isn’t validating input. In other words, if you have a number, but are passing it into SQL as a string, someone could put injection code in there; However, if you type your parameter as an int, if someone tries sticking text in there it will not get passed to SQL.

2. All of these solutions are well and good - so long as you are the programmer- if you use open source, however, you are at the mercy of the people that coded it. If this is the case two suggestions:

1. Many Open Source programs let you specify prefixes for your tables - go ahead and specify something unique as a prefix. while this might not prevent injection attacks, it will serve to lessen the impact (i.e. if the attack is looking for a table called users and your table is cg_users, the SQL won’t work)

2. Another option, if you know which tables potentially can be injected with malicious code, you can run a nightly process to scan their data and flag suspect records.

HTH

Make sure you are escaping your database input.
Also do not allow “CAST(” to be used.

Here’s a snipplet of what I use with ASP.
Make some type of function from it and check all user submitted inputs.

‘SQL Injection specific
tmpStr = lcase(INPUT_FROM_USER)
If instr(1, tmpStr, “nvarchar”) > 0 Then bEject = True
If instr(1, tmpStr, “varchar”) > 0 Then bEject = True
If instr(1, tmpStr, “cast(”) > 0 Then bEject = True
If instr(1, tmpStr, “cast (”) > 0 Then bEject = True
If instr(1, tmpStr, “cast%20(”) > 0 Then bEject = True
If instr(1, tmpStr, “(0x”) > 0 Then bEject = True
If instr(1, tmpStr, “drop table”) > 0 Then bEject = True
If instr(1, tmpStr, “exec(”) > 0 Then bEject = True
If instr(1, tmpStr, “exec (”) > 0 Then bEject = True
If instr(1, tmpStr, “exec%20(”) > 0 Then bEject = True
If instr(1, tmpStr, “;–”) > 0 Then bEject = True
If bEject = True Then ShowErrorMessage “SQL Server refused to accept your input.”

Doing instr and stuff is *not* suffient. “exec(” may be encoded as “Exec%28″, or “%45xec(” (or some 100 other combinations).

Checking user input for the single qoute (if your SQL statement is using single qoute) is the most efficient if you don’t want to do it by Parameters.AddWithValue(..). This code should look something like (replace ‘ with ”)

SQL = “select * from table where name=’” + replace(i_UserInput, “‘”, “””) + “‘”

Preferrably, use URLDecode on i_UserInput before passing it to the SQL.

Or, do a reversed check - remove all characters not matching (eg, allow only “a-z, 0-9″)

/Tomas

Tomas,
The problem is these latest SQL injection attacks are not using any single quotes, it used an encoded string with Cast. So then the best method might be to attempt to decode, check for single quotes and also check for cast.
The actual attack looked like this:

DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0×440045004300…7200%20AS%20NVARCHAR(4000));

I have snipped the actual attack, however there are no single quotes.

It turns out I was silly and put those “%20″’s in my code without fully testing if that type of attack was possible. After further tests, the encoded strings do not get executed by mySQL using ASP ODBC drivers so in my case my code will work just fine. I welcome you to attempt and prove me otherwise, my site is live and waiting for you.

Watching my logs I see a lot of cast + hex values coming in and are handled correctly. Today, however, I am noticing a lot of forward slash+singlequote+forward slash then a valid url. Many of these are combinged with the cast function from some IP. Does anyone know the significance of the slash, quote, slash in the url they are requesting? Thanks

I rewrote the malicious code to go through and restore the corrupted database.

http://8ways.net/sql-injection-attack-defence/

I began this discussion to discuss public available web proxies:

Which are really anonymous?

Which can unblock facebook, myspace etc, in other words: are fresh ?

Which can you recommend?

Thanks for your help,
Dschibut

P.S.: In my land, the freedom of speech is somehow limited, please give me a hint, if you have doubts about your recommendation.

Hi!

I would like make better my SQL capabilities.
I red that many SQL resources and still feel, that I am not a whise man
in SQL. What can you recommend?

Thanks,
Werutz

If you are using mySQL, try this site:
http://www.artfulsoftware.com/infotree/queries.php?bw=1276
Also
http://www.artfulsoftware.com/infotree/mysqlquerytree.php