Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Dynamic SQL Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 6:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:14 AM
Points: 251, Visits: 615
My fault but
that was just for an example.
Post #1395640
Posted Wednesday, December 12, 2012 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:24 AM
Points: 155, Visits: 390
u should use perameterized dynamic sql to get away from the sql injection
Post #1395684
Posted Wednesday, December 12, 2012 8:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Wednesday, December 12, 2012 8:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Wednesday, December 12, 2012 8:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:24 AM
Points: 1,030, Visits: 1,753
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
Posted Wednesday, December 12, 2012 9:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Posted Wednesday, December 12, 2012 11:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:14 AM
Points: 251, Visits: 615
you all people are right at your points.
Post #1396002
Posted Thursday, December 13, 2012 2:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:33 PM
Points: 2,208, Visits: 3,323
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1396390
Posted Thursday, December 13, 2012 4:27 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 612, Visits: 2,860
Sean Pearce (12/11/2012)

+1


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"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

My blog
Post #1396432
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse