How much sanitizing is needed for Web Services that call Stored Procedures?

Question

I am building a series of web services in VB.Net

Each of the web services takes multiple string values, performs some validation/processing then calls a Stored Procedure using Linq to SQL. Some of the string contains user data that is stored in the database:

These string values passed from the web service are escaped to trap single quote characters, semi colons and the various bracket types.

I am calling the SP using the datacontext.spname(parameter1, parameter2) method.

The aim is to ensure that the web services are as resilient as possible, while still being performant.

Have I done enough to prevent SQL injection attacks?

Answer 1

Usually you are good, but there are a few caveats:

• Careful of stored procs that use sp_executesql or exec. You can pass in a query in the param and end up executing it.

• Careful with LIKE sections of queries cause they can be widened with % if likened to a param.

• Fields used in webpages may need some extra processing before being sent in, to avoid cross site scripting. (which you should also defend against when pulling information out)

Answer 2

I know for a fact that LINQ to SQL queries all the data send to the database via SQL parameters -- which keeps you safe from SQL injection. I'm not entirely sure, but since LINQ abstracts the stored procedure, it too most likely passes the arguments to the stored procedures in the same manner.

What does that mean? You don't have to worry about sanitizing your data because LINQ will take care of it. You could of course test it out with a simple SQL injection type attack -- something like a harmless insert or select.

Answer 3

If you're using parameters then you don't need to sanitise at all as single quotes and the other sql injection nasties get escaped for you.

It's probably a bad idea to sanitise on input depending on the data you're storing. If you're storing things that end up embedded in a web page and you encode/sanitise them on data entry what happens if your sanitation code has a bug? You end up with data in the database that will cause problems on output and no easy way to fix it without an update over all your data. It's better to sanitise when you output data as corrections to the sanitation code will then run against all data. You also have the advantage of easier searching in SQL should that be a concern.

I'd limit the web service to obvious things, null and range checks.