Whenever you run dynamic SQL code from an application or in a stored procedure, make sure you clean (called “escaping” in developer-speak) all those apostrophes and semicolons, or you may find yourself on the business end of an SQL injection.

Example

Say you have a stored procedure that executes dynamic SQL from a user input. A very simple example:

CREATE PROCEDURE dbo.sp_testproc
    @query    varchar(1000)
AS

DECLARE @sql varchar(1000);
SET @sql='SELECT * FROM dbo.Employees WHERE '+@query;
EXEC(@sql);

Needless to say, it is really dangerous to allow people to insert ad-hoc SQL code. Here’s what could happen:

EXECUTE dbo.sp_testproc
    @query='currentlyEmployed=1; DELETE FROM dbo.Employees';

In the example above, the user can wipe the dbo.Employees table because he uses a semicolon to separate a statement into two. Actually, a line break would do just as well.

Replacing semicolons, etc

As a step in validating user-input, you can use the REPLACE() function to eliminate characters or strings in the user input variable.

SET @query=REPLACE(@query, ';', '');
SET @query=REPLACE(@query, CHAR(10), '');
SET @query=REPLACE(@query, CHAR(13), '');

Escaping apostrophes

Apostrophees can be used for a similar purpose when generating SQL injections. You can provide apostrophes in regular text strings in T-SQL, but they are escaped with another apostrophe.

SET @query=REPLACE(@query, '''', '''''');

This may look tricky at first, but remember that an escaped apostrophe is actually two apostrophes when in a string. So to set a variable to the value “Life’s good”, you’d use the following T-SQL

SET @status='Life''s good';

So when you’re escaping apostrophes, you’re replacing a single apostrophe (”) with a double apostrophe (””). And because these are string values, you add an apostrophe before and after.

Or don’t do ad-hoc SQL at all

This is probably the best option whenever your code receives user inputs as arguments.

On the bright side..

Linked from xkcd.com under Creative Commons Attribution-NonCommercial 2.5 License. Check out lots of other excellent comics on xkcd.com!