Avoid dynamic SQL

Question

Avoid dynamic SQL

Viewing 15 posts - 16 through 30 (of 79 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

The other thing to consider is that not all dynamic SQL receives paramaters from the outside world. Such dynamic SQL needs virtually no protection.

As a side bar... let's see some ways to avoid SQL injection straight from the horse's mouth...

http://msdn.microsoft.com/en-us/library/ms161953.aspx

The GOOGLE phrase for that little gem is "HOW TO AVOID SQL INJECTION MICROSOFT SQL SERVER"

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Welsh Corgi (2/26/2010)
OK, Jeff thank you for your criticism.
I did not intend to make anyone feel uncomfortable but as much as you add to this forum you make me feel very uncomfortable.
If I said something that was offensive then I appologive.
I'm not sure what I did but your language is very strong and is not condusive to a constructive dialoge.
When you present a problem the standard practice is to provide a solution.
Why are you so negative and hostile?

I'm not being negative or hostile. I just don't talk as much as you do and you're mistaking my shortness for hostility. I do get a little bit upset when someone recommends something that is totally wrong with the same authorative tone that you used and thought I did a pretty good job of not actually being hostile about it. Perhaps it's time for you to reexamine your own posts.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Welsh Corgi (2/26/2010)
I would respectfully request that the members of this forum be professional.
You should conduct yourself on a professional manner.
If a forum manner pots something that is totally stupid as an experienced professional you might want to consider exercising professional etiquette.

I am being professional. I even gave you the Google lookup for my favorite article on how to prevent SQL Injection. The professional etiquette missing here is the fact that you posted something totally wrong and now you're criticising the etiquette of the people who told you so. I didn't say you were stupid or anything. I just said you were wrong and you need to Google it.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Heh... I tried. Whatever WC. Have a good one.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Dynamic SQL is a very good tool for situations where you have stored procedure input parameters that may or may not be used for selection criteria. By constructing the statement dynamically, you let SQL Server have the best chance of creating an efficient query plan. If you have many optional parameters, trying to code each possible combination can quickly get out of hand.

In SQL 2005 you can avoid the limitations of SQL 2000 and before of the user having to have direct access to the tables by using the EXECUTE AS option in the stored proc that executes the dynamic SQL.

You can avoid SQL injection by making sure you do not use input parameters to construct SQL statements directly. Instead, use sp_executesql and pass the parameters through as input parameters as illustrated below. Query plans for statements constructed this way can be cached by SQL Server, so you do not incur the overhead of a recompile for the commonly used options.

Example:

create procedure MyProc

@ProcParam1int,

@ProcParam2int,

@ProcParam3int

with

execute as 'SomeUser'

as

declare@sql nvarchar(4000),

@param_list nvarchar(4000)

-- Construct SQL statement dynamically

set @sql = N'select col_a from mytable where col1 = @param1 '+

case when @ProcParam2 is null then N'' else N' and col2 = @param2 ' end+

case when @ProcParam3 is null then N'' else N' and col3 = @param3 ' end

set @param_list = N'@param1 int, @param2 int, @param3 int'

exec sp_executesql

@sql,

@param_list,

@param1= @ProcParam1,

@param2= @ProcParam2,

@param3= @ProcParam3

Paul White SSC Guru Points: 150467 More actions · Answer 11

Jeff Moden (2/26/2010)
Heh... poor ol' dynamic SQL... nobody loves it anymore.

Well maybe 'love' is too strong a word, but I do use it, hopefully appropriately.

Erland Sommarskog has a great guide to it, which hopefully addresses some of the concerns raised in this thread.

@Welsh Corgi: at the risk of joining the debate, might I respectfully suggest you dial-down your current sensitivity setting a notch or two? Jeff's a great guy, and nothing in his posts deserves the comments you have made. It's sometimes difficult to see the humour in other people's posts, please bear that in mind!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Welsh Corgi (2/26/2010)
Thank you Jeff. I read your link concerning the SQL Injections.
However there is a lot more to consider that what is addressed in this article.
You are a respected member of this forum but I do not appreciate the hits below the belt.
However you do not have to be so disreprectful in your replies for it is not condussice to a constructive dialogue,
You re are very smart but you are also very negative and cocky...

I haven't hit you below the belt... yet. Please back off with all the name calling and negativity. There's no need for it.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 13

There are people who love "Dynamic SQL". I being one of them. How can one forget the "Dynamic Cross Bars" and the "Dynamic Pivots". Dynamic SQL is really useful in writing repetitive statements. Personally, i would say "I love Dynamic SQL" and hopefully someday i will become a DBA and a responsible one at that for sure.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 15

...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/