SQL Injection

[Zippeh]

How much of a problem is SQL injection if you're using stored procedures with parameterised arguments??

 

[Rabbitfacedkilla]

That depends, it is a large threat, as injection attacks are relatively easy to invoke. What is the scope of the project?

 

[Zippeh]

We're going through a lot of our older systems and are noticing some holes where there is SQL used within the ASP, but a lot of sites say that using stored procedures and parameters does minimise the risk of SQL injection. I was just wondering whether it is necessary to add other check to the parameters in this case?

 

[pointdexterish]

To follow best practices while developing, I would still create a "Sanitize" function and pump all of your inputs through it before letting them loose on your SQL server. I am not sure if parameterized stored functions are immune from injection - but a few sloppy lines of legacy code could put your data at risk.

Why bother taking a chance - Sanitize!

 

[magelus]

what would be some good counter measures to employ in said sanitize function?

 

[pointdexterish]

A function developed to sanitize inputs against SQL Injection should do several things (in my experience these have been the protections I've used):

• First do a character by character check for escape chars such as (but not exclusively): ' " / ` ~ ; : \ | and whatnot
• Also do string by string checking for SQL statement keywords such as (but not exclusively): SELECT, FROM, WHERE, DROP, etc
• There is also the idea of creating specific users inside of your SQL-space to execute certain classes of commands. Think of this as partitioning out privelages to a special user who can only do precisely what needs to be done to get the job completed. If users should not be able to DROP records, tables, etc then why run queries that contain their input with a user who has the ability to DROP?

Obviously your application requirements will drive how you can implement these, and other security measures. There are plenty of great articles out there on SQL security - get your Google on if you want to brush up and get learned.

 

[Cardboard Hammer]

Watch out for EXEC using user input

 

[Cardboard Hammer]

Following up on my previous post, as long as you do not use EXEC on a string built with user input, you are completely bulletproof against injection when using stored procedures.

Toward the bottom of this thread (10th post down) http://www.webdeveloper.com/forum/showthread.php?s=&threadid=34519 I've got more info on SQL injection and stored procedures, along with an example of unsafe usage of EXEC in a stored procedure.

 

[Zippeh]

So let me get this straight...

If I'm using stored procedures with parameters, and not using the EXEC keyword within the stored procedures, then I need not worry about SQL injection in those stored procedures?

If so....

 

[Cardboard Hammer]

Correct.

 

[enkafan]

Cardboard hit the nail on the head. It you are working with sprocs, that takes care of a lot of problems people run into with single quotes and things like OR statements. But I've worked on quite a few systems that have sprocs that look like this:

CREATE PROC SearchStates
   @States varchar(255)
AS
EXEC ('SELECT * FROM Stuff WHERE States IN (''' + @States + ''')

That's the one big problem you'll run into.

 

[Zippeh]

Stunning

Haven't got any stored procs like that

Thanks a lot for your help lads!