Quick question for all you programmers. I’m dealing with an MS SQL injection problem on a site running Windows 2000 and MS SQL 2003. Has anyone seen/solved this problem yet?
by
John Biggs
on
June 4, 2008
Comments 
Leave Comment
Commenting Options
Enter your personal information to the left, or sign in with your Facebook account by clicking the button below.
Alternatively, you can create an avatar that will appear whenever you leave a comment on a Gravatar-enabled blog.
- Actively Discussed Posts
- Sunday Giveaway: See the world with your very own Vue camera system
959 comments - Why the CrunchPad mattered
158 comments - Monday Giveaway: Samson Go Mic
114 comments - Real time, real discussion, real reporting: choose two
66 comments - Tuesday Giveaway： MittenBerry
29 comments
- There are no posts to display.
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.
Sorry, yes MSSQL 2K. Why? Because the folks I’m helping are using it. I’m moving them to LAMP soon.
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
Hi people,
I recently registered to this forum http://www.crunchgear.com and hope that somebody can
give me an advice on the forex market – I am looking for an introduction
for noobs. I have already some knowledge about shares. (Hope this is the adequate category.)
Any help is so much appreciated. Most important question: can a noob make money on the forex market?
Thanks,
Jim
Hi, just registered today, can someone give me some suggestions on which ebooks or guides are good for newbies like me?
Hello folks, I just went akross this awesome forum through google and I like the especialy this form. I really like the design and the team does its job verry good.
I´m Andrew and I´m pleased to be here :)
Greetings
Phentrimine is used for treating female infertility.
http://forum.hackforce.ru/index.php
У нас на форуме вы можете научиться взламыватьстраницы вконтакте,однокласники, угонять icq номера и мыла,взламывать сайты,форумы,чаты и многое другое…..
Заходи к нам,общайся,учить новому и учавствуй в жизни проекта…
Форум проводит набор МОДЕРАТОРОВ,каждый из вас может стать им…..
Также у нас работает бесплатный и анонимный онлайн сниффер.по адресу http://sniffer.hackforce.ru
Прошу прошения у администратора если создал тему не в том разделе…перенесите пожалуйста в нужный раздел
Привет всем!
Пару дней назад нашли новую дыру…
Вот описание – http://alex1k.livejournal.com/317.html