SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameterized dynamic SQL


Parameterized dynamic SQL

Author
Message
Dennis Post
Dennis Post
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 547
I was writing some code to check out the check_constraints of tables spread out accross multiple DBs in a partitioned view
My idea was to use a parameterized query.
Turns out you cannot use parameters for object names.

Is this correct or is there another way to do this?
Something to do with SQL Injection? (No fear of)

Also, is it no longer necesseay to use + in dynamic SQL? (See test 4)

DECLARE 
@SQL NVarchar(1000),
@DBName NVarchar(50),
@CKName NVarchar(50),
@Tbl NVarchar(50)

SET @DBName = 'Master'
SET @CKName = 'FakeName'
SET @Tbl = 'Check_Constraints'

-- 1: Works
SET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints'
EXEC (@SQL)

-- 2: Works
SET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = ''@CK'''
EXEC sp_ExecuteSQL
@Stmt = @SQL,
@Parms = N'@CK NVarchar(50)', @CK = @CKName

-- 3: Works
SET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = @CK'
EXEC sp_ExecuteSQL
@Stmt = @SQL,
@Parms = N'@CK NVarchar(50)', @CK = @CKName

-- 4: Works ?? Same as WHERE Name = ''' + @CKName + '''' ??
SET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = ''@CKName'''
EXEC sp_ExecuteSQL
@Stmt = @SQL,
@Parms = N'@CK NVarchar(50)', @CK = @CKName

-- 5: Doesn't work
SET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = @CKName'
EXEC sp_ExecuteSQL
@Stmt = @SQL,
@Parms = N'@CK NVarchar(50)', @CK = @CKName
/*
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@CKName".
*/

-- 6: Doesn't work
SET @SQL = N'SELECT * FROM Master.sys.@Tbl2 WHERE Name = @CK2'
EXEC sp_ExecuteSQL
@Stmt = @SQL,
@Params =N'@Tbl2 NVarchar(50), @CK2 NVarchar(50)', @CK2 = @CKName, @Tbl2 = @Tbl
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@Tbl2'.
*/

-- 7: Doesn't work
SET @SQL = N'SELECT * FROM @DBName2.sys.Check_Constraints'
EXEC sp_ExecuteSQL
@Stmt = @SQL,
@Params =N'@DBName2 NVarchar(50)', @DBName2 = @DBName
/* Error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
*/



Thanks



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7211 Visits: 6431
Would this work?


DECLARE @DB VARCHAR(100) = 'MyDB'

SELECT *
FROM @DB.sys.check_constraints




If not, why would it work in dynamic SQL?

Obviously, you've found an approach that does work though.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84933 Visits: 41071
dwain.c (11/21/2012)
Would this work?


DECLARE @DB VARCHAR(100) = 'MyDB'

SELECT *
FROM @DB.sys.check_constraints




If not, why would it work in dynamic SQL?

Obviously, you've found an approach that does work though.


I haven't fired up 2k12 yet but I'd just bet that won't work. You cannot use variables for object names like that.

The @CK thing being used in the queries is a pass through variable which is a trait of using sp_Execute SQL. Notice that where it's being used is not an object but acts just like what a variable would do in a norma query.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7211 Visits: 6431
Jeff Moden (11/21/2012)
dwain.c (11/21/2012)
Would this work?


DECLARE @DB VARCHAR(100) = 'MyDB'

SELECT *
FROM @DB.sys.check_constraints




If not, why would it work in dynamic SQL?

Obviously, you've found an approach that does work though.


I haven't fired up 2k12 yet but I'd just bet that won't work. You can use variables for object names like that.

The @CK thing being used in the queries is a pass through variable which is a trait of using sp_Execute SQL. Notice that where it's being used is not an object but acts just like what a variable would do in a norma query.


Did you mean "You cannot use variables for object names like that." ?

I know. But wouldn't it be cool if you could. Think about it - naming columns on the fly! God what we could do with that to confuse the masses! w00t


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84933 Visits: 41071
D.Post (11/21/2012)
Is this correct or is there another way to do this?
Something to do with SQL Injection? (No fear of)


No. No way to stipulate the database name in a variable without using dynamic SQL of some sort.

So far as SQL injection goes, your code is wide open for it the way @DBName is currently used. The best way around it is to check that the content of @DBName actually exists in sys.databases or throw a fatal error.

Items 2 and 4 may run but they don't actually work the way you would expect. Since you double single-quoted the @CK variable names, the variable names become quoted literals rather than variables.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84933 Visits: 41071
dwain.c (11/21/2012)
Jeff Moden (11/21/2012)
dwain.c (11/21/2012)
Would this work?


DECLARE @DB VARCHAR(100) = 'MyDB'

SELECT *
FROM @DB.sys.check_constraints




If not, why would it work in dynamic SQL?

Obviously, you've found an approach that does work though.


I haven't fired up 2k12 yet but I'd just bet that won't work. You can use variables for object names like that.

The @CK thing being used in the queries is a pass through variable which is a trait of using sp_Execute SQL. Notice that where it's being used is not an object but acts just like what a variable would do in a norma query.


Did you mean "You cannot use variables for object names like that." ?

I know. But wouldn't it be cool if you could. Think about it - naming columns on the fly! God what we could do with that to confuse the masses! w00t


Yes... that's what I meant. I went back and corrected the original post. Thanks for the catch on my phat phingering.

Not only would it be cool to be able to name object names with a variable but it would also be cool if we could use variables and column names in the same SELECT like we can with UPDATE. Instead of "quirky updates", we could use "not so quirky SELECTs" were we can use an ORDER BY and the whole shebang!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7211 Visits: 6431
Jeff Moden (11/21/2012)

Not only would it be cool to be able to name object names with a variable but it would also be cool if we could use variables and column names in the same SELECT like we can with UPDATE. Instead of "quirky updates", we could use "not so quirky SELECTs" were we can use an ORDER BY and the whole shebang!


Somewhere, somewhen I saw an article where someone had interviewed you and asked what you thought should be included in the next version of SQL or what your biggest gripes were. Add that one to the list.

For the record, yeah that would also be way cool!

The only question would be, if CELKO sits on the ISO standards committee would he approve of it? He might consider it a "hillbilly dialect." Hehe


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Dennis Post
Dennis Post
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 547
Ok, so we have to use dynamic queries for object names.
Make sure to check the object against the sys views.

DECLARE 
@DBName NVarchar(50)
SET @DBName = 'Master1'
IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
PRINT 'Database : ' + @DBName + ' does not exist'
RETURN
END



I'm assuming here that no injection is possible using PRINT.
People could inject another valid object name though.
Could cause a bit of a hassle when using DELETE or TRUNCATE.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4960 Visits: 5478
D.Post (11/22/2012)
Ok, so we have to use dynamic queries for object names.
Make sure to check the object against the sys views.

DECLARE 
@DBName NVarchar(50)
SET @DBName = 'Master1'
IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
PRINT 'Database : ' + @DBName + ' does not exist'
RETURN
END



I'm assuming here that no injection is possible using PRINT.
People could inject another valid object name though.
Could cause a bit of a hassle when using DELETE or TRUNCATE.



That is not dynamic SQL...

But if you build an SQL string like that and execute it using sp_executesql or EXEC() command, you can inject terrible code into it. Here is your code implemented as Dynamic SQL:


DECLARE
@DBName NVarchar(50)

SET @DBName = 'Master1'

IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
-- example of dynamic sql
DECLARE @sql NVARCHAR(1000)
SET @sql = 'PRINT ''Database : ' + @DBName + ' does not exist'''
EXEC (@sql)
END



Now let's try to inject something to it (taking @DBName is input parameter):


CREATE TABLE check_injection (i int)
GO

-- Check for sql injection
select * from check_injection
go

DECLARE
@DBName NVarchar(50)

-- SET @DBName = 'Master1'
-- Someone injected this bit:
SET @DBName = ''';DROP TABLE check_injection; PRINT ''bb:-)'';--'

IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
-- example of dynamic sql
select QUOTENAME(@DBName)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'PRINT ''Database : ' + @DBName + ' does not exist'''
EXEC (@sql)
END

select * from check_injection



With enough rights, you may drop the whole database :-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Dennis Post
Dennis Post
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 547
Thanks for the input guys! :-D

@Eugene
That is not dynamic SQL...

I know. I was just demonstrating Jeffs suggestion of checking for a valid object and stopping if it isn't valid.
So far as SQL injection goes, your code is wide open for it the way @DBName is currently used. The best way around it is to check that the content of @DBName actually exists in sys.databases or throw a fatal error.

Thanks for the injection example!!
Time to test some of my apps and SPs! :-P



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search