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


Dynamic SQL


Dynamic SQL

Author
Message
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
My fault but
that was just for an example.
Snargables
Snargables
Right there with Babe
Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)Right there with Babe (729 reputation)

Group: General Forum Members
Points: 729 Visits: 805
u should use perameterized dynamic sql to get away from the sql injection
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23267 Visits: 9730
jeetsingh.cs (12/12/2012)
My fault but
that was just for an example.


Yep. And it's a valid sample. Only reason I pointed out improvements in it is that the original question was from someone who appears to not have experience in the subject, so I wanted to make sure he gets the full info on it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23267 Visits: 9730
Snargables (12/12/2012)
u should use perameterized dynamic sql to get away from the sql injection


Yes. But when database objects are part of what's dynamic, you can't parameterize those. So you have to protect against injection in other ways, like querying the relevant system views to make sure the object names are real, and using QuoteName() to make sure it's encapsulated properly.

Helps protect against injection, and against errors in object names.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
venoym
venoym
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1459 Visits: 2082
GSquared (12/12/2012)
jeetsingh.cs (12/12/2012)
See Dyanmic Sql is used where we dont want to hard code the values in
our query.
For example if we wan to run this command against a database
than
Create procedure dynamic_query(
@dbname As nvarchar(25)
)
as
BEGIN

declare @sql_txt nvarchar(MAX)

SET @sql_txt=' Use '+@dbname +' select * from sys.objects'
EXEC(@SQL)
END


I would recommend modifying that so that it first checks to see if @dbname is a valid database name (check vs sys.databases), then use QuoteName() to make sure it's going to deal with non-alpha characters correctly.

These two steps will avoid a number of potential errors, and also make it injection-safe.

Also, Exec() won't accept an NVarchar(max) input string.


I might have missed something, but I believe SQL Server 2005 was the first to allow NVARCHAR(MAX) in EXEC calls and in SP_ExecuteSQL as well.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23267 Visits: 9730
You're right. I'm so used to using sp_executeSQL (which definitely does nvarchar(max)), that I missed that exec() had been changed. I'm used to getting an error message on large data types on that one, from way back when.

But the other points, about checking the validity of the object and using QuoteName(), definitely matter.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
you all people are right at your points.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7752 Visits: 7140
Whenever possible, you should use:
EXEC sp_executesql @sql [rather than EXEC(@sql)]
because it is far less susceptible to SQL injection, although not foolproof depending on what you are trying to make dynamic.

If you just need to have a stored proc run in the context of a given db, you can do that far more effectively by:
1) prefixing the proc name with sp_
2) creating the proc in the master db
3) setting the proc as a "system object"

Then you can use it from any db and it functions within the context of that db.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5061 Visits: 7697
Sean Pearce (12/11/2012)

+1

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
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