locked
SQL Injection vulnerabilities? Two questions: RRS feed

  • Question

  •  

    1) I have SQL Server 2000 stored procedures, well made without any dynamic SQL and they take distinct parameters for everything. I am calling them from VB DLLs with the connection.execute command. Normally, each parameter of the sproc would be specified before execution and we know we're good against an injection attack. However, since a stored procedure with, say, two parameters can be called like this:

     

     ReturnCustomers 'Miles', 'Female'

     

    my connection executions look like this:

     

    sql = "ReturnCustomers 'Miles', 'Female' "

     

    con.execute sql

     

    Is this susceptible to an attack? In my estimation, the stored procedure itself is immune because it has declared parameters and will work properly. However, (assuming that's correct), if additional code is appended after that 'Female', is it possible for the con.execute method to execute multiple commands, i.e. an attacker drops in a completely distinct second statement appended to 'Female', will the execute method execute both?

     

    2) This is still another SQL injection question: the out-of-box Formview control with ASP.NET 2.0 ties in nicely to SQLDatasources. Their command can be set with text or stored procedures. If I'm using text--the sqldatasource itself creates the SQL code on the fly--is this vulnerable to an injection? My assumption is yes, but somewhere on the net I came across an opinion that these parameterize everything in the background anyway, so the defense offered by parametered stored procedures is essentially there for Text-type sql commands with a sql datasource.

     

    Thanks so much for answers!

    Tuesday, June 24, 2008 11:46 PM

Answers

  • I am not sure about number 1, but it looks like it might be injectable, by putting a semicolon, and then a command behind it.  For number 2, you have to look to see if it is binding to parameters or concatenating inline.  If it was all built using the designer and not adding code in the codebehind manually, then in my experience, the designer uses parameters for binding values provided by the user, but it is not guaranteed.  The best thing is to not use text and force the use of stored procedures.  This does a couple of things, like enforce stronger security by removing the need for table level access to the application account, and restricting it to only perform the actions available through the stored procedures the app has execute access on.

     

    Tuesday, June 24, 2008 11:56 PM
  • I dont know about number 2; you would have to validate the code yourself to see what it is doing.  For number 1, you are defenitely susceptable to injection, if the values being passed into the sp are in fact user input.  Since you are using an execute statement a user could put a whole new statement in with the stored procedure exection.

     

    Code Snippet

    create table t(

    id int,

    Miles INT,

    Gender varchar(10)

    )

    go

    insert into t values (1,10,'Female');

    insert into t values (2,150,'Female');

    go

    create procedure ReturnCustomers

    @Miles int,

    @Gender varchar(10)

    as

    begin

    select *

    from t

    where Miles < @Miles and

    Gender = @Gender

    end

    go

    declare @sql nvarchar(500),

    @Miles int,

    @Gender Varchar(50)

    set @Miles = 100

    set @Gender = '''Female''' + '; drop table t;'

    set @sql = N'ReturnCustomers ' + Convert(varchar(10),@Miles) + ', ' + @Gender

    --print @sql

    exec(@sql)

    select *

    from t

    drop procedure ReturnCustomers

     

     

    Wednesday, June 25, 2008 5:18 PM

All replies

  • I am not sure about number 1, but it looks like it might be injectable, by putting a semicolon, and then a command behind it.  For number 2, you have to look to see if it is binding to parameters or concatenating inline.  If it was all built using the designer and not adding code in the codebehind manually, then in my experience, the designer uses parameters for binding values provided by the user, but it is not guaranteed.  The best thing is to not use text and force the use of stored procedures.  This does a couple of things, like enforce stronger security by removing the need for table level access to the application account, and restricting it to only perform the actions available through the stored procedures the app has execute access on.

     

    Tuesday, June 24, 2008 11:56 PM
  • &n******sp;

    Thanks for the responses. I think stored procedures for all interaction is ideal. I'll set that on the .net 2.0 app, certainly.I also wonder if a semi-colon would allow multiple executions with a single .execute. That's definitly the crux. My guess is it pro******a******ly would allow it, as you mention.

    Wednesday, June 25, 2008 4:11 PM
  • I dont know about number 2; you would have to validate the code yourself to see what it is doing.  For number 1, you are defenitely susceptable to injection, if the values being passed into the sp are in fact user input.  Since you are using an execute statement a user could put a whole new statement in with the stored procedure exection.

     

    Code Snippet

    create table t(

    id int,

    Miles INT,

    Gender varchar(10)

    )

    go

    insert into t values (1,10,'Female');

    insert into t values (2,150,'Female');

    go

    create procedure ReturnCustomers

    @Miles int,

    @Gender varchar(10)

    as

    begin

    select *

    from t

    where Miles < @Miles and

    Gender = @Gender

    end

    go

    declare @sql nvarchar(500),

    @Miles int,

    @Gender Varchar(50)

    set @Miles = 100

    set @Gender = '''Female''' + '; drop table t;'

    set @sql = N'ReturnCustomers ' + Convert(varchar(10),@Miles) + ', ' + @Gender

    --print @sql

    exec(@sql)

    select *

    from t

    drop procedure ReturnCustomers

     

     

    Wednesday, June 25, 2008 5:18 PM
  •  

    Thanks. I did try your code and confirm that it is susceptible, because the sproc will run with the parameters we want it to have and then the semi-colon will allow a new statement to run. This is good news because now I know I'm not going down the wrong path Smile
    Wednesday, June 25, 2008 6:05 PM