<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
Dynamic SQL
19 posts, Page 2 of 2
««
1
2
Dynamic SQL
Rate Topic
Display Mode
Topic Options
Author
Message
jeetsingh.cs
jeetsingh.cs
Posted Wednesday, December 12, 2012 6:40 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179,
Visits: 380
My fault but
that was just for an example.
Post #1395640
Snargables
Snargables
Posted Wednesday, December 12, 2012 7:58 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:49 PM
Points: 118,
Visits: 250
u should use perameterized dynamic sql to get away from the sql injection
Post #1395684
GSquared
GSquared
Posted Wednesday, December 12, 2012 8:02 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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
Post #1395685
GSquared
GSquared
Posted Wednesday, December 12, 2012 8:04 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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
Post #1395687
venoym
venoym
Posted Wednesday, December 12, 2012 8:18 AM
Right there with Babe
Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 720,
Visits: 1,197
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.
Post #1395691
GSquared
GSquared
Posted Wednesday, December 12, 2012 9:02 AM
SSCoach
Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439,
Visits: 9,569
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
Post #1395737
jeetsingh.cs
jeetsingh.cs
Posted Wednesday, December 12, 2012 11:36 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179,
Visits: 380
you all people are right at your points.
Post #1396002
ScottPletcher
ScottPletcher
Posted Thursday, December 13, 2012 2:01 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 1,319,
Visits: 1,768
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)
One man with courage makes a majority. Andrew Jackson
Post #1396390
Alan.B
Alan.B
Posted Thursday, December 13, 2012 4:27 PM
SSC Veteran
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223,
Visits: 1,137
Sean Pearce (12/11/2012)
+1
-- AJB
xmlsqlninja.com
Post #1396432
« Prev Topic
|
Next Topic »
19 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
