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.