How to identify that dynamic SQL fail?

Question

0.00/5 (No votes)

See more:

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?

SQL

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

What I have tried:

I don't want to fetch last identity of table.I have done that and removed it.

Posted 11-Dec-16 23:43pm

Member 11589429

Updated 12-Dec-16 0:23am

Andy Lanng

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-12-12T00:22:00

Solution 1

I would strongly suggest that you drop this approach: it leaves you wide open to SQL Injection attacks which can damage or destroy your DB, depending on what is passed to your SP.
In addition, I'd suggest that you change that to list the columns you insert into: if you are using IDENTITY fields, then they are likely to be at the front of columns list, and unless you name the columns, SQL will insert to rows in the current column order. If teh first row is IDENTITY, the SQL will always fail.

Posted 12-Dec-16 0:22am

OriginalGriff

Comments

Member 11589429 12-Dec-16 7:33am

I have changed my code.
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

Still sql injection will happen?

OriginalGriff 12-Dec-16 7:46am

Yes.
When you concatenate strings, you pass the text through to SQL as part of the command:
INSERT INTO Customers (MyColumn) VALUES ('whatever the user typed')
So if the user types "Value to insert" it's fine:
INSERT INTO Customers (MyColumn) VALUES ('Value to insert')
If the user types "X');DROP TABLE Customers;--"
Then what SQL executes is:
INSERT INTO Customers (MyColumn) VALUES ('X';DROP TABLE Customers;--')
Which looks to SQL like three separate commands:
INSERT INTO Customers (MyColumn) VALUES ('X';
DROP TABLE Customers;
--')
So SQL does the insert, drops the table and ignores the comment. Try it: back up your DB and give it a go...
And if you have a web interface, they can do this from *anywhere* ...

Member 9831003 13-Dec-16 4:14am

you can try below code.

DECLARE @Inserttbl nvarchar(4000)
DECLARE @TableName varchar(50)
DECLARE @Text varchar(50)
--DECLARE @count int
SET @TableName = 'test'
SET @Text = NULL

if (@Text is not null)
begin
SET @Inserttbl = ' Insert into dbo.' + @TableName + ' values (3,' +''''+@Text+'''' + ',1)'

EXEC sp_executesql @Inserttbl
end
else
begin
Print 'NULL Value will not inserted'
end

And yes there is possibility of sql injection even under sp_executesql. Check below link also for validating your parameter
https://msdn.microsoft.com/en-us/library/ms161953(SQL.105).aspx