artful at rogers.com
artful at rogers.com
Sun Nov 12 02:47:03 CST 2006
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