[DB-SIG] paramstyles (mysql string length?)

M.-A. Lemburg mal at egenix.com
Tue Apr 25 12:15:52 CEST 2006


Ian Bicking wrote:
> Chris Clark wrote:
>> In reference to finding question marks in SQL queries and ignoring 
>> string literals
>>
>> M.-A. Lemburg wrote:
>>> Does anyone know a good implementation of such a search&replace
>>> mechanism ?
>>>  
>> I've not seen any existing code to deal with this BUT there are a couple 
>> of places to check:
>>
>>     * SnakeSQL <http://www.pythonweb.org/projects/snakesql/>
>>     * gadfly <http://gadfly.sourceforge.net/>
>>
>>
>> as they are pure Python database engines and so have some SQL parsing 
>> code in them already that maybe usable.
>>
>> But you can always cheat and iterate through the SQL string and just 
>> count (incrementing and decrementing) single quotes.
>> You need to remember to deal with the special case of embedded single 
>> quotes (by keeping a mini back buffer) like this:
>>
>>     'it''s Monty Python'
> 
> Postgres and MySQL allow 'it\'s Monty Python'.  Parsing SQL for every 
> query seems like a bit of a performance problem, especially because the 
> parsing itself can be different for different databases.

I don't think that's much of a problem: each database module will
have to implement whatever parsing is needed. In most cases,
this will only have to be some counting of quotes to avoid
replacing question marks inside SQL literals.

You can fast track this by searching for a quote first: in many
cases you won't need to bother with the more complicated SQL
literal aware search&replace at all, if there are no literals
in the SQL statement.

Perhaps we could collect a few quote quoting rules for the various
backends.

So far we have:

* '' - standard SQL quoted quote
* \' - C style quoted quote

We probably also need to look at different ways to write a SQL
literal. Using single quotes is standard SQL, but there might
also be some backends which use or support double quotes.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Apr 25 2006)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::


More information about the DB-SIG mailing list