Avoid SQL Injection with Parameters

The best way to avoid SQL Injection is with parameters. With parameters you can’t get SQL Injection.

You only get SQL Injection where you are building a SQL statement by concatenating your parameter values in with your SQL statement.

Annoyingly many TSQL statements don’t take parameters, CREATE DATABASE for instance, or really annoyingly ALTER USER. In these situations you have to rely on using QUOTENAME or REPLACE to avoid SQL Injection. (Kimberly Tripp takes about this in her recent blog post Little Bobby Tables, SQL Injection and EXECUTE AS)

The same applies if you are dynamically choosing columns or tables, in these cases you have to concatenate your values together but it is essential that you protect yourself.

To use parameters you use sp_executesql like this

ALTER PROCEDURE GetMembers

(

     @FirstName nvarchar(50)

)

AS

DECLARE @ExecStr nvarchar(2000)

SELECT @ExecStr = N'SELECT * FROM dbo.member WHERE FirstName = @FirstName'

EXEC sp_executesql @ExecStr, N'@Firstname nvarchar(2000)', @FirstName

 

You can see here that our SQL statement uses parameters and so we are protected from SQLInjection.

Published Monday, April 5, 2010 9:07 PM by simonsabin

Comments

# Twitter Trackbacks for Avoid SQL Injection with Parameters - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Avoid SQL Injection with Parameters - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

Tuesday, April 6, 2010 5:49 AM by Kimberly L. Tripp

# re: Avoid SQL Injection with Parameters

Hey there Simon - While I do agree that sp_executesql can be used to avoid SQL Injection, the primary reason for my post was to focus on what's required when DSE (dynamic string execution) through EXEC is required.

A few people have pointed out sp_executesql and I made a few modifications to the post to even further clarify that minimizing SQL Injection with *EXEC* was my point.

However, from a performance perspective there are cases where sp_executesql can be really problematic. And, this is going to be my next post... coming soon... I promise!

I hope all is well with you!!

Cheers,

kt

Tuesday, April 6, 2010 8:00 AM by Kimberly L. Tripp

# re: Avoid SQL Injection with Parameters

OK... I did a bit more on this tonight. Will add a lot more on this over the next couple of days!

But, here's my first post on the topic: www.sqlskills.com/.../EXEC-and-sp_executesql-how-are-they-different.aspx.

Enjoy!

kt