bugtraq - RE: Paper: SQL Injection Attacks by Example

lists.openwall.net		lists / announce owl-users owl-dev john-users john-dev passwdqc-users yescrypt popa3d-users / oss-security kernel-hardening musl sabotage tlsify passwords / crypt-dev xvendor / Bugtraq Full-Disclosure linux-kernel linux-netdev linux-ext4 linux-hardening linux-cve-announce PHC
Open Source and information security mailing list archives

Hash Suite: Windows password security audit tool. GUI, reports in PDF.

[<prev] [next>] [day] [month] [year] [list]

Date: Wed, 5 Jan 2005 16:44:08 -0500
From: "Scovetta, Michael V" <Michael.Scovetta@...com>
To: "Chip Andrews" <chip@...security.com>
Cc: "David Litchfield" <davidl@...software.com>,
	"Steve Friedl" <steve@...xwiz.net>, <bugtraq@...urityfocus.com>
Subject: RE: Paper: SQL Injection Attacks by Example

Chip--

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

  ps = con.prepareStatement("update people set name = ? where nid = ?");
  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@...com 

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

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
>
>  
>