SQL Injection

Considering SQL injection attacks (and not performance reasons) what is difference between the two examples below? Taking the two examples into consideration I dont understand how using a SP is any safer against attacks?


MySql = "SELECT field1 FROM table1 WHERE field2 = @search"
MyCommand = New SqlCommand(MySql, MyConnection)
Dim sp As SqlParameter = new SqlParameter
sp = MyCommand.Parameters.Add("@search",SqlDbType.VarChar)
sp.Value = Request.QueryString("search")


Versus

MyCommand = New SqlCommand("usp_search", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
Dim sp As SqlParameter = new SqlParameter
sp = MyCommand.Parameters.Add("@search",SqlDbType.VarChar)
sp.Value = Request.QueryString("search")

usp_search code:

CREATE PROCEDURE usp_search @search varchar(50)
SELECT field1
FROM table1
WHERE field2 = @search
GO

3 Comments

  • Either one of those is safe. The comparison you should be making is not permanent stored proc vs. on-the-fly stored proc (which is what you're getting when you use the @ syntax in the command text), but between using parameters and using concatenated text. Here's the BAD version, which is vulnerable to SQL injection:





    MySql = "SELECT field1 FROM table1 WHERE field2 = " & Request.QueryString("search")

  • The point is about string concat into a querystring.





    example:





    string sqlstring = "";


    string id = "";





    user input of: 1001' drop table orders --





    sqlstring = "SELECT HasShipped" +


    " FROM detail WHERE ID='" + Id + "'";


    SqlCommand cmd = new SqlCommand(sqlstring,sql);





    Gives --


    ID: 1001' drop table orders --


    SELECT HasShipped


    FROM detail


    WHERE ID= '1001 drop table orders -- '





    This is fine as long as you don't need your orders table.





    Ken

  • Travis: The other issue is permissions: If your code uses dynamic SQL rather than stored procedures, you'll need to give your application direct access to the database tables. If the permissions include write access, then SQL injections attacks become possible.





    With stored procs, your app only requires access to the procs, so it's impossible for a user to manipulate the tables in a way that you did not intend.

Comments have been disabled for this content.