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.
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 '[email protected]; 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), '');
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!