cdog
Perch
With all the posts on sql injections lately thought that this might be possibly handy
http://forums.asp.net/t/1254125.aspx
http://forums.asp.net/t/1254125.aspx
Coding techniques for protecting against Sql injection
- Thread starter cdog
- Start date
antic
Perch
Hmm, I'd be careful using that example, which rejects *every* querystring and form field containing strings in it like "end", "@", "select" and other normal english words or useful symbols. Using an HttpModule like that affects *everything* across the entire site/app in potentially troublesome ways. You could really muck up your web app. Using the example as provided will stop all your email fields from being accepted as it rejects any use of "@" across the entire app, which is rather silly.
Also, that code will not protect against JavaScript injection which, oddly enough, is what the blog post that the forum post links so is talking about. JavaScript hacking was how those "500,000 web pages" got affected. It wasn't classic SQL injection that only does funky SQL things like drop tables from your db. A JS hack does stuff like deliver viruses to the browser or redirect your sensitive form submissions to other sites.
I recommend simply applying a cleanup function directly to any string variable you use in your sql statements, whether they come from the querystring/form or not. Remember the cleanup function has to eliminate HTML (script tags) as well, otherwise you're not protected against JS hacks.
Doing a Replace(sql, "'", "''") is a temporary stop-gap but doesn't stop JS hacks and malicious T-SQL code still ends up in the database which could be harmful later on.
My tips:
1. If you detect any of the following T-SQL then reject the input entirely: "@@", ";--", "/*", "*/", "sp_", "sysobjects", "syscolumns" and "exec " (none of these should interfere with legitimate inputs). If you run a tech forum, of course, this doesn't apply.
2. If you detect "<", ">", "<", "&rt;" (and their numeric equivalents) and no HTML is expected, reject the input entirely. If your app is a CMS, for example, this doesn't apply.
3. After the above, perform the usual single-quote escape Replace(sql, "'", "''") before adding the string to your inline SQL.
4. IMPORTANT: When outputting strings from your database which came from user input, always use Server.HTMLEncode. This is a common oversight but it stops all script attacks. Any malicious HTML which happens to slip through will appear harmlessly in the browser. All it will do is make your page ugly but it won't be parsed.
That will provide very good protection without breaking your app. Inline SQL is useful and legitimate (phpBB uses it a lot) but you have to be very careful with it indeed.
Also, that code will not protect against JavaScript injection which, oddly enough, is what the blog post that the forum post links so is talking about. JavaScript hacking was how those "500,000 web pages" got affected. It wasn't classic SQL injection that only does funky SQL things like drop tables from your db. A JS hack does stuff like deliver viruses to the browser or redirect your sensitive form submissions to other sites.
I recommend simply applying a cleanup function directly to any string variable you use in your sql statements, whether they come from the querystring/form or not. Remember the cleanup function has to eliminate HTML (script tags) as well, otherwise you're not protected against JS hacks.
Doing a Replace(sql, "'", "''") is a temporary stop-gap but doesn't stop JS hacks and malicious T-SQL code still ends up in the database which could be harmful later on.
My tips:
1. If you detect any of the following T-SQL then reject the input entirely: "@@", ";--", "/*", "*/", "sp_", "sysobjects", "syscolumns" and "exec " (none of these should interfere with legitimate inputs). If you run a tech forum, of course, this doesn't apply.
2. If you detect "<", ">", "<", "&rt;" (and their numeric equivalents) and no HTML is expected, reject the input entirely. If your app is a CMS, for example, this doesn't apply.
3. After the above, perform the usual single-quote escape Replace(sql, "'", "''") before adding the string to your inline SQL.
4. IMPORTANT: When outputting strings from your database which came from user input, always use Server.HTMLEncode. This is a common oversight but it stops all script attacks. Any malicious HTML which happens to slip through will appear harmlessly in the browser. All it will do is make your page ugly but it won't be parsed.
That will provide very good protection without breaking your app. Inline SQL is useful and legitimate (phpBB uses it a lot) but you have to be very careful with it indeed.
There is an update posted at ISC's diary:
http://isc.sans.org/diary.html?storyid=4610
It has link to an asp function contributed by one of the readers there to help prevent injection.
Microsoft also has a security advisory out:
http://www.microsoft.com/technet/security/advisory/954462.mspx
Both of them are a good read for those interested.
http://isc.sans.org/diary.html?storyid=4610
It has link to an asp function contributed by one of the readers there to help prevent injection.
Microsoft also has a security advisory out:
http://www.microsoft.com/technet/security/advisory/954462.mspx
Both of them are a good read for those interested.
antic
Perch
the javascript injection was being put into SQL DBs replacing data in tables with the javascript code just FYI antic
Yes, my point though is that doesn't require T_SQL injection to do that. If your app stores any user-entered data in the db and later displays it as-is, that is the exact same risk as far as javascript hacks are concerned. So looking for T_SQL code isn't enough, you have to check for HTML too.
Displaying all user-entered data with Server.HTMLEncode prevents this from occurring, as any malicious HTML never gets parsed by the browser. If the infected web apps had done that one thing, no javascript would have been able to run.
antic
Perch
To quote: "ASP is an older generation Web scripting language would require a bit more work to prevent SQL injection from happening."
This is a ridiculous statement! All languages, whether php, .NET or whatever, need to take the same precautions in filtering input from from fields and querystrings. In that article he's simply saying "use parameterized queries". That's nothing new, and it's not always practical. pbpBB uses inline HTML all over the place, and they have their own function to sanitise user input. Checking strings for unwanted content is not rocket science. He picks on ASP when it's a more recent technology than php, python, cgi etc. yet they all share the same "problem".
Don't know if you've read that post, but is any function which replaces normal English words going to be useful at all? If I type, "I don't think they'll declare war on Iran" then it's rejected because I used the word "declare". "Hey go to www.thisissilly.com" is rejected because I typed "www".
Imagine I'm using someone's "contact us" web form which uses that code. I type: "Hey, drop into my office and we'll go for an executive's lunch, somewhere with a set menu and great selection of desserts." He will receive "Hey, my office we'll for an utive's lunch, some with a menu great ion of desserts.
So that script is great for preventing T-SQL and business meetings. On top of that, it's very badly written. Here's an article from 2002 on 4GuysFromRolla, a very credible source of coding info. It's not out of date because the problem is as old as web coding itself. http://www.4guysfromrolla.com/webtech/112702-1.shtml. It explains the basics of both SQL injection and scripting attacks and provides a starting point for tackling them.
antic
Perch
Holy cow, that sounds like a major PITA.
How's this for an idea.. get someone to write you a simple IIS ISAPI filter which does the following:
1. When IIS starts up, it reads from 2 simple text files: a) a list of domain names (1 per line), and b) a list of bad strings (1 per line).
2. Each hit on those domain names makes the filter extract all occurrences of the bad strings from both querystring and form data. Things like "<", ">", "drop table" etc. Also it quote-escapes every submitted form field which has a single quote in it.
I'd write it myself for you if I knew how to make ISAPI filters. The coding would be extremely simple. All you do is add repeat offenders to the list of domain and tell them they'll be taken off it if they fix their apps.
This will save you lots of time restoring databases I'm sure. It shouldn't affect their apps in 99% of cases. But if they complain, they can go run it on someone else's server.
How's this for an idea.. get someone to write you a simple IIS ISAPI filter which does the following:
1. When IIS starts up, it reads from 2 simple text files: a) a list of domain names (1 per line), and b) a list of bad strings (1 per line).
2. Each hit on those domain names makes the filter extract all occurrences of the bad strings from both querystring and form data. Things like "<", ">", "drop table" etc. Also it quote-escapes every submitted form field which has a single quote in it.
I'd write it myself for you if I knew how to make ISAPI filters.
The coding would be extremely simple. All you do is add repeat offenders to the list of domain and tell them they'll be taken off it if they fix their apps.This will save you lots of time restoring databases I'm sure. It shouldn't affect their apps in 99% of cases. But if they complain, they can go run it on someone else's server.
problem is that ISAPI filters make logging wrong (each 1 byte shows as 1 bit, already have had problems with this one because when turning off the ISAPI it makes 8x more bandiwdth used), this is due to Hsphere ISAPI logging plugin.
In addition the strings passed to IIS are not that easily stopped unless again, you stop DECLARE, VARCHAR etc(varchar may be more adept to being blocked than DECLARE as it is not going to be used in URL patterns much in normal usage)
(each 1 byte shows as 1 bit, already have had problems with this one because when turning off the ISAPI it makes 8x more bandiwdth used), this is due to Hsphere ISAPI logging plugin.In addition the strings passed to IIS are not that easily stopped unless again, you stop DECLARE, VARCHAR etc(varchar may be more adept to being blocked than DECLARE as it is not going to be used in URL patterns much in normal usage)
antic
Perch
As long you escape all single quotes, then they can inject DECLARE and "--" all they like, it's not going to work.
If the injection is '; drop table users; --
It will turn:
SELECT * FROM users WHERE username = ''; drop table users; -- mylogin' AND password = 'mypass'
(which causes the damage)
Into:
SELECT * FROM users WHERE username = '''; drop table users; -- mylogin' AND password = 'mypass'
Note the escaped quote ('') so no damage is done since the login name being checked simply turns into '; drop table users; -- mylogin (single quote, semicolon and hyphens just become part of the string)
But yeah.. bummer about the logs. There must be another way of patching into the ASP worker process.. I believe you can do it with .NET.
Anyway, does it matter if it changes the logs? It will only alter HTTP requests for the bad-listed domains, not every request that comes in.
Just another incentive for them to fix their app.
If the injection is '; drop table users; --
It will turn:
SELECT * FROM users WHERE username = ''; drop table users; -- mylogin' AND password = 'mypass'
(which causes the damage)
Into:
SELECT * FROM users WHERE username = '''; drop table users; -- mylogin' AND password = 'mypass'
Note the escaped quote ('') so no damage is done since the login name being checked simply turns into '; drop table users; -- mylogin (single quote, semicolon and hyphens just become part of the string)
But yeah.. bummer about the logs.
There must be another way of patching into the ASP worker process.. I believe you can do it with .NET.Anyway, does it matter if it changes the logs? It will only alter HTTP requests for the bad-listed domains, not every request that comes in.
Just another incentive for them to fix their app.
As long you escape all single quotes, then they can inject DECLARE and "--" all they like, it's not going to work.
If the injection is '; drop table users; --
It will turn:
SELECT * FROM users WHERE username = ''; drop table users; -- mylogin' AND password = 'mypass'
(which causes the damage)
Into:
SELECT * FROM users WHERE username = '''; drop table users; -- mylogin' AND password = 'mypass'
Note the escaped quote ('') so no damage is done since the login name being checked simply turns into '; drop table users; -- mylogin (single quote, semicolon and hyphens just become part of the string)
But yeah.. bummer about the logs.
Anyway, does it matter if it changes the logs? It will only alter HTTP requests for the bad-listed domains, not every request that comes in.
Just another incentive for them to fix their app.
no if you applied the ISAPI, it would BENEFIT them as in reduce their logs by 8x, we'd have some people faking sql injections to get it
antic
Perch
Eh? Why would escaping single quotes in http requests reduce the log size by 8x?
And nobody would want their requests altered if they had a choice. For one thing it would stop CMS's from working properly as all <> tags would be removed from form POSTs.
oh not talking about escaping, the ISAPI would reduce it
I have been working too many hours not following ever detail of what you said I guess. I am not much of a programmer anyway.
about POSTS, the sql injections are happening just as much from GET requests in very bad coding!
cdog
Perch
If coders just accepted the fact that malicious people WILL try to break their apps and that users WILL enter bad and unexpected data into their forms and applications then wrote defensive code that allowed for these scenarios you would have to assume that hackers wouldnt always have an easy time of it.
I agree with antic, HtmlEncoding, search and replace and some regular expressions done server side should be a good starting point. I wonder how many developers actually do security testing against their code before publishing it to the web. 8)
I agree with antic, HtmlEncoding, search and replace and some regular expressions done server side should be a good starting point. I wonder how many developers actually do security testing against their code before publishing it to the web. 8)
antic
Perch
oh not talking about escaping, the ISAPI would reduce it
Ah ok.. haven't used one before so didn't know about that.
The filter would strip stuff from querystrings as well as forms, so both GET and POST requests would be made safe. Anyway if I find something useful for you guys I'll let ya know.
antic
Perch
This looks like an interesting tool from MS:
Microsoft Source Code Analyzer for SQL Injection
http://www.microsoft.com/downloads/...6E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en
Also the article Preventing SQL Injections in ASP
http://msdn.microsoft.com/en-us/library/cc676512.aspx
Microsoft Source Code Analyzer for SQL Injection
http://www.microsoft.com/downloads/...6E-A599-4FCB-9AB4-A4334146B6BA&displaylang=en
Also the article Preventing SQL Injections in ASP
http://msdn.microsoft.com/en-us/library/cc676512.aspx
nice! MS is not sitting back on this one at least, even though it is not their 'fault' at all!
antic
Perch
Here's something that doesn't immediately spring to mind.. dynamically selecting field names and escaping the square brackets used.
http://msdn.microsoft.com/en-us/magazine/cc163523.aspx
Say you are displaying a list of site members and have a dropdown list of fields by which to sort the list. The "value" attribute of the dropdown options contain the actual db field names on which to sort (ok so that's an extremely dumb thing to do, but I bet it's been done).
In that example, you not only have to escape any quotes, you have to escape square brackets [] as well, because your SQL is dynamically specifying a db object (the field name).
http://msdn.microsoft.com/en-us/magazine/cc163523.aspx
Say you are displaying a list of site members and have a dropdown list of fields by which to sort the list. The "value" attribute of the dropdown options contain the actual db field names on which to sort (ok so that's an extremely dumb thing to do, but I bet it's been done).
In that example, you not only have to escape any quotes, you have to escape square brackets [] as well, because your SQL is dynamically specifying a db object (the field name).