At 10:03 AM 2/17/2012 -0500, Ken Dibble wrote: > >It has to be said, the use of parameters is pretty well accepted as best > >practice as far as I know, and I also agree with not re-inventing the > >wheel to sanitise input yourself when you could let the DB backend do > >it, and take advantage of all the expertise and design that went into > >it.
... I've been busy with a lot of stuff but this thread was pretty interesting. But a thought occurred to me, what about combining both approaches? In other words, use string concatenation to structure the SQL command, but in that construction, use 'parameter' variable. For example, instead of a string looking like: SELECT payday, msbullcrap, worstos FROM mysecretdata WHERE adminwhat = 'admin' It would look like: SELECT payday, msbullcrap, worstos FROM mysecretdata WHERE adminwhat = ?varadmin And of course, varadmin would have been set to the string passed by the user. But the rest of the string would have been constructed according to whatever logic. It seems I usually want dynamic "structure" of my SQL (field lists, join conditions, etc). So I could still build my string up with that in mind but just don't sub in the user string directly. Just a thought. As a data point from me, I had a couple different situations where if I would have "hardcoded' the queries and only used parameters, my SQL Server stored proc would have been something like 60,000 lines long. With the string concatenation approach, I think it ended up at a couple hundred. But I'm totally endorsing the approach just because of convenience (that's the MS-way for cripes sake!). But in our case, the proc was only accessed through read-only accounts. Side question: did I hear correctly that you could supposedly put a "admin &&" in a SQL string and the result would be the connection would be changed to an admin logon? What the <censored>? Surely SQL DB designers couldn't have been that totally stupid. I thought the connection initiation was what determined the "rights" of the connection. But if someone is saying a simple SQL query can suddenly change its connection's access rights... Wow. I'm so happy I've continued to use VFP for most of my projects. And I have to add a parting word about "best practices" -Take 10 monkeys and put them in a cage -Put a big ramp in there and put a bunch of bananas up at the top. -Put shock collars around the monkeys so that every time any one of them goes on the ramp, they all get shocked. -After a while, you'll see that the monkeys will beat the crap out of any one of their group that approaches the ramp. -Remove the shock collars, and replace one of the monkeys with a new one. -He'll naturally go for the bananas and will promptly get the crap kicked out of him -Wait a week and replace another one of the original monkeys. He'll get beat up as well - even by the one that was new just a week prior. -Every week replace an original monkey until all the original group is gone. -You'll see from that point on, the ramp and bananas will be avoided and any new monkey will get the crap beat out him when they approach it. This will continue even though the reason for the avoidance has been removed and none of the ones that knew why to avoid it are around. This, my friends, is how best practices get defined. :-) OK. Maybe a little exaggerated. But the point is a best practice is truly only useful if you fully understand the reason behind it, and know its every advantage/disadvantage. Rarely in the computer industry is such depth of knowledge present, and the trend is getting worse. All the varying "certifications" are examples. And, as usual, PHB's think they are on top of it by stating "our project must use best practices." Side note: if you get on a project where that statement is thrown out, make sure get an exact list of best practices intended. Or, on the other hand, you could just leave it as is and when the project gets behind schedule you can simply point to the added complexity of this-or-that best practice. So that's why generally the term "best practice" is usually a misnomer in our industry. And remember to examine the source of whom declares a 'best practice' (and MS best practice may not be an open source best practice). I, for one, claim my most useful best practice is "Do not use a Windows OS" :-) -Charlie _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
