SQL Injection

[A.M]

Hi,

I have to check all textboxes in my web application for SQL injection.
Is there any ready product that detect SQL inhection patterns?
A regular expression also would be helpfull.

Any help would be apprecited,
Ali

[Joe Kaplan \(MVP - ADSI\)]

It seems to me you would want to do two things here as there are two
different problems:

Check all of your SQL code to ensure that you are using parameterized
queries
Verify that your input contains only valid input based on what is being
requested

Parameterized queries in ADO.NET will prevent SQL injection attacks. If you
are building queries by creating SQL strings on the fly, then you should
concentrate on fixing that first. You can still use parameterized queries
without stored procedures if you don't want to or can't use them.

The next thing you want to do is ensure that your input conforms to what it
should be. This will help prevent all sorts of other attacks besides SQL
injection such as Cross Site Scripting.

Validating input should be done based on what is allowed, not based on what
is not allowed, so trying to look for signs of SQL injection in your inputs
is the wrong way to go.

Regular expressions are excellent tools for validating input, but they are
"domain dependent", meaning that no one regular expression can validate any
random text. It depends on what is required.

[url]http://www.regexlib.com/[/url] is an excellent source of regular expressions,
especially for .NET.

The bottom line is that you need to carefully validate input AND make sure
your database code is not suceptible to SQL injection. You shouldn't just
do one or the other. Read "Writing Secure Code" and/or the "Code Secure"
column on MSDN for more info.

HTH,

Joe K.

"A.M" <IHateSpam@sapm123.com> wrote in message
news:OfAcxND4DHA.1428@TK2MSFTNGP12.phx.gbl...

> Hi,
>
> I have to check all textboxes in my web application for SQL injection.
> Is there any ready product that detect SQL inhection patterns?
> A regular expression also would be helpfull.
>
> Any help would be apprecited,
> Ali
>
>

[scottrm]

I am looking at writing a routine which will validate input to be used in database queries so as to protect against sql injection attacks. Obviosly I can look at removing items like single quotes however I have read that there are means of circumventing this such as by using hexadecimal or the char function to encode the query. Is there any function/technique I can use to process the query string first before I run the routines to remove single apostrophes etc. which will ensure that the validation will work ok.

[Bob Barrows]

scottrm wrote:

> I am looking at writing a routine which will validate input to be
> used in database queries so as to protect against sql injection
> attacks. Obviosly I can look at removing items like single quotes
> however I have read that there are means of circumventing this such
> as by using hexadecimal or the char function to encode the query. Is
> there any function/technique I can use to process the query
> first before I run the routines to remove single apostrophes etc.
> which will ensure that the validation will work ok.

You shouldn't be removing the apstrophes: you should be escaping them by
replacing them with two apostrophes, using the Replace function.

Better yet, you should be using stored procedures, or parameterize your
dynamic sql if you must use it.

Here are some links:
see the SQL Injection FAQ at [url]www.sqlsecurity.com[/url]

SQL Injection Attacks - Are You Safe? by Mitchell Harper - 6/17/2002
[url]http://www.webmasterbase.com/article/794[/url]
Interesting but mostly covers things that can happen with SQL Server but not
with Access as the database.

Data Sanitization - Reducing Security Holes in an ASP Web Site by Craig
Atkins
- 11/27/2002
[url]http://www.4guysfromrolla.com/webtech/112702-1.shtml[/url]
How do we Protect Against SQL Injection?
What are Regular Expressions?

There was a great white paper somebody posted a few weeks ago. I don't have
time to find it right now, perhaps if you do a Google search ...

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[[MSFT]]

Hello,

Thank you for using the community. Regarding the issue, I agree with Bob
that we should replace apstrophes to protect SQL injection attacks, not hex
encode. And I think the link he provide was very helpful on this issue, you
may browse following link:

[url]http://www.sitepoint.com/article/sql-injection-attacks-safe/5[/url]

They are general ways we can use to protect the SQL injection attacks.

Luke
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[scottrm]

Thanks for the response but maybe I have not made myself clear. My thinking is that it may be possible for an attacker to hex encode (or similar means) single apostrophes or other bad characters to avoid any validation routines such as the one described in the article and it is this I want to find a way to avoid.

[[MSFT]]

If an attack hex encode the bad characters in SQL query expression, sql
server may not recognize the expression, and report syntax error. I can't
figure out a sample with hex encode and possible injection attack. If you
have such one on hand, please let me know. We may discuss them to see how
to protect.

Thanks,

Luke
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[scottrm]

There are are couple of examples of how to avoid validatio
from the papers [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url] (Page 18 using char function) and

[url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url] (page 12 using the hex encoding method

[Bob Barrows]

scottrm wrote:

> There are are couple of examples of how to avoid validation
> from the papers
> [url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url] (Page 18
> using char function) and
>
> [url]http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf[/url] (page
> 12 using the hex encoding method)

I see nothing in this article that invalidates the principle that passing
parameters to a stored procedure using a Command object is safe (unless the
procedure is executed with a dynamic sql statement, or unless the procedure
builds its own dynamic sql statement). Sure, there's the statement that new
attacks are " ... being discovered all the time." But that statement also
applies to any defenses you can come up with for this hex attack you are
worried about. In my mind, this is much more possible than circumventing the
stored procedure parameter defense. ALL sql injection attacks depend on the
use of dynamic sql. If you do not use dynamic sql you are safe. If a way is
found to inject sql into a parameter value passed by a Parameter object,
then we may as well shut everything down.

I realize that you have a bunch of legacy code that would need to be
rewritten. However, wouldn't much of that code need to be revised anyways to
implement any defenses you come up with?

BTW, you do not have to use an explicit Command object and its Parameters
collection to be safe. You can also pass parameter values via the
"procedure-as-connection-method" technique:

'no resultset returned (INSERT, DELETE, UPDATE,etc.):
conn.procedurename parmval1,...,parmvalN

'resultset returned:
set rs=createobject("adodb.recordset")
conn.procedurename parmval1,...,parmvalN, rs

This techniques causes ADO to create a command object behind the scenes and
populate its Parameters collection. (Note: this technique is not usable in
ADO.Net)

This may reduce the pain you foresee in rewriting your legacy code.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[David C. Holley]

Try this...

[url]http://www.sitepoint.com/article/794[/url]

In addition to the technique mentioned, I also test wether or not the
value of a query parameter is all numeric if the query parameter in
question requires a numeric value.

scottrm wrote:

> I am looking at writing a routine which will validate input to be used in database queries so as to protect against sql injection attacks. Obviosly I can look at removing items like single quotes however I have read that there are means of circumventing this such as by using hexadecimal or the char function to encode the query. Is there any function/technique I can use to process the query string first before I run the routines to remove single apostrophes etc. which will ensure that the validation will work ok.
>

[mockworld]

Hello, Does ColdFusion automatically invalidate any attempts to inject SQL statements (eg., 1=1) when doing a query, or is this something we need to code for?

Steve

[blewis]

I would recommend taking a look at the CFQUERYPARAM tag, used inside CFQUERY. This can be used to prevent injection and also do field data type checking.

Bryan

[mockworld]

ok, thanks Bryan.

[Kiriran]

simple question. is it possible to do sql injections to CF pages? i tried it a little bit but seems not to work in coldfusion. just want to be sure since i'm not a pro in sql and sql commands

[mpwoodward *TMM*]

On 2005-05-03 07:09:01 -0500, "Kiriran" <webforumsuser@macromedia.com> said:

> simple question. is it possible to do sql injections to CF pages? i
> tried it a little bit but seems not to work in coldfusion. just want to
> be sure since i'm not a pro in sql and sql commands

If you mean SQL injection as in the nasty kind where people try to tack
on malicious SQL commands to what the application is supposed to be
doing, then yes this is possible. The best way to protect yourself
against this is to use cfqueryparam for all your variables that are
going to interact with your database, as well as validate the heck out
of everything both client and server-side.

Matt
--
Matt Woodward
[email]mpwoodward@gmail.com[/email]
Team Macromedia - ColdFusion

[Kiriran]

ah thanks :) well i encrypt it most times and yes use cfqueryparam so i think i'm on the save side. or at least i hope so ;)

[Kiriran]

may i ask another question. what does <cfqueryparam> exactly? beside validating the datatyp, is it to make sure nothing executes (no sql commands?)

[Jochem van Dieten - TMM]

Kiriran wrote:

> may i ask another question. what does <cfqueryparam> exactly?

It separates the query string from the variables and sends them
to the database separately. The words to Google for are "bind
variables" and "prepared statement".

> beside validating the datatyp, is it to make sure nothing executes (no sql commands?)

Correct.

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[urfriendx]

Can someone give me an example of how sql injection is done in CFMX?

I read that single quotes are automatically escaped/doubled.

let's say that the code is
select * from mytable where firstName = '#form.firstName#'

I tried to enter
aaa' ; drop table test; select 'blah
which should end up being
select * from mytable where firstName = 'aaa' ; drop table test; select
'blah'
Looks like perfect syntax.

but it automatically gets turned into:
select * from mytable where firstName = 'aaa'' ; drop table test; select
''blah'

What gives? How does a hacker get around that?

[CF_Oracle]

If passed variables of varchar type are not protected by cfqueryparam a hacker
can use for example UNION statement adding it as a part of variable that could
be interpreted as valid part of a query and select data from any uprotected by
granted permissions tables.