Re: Paper: SQL Injection Attacks by Example

From: Chip Andrews (chip_at_sqlsecurity.com)
Date: 01/05/05

  • Next message: Michael Silk: "RE: Paper: SQL Injection Attacks by Example"
    Date: Wed, 05 Jan 2005 16:37:51 -0500
    To: "Scovetta, Michael V" <Michael.Scovetta@ca.com>
    
    

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


  • Next message: Michael Silk: "RE: Paper: SQL Injection Attacks by Example"

    Relevant Pages

    • Re: how to code to avoid SQL insertion attacks
      ... > vulnerable to SQL injection as someone executing SQL select statements. ... > prepared statements is that you are responsible only for providing the ... you are obviously not aware of the purpose of an "sql injection attack" ... NOT because you used a stored procedure. ...
      (comp.lang.java.programmer)
    • Re: SQL Injection Prevention
      ... against SQL injection than parameterized call to stored procedure. ... > vulnerabilities. ... >> doesn't mean anything about good programming practices what so ever. ...
      (microsoft.public.sqlserver.server)
    • Re: SQL Injection Prevention
      ... against SQL injection than parameterized call to stored procedure. ... > vulnerabilities. ... >> doesn't mean anything about good programming practices what so ever. ...
      (microsoft.public.dotnet.security)
    • RE: Paper: SQL Injection Attacks by Example
      ... I like the Java syntax much better than the .net syntax... ... Subject: Paper: SQL Injection Attacks by Example ... To the following Stored Procedure implementation: ... procedure using a parameterized query". ...
      (Bugtraq)
    • Re: SQL Injection Prevention
      ... >> stored procedure, you have the same level of protection against SQL ... > select statement is better protected against SQL injection than stored ... >> You can check my blog post that I refered in my prevoius post ot that ...
      (microsoft.public.sqlserver.server)