[AccessD] Worth Upgrading for?

I have a situation where I use stored procedures to run the same code across databases that have not 
even been created right now.  As I get an order I create a brand new database, built result tables, 
pull data sets and store the data permanently in tables in the database for that order.  I run about 
15 stored procedures that dynamically create sql in order to build the data tables and fill them 
with data, export them out for processing, import the processed results back in etc.

While I am no SQL Server (or SQL) expert, and it might be possible to perform this stuff without 
dynamic SQL, I see absolutely no reason not to use dynamic SQL.  I have to feed parameters for what 
database and even the names of tables in to these stored procedures, which then build up SQL 
statements to direct the operations to the correct databases and tables.

My databases don't interface to the public, so where is the SQL injection coming from?

The queries take many minutes, and the compilation of the SQL takes a few seconds so the speed issue 
of not having the SQL optimized is not an issue (assuming that it even could be given that the fact 
that the database and tables don't even exist until I receive the order).

So send me off to the gallows I guess eh?

John W. Colby
www.ColbyConsulting.com


Drew Wutka wrote:
> I did ask.  List has been quiet lately though! LOL
> 
> As a side note on here, several of the interfaces I design have no
> typable user input.  Clicking options, and dedicated lists are about it.
> However, the real crunch is that so many systems may have more then just
> the WHERE statement needing to be set.  Sometimes I'm pulling different
> joins or even fields. 
> 
> Before this turns into a battle, I am not saying there is anything wrong
> with storing SQL and everything you want in the db itself.  It will
> definitely be optimized and secured.  But not all situations fit that
> bill very well.
> 
> Drew
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
> Sent: Tuesday, July 28, 2009 4:35 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Worth Upgrading for?
> 
> True, but I'd say most (but not all) people that are using dynamic SQL
> aren't sanitizing their inputs from users.
> 
> If the front end is designed to only use listboxes and list limited
> comboboxes,
> then why not send only their numeric IDs as stored procedure input
> parameters?
> It would be a lot less data going back to the server.
> 
> Drew was just asking why 'dynamically constructed' SQL statement is such
> a
> sore subject.
> 
> 
> 
> 
> On Tue, Jul 28, 2009 at 1:59 PM, Stuart McLachlan
> <stuart at lexacorp.com.pg>wrote:
> 
>> I keep hearing this same old mantras:
>>
>> 1.   Dynamic construction = SQL injection.
>>
>> Cr*p! ( if you'll pardon my french)
>>
>> Dynamic construction = SQL injection IF AND ONLY IF:
>>
>> a.  You take text input from your user as part of the construction
>> b.  You do not sanitize the text.
>>
>> There's no way that you are exposed to SQL injection if you base your
> SQL
>> statement on
>> such the current content of OptionBoxes, ListLimited ComboBoxes,
> selections
>> in Listboxes
>> etc,ect.
>>
>> 2. Dynaic SQL isn't optimised.
>>
>> Big deal!
>>
>> Design your indexes properly and what's the real performance
> difference
>> between:
>>
>> "myProcedure Param1, Param2, Param3"
>> and
>> "Select ,,,,,, from myTable where .. Param1 and .... Param2 ,,,, and
> Param3
>> order by ....."
>>
>>
>> --
>> Stuart
>>
>> On 28 Jul 2009 at 10:05, David McAfee wrote:
>>
>>> Because it opens its self up to SQL injection.
>>>
>>> Also dynamic SQL isn't optimized.
>>>
>>> On Tue, Jul 28, 2009 at 7:16 AM, Drew Wutka <DWUTKA at marlow.com>
> wrote:
>>>> Why is 'dynamically constructed' SQL statements such a sore
> subject?
>>>> This is a little bit of a shocker to me.
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>