[38077] in bugtraq
Re: Paper: SQL Injection Attacks by Example
daemon@ATHENA.MIT.EDU (Chip Andrews)
Wed Jan 5 17:07:27 2005
Message-ID: <41DC5E2F.2010501@sqlsecurity.com>
Date: Wed, 05 Jan 2005 16:37:51 -0500
From: Chip Andrews <chip@sqlsecurity.com>
MIME-Version: 1.0
To: "Scovetta, Michael V" <Michael.Scovetta@ca.com>
Cc: David Litchfield <davidl@ngssoftware.com>,
Steve Friedl <steve@unixwiz.net>, bugtraq@securityfocus.com
In-Reply-To: <8DC79B1EA961734C852BE2D5ECC069A001A7E220@usilms26.ca.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
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
>
>
>
