Welcome to 2020, may I take you coat? How about your Customer Data?

That’s right, it’s now 2020, we’re already in the 2nd decade of the “new” millennium. A lot has changed in the last 20 years: High Speed Broadband (cough for most of us) is common place, Computers are faster than ever, cars are more economical, the polar icecaps are smaller (apparently), and a Happy Meal for McDonald’s isn’t 99p any more.

There are some things that haven’t change too: The UK is still part of the EU (at time of writing), your neighbour’s dog still barks all night, and customer data is still wide open to theft from SQL injection.

Yes, that classic subject, and my biggest pet peeve of them all… SQL injection.

Like I said, it’s 2020, so why are you still writing dynamic statements that look like this?

SET @SQL = 'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ' + @MyValue

Why are you still writing application code that creates SQL statements like this?

string SQLStatement;
SQLStatement = "SELECT * FROM MyTable WHERE UserName = " & txtTextBox1.text;

If there’s something you need to learn from people’s past mistakes experiences in the last decade it’s that SQL Injection is still a very real problem and all of us need to stop writing code like the above. For those of you that write SQL, that means properly quoting our dynamic objects (using QUOTENAME) and for those of that write application code that means using Parameters, not concatenating (user) input values to the query. There’s 100’s of languages, so I’m not going to show you how to do this for all of them (just the one’s above) but if you don’t know how to in the language you use, then you need to learn; now.

The correct way

Dynamic SQL:

SELECT @SQL = N'SELECT * FROM ' + QUOTENAME(t.[name]) + N' WHERE ' + QUOTENAME(c.[name]) + N' = @MyValue;' --Really this should have the schema too
FROM sys.tables t
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.[name] = @TableName
  AND c.[name] = @ColumnName;

EXEC sp_executesql @SQL, N'@MyValue varchar(15)', @MyValue;


string statement;
statement = "SELECT * FROM MyTable WHERE UserName = @UserName;";
SqlCommand command = new SqlCommand(statement, connection);
command.Parameters.Add("@UserName", SqlDbType.NVarChar,128);

Leave a Reply

Your email address will not be published. Required fields are marked *