title: How can I use a temp table and parameters with sp_executesql?
body: <p>I added quick-and-dirty whole database search using a stored procedure inspired from <a href="https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008">this SO question</a>. It works fine, but I'm worried about SQL injection. Of course I use an SQL parameter :</p>
  
  <pre><code> string query = GetUserInput();
   using (SqlConnection con = new SqlConnection(conString))
   {
          using (SqlCommand cmd = new SqlCommand("SearchAllTables", con))
          {
                  cmd.CommandType = CommandType.StoredProcedure;
  
                  cmd.Parameters.Add("@SearchStr", SqlDbType.VarChar).Value = query;
  
                  con.Open();
                  SqlDataReader reader = cmd.ExecuteReader();
  </code></pre>
  
  <p>However, since the SP builds a query and execute it, SQL injection is still possible. Relevant part:</p>
  
  <pre><code>CREATE PROC [dbo].[SearchAllTables] 
  (
  @SearchStr nvarchar(100)
  )
  AS
  BEGIN
  
  CREATE TABLE #Results (TableName nvarchar(370), ColumnName nvarchar(370), ColumnValue nvarchar(3630), TableId int)
  --snip boring part
  
  SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  INSERT INTO #Results
  EXEC
  (
      'SELECT ''' + @TableName +''', ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' + @ColumnName + '), 3630), [id]
       FROM ' + @TableName + ' (NOLOCK) ' +
       ' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE ' + @SearchStr2 --This is vulnerable
  )
  
  --snip all the END
  
  SELECT TableName, ColumnName, ColumnValue, TableId FROM #Results
  </code></pre>
  
  <p>I want to change it to use sp_executesql with parameters to prevent SQL injection. I changed it to:</p>
  
  <pre><code>declare @query nvarchar(1000)
  set @query= 'SELECT ''' + @TableName +''', ''' + @TableName + '.' + @ColumnName + ''', LEFT(CONVERT(varchar(max), ' 
              + @ColumnName + '), 3630), [id] FROM ' + @TableName + ' (NOLOCK) ' +
              ' WHERE CONVERT(varchar(max), ' + @ColumnName + ') LIKE @term'
  INSERT INTO #Results            
  EXEC sp_executesql @query, N'@term nvarchar(100)', @term=@SearchStr2;
  </code></pre>
  
  <p>However I'm not getting any results now. I tried including the "insert into" in the query and using global temp tables but no dice. What can I do to prevent SQL injection and get the results back? Or am I wrong in my approach?</p>