[Baypiggies] newbie troubles with dbapi parameter formatting
Chris.Clark at ingres.com
Fri Mar 24 21:12:22 CET 2006
Shannon -jj Behrens wrote:
> On 3/24/06, Aahz <aahz at pythoncraft.com> wrote:
>> On Fri, Mar 24, 2006, Jimmy Retzlaff wrote:
>>> I haven't seen a dbapi module that will help with your particular case
>>> directly. The parameter support in execute looks at the query template
>>> to determine how many SQL values to generate, not the value(s) you
>> My preference is to spell out the list explicitly rather than relying on
>> execute() to handle the interpolation:
>> in_clause = ','.join(["'%s'" for x in a_list)
>> sql = "SELECT * FROM table WHERE column IN (%s)" % in_clause
>> Note that I'm always careful to surround SQL queries in double-quotes or
>> triple-quotes in case I add a manual parameter with a string later...
> Also be sure to protect yourself against SQL injection attacks using
> whatever is appropriate to escape stuff in the a_list.
Excellent advice from jj, I would go with a hybrid approach (note
untested!) to create a parameter marker list and let the database driver
deal with escape stuff:
var_marker = r'%s'
#var_marker = '?'
#var_marker = r'%s'
# etc.based on whatever driver.paramstyle defines
# not very pythonic, hopefully this is easy to read
# albeit not efficient
in_clause = var_marker
for x in a_list[1:]:
in_clause = in_clause + ',%s'%(var_marker)
sql = "SELECT * FROM table WHERE column IN (%s)" % in_clause
More information about the Baypiggies