[Baypiggies] newbie troubles with dbapi parameter formatting

Shannon -jj Behrens jjinux at gmail.com
Fri Mar 24 20:36:42 CET 2006


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
> > supply. In your case the answer is 1 so it tries to turn whatever you
> > pass in into 1 SQL value. It works great for things like "SELECT * FROM
> > table WHERE column IN (%s, %s, %s)" when you supply a tuple of 3
> > parameters, but not for your case. What I do in this case is generate
> > the SQL template dynamically:
> >
> > listTemplate = ','.join(['%s']*len(a_list))
> > sqlTemplate = 'SELECT * FROM table WHERE column IN (%s)' % listTemplate
> > cursor.execute(sqlTemplate, a_list)
>
> 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
> cursor.execute(sql)
>
> 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.

-jj


More information about the Baypiggies mailing list