Prev: Drop all table and repopulate
Next: query perfomance issue with Select statment in the Select list
From: Dan Guzman on 1 Jul 2008 22:24
> building all in the client , not just WHERE clause will be a mess.

IMHO, building a SQL statement for an ad-hoc user query is going to be
somewhat messy no matter where you build the SQL. I think it's important
that you do this securely regardless of where you choose to build the
statement. If you build the SQL in the proc, I suggest you specify
CommandType.StoredProcedure and use the parameterized form of sp_executesql
to prevent injection.

> 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

I agree that procs can be a good interface. However, if the interface is
watered down too much with a lot of optional parameters, it becomes mushy
and isn't much of an interface at all.

--
Hope this helps.

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

"Bassam" <egbas(a)yahoo.com> wrote in message
news:%23LC4o332IHA.4476(a)TK2MSFTNGP02.phx.gbl...
> 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
>>>
>>>
>>
>

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