December 12, 2016 at 3:36 am
I have created one trigger,inside it executing dynamic sql.
Here is sample script
When I specifying @Text to some value,it work (after exec showing 1 row affetected)but when I am specifying null,
record not get added into table(Message:query executed sucessfully).I want like like his,if record not get added into sample table,pls add into Sample 2 without dynamic insertion.How to find out dynamic exec successfully or not.I don't want fetch last identity of table.How to do that?
declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @Text varchar(50)
declare @count int
set @TableName ='sample'
set @Text = null
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values (3,'+@Text+',1)'
exec sp_executesql @Inserttbl
December 12, 2016 at 3:40 am
By default concatenating with null, the entire text will become null.
Try @text='null'
December 12, 2016 at 3:48 am
Thanks for reply.U mean to say like this
declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @Text varchar(50)=null
declare @count int
set @TableName ='sample'
set @Text = null
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values (3,'+@Text+',1)'
exec sp_executesql @Inserttbl
No ,I am using dynamice sql.In simple sql it will work not here.I already done that.No use.
December 12, 2016 at 3:51 am
You can parameterise @Text to stop this happening. The syntax below is from my memory, so please check it's correct before using.SET @Inserttbl = N'INSERT INTO Test.dbo.' + @TableName + N' VALUES (3, @Text, 1)'
EXEC sp_executesql
@stmt = @Inserttbl
,@params = N'@Text varchar(50)'
,@Text = NULL
John
December 12, 2016 at 3:54 am
Probably this will clear your doubt.
declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @Text varchar(50)=null
declare @count int
set @TableName ='sample'
set @Text = null
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values (3,'+@Text+',1)'
select @Inserttbl
set @Text = 'null'
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values (3,'+@Text+',1)'
select @Inserttbl
Please see the link below for details on this behavior.
December 12, 2016 at 4:10 am
Thanks @joeroshan for ur help.I have done like this.Here I just printed msg but in reality I will do insert.
declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @Text varchar(50)=null
declare @count int
set @TableName ='sample'
set @Text = null
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values (3,'+@Text+',1)'
if(@Inserttbl is not null)
print 'Insert into sample'
else
print 'Insert into sample2'
December 12, 2016 at 5:00 am
pinky_sam05 (12/12/2016)
Thanks @joeroshan for ur help.I have done like this.Here I just printed msg but in reality I will do insert...
You are welcome. Glad you got it worked. I was trying to point out what is happening with the code. You can consider John's approach of using parameterized SQL to be safer.
December 12, 2016 at 5:12 am
Actually I have done like this.Let me still sql injection take place?
declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @PId int
declare @Text varchar(50)
declare @count int
declare @status int
set @TableName ='sample'
set @Text = null
set @PId = 3
set @status =1
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values ('+@PId+','+@Text+','+@Status+')'
exec sp_executesql @Inserttbl
December 12, 2016 at 5:21 am
You won't get any SQL injection with that. In fact, you won't get anything, because it fails with a conversion error.
Please will you post your whole trigger definition? You're vulnerable to SQL injection if the values for those parameters are provided by user input. But even if they're not, you should still parameterise. You'll probably get better performance through plan reuse, and if any other developer in your organisation comes along and copies your syntax, you'll be safe in the knowledge that their code is safe from SQL injection.
John
December 12, 2016 at 5:26 am
It depends on how and from where you get values for your parameters. Probably you are alright here, but it is good practice to use parameterized whenever possible.
December 12, 2016 at 5:31 am
I already done insertion through parameter only...
December 12, 2016 at 5:54 am
You've already got an example on how to parametrize your dynamic sql, but you kept using the string concatenation. That's a basic NONO in my ruleset.
Why do you want to use dynamic code? How many tables with the same structure do you have? That's simply ridiculous. When you insert, you should know where you're going to insert.
Anyhow, here's an example that should work for you.
declare @Inserttbl nvarchar(4000);
declare @TableName varchar(50);
declare @PId int;
declare @Text varchar(50);
declare @count int;
declare @status int;
set @TableName ='sample';
set @Text = null;
set @PId = 3;
set @status =1;
set @Inserttbl = ' Insert into Test.dbo.' + QUOTENAME(ISNULL(@TableName, 'sample2')) +' values (@PId,@Text,@Status);';
exec sp_executesql @Inserttbl, N'@PId int, @Text varchar(50), @status int', @PId, @Text, @status;
December 12, 2016 at 9:37 pm
Sql injection will not take place with this code.I already tried.
declare @Inserttbl nvarchar(4000)
declare @TableName varchar(50)
declare @PId varchar(5)
declare @Text varchar(50)
declare @status varchar(5)
set @TableName ='sample'
set @Text = 'Helping hand;Delete table Test2';
set @PId = 3
set @status =1
set @Inserttbl = ' Insert into Test.dbo.' +@TableName +' values ('+@PId+','+@Text+','+@Status+')'
exec sp_executesql @Inserttbl
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply