Bugtraq mailing list archives
RE: Paper: SQL Injection Attacks by Example
From: "Scovetta, Michael V" <Michael.Scovetta () ca com>
Date: Wed, 5 Jan 2005 16:44:08 -0500
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 () ca com -----Original Message----- From: Chip Andrews [mailto:chip () sqlsecurity com] 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 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
Current thread:
- Paper: SQL Injection Attacks by Example Steve Friedl (Jan 05)
- RE: Paper: SQL Injection Attacks by Example David Litchfield (Jan 05)
- <Possible follow-ups>
- RE: Paper: SQL Injection Attacks by Example Scovetta, Michael V (Jan 05)
- Re: Paper: SQL Injection Attacks by Example Chip Andrews (Jan 05)
- Re: Paper: SQL Injection Attacks by Example Cory Foy (Jan 05)
- RE: Paper: SQL Injection Attacks by Example David Litchfield (Jan 05)
- RE: Paper: SQL Injection Attacks by Example Michael Silk (Jan 05)
- RE: Paper: SQL Injection Attacks by Example Scovetta, Michael V (Jan 05)
- RE: Paper: SQL Injection Attacks by Example Sergey Chernyshev (Jan 06)