<<< Date Index >>>     <<< Thread Index >>>

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