[DB-SIG] Test suite update for format conversion accuracy.

[Michael Bayer]

On May 20, 2013, at 12:16 PM, Daniele Varrazzo <daniele.varrazzo at gmail.com> wrote:

> 
> Oh, right, sqlite (the library, not the python module) can deal with a
> question mark in a string ok. Wrong example then.
> 
> Grepping for '?' in the _sqlite directory in Python source suggests that
> literals ? are not looked for anywhere; this implies that the module
> doesn't process them before passing the string to the client library
> (that in the sqlite specific case is *the* database, in client-server
> libraries this is not the case but I don't have any handy to test).
> 
> If any module wanted to support a placeholder different than the native
> one would find the need to parse the query string, which I don't think
> is a reasonable thing to ask to a client library.

I think part of the wisdom of "?" in any case is that the "?" symbol is not something that ever needs to be in a SQL statement that uses bound parameters.  Whereas the % symbol certainly is - for one thing, its the modulus operator on many platforms, including Postgresql.

The use case where a statement has a mix of directly embedded parameters and bound parameters isn't a real need.   If you're using bound parameters, that's what you should be doing across the board; embedding literals directly into a SQL statement is something we only do for command-line one-offs - in a real application, it's one of the most commonly exploited security holes in modern software.

The only need I can see where one would need to mix literals and bound parameters is when using drivers like that of Firebird and some variants of ODBC where you can't put bound parameters in certain places, like in the columns clause. For example, Firebird and certain flavors of ODBC won't let you do this:

	SELECT ? = ? 

because the driver wants to assign typing information to the incoming expressions, and it does this by looking at the context of the bound parameter.

Quote-counting is an easy way to work around this, as the rules for quoting in SQL are very simple.  

If solid reference implementations for parsing the ? could be developed, all the DBAPIs could use them and then we'd have guaranteed consistent behavior on all platforms.