SQL injection rules - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL injection rules

Author

Topic

Rovastar
Starting Member

38 Posts

Posted - 2008-07-11 : 08:01:20

Hi there folks,

Not been here for a while as not using SQL directly atm but I could do with your expert knowledge – I know some of your have excellent queries with cool SQL tricks. :)

I am an Internet hosting admin consultant specifically with Microsoft’s IIS.

I am trying to tackle SQL injection. Now I know that web SQL injections are really a problem for developers but as a IIS admin and SQL DBA I know that we IIS people get blamed and SQL DBAs get blamed for these issues! And I want to help prevent them at the source in the query string.

Microsoft have written a new free tool for IIS called URLSCAN 3.0 (in beta atm) and it scans the query strings.
(URLscan info- http://learn.iis.net/page.aspx/473/using-urlscan/)
This is great and a positive step forward….however…. am not happy with the recommended settings.

The way it works for SQL injection is that it reject certain words in a rule.

http://blogs.iis.net/nazim/archive/2008/06/30/using-the-new-rules-configuration-in-urlscan-v3-0-beta-part-2.aspx

Now they have already taken on some of my suggestions simple things like adding convert as that might be used in an attack like cast ( actually they missed it out again in the this example…. *sigh*)

Here is their example.
[SQL Injection]
AppliesTo=.asp,.aspx
DenyDataSection=SQL Injection Strings
ScanUrl=0
ScanAllRaw=0
ScanQueryString=1
ScanHeaders=
[SQL Injection Strings]
--
%3b ; a semicolon
/*
@ ; also catches @@
char ; also catches nchar and varchar
alter
begin
cast
create
cursor
declare
delete
drop
end
exec ; also catches execute
fetch
insert
kill
open
select
sys ; also catches sysobjects and syscolumns
table
update
Now that is the background here are the questions.

a) Have omitted any words that could be dangerous that an attacker could use in an SQL injection?

My knowledge of SQL commands in not enough and the attacks now are getting complex. (apparently the list was compiled by SQL experts at MS)

Are there any new commands in SQL 2008 that emulate the functionality of these ‘dangerous’ commands? And (I know it is completely out of the scope) here but any commands also for mySQL, Oracle, etc

b) One of the biggest problems of having such generic terms is that they stop valid terms and break websites as they will reject any query string that contain these words. How can we improve on these terms?

So for example a legitimate query string with ‘podcast’ or ‘casting’ in it will be rejected because of the word CAST.

Now I am looking at getting a more detailed list that is more specific that will stop attacks. My logic here could be wrong so please let me know.

Lets take CAST example:

CAST is a function and in the form

CAST(blahblah)

So instead of rejecting the word CAST I am proposing rejecting

CAST(

Also I am thinking that you can have whitespaces after the cast and before the (

As in CAST (

And when you encode the URL a standard space will become %20 so I will reject

CAST%

(More details and thoughts in my comments on the Microsoft IIS site here: http://blogs.iis.net/nazim/archive/2008/06/30/using-the-new-rules-configuration-in-urlscan-v3-0-beta-part-2.aspx)

I hope I am not losing you here. ;)

What I want to know is there any other way that (in this example) CAST can called to run the function

e.g.

CAST[
CAST{
Etc

So I am looking at the functionality of the commands.

Lets take another example ALTER

By my logic ALTER needs to work with another command like ALTER DATABASE
And again it needs a white space to continue to be valid SQL or does it? Could you put another character in there and it still function I hope not but I am unsure

ALTER.TABLE, etc

Basically what characters does SQL ignore? Or could tricked with.

e.g. with SELECT it has been pointed out that you can use SELECT*FROM and that is valid. I hoping that * is a special case just for SELECT.

More details of an example of the conversation

http://forums.iis.net/t/1150330.aspx

And from that the next bit of logic I am trying to implement is that I am think for commands to be valid you need to have white space *before* the commands. This I am unclear about also.

Can you tell me which of the prohibited words in the list *need* whitespace before (or after) them to function in SQL?

And I thank you all for you time in reading this sorry it was so long but there was a lot of get in. I hope I explained myself enough. I am trying to get a decent solution that will help give all web/DBA admin an easier life and SQL injection is currently one of the biggest pains. ………..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-11 : 08:33:28

Drop everything you do!
Always use parametrized queries to avoid SQL injection as far as possible.

"Valter Borges" can't login with the technique above.
You can't search for "Menachem Begin" with the technique above.

E 12°55'05.25"
N 56°04'39.16"

Rovastar
Starting Member

38 Posts

Posted - 2008-07-11 : 08:46:13

quote:
Originally posted by Peso

Drop everything you do!
Always use parametrized queries to avoid SQL injection as far as possible.

"Valter Borges" can't login with the technique above.
You can't search for "Menachem Begin" with the technique above.

E 12°55'05.25"
N 56°04'39.16"

Sorry Peso maybe I didn't explain myself properly. I understand that are numerous way like javascript protection and parametrized queries which are best practice but you have to do them for every case. I know that is good practice but I want to stop as much as possible at the URL level.

I want work on a general rule on the URI string for all request to the webserver. I have several clients that are web hosters and a rule based on the URL is very useful. I server could contain hundreds of sites one rule looking at all URL requests is a lot easier to manage then going through each site by hand.

This simply offer another layer of protection so less gets through the web presentation tier to the database SQL tier. I am not saying you should not do parametrized queries or javascript you should do them as well. URL scanning is anotehr tool for the defence that is not used much yet. Some IDS/firewalls do some of this URL/ http headers but from what I have seen not very good at the moment.

Rovastar
Starting Member

38 Posts

Posted - 2008-07-11 : 08:51:14

quote:
Originally posted by Peso

"Valter Borges" can't login with the technique above.
You can't search for "Menachem Begin" with the technique above.

E 12°55'05.25"
N 56°04'39.16"

Like I explained the current recommend rules are poor. That is why I want to improve them.

there is also thoughts of having say more multiple occurrences prohibited words in a URL for it to fail.

Also in search these terms are often display like
http://www.sqlteam.com/search.aspx?cx=011171816663894899992%3Aaow51lf_dim&cof=FORID%3A9&q=%22Menachem+Begin%22&sa=Search#204

Menachem+Begin

banning certain terms like:

0BEGIN%

instead of

BEGIN

for

" BEGIN " which is one of the cases I am thinking of

%20BEGIN%20 as parsed in the URL and allowing '+begin'

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-07-11 : 09:19:54

This sort of "word checking" also fails to deal with instructions also passed as Hex...i.e. ALTER passed into you as Hex('00 05 2F 3E 2D')...(or whatever the hex values for each of the letters A,L,T,E,R are).

This approach will generate loads of "false positives"....and also let through far more hits.

The phrase "p***ing in the wind" comes to mind. Save your energy - get the developers to do things right. It might be simpler for you to put together a demo/training site...where the problem is shown and the solution displayed. Then you'll have fulfilled your responsibility.

Rovastar
Starting Member

38 Posts

Posted - 2008-07-11 : 09:35:01

quote:
Originally posted by AndrewMurphy

This sort of "word checking" also fails to deal with instructions also passed as Hex...i.e. ALTER passed into you as Hex('00 05 2F 3E 2D')...(or whatever the hex values for each of the letters A,L,T,E,R are).

This approach will generate loads of "false positives"....and also let through far more hits.

The phrase "p***ing in the wind" comes to mind. Save your energy - get the developers to do things right. It might be simpler for you to put together a demo/training site...where the problem is shown and the solution displayed. Then you'll have fulfilled your responsibility.

ASCII has to be URL encoded and multiple parsing of this will pick up the ALTER or it has to be converted via something like CAST.

Maybe I'm wrong here. Maybe you know another way.

The 'pass the buck' blame culture is exactly what I am trying to avoid! Of course I could say it is someone else's fault. I will still have to restore the damn databases because the idiot dev don't do their job properly. I know they are responsible. What if hackers shutdown the server in an attack? What if it then corrupts the database?

If the site is hacked with a XSS/SQL injection combo then the site is hacked. I want to stop this happening before I do a blamestorming session.

In the real world it is not as simple as - get the devs to fix it.

One of my clients is a dev. he is looking after a site with tens of thousands of potential pages and some of them are not that easy to manage. When you support legacy sites it is very difficult.

Or take when I do consultancy hosting for a large corp a third party dev will do one of there site and we will host it. I *expect* them to sanitize the input but what if they do not. The site is hacked. Yeah io can blame them but the corp image still gets a battering. Of course that in not my responsibility but I want to do all I can to minimise this.

Like I say it is not a replacement for good secure coding - far from it. It is to highlight certain areas and to stop it at the source. Scripted attacks generalize I want to reject them before the page has a chance to be processed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources