Paper: SQL Injection Attacks by Example

[Steve Friedl]

Hello folks (and Happy New Year),

I recently posted this to the PEN-TEST list, but it was suggested that
the wider Bugtraq readership might benefit from it.

During a recent security review for a customer, I was able to completely
compromise his web application in about two hours using SQL Injection,
logging in as the Chief Information Officer.

I've written a paper on SQL Injection Attacks, not so much as a tutorial,
but an illustrated overview showing the process (those with only a casual
knowledge of SQL have told me it's easy to understand).

Those who write (or test) web applications really ought to know about SQL
Injection attacks, because the bad guys certainly do.

SQL Injection Attacks by Example
http://www.unixwiz.net/techtips/sql-injection.html

Steve

--
Stephen J Friedl | Security Consultant | UNIX Wizard | +1 714 544-6561
www.unixwiz.net | Tustin, Calif. USA | Microsoft MVP | steve@unixwiz.net

[David Litchfield]

Hi Steve,
Nice paper. However, one small nitpick - under "Mitigations" you list using
stored procedures if the database supports them. I've seen other people
suggest this as a defensive strategy as well.

Using stored procedures will *not* protect you from SQL injection attacks.
Firstly, they can be injected into just as easily as a select statement.
Secondly, the procedure itself can be vulnerable to SQL injection. I have
seen for example, procs that use double quotes internally and single quotes
on input.

That said, stored procedures are generally faster so it's better to use them
for performance reasons, anyway.

Cheers,
David Litchfield

[Scovetta, Michael V]

David,

Actually, to nitpick your comment a bit, stored procedures usually have
typed input variables:

create procedure foo ( a int, b varchar(20) ) as ...

At least in MSSQL, you'd have to do something bad like use sp_executesql
or some other function that will re-form a complete sql query and pass
that to the interpreter. As long as you do more sensible stuff like:

insert into table (name, age) values (@b, @a)

you should be fine.

Michael Scovetta
Computer Associates
Senior Application Developer

-----Original Message-----
From: David Litchfield [mailto:davidl@ngssoftware.com]=20
Sent: Wednesday, January 05, 2005 2:20 PM
To: 'Steve Friedl'; bugtraq@securityfocus.com
Subject: RE: Paper: SQL Injection Attacks by Example

Hi Steve,=20
Nice paper. However, one small nitpick - under "Mitigations" you list
using
stored procedures if the database supports them. I've seen other people
suggest this as a defensive strategy as well.

Using stored procedures will *not* protect you from SQL injection
attacks.
Firstly, they can be injected into just as easily as a select statement.
Secondly, the procedure itself can be vulnerable to SQL injection. I
have
seen for example, procs that use double quotes internally and single
quotes
on input.

That said, stored procedures are generally faster so it's better to use
them
for performance reasons, anyway.

Cheers,
David Litchfield

[Scovetta, Michael V]

Chip--

I agree-- and for the Java junkies in the house:

ps =3D con.prepareStatement("update people set name =3D ? where nid =
=3D ?");
ps.setString(1, request.getParameter("name"));
ps.setString(2, request.getParameter("nid"));
ps.executeUpdate();

I must say, I like the Java syntax much better than the .net syntax...


Michael Scovetta
Computer Associates
Senior Application Developer
tel: +1 631 342 3139
cell: +1 813 727 5772
Michael.scovetta@ca.com=20
=20

-----Original Message-----
From: Chip Andrews [mailto:chip@sqlsecurity.com]=20
Sent: Wednesday, January 05, 2005 4:38 PM
To: Scovetta, Michael V
Cc: David Litchfield; Steve Friedl; bugtraq@securityfocus.com
Subject: Re: Paper: SQL Injection Attacks by Example

Michael,

I think David's point was that the lack of input validation that caused=20
the SQL injection problem in the first place will not be mitigated by=20
changing to a stored procedure. For example if we changed the following

standard query implementation:

Set myRS =3D Conn.execute("select foo from bar where id=3D" &=20
request.form("someIntValue"))

To the following Stored Procedure implementation:

Set myRS =3D Conn.execute("exec usp_getFooBar " &=20
request.form("someIntValue"))

We have not mitigated anything. (simply supply the following exploit=20
code in the second example: 1;exec master..xp_cmdshell=20
'blahblahblah'-- etc etc) It doesn't matter that the stored procedure=20
input was well typed - our injection happens outside the stored=20
procedure anyway. And, as you mentioned, if the stored procedure uses=20
the EXECUTE statment or sp_executesql procedures then we *may* still=20
have a SQL injection issue INSIDE the stored procedure as well.

If the response is "well, of course, you need to call your stored=20
procedure using a parameterized query". However, if we used=20
parameterized queries then both are mitigated so changing to a stored=20
procedure is a wash.=20

The correct way to do data access above is like this (C# sample):=20
(whether you use stored procs or not)

//Begin Sample
con =3D new SqlConnection(YourConnectionString);
con.Open();
string CommandText =3D "usp_getFooBar";
cmd =3D new SqlCommand(CommandText,con);
cmd.CommandType =3D StoredProcedure; //Change to Text for an adhoc =
query
cmd.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int );
cmd.Parameters["@ID"].Value =3D Request.Form("someIntValue");
SqlDataReader rdr =3D cmd.ExecuteReader();
//close stuff as usual
//End Sample

Chip Andrews
www.sqlsecurity.com


Scovetta, Michael V wrote:

>David,
>
>Actually, to nitpick your comment a bit, stored procedures usually have
>typed input variables:
>
> create procedure foo ( a int, b varchar(20) ) as ...
>
>At least in MSSQL, you'd have to do something bad like use
sp_executesql
>or some other function that will re-form a complete sql query and pass
>that to the interpreter. As long as you do more sensible stuff like:
>
> insert into table (name, age) values (@b, @a)
>
>you should be fine.
>
>Michael Scovetta
>Computer Associates
>Senior Application Developer
>
> =20
>

[Chip Andrews]

Michael,

I think David's point was that the lack of input validation that caused
the SQL injection problem in the first place will not be mitigated by
changing to a stored procedure. For example if we changed the following
standard query implementation:

Set myRS = Conn.execute("select foo from bar where id=" &
request.form("someIntValue"))

To the following Stored Procedure implementation:

Set myRS = Conn.execute("exec usp_getFooBar " &
request.form("someIntValue"))

We have not mitigated anything. (simply supply the following exploit
code in the second example: 1;exec master..xp_cmdshell
'blahblahblah'-- etc etc) It doesn't matter that the stored procedure
input was well typed - our injection happens outside the stored
procedure anyway. And, as you mentioned, if the stored procedure uses
the EXECUTE statment or sp_executesql procedures then we *may* still
have a SQL injection issue INSIDE the stored procedure as well.

If the response is "well, of course, you need to call your stored
procedure using a parameterized query". However, if we used
parameterized queries then both are mitigated so changing to a stored
procedure is a wash.

The correct way to do data access above is like this (C# sample):
(whether you use stored procs or not)

//Begin Sample
con = new SqlConnection(YourConnectionString);
con.Open();
string CommandText = "usp_getFooBar";
cmd = new SqlCommand(CommandText,con);
cmd.CommandType = StoredProcedure; //Change to Text for an adhoc query
cmd.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int );
cmd.Parameters["@ID"].Value = Request.Form("someIntValue");
SqlDataReader rdr = cmd.ExecuteReader();
//close stuff as usual
//End Sample

Chip Andrews
www.sqlsecurity.com


Scovetta, Michael V wrote:

>David,
>
>Actually, to nitpick your comment a bit, stored procedures usually have
>typed input variables:
>
> create procedure foo ( a int, b varchar(20) ) as ...
>
>At least in MSSQL, you'd have to do something bad like use sp_executesql
>or some other function that will re-form a complete sql query and pass
>that to the interpreter. As long as you do more sensible stuff like:
>
> insert into table (name, age) values (@b, @a)
>
>you should be fine.
>
>Michael Scovetta
>Computer Associates
>Senior Application Developer
>
>
>

[Cory Foy]

Scovetta, Michael V wrote:
> At least in MSSQL, you'd have to do something bad like use sp_executesql
> or some other function that will re-form a complete sql query and pass
> that to the interpreter. As long as you do more sensible stuff like:
>
> insert into table (name, age) values (@b, @a)
>
> you should be fine.

Except that I've seen webbie-type people who will execute a stored proc
by doing:

strSQL = "exec userLogin " + userName + " " + userPassword

which would be still be subject to a SQL Injection attack if I simply
had a semicolon in the userPassword and then was able to pass any other
query to it.

Cory

[David Litchfield]

>David,
>Actually, to nitpick your comment a bit, stored procedures usually have
typed input variables:
>create procedure foo ( a int, b varchar(20) ) as ...

This has no bearing on the issue.

If from the code of the app I perform something like

strSQL = "exec foo(" & request.querystring("a") & "," &
request.querystring("b") & ")"
Conn.execute(strSQL)

then I can either set a in the query string to

?a=1,'foo');exec+master..xp_cmdshell+'dir'--&b=foo

Or alternatively I can set b in the query string to

?a=1&b=foo');exec+master..xmp_cmdshell+'dir'--

Either way I'm using a stored procedure and I can inject into the app.

Cheers,
David

[Sergey Chernyshev]

I believe changing to stored procedures is good not just because it's
impossible to inject something to the query the same way it's done with
regular queries - but because in addition to that you can restrict
permissions for connected user to run only stored procedures and be even
more specific with them.

This way no injected code will be able to execute because of permissions and
it will allow control of that on DB server level versus web app code level.

Sergey



-----Original Message-----
From: David Litchfield [mailto:davidl@ngssoftware.com]
Sent: Wednesday, January 05, 2005 2:20 PM
To: 'Steve Friedl'; bugtraq@securityfocus.com
Subject: RE: Paper: SQL Injection Attacks by Example

Hi Steve,
Nice paper. However, one small nitpick - under "Mitigations" you list using
stored procedures if the database supports them. I've seen other people
suggest this as a defensive strategy as well.

Using stored procedures will *not* protect you from SQL injection attacks.
Firstly, they can be injected into just as easily as a select statement.
Secondly, the procedure itself can be vulnerable to SQL injection. I have
seen for example, procs that use double quotes internally and single quotes
on input.

That said, stored procedures are generally faster so it's better to use them
for performance reasons, anyway.

Cheers,
David Litchfield