February 20, 2012 at 10:00 am
It would appear that sp_executesql is losing my quotes when attempting to do an insert into a table, see breakdown of my stored proc below. What am I doing wrong?
BEGIN TRAN
drop procedure my_proc
go
drop table my_test
create table my_test (myval varchar(300))
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure my_proc @val varchar(300)
as begin
declare @sql nvarchar(max)
-- Normal insert... This works!
insert into my_test (myval)
select @val
-- using SP_executesql... This doesnt work!
SELECT @sql = ' INSERT INTO my_test '
+ '(myval) VALUES(''' + ISNULL(@val,'') + ''')'
select @sql
EXEC sp_executesql @sql, N'
@val nvarchar(300)'
,@val
end
go
exec my_proc 'test without quotes'
exec my_proc 'test with ''quotes'
select * from my_test
ROLLBACK
February 20, 2012 at 10:07 am
To get full protection from SQL injection, you should use sp_executesql with parameters. Also, it will sort out your problems with quotes...
February 20, 2012 at 11:52 am
Try this:
create table #my_test (myval varchar(300))
go
declare @sql nvarchar(max)
declare @parmdef nvarchar(25)
DECLARE @param nvarchar(300)
SET @sql = 'INSERT INTO #my_test (myval) VALUES(@value)'
SET @parmdef = N'@value varchar(300)'
SET @param = 'test string'
select @sql
EXEC sp_executesql @sql,@parmdef,@value = @param
SELECT * FROM #my_test
DROP TABLE #my_test
Jared
CE - Microsoft
February 20, 2012 at 12:15 pm
Honestly, I'm seriously confused. Since you've already identified that there is a way to do this without the use of dynamic SQL, why do you insist on using dynamic SQL?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2012 at 12:19 pm
Jeff Moden (2/20/2012)
Honestly, I'm seriously confused. Since you've already identified that there is a way to do this without the use of dynamic SQL, why do you insist on using dynamic SQL?
lol I figured this was a simple way to learn to use sp_executesql. Taught myself pretty quick with this example!
Jared
CE - Microsoft
February 20, 2012 at 1:09 pm
Many thanks, I've been looking at options for avoiding sql insert when inserting a new row to a table. I want to use sp_executesql with parameters and wasnt sure how it was done, but came across the problem. "Sqlknowitall" has demontsrated how to do sp_executesql properly, I believe.
February 20, 2012 at 1:11 pm
AllanP999 (2/20/2012)
Many thanks, I've been looking at options for avoiding sql insert when inserting a new row to a table. I want to use sp_executesql with parameters and wasnt sure how it was done, but came across the problem. "Sqlknowitall" has demontsrated how to do sp_executesql properly, I believe.
Ok, now I have to ask... Why would you avoid an insert? Using a stored procedure and passing your insert values is much better than executing dynamic sql. Why would you choose to do it this way?
Jared
CE - Microsoft
February 20, 2012 at 1:19 pm
Perhaps I should have said that I'm trying to prevent sql insertions.
Is a stored procedure with parameters sufficient to prevent all sql insertions? In this instance, is sp_executesql with parameters completely unnecessary?
February 20, 2012 at 1:30 pm
AllanP999 (2/20/2012)
Perhaps I should have said that I'm trying to prevent sql insertions.Is a stored procedure with parameters sufficient to prevent all sql insertions? In this instance, is sp_executesql with parameters completely unnecessary?
Not if your stored procedure is using dynamic SQL. If it is not, you should be ok. Try this:
CREATE TABLE #test (insertedValue varchar(50));
CREATE PROCEDURE usp_test_inj @insVal VARCHAR(50)
AS
BEGIN
INSERT INTO #test
SELECT @insVal
END;
EXEC usp_test_inj 'jared; DROP TABLE #test'
EXEC usp_test_inj '''jared''; DROP TABLE #test'
SELECT * FROM #test
--DROP TABLE #test
--DROP PROCEDURE usp_test_inj
Because it is parameterized it passes the entire parameter as 1 string.
Jared
CE - Microsoft
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply