SQL Server: SPROC: Code hardening from SQLi

[alex_read]

Hi everyone!

In writing a current web application, I had a thought about my stored procedure. Lets say it's a simple one as below:

Create procedure SaveToTable1
    @ValueToSave varchar(25)
AS
    INSERT INTO Table1 (Field1) VALUES (@ValueToSave)

Now, whilst I can write and secure web code from SQL injection attacks, and set permissions on SQL Server to preven anyone accessing this, I wondered if I could also be a little more paranoid and add an additional overhead of a check within the procedure.

I tried on search engines to look for stored procedure code hardening, but haven't turned up much. Some sites are saying SQL Server will check for invalid characters if parameters (like in that statment above) are used - similar to the way .Net code does. I don't know if there's any truth in this, or whether any of you might have your own parsing code - I just thought I'd ask everyones opinion to better understand/learn this please.

Thanks,
Alex

[jmcilhinney]

I'm not sure what you're trying to achieve. If you're talking about SQL injection then it can't happen with parameters. As an example, let's say you have this in VB code:

Dim sql As String = "INSERT INTO Table1 (SomeColumn) VALUES ('" & someValue & "')"

Now, suppose a user was able to input data such that the value of someValue was "Hello World'); DELETE FROM Table1; INSERT INTO Table1 (SomeColumn) VALUES ('Hello World". That means that the actual SQL code that you would be executing would be:

INSERT INTO Table1 (SomeColumn) VALUES ('Hello World'); DELETE FROM Table1; INSERT INTO Table1 (SomeColumn) VALUES ('Hello World')

I'm sure you would agree that that's not good. You would be inserting a row, then deleting every row, then inserting a row. Not what you had in mind from the SQL code that you wrote. It would be possible for a clever user to inject even more damaging SQL code into your statements.

Now, let's say that your VB code looked like this instead:

Dim sql As String = "INSERT INTO Table1 (SomeColumn) VALUES (@SomeColumn)"
 
myCommand.Parameters.AddWithValue("@SomeColumn", someValue)

In that case the database would simply insert a single row with the value "Hello World'); DELETE FROM Table1; INSERT INTO Table1 (SomeColumn) VALUES ('Hello World" in the SomeColumn column. That's why allowing the user to insert literal values into an SQL statement is bad and using parameters is good. Unless at some point you actually execute the contents of the SomeColumn column as SQL then you're protected, no matter what it contains.

[alex_read]

Hi, and thanks for the reply .

I understand SQLi and have added precautions to my .Net code just as you did above, I really wondered whether anyone went to the additional step of additing checks into the stored procedure code as well. I was just thinking that, from a server level, there might be another developer with access privelages to run that procedure and whether anyone added validation within SProcs to verify non - insecure argument code was passed. Or as an alternative, whether SQL Server does a check itself.

Thanks again