Prev: Drop all table and repopulate
Next: query perfomance issue with Select statment in the Select list
From: Bassam on 1 Jul 2008 09:25
Hello,

http://www.sommarskog.se/dynamic_sql.html

I've read Erland's new article about Dynamic SQL , The Curse and Blessings
of Dynamic SQL , I have to say , I disagree !
The solution he offers thru sp_executesql is very complicated in many
situations , like , if we have 10 parameter options for the user to choose
from in the client , its not just ' = ' that the user can select for the
parameter , it can be > , < , = , Like , Not Like , Between , Not Between ,
etc.
If I prepare a possible route in the stored procedure for all the possible
10 parameters available to the user for 1 report , then I have a nightmare
procedure to build !

An alternative is to carefully build the dynamic WHERE part in the client to
avoid injection eliminating apostrophes or -- , I already built that
complex procedure with vb.net in the client to produce the WHERE , then I
used it over many complex reports that depend on dynamic stored procedures

SQL Injection issue
Build the dynamic WHERE carefully to avoid it , till MS can offer a very
simple solution like
Begin Single Statement
END Single Statement

then this injection will produce an error saying “Multiple statements are
not allowed”
Begin Single Statement
SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE
orders --'
END Single Statement

Query-Plan Reuse issue
how long it takes to create a plan ? one second ? that's not an issue in our
case where about 200 users are accessing SQL Server at a given time and
certainly not them all are using that report at the same moment , maybe its
an issue with 2000 user ?

it can be also a simple solution from MS by offering Forced parameterization
in the procedure level ,currently it is in database level , Then the plan
will be reused for the dynamic statement if called again with same
parameters , don't know im i right in this one or wrong , you are the
experts! , a statement like that can do it per procedure :
AS
Force Parameterization

Permission Issue
what's the problem of giving permission to users to table level ? so using
dynamic SQL in procedures will prevent that , anyway we create those roles
for every module , like the Invoice module for example

role_Invoice_Select , role_Invoice_Update , role_Invoice_Insert ,
role_Invoice_Delete

and put users carefully in those roles as necessary, plus client side custom
security like

* Can open the form
* Can Print
* Can Export to PDF
* Can search for old invoices

etc , to further control any module , so we don't care about giving
permissions to procedures in Dynamic SQL mode

-----

Finally , i know that Erland Sommarskog is a super brilliant and smart MVP
, we use a lot of his answers in this newsgroup , I just don't see his
emphasizing on using dynamic SQL thru executesql to be the 95% solution as
he is saying in the article for the reason i mentioned in first paragraph of
this post , at least not for us with midsize business and LAN/WAN/VPN only
client access to SQL Server (not web)

Welcome to any comments but without harsh statements used to be used by
CELKO :) , I already confess , I'm not an expert

Thanks
Bassam


From: Dan Guzman on 1 Jul 2008 08:47
> An alternative is to carefully build the dynamic WHERE part in the client
> to avoid injection eliminating apostrophes or -- , I already built that
> complex procedure with vb.net in the client to produce the WHERE , then I
> used it over many complex reports that depend on dynamic stored
> procedures

Why not build a parameterized SQL statement in the application code? This
will avoid the injection issue entirely.

I have to go now but will comment on your other points later.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Bassam" <egbas(a)yahoo.com> wrote in message
news:O0tncT32IHA.1720(a)TK2MSFTNGP05.phx.gbl...
> Hello,
>
> http://www.sommarskog.se/dynamic_sql.html
>
> I've read Erland's new article about Dynamic SQL , The Curse and Blessings
> of Dynamic SQL , I have to say , I disagree !
> The solution he offers thru sp_executesql is very complicated in many
> situations , like , if we have 10 parameter options for the user to
> choose from in the client , its not just ' = ' that the user can select
> for the parameter , it can be > , < , = , Like , Not Like , Between , Not
> Between , etc.
> If I prepare a possible route in the stored procedure for all the possible
> 10 parameters available to the user for 1 report , then I have a
> nightmare procedure to build !
>
> An alternative is to carefully build the dynamic WHERE part in the client
> to avoid injection eliminating apostrophes or -- , I already built that
> complex procedure with vb.net in the client to produce the WHERE , then I
> used it over many complex reports that depend on dynamic stored
> procedures
>
> SQL Injection issue
> Build the dynamic WHERE carefully to avoid it , till MS can offer a very
> simple solution like
> Begin Single Statement
> END Single Statement
>
> then this injection will produce an error saying “Multiple statements are
> not allowed”
> Begin Single Statement
> SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE
> orders --'
> END Single Statement
>
> Query-Plan Reuse issue
> how long it takes to create a plan ? one second ? that's not an issue in
> our case where about 200 users are accessing SQL Server at a given time
> and certainly not them all are using that report at the same moment ,
> maybe its an issue with 2000 user ?
>
> it can be also a simple solution from MS by offering Forced
> parameterization in the procedure level ,currently it is in database level
> , Then the plan will be reused for the dynamic statement if called again
> with same parameters , don't know im i right in this one or wrong , you
> are the experts! , a statement like that can do it per procedure :
> AS
> Force Parameterization
>
> Permission Issue
> what's the problem of giving permission to users to table level ? so using
> dynamic SQL in procedures will prevent that , anyway we create those roles
> for every module , like the Invoice module for example
>
> role_Invoice_Select , role_Invoice_Update , role_Invoice_Insert ,
> role_Invoice_Delete
>
> and put users carefully in those roles as necessary, plus client side
> custom security like
>
> * Can open the form
> * Can Print
> * Can Export to PDF
> * Can search for old invoices
>
> etc , to further control any module , so we don't care about giving
> permissions to procedures in Dynamic SQL mode
>
> -----
>
> Finally , i know that Erland Sommarskog is a super brilliant and smart
> MVP , we use a lot of his answers in this newsgroup , I just don't see his
> emphasizing on using dynamic SQL thru executesql to be the 95% solution as
> he is saying in the article for the reason i mentioned in first paragraph
> of this post , at least not for us with midsize business and LAN/WAN/VPN
> only client access to SQL Server (not web)
>
> Welcome to any comments but without harsh statements used to be used by
> CELKO :) , I already confess , I'm not an expert
>
> Thanks
> Bassam
>
>

From: Bassam on 1 Jul 2008 10:30
Stored Procedures are better because :

1. when the code is in a stored procedure it is certainly much easier for
administration purposes , and any update after that
2. following the concept to deal with the database thru one interface , that
is stored procedures is very convenient , we standardize on that in static
and dynamic modes

building all in the client , not just WHERE clause will be a mess.

thank you
Bassam


"Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message
news:21A2CB57-AF33-4B8D-AB56-2433A6EC2355(a)microsoft.com...
>> An alternative is to carefully build the dynamic WHERE part in the client
>> to avoid injection eliminating apostrophes or -- , I already built that
>> complex procedure with vb.net in the client to produce the WHERE , then I
>> used it over many complex reports that depend on dynamic stored
>> procedures
>
> Why not build a parameterized SQL statement in the application code? This
> will avoid the injection issue entirely.
>
> I have to go now but will comment on your other points later.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Bassam" <egbas(a)yahoo.com> wrote in message
> news:O0tncT32IHA.1720(a)TK2MSFTNGP05.phx.gbl...
>> Hello,
>>
>> http://www.sommarskog.se/dynamic_sql.html
>>
>> I've read Erland's new article about Dynamic SQL , The Curse and
>> Blessings of Dynamic SQL , I have to say , I disagree !
>> The solution he offers thru sp_executesql is very complicated in many
>> situations , like , if we have 10 parameter options for the user to
>> choose from in the client , its not just ' = ' that the user can select
>> for the parameter , it can be > , < , = , Like , Not Like , Between , Not
>> Between , etc.
>> If I prepare a possible route in the stored procedure for all the
>> possible 10 parameters available to the user for 1 report , then I have
>> a nightmare procedure to build !
>>
>> An alternative is to carefully build the dynamic WHERE part in the client
>> to avoid injection eliminating apostrophes or -- , I already built that
>> complex procedure with vb.net in the client to produce the WHERE , then I
>> used it over many complex reports that depend on dynamic stored
>> procedures
>>
>> SQL Injection issue
>> Build the dynamic WHERE carefully to avoid it , till MS can offer a very
>> simple solution like
>> Begin Single Statement
>> END Single Statement
>>
>> then this injection will produce an error saying “Multiple statements are
>> not allowed”
>> Begin Single Statement
>> SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE
>> orders --'
>> END Single Statement
>>
>> Query-Plan Reuse issue
>> how long it takes to create a plan ? one second ? that's not an issue in
>> our case where about 200 users are accessing SQL Server at a given time
>> and certainly not them all are using that report at the same moment ,
>> maybe its an issue with 2000 user ?
>>
>> it can be also a simple solution from MS by offering Forced
>> parameterization in the procedure level ,currently it is in database
>> level , Then the plan will be reused for the dynamic statement if called
>> again with same parameters , don't know im i right in this one or wrong ,
>> you are the experts! , a statement like that can do it per procedure :
>> AS
>> Force Parameterization
>>
>> Permission Issue
>> what's the problem of giving permission to users to table level ? so
>> using dynamic SQL in procedures will prevent that , anyway we create
>> those roles for every module , like the Invoice module for example
>>
>> role_Invoice_Select , role_Invoice_Update , role_Invoice_Insert ,
>> role_Invoice_Delete
>>
>> and put users carefully in those roles as necessary, plus client side
>> custom security like
>>
>> * Can open the form
>> * Can Print
>> * Can Export to PDF
>> * Can search for old invoices
>>
>> etc , to further control any module , so we don't care about giving
>> permissions to procedures in Dynamic SQL mode
>>
>> -----
>>
>> Finally , i know that Erland Sommarskog is a super brilliant and smart
>> MVP , we use a lot of his answers in this newsgroup , I just don't see
>> his emphasizing on using dynamic SQL thru executesql to be the 95%
>> solution as he is saying in the article for the reason i mentioned in
>> first paragraph of this post , at least not for us with midsize business
>> and LAN/WAN/VPN only client access to SQL Server (not web)
>>
>> Welcome to any comments but without harsh statements used to be used by
>> CELKO :) , I already confess , I'm not an expert
>>
>> Thanks
>> Bassam
>>
>>
>

From: Andrew J. Kelly on 1 Jul 2008 09:27
I don't think Erland was endorsing any particular method. I feel the purpose
of the article was to show people the choices available and to let them
decide what was best for their own environment. No one solution is right
for everyone. What you show is right for you will not work for many others.
Dynamic SQL can be used well or it can be abused but that is up to the
person doing the implementation. For instance you say that 1 second is not a
problem for compile time. For most of my customers 1 second for compile time
on each execution would be a killer for sure. Take the great information
Erland provided and use it as you need to but I don't know how you can
simply say you disagree with an information source such that this one in
which he clearly shows both sides. Even the title fits.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Bassam" <egbas(a)yahoo.com> wrote in message
news:O0tncT32IHA.1720(a)TK2MSFTNGP05.phx.gbl...
> Hello,
>
> http://www.sommarskog.se/dynamic_sql.html
>
> I've read Erland's new article about Dynamic SQL , The Curse and Blessings
> of Dynamic SQL , I have to say , I disagree !
> The solution he offers thru sp_executesql is very complicated in many
> situations , like , if we have 10 parameter options for the user to
> choose from in the client , its not just ' = ' that the user can select
> for the parameter , it can be > , < , = , Like , Not Like , Between , Not
> Between , etc.
> If I prepare a possible route in the stored procedure for all the possible
> 10 parameters available to the user for 1 report , then I have a
> nightmare procedure to build !
>
> An alternative is to carefully build the dynamic WHERE part in the client
> to avoid injection eliminating apostrophes or -- , I already built that
> complex procedure with vb.net in the client to produce the WHERE , then I
> used it over many complex reports that depend on dynamic stored
> procedures
>
> SQL Injection issue
> Build the dynamic WHERE carefully to avoid it , till MS can offer a very
> simple solution like
> Begin Single Statement
> END Single Statement
>
> then this injection will produce an error saying “Multiple statements are
> not allowed”
> Begin Single Statement
> SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE
> orders --'
> END Single Statement
>
> Query-Plan Reuse issue
> how long it takes to create a plan ? one second ? that's not an issue in
> our case where about 200 users are accessing SQL Server at a given time
> and certainly not them all are using that report at the same moment ,
> maybe its an issue with 2000 user ?
>
> it can be also a simple solution from MS by offering Forced
> parameterization in the procedure level ,currently it is in database level
> , Then the plan will be reused for the dynamic statement if called again
> with same parameters , don't know im i right in this one or wrong , you
> are the experts! , a statement like that can do it per procedure :
> AS
> Force Parameterization
>
> Permission Issue
> what's the problem of giving permission to users to table level ? so using
> dynamic SQL in procedures will prevent that , anyway we create those roles
> for every module , like the Invoice module for example
>
> role_Invoice_Select , role_Invoice_Update , role_Invoice_Insert ,
> role_Invoice_Delete
>
> and put users carefully in those roles as necessary, plus client side
> custom security like
>
> * Can open the form
> * Can Print
> * Can Export to PDF
> * Can search for old invoices
>
> etc , to further control any module , so we don't care about giving
> permissions to procedures in Dynamic SQL mode
>
> -----
>
> Finally , i know that Erland Sommarskog is a super brilliant and smart
> MVP , we use a lot of his answers in this newsgroup , I just don't see his
> emphasizing on using dynamic SQL thru executesql to be the 95% solution as
> he is saying in the article for the reason i mentioned in first paragraph
> of this post , at least not for us with midsize business and LAN/WAN/VPN
> only client access to SQL Server (not web)
>
> Welcome to any comments but without harsh statements used to be used by
> CELKO :) , I already confess , I'm not an expert
>
> Thanks
> Bassam
>
>

From: Bassam on 1 Jul 2008 10:58
Well, your answer is balanced permitting a solution to construct the WHERE
in client and use it to run a dynamic SQL using EXEC which can be fine for
some implementations if properly handled , but in the article near the top
, there are statement saying

"Before I say anything else, permit me to point out that these are examples
of bad usage of dynamic SQL" , pointing to using EXEC and not sp_executesql

That's why I disagree only to this concept .

while I agree with your answer down , there is a difference , I'm saying if
parameters can have multiple range on it like = , <> , between , not between
, etc , then it will be a nightmare to implement it in real world.

Bassam




"Andrew J. Kelly" <sqlmvpnooospam(a)shadhawk.com> wrote in message
news:%2375xD532IHA.784(a)TK2MSFTNGP04.phx.gbl...
>I don't think Erland was endorsing any particular method. I feel the
>purpose of the article was to show people the choices available and to let
>them decide what was best for their own environment. No one solution is
>right for everyone. What you show is right for you will not work for many
>others. Dynamic SQL can be used well or it can be abused but that is up to
>the person doing the implementation. For instance you say that 1 second is
>not a problem for compile time. For most of my customers 1 second for
>compile time on each execution would be a killer for sure. Take the great
>information Erland provided and use it as you need to but I don't know how
>you can simply say you disagree with an information source such that this
>one in which he clearly shows both sides. Even the title fits.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
>
> "Bassam" <egbas(a)yahoo.com> wrote in message
> news:O0tncT32IHA.1720(a)TK2MSFTNGP05.phx.gbl...
>> Hello,
>>
>> http://www.sommarskog.se/dynamic_sql.html
>>
>> I've read Erland's new article about Dynamic SQL , The Curse and
>> Blessings of Dynamic SQL , I have to say , I disagree !
>> The solution he offers thru sp_executesql is very complicated in many
>> situations , like , if we have 10 parameter options for the user to
>> choose from in the client , its not just ' = ' that the user can select
>> for the parameter , it can be > , < , = , Like , Not Like , Between , Not
>> Between , etc.
>> If I prepare a possible route in the stored procedure for all the
>> possible 10 parameters available to the user for 1 report , then I have
>> a nightmare procedure to build !
>>
>> An alternative is to carefully build the dynamic WHERE part in the client
>> to avoid injection eliminating apostrophes or -- , I already built that
>> complex procedure with vb.net in the client to produce the WHERE , then I
>> used it over many complex reports that depend on dynamic stored
>> procedures
>>
>> SQL Injection issue
>> Build the dynamic WHERE carefully to avoid it , till MS can offer a very
>> simple solution like
>> Begin Single Statement
>> END Single Statement
>>
>> then this injection will produce an error saying “Multiple statements are
>> not allowed”
>> Begin Single Statement
>> SELECT * FROM dbo.Orders WHERE 1 = 1 AND ShipName LIKE '' DROP TABLE
>> orders --'
>> END Single Statement
>>
>> Query-Plan Reuse issue
>> how long it takes to create a plan ? one second ? that's not an issue in
>> our case where about 200 users are accessing SQL Server at a given time
>> and certainly not them all are using that report at the same moment ,
>> maybe its an issue with 2000 user ?
>>
>> it can be also a simple solution from MS by offering Forced
>> parameterization in the procedure level ,currently it is in database
>> level , Then the plan will be reused for the dynamic statement if called
>> again with same parameters , don't know im i right in this one or wrong ,
>> you are the experts! , a statement like that can do it per procedure :
>> AS
>> Force Parameterization
>>
>> Permission Issue
>> what's the problem of giving permission to users to table level ? so
>> using dynamic SQL in procedures will prevent that , anyway we create
>> those roles for every module , like the Invoice module for example
>>
>> role_Invoice_Select , role_Invoice_Update , role_Invoice_Insert ,
>> role_Invoice_Delete
>>
>> and put users carefully in those roles as necessary, plus client side
>> custom security like
>>
>> * Can open the form
>> * Can Print
>> * Can Export to PDF
>> * Can search for old invoices
>>
>> etc , to further control any module , so we don't care about giving
>> permissions to procedures in Dynamic SQL mode
>>
>> -----
>>
>> Finally , i know that Erland Sommarskog is a super brilliant and smart
>> MVP , we use a lot of his answers in this newsgroup , I just don't see
>> his emphasizing on using dynamic SQL thru executesql to be the 95%
>> solution as he is saying in the article for the reason i mentioned in
>> first paragraph of this post , at least not for us with midsize business
>> and LAN/WAN/VPN only client access to SQL Server (not web)
>>
>> Welcome to any comments but without harsh statements used to be used by
>> CELKO :) , I already confess , I'm not an expert
>>
>> Thanks
>> Bassam
>>
>>
>

 |  Next  |  Last
Pages: 1 2
Prev: Drop all table and repopulate
Next: query perfomance issue with Select statment in the Select list