>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.
Since I've already displayed considerable ignorance, apparently, in this forum, I might as well continue to the logical conclusion. In VFP, I do not have to use single quotes to delimit strings in SQL queries. I can use double quotes or brackets. This obviates a whole class of SQL injection problems in most real-world situations, since [ or ] would rarely be valid input. (And this is actually what I've been doing in recent years; my login code is very, very old.) Aside from the SQL injection issue, it is very common for single quotes to be part of a string that a user would want included in a query. So why are SQL query languages constructed to require use of single quotes as string delimiters? In MS SQL Server, you can use the QUOTED_IDENTIFIER setting. It is often turned on by ODBC and OLE DB drivers. When it's on, you are forced to use single quotes to delimit strings, but if you can set QUOTED_IDENTIFIER off, then you can use double-quotes or brackets to delimit strings. This should have been the default setting, IMO. But at least the option is available in MS SQL Server. In MySQL and PostgresSql the only allowable string delimiter that I can find in my attempts to search the documentation is the single quote. Yeah, we can let the DB back-end "do it", if we're willing to give up most of the power that dynamic SQL makes available and resort to maintaining hundreds of static queries or views. (As I understand it, you can't use ? parameters to insert SQL object names, such as those of tables or columns, and the ? parameter purists also seem to frown on concatenating those things into a query string, even when they can only get there when the user picks them out of a read-only list.) But why is it considered a good idea to have DB back-ends carry that overhead of internally escaping single quotes? Wouldn't it have been waaaay simpler to standardize on some character that has no business being in anything a user might enter into a query, like, for example, one of those old high-ASCII border-drawing characters we used to use to make fancy screens in the DOS days? Comment characters are easy to strip out. Type-checking is also easy. If we could have a universal string delimiter, honestly, wouldn't something like that really be preferable to having to write hundreds of static queries, views or stored procedures? Ken Dibble www.stic-cil.org _______________________________________________ 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.
