[Qt-interest] QSqlQuery and escaping

[Andre Somers]

Op 2-12-2010 7:12, Jeffery MacEachern schreef:
> On Wed, Dec 1, 2010 at 22:09, Andre Somers<andre at familiesomers.nl>  wrote:
>> Op 2-12-2010 6:44, Jeffery MacEachern schreef:
>>> In this[0] documentation page, under the placeholder/binding section, it states
>>>
>>> "Besides performance, one advantage of placeholders is that you can
>>> easily specify arbitrary values without having to worry about escaping
>>> special characters."
>>>
>>> Am I understanding correctly that it will automagically take care of
>>> proper escaping without any additional code?  Are there any details on
>>> how this is handled?
>> AFAIK, there are two mechanisms in place.
>> The first and most important is that some drivers support placeholders
>> themselves. That means that the driver actually gets the SQL string
>> including the placeholders, and a blob of binairy data with a predefined
>> format. That makes escaping in string formats unneeded. The seconds
>> mechanism that works for drivers that don't support this feature and in
>> case you are not using placeholders, is a part of the Qt driver.
>> QSqlDriver contains a method formatValue() that takes care of creating a
>> string representation that is escaped.
>>
>> You should check the implementation of the Qt SQL drivers to make sure,
>> but this is my understanding of the topic.
> Alright, so just to clarify, if the database has native support,
> escaping is unnecessary, and if it doesn't, it relies on the driver
> developers doing things the Right Way?
Basically, yes, though you'd have to check if the escaping Qt does is 
really the Right Way (TM) in all cases. The way the base implementation 
does the formatting (and thus the escaping) is documented in the 
QSqlDriver docs. I don't know if any of the stock drivers reimplement 
it, but that's easy enough to check.

Note that while not properly documented, it would be wise to as much as 
possible use QSqlDriver::sqlStatement to create your SQL strings. That 
will increase portability of your application across databases. 
Unfortunately that method is rather limited in the kinds of SQL 
statements you can create, but it is a start. Caveat: field names are 
not properly escaped! I have filed a bug for that, but it seems to be 
low priority. It depends on what you want to achieve if that is a problem.

Again, this is just my understanding of the topic, I may be off.

André