Help formatting a mysql query string
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
> - You use executemany to insert several lines.
> 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
>> 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?
Thank you for explaining this so clearly it helps me a great deal.
All the best
More information about the Python-list