Michael Maddison
michael at ddisolutions.com.au
Sun Nov 12 15:54:54 CST 2006
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