Help formatting a mysql query string

Rigga rigga at hasnomail.com
Mon Jun 28 21:10:08 CEST 2004


Pierre-Frédéric Caillaud wrote:

> 
> The PHP way is awful (you have to escape your strings and
> if you forget, you have a vulnerability)..
> 
> Is you want to have dynamic fields you can do :
> 
> cursor.execute( "INSERT INTO mytable SET %s=%s", (fieldname, value) )
> 
> or you could also do this :
> 
> query = "INSERT INTO mytable SET %s=%%s" % fieldname
> cursor.execute( query, (value,) )
> 
> The last one is preferred if
> - your SQL library precompiles and reuses queries (I don't know if it
> does)
> - You use executemany to insert several lines.
> 
> HOWEVER
> The last variant has a security vulnerability : fieldname is not quoted.
> 
> Solution :
> On entering your script, test :
> if fieldname not in ('field1', 'field2'):
> raise ValueError, "Invalid field name"
> 
> 
>> Thanks for the help, sorry I posted this twice, my news reader was not
>> showing the original post so i resubmitted it.  I normally work with php
>> thats why I was trying to build it as a string. I now see how I can load
>> the data values from my variables, however is the same possible for the
>> fields? I know you say its best to specify the column names etc however
>> my
>> script is parsing a web page and getting the field headings (which will
>> stay the same), data and table name so I wanted to make the script handle
>> all this rather than having to have a seperate cursor.execute() for each
>> table I want to update - does this make sense?
>>
>> Regards
>>
>> Rigga
Thank you for explaining this so clearly it helps me a great deal.

All the best

R



More information about the Python-list mailing list