title: SQL Server: Sanitizing @param against injection attacks
body: <p>For the sake of argument, let's just say I <strong>have to</strong> create a local variable containing a SQL query that has an INSERT:</p>
  
  <pre><code> DECLARE @insert NVARCHAR(MAX)
   SELECT @insert = 'INSERT INTO [dbo].[' + @table + '] VALUES...
   EXEC (@insert) 
  </code></pre>
  
  <p>This INSERT is also going to contain a column value:</p>
  
  <pre><code> DECLARE @insert NVARCHAR(MAX)
   SELECT @insert = 
    'INSERT INTO [dbo].[' + @table + '] VALUES (N''' + @message + ''')'
   EXEC (@insert) 
  </code></pre>
  
  <p>Now, I'm obviously concerned about an injection attack, and would like to ensure that @message's value can't make @insert's value malicious or malformed as a query to EXEC.</p>
  
  <p>This brings us to my question: is escaping the ' characters in @message sufficient? Are there any other characters that could appear in @message that could escape out?</p>
  
  <p>Example:</p>
  
  <pre><code> DECLARE @insert NVARCHAR(MAX)
   SELECT @message = REPLACE(@message,'''','''''')
   SELECT @insert = 
    'INSERT INTO [dbo].[' + @table + '] VALUES (N''' + @message + ''')'
   EXEC (@insert)  
  </code></pre>
  
  <p>(When I say "<strong>have to</strong>", this is because my query is in a stored procedure, and this stored procedure accepts @table, which is the destination table to INSERT into. I'm not interested in discussing my architecture or why the table to INSERT into is "dynamically" specified via a procedure parameter. Please refrain from commenting on this <em>unless there's another way besides EXEC()ing a query to specify a table to INSERT into when then table name is received as a procedure parameter</em>.)</p>