Friday, October 19, 2007

SQL Injection in Stored Procedure

Let us examine SQL Injection in Stored Procedure. This would be 1 of the vulnerable cases.

The Server Side Code would be something like:
oCmd.CommandText = "sp_login";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( "@loginId", strUserName);
oCmd.Paramerters.Add( “@password”, strPassword);
oCon.Open();
string result = (string)oCmd.ExecuteScalar();
oCon.Close();
====================================================================
The Stored Procedure would be:
CREATE PROC sp_login (@loginid nvarchar(25),@password)
AS
DECLARE @SQLString NVARCHAR(500)
DECLARE @loginid VARCHAR(64)
DECLARE @password VARCHAR(64)

/* Build the SQL string once.*/

SET @SQLString = 'SELECT * from cust_users WHERE login_id = '+ ''''+@loginid+'''' + 'AND password = '+ ''''+@password+''''

EXECUTE sp_executesql @SQLString

====================================================================

If the user input is as follows:
loginId = ' OR 1=1 --
password = junk

The above stored procedure will have an injection attack. The procedure executing will return all the rows because of the injected SQL.

Cheers.

8 comments:

  1. How is that different from any normal SQL injection attack?

    ReplyDelete
  2. @lance:

    Usually while SQL Injection attacks are found in dynamic sql code written in the server-side code itself. The security experts recommend developers to write stored procedures.

    However, cases are found where stored procedure again has a dynamic sql built which makes it vulnerable. This was one such attempt to describe that SQL Injection mitigation requires no dynamic sql code.

    ReplyDelete
  3. it will work against Stored Prodedure
    or not , Can u help me

    ReplyDelete
  4. @Utsav:

    It will definitely work against a stored procedure if a dynamic sql is written inside the procedure.

    I came across an excellent article from Oracle on "How to write injection proof PL/SQL". Here is the link: http://www.oracle.com/technology/tech/pl_sql/pdf/how_to_write_injection_proof_plsql.pdf

    ReplyDelete
  5. Hi Dharmesh,

    I have tried this, but it seems that this logic does not work in either SQL Server 2005 or SQL Server 2008.

    Or is it like you have tested in SQL Server 2000 and you forgot to mention it.

    If this is the case then it means that Stored Procedure are still a safe option than normal SQL Queries.

    Thanks & Regards,
    Dhiraj Ranka

    Follow me on Twitter,
    http://twitter.com/dhiraj_21985

    ReplyDelete
  6. i tried dude but it didn't worked it returns 0 rows afftected.

    ReplyDelete
  7. Why are using sp_executesql? if you are making a stored procedure use the same to pass the parameter and execute query. So that there will be no chance of SQL Injection even with dynamic query.

    Thanks & Regards,
    Dhiraj Ranka
    http://twitter.com/dhiraj_21985

    ReplyDelete
  8. i without a doubt enjoy your own writing taste, very useful.
    don't give up as well as keep penning in all honesty , because it just simply well worth to follow it,
    looking forward to view a lot more of your own content pieces, have a pleasant day!

    ReplyDelete