I learned something interesting about SQL parameters today.  In my C# code I was passing a comma separated string as a parameter to a stored procedure using SqlParameter, but it was allowing ' through unchecked causing havoc in the stored procedure.  It turns out when you use dynamic SQL in the stored procedure you lose the safety of the parameter.

Pseudocode Example:

CREATE PROCEDURE [dbo].[CommaSeparatedNames]

@Names NVARCHAR(MAX)

DECLARE @Name NVARCHAR(MAX)

DECLARE @NameQuery NVARCHAR(MAX) SET @NameQuery = 'SELECT * FROM Names WHERE '

-- Split @Names into a @Name, such that

-- each name is wrapped, @NameQuery = @NameQuery + ' Name LIKE ''' + @Name + ''' OR '

-- generating a query like SELECT * FROM Names WHERE Name LIKE 'bob' OR Name LIKE 'john'

EXECUTE sp_executesql @NameQuery

When a name was passed with ' in it, the query generation fails.  I have to explicity replace the ' with '' in the C#.