Dynamic SQL
-
Hi,
has anyone got an example of a Dynamic SQL and what is it and why it would benefit using this?
Thanks
-
All your answers lie here:
http://www.sommarskog.se/dynamic_sql.html
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics. -
toddasd (12/5/2012)
All your answers lie here:Another person who posts the same dynamic sql article.
i highly recommend the suggested reading.
For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url] -
Sorry what is the above meant to be saying to me?
-
SQL_Kills (12/11/2012)
Sorry what is the above meant to be saying to me? -
SQL_Kills (12/11/2012)
Sorry what is the above meant to be saying to me?One of the common flaws in dynamic SQL is that it allows what's called "SQL Injection". Simple version = someone types SQL commands into a form field, and the commands get run in the database. Hence "Robert DROP TABLE dbo.Students" caused the table dbo.Students to be dropped.
It's an important thing to know about if you deal at all with dynamic SQL. Whether that's in the database layer (stored procedures that use dynamic SQL), or in the application (inline SQL), or anywhere in between (Data Access Layer, and so on).
There are methods to avoid this issue. The comic is about that.
- 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
-
@sean...THAT's AWESOME 😀
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
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
-
jeetsingh.cs (12/12/2012)
See Dyanmic Sql is used where we dont want to hard code the values inour 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.
- 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
-
My fault but
that was just for an example.
-
u should use perameterized dynamic sql to get away from the sql injection
-
jeetsingh.cs (12/12/2012)
My fault butthat 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
-
Snargables (12/12/2012)
u should use perameterized dynamic sql to get away from the sql injectionYes. 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
-
GSquared (12/12/2012)
jeetsingh.cs (12/12/2012)
See Dyanmic Sql is used where we dont want to hard code the values inour 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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply