[dba-SQLServer] Job Change, and a Question

Jim Lawrence accessd at shaw.ca
Sun Nov 12 16:53:05 CST 2006


Michael... Just a note... To the best of my knowledge SPs are faster at
least the second time they are run and this is usually the case when a site
is active. The SP when ran first is compiled and optimized and every other
immediate access uses the compiled version in memory. When only sending
parameters to an SP a lot less data is needed to be sent to DB and this
should also produce a speed increase of course virtually un-measurable. 

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Sunday, November 12, 2006 1:55 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Job Change, and a Question

I basically agree with Arthur but would question the statement about
sprocs being faster.
This is a generalisation of course, but,  SQL + Access (IIRC) even DO
store execution plans for
dynamic SQL.  
Another argument to use sprocs is that  you the dba can then optimise
the SQL within without
recompiling the exe/dll's of the app.

cheers

Michael Maddison

DDI Solutions Pty Ltd
michael at ddisolutions.com.au
Bus: 0260400620
Mob: 0412620497
www.ddisolutions.com.au 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
artful at rogers.com
Sent: Sunday, 12 November 2006 7:47 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Job Change, and a Question

I definitely agree with the sandbox but disagree with the idea of
letting them run dynamic SQL even against a sandbox DB. Far better to
inspect their requirements, with their help, and to design sprocs and
UDFs that attempt to fulfill them, IMO. These can be refined in the
sandbox. For example, their UI presents three combo-boxes, and you write
sprocs to deliver what is required to populate them, perhaps returning
an OUT parameter so it's easy for them to grab the selected value. You
work through the UI with their guidance and deliver sprocs that do what
they need.

>From what you wrote, it appears that this project is FE-driven. That in
itself is not a bad thing, but you must keep them constantly aware of
the costs and risks of dynamic SQL. You could even prove this point by
a) comparing the speed of a dynamic statement vs. an equivalent sproc;
and b) demonstrating a SQL injection that destroys all the rows in some
table that is critical to the operation.

The first is easily constructed. Suppose that the statement accepts
three variables and then constructs a statment along the lines of:

SELECT * FROM SomeTables WHERE Column1 = Var1 AND Column2 = Var2 and
Column3 = Var3

Admittedly a simplistic example, but the logic extends. All this would
require is a sproc that accepts 3 variables and does the equivalent
Select. This takes advantage of the compiler and the optimizer, while
the dynamic statement can never do either. Against a large number of
rows, the difference won't even require a timer -- you can see it.

The second requires a little imagination, but goes approximately like
this. The UI wants the name of the customer. The user types in:

Joe Blow'; Go; Delete Customers --

This string gets piped into a dynamic statement along the lines of:

Insert Into Customers 'Joe Blow'; Go; Delete Customers --'

Note a couple of things in this improvised example. The user is
knowledgable enough to anticipate that the argument will be wrapped in
single quotes, so includes one at the alleged end of the data-entry part
of the string, but then the rest of the string gets plugged in as
standard and legitimate SQL, and executes (absent countermeasures, of
course). The simplest countermeasure is a sproc that accepts the
parameter(s).

It's 3am and this was not a great example of SQL injection, but for more
info and better examples, google SQL Injection and you will find lots.

It is also true that some situations demand dynamic SQL, for example
when a screen allows the user to select among 20 or 30 controls, leaving
most blank (null) and choosing a random 5 to fill in. It is difficult
(but not impossible) to write the sproc that can deal with such a
situation. I emphasize that it is NOT impossible, but I realize that the
techniques required are beyond the ken of many SQL developers.

Once you adopt the sproc-route, you need 4 sprocs per table, plus
sprocs/UDFs to populate various listboxes and combo-boxes etc.
throughout the interface. The UI guys' responsibility is to gather the
user input, then call the appropriate sprocs. If the back-end is solid,
then even if the front-end has logical holes, the worst that can happen
is an ugly message indicating failure. I can live with that.

A.

----- Original Message ----
From: MartyConnelly <martyconnelly at shaw.ca>
To: dba-sqlserver at databaseadvisors.com
Sent: Saturday, November 11, 2006 6:21:00 PM
Subject: Re: [dba-SQLServer] Job Change, and a Question

Could you not give them a development or sandbox copy of the database
for them to bang away at. Or even a MSDE 2.0 personal copy.
When their code goes to production tests convert the SQL to SProcs.

Liz Doering wrote:

>Dear SQL Server List,
>
>It is no doubt due to lurking on this list that I have now landed 
>myself in the kettle of soup I'm in.
>
>Two weeks ago, I was a all-Access developer at a tiny almost-all-Access

>shop.  Now I am the SQL Server DBA on a new project internal to Wells
Fargo.
>That I was able to stumble through the interview satisfactorily was due

>to you folks having put enough concepts in my head so that I could 
>avoid sounding like a complete idiot.
>
>Of course, now that I've been on the job for a week, the veneer of 
>knowledge is looking thinner and thinner, and the number of questions I

>can't answer is growing daily.
>
>Here's the most recent poser:  They're using SmallTalk for this 
>project, which is actually a major extension of another application 
>which has been in use for 10+ years.  They are devoted to using Windows

>authentication.  The developers would like SmallTalk to be able to run 
>SQL statements directly from their code, however, they can only do that

>if the logged-in user has such rights.  Which raises the specter of a 
>savvy user running random SQL statements directly against the database.

>The solution has been to disallow running any "unapproved" SQL 
>statements by requiring that only sprocs can be run, but the 
>development team isn't happy with that solution, so they are asking me
for alternatives.
>
>I'm getting more confused as I write this, so I'll guarantee you that 
>there are questions I don't know enough to ask. Can you recommend some 
>reading for me?  Do any of you have any specific ideas for this
problem?
>
>Thanks so much!  Hopefully I'll be wise enough to contribute here 
>eventually!
> 
>
>Liz
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>  
>

--
Marty Connelly
Victoria, B.C.
Canada

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list