Re: [UDD] Changing aux.py to fix quoting

[Paul Wise <pabs@debian.org>]

On Mon, Feb 6, 2012 at 3:58 AM, Andreas Tille wrote:
> On Sun, Feb 05, 2012 at 10:11:24AM +0800, Paul Wise wrote:
>> On Sun, Feb 5, 2012 at 12:29 AM, Andreas Tille wrote:
>>
>> > The quotation is actually used to feed strings into prepared statements.
>>
>> Thats confusing, isn't the whole point of prepared statements that you
>> don't have to escape things?
>
> Hmmm, good point.  I have not invented this quote function which has the
> additional purpose to care for proper utf-8 encoding.
>
> The current ddtp_gatherer.py is using things like:
>
>  query = "PREPARE ddtp_delete (text, text) AS DELETE FROM %s WHERE release = $1 AND language = $2" % my_config['table']
>  cur.execute(query)
>  ...
>  query = "EXECUTE ddtp_delete (%s, %s)" % (quote(rel), quote(lang))
>  cur.execute(query)
>
> At least the code I wrote is using quote exclusively in connection with
> EXECUTE of a previousely PREPAREd statement.  The purpose was the
> (enforced) UTF-8 conversion.  When using it with PostgreSQL I needed
> to change the quoting as described in the initial mail.

I wasn't talking about PREPARE statements, hadn't even heard of them.

It appears the code is using standard Python string interpolation (and
thus needs the slash insertion) instead of what I was referring to:

              query = " EXECUTE ddtp_packages_recieve_description_md5
(%s, %s, %s, %s, %s)" % \
                    tuple([quote(item) for item in (self.pkg.package,
self.pkg.distribution, self.pkg.component, \
                     self.pkg.release, self.pkg.version)])
              self.log.debug("execute query %s", query)
              cur.execute(query)

Looks like the reason for using string interpolation instead of
prepared statements was to be able to print out the queries for
debugging.

What I was referring to when I was talking about prepared statements
was stuff like this (from the derivatives census code):

cur.execute('SELECT version FROM srcpkg WHERE name=%s AND version=%s
LIMIT 1;', (name, version))

Notice there is no string interpolation here (despite using %s in the
string) and psycopg2/postgres handles the individual variables instead
of pre-encoding them and stuffing them into a query string using
Python string interpolation.

Here is how I would rewrite the above example from the ddtp_gatherer.py:

              cur.execute('EXECUTE
ddtp_packages_recieve_description_md5 (%s, %s, %s, %s, %s)',
(self.pkg.package, self.pkg.distribution, self.pkg.component,
self.pkg.release, self.pkg.version))

Instead of manually logging queries, I would probably use the
LoggingConnection class instead of the normal connection class.

-- 
bye,
pabs

http://wiki.debian.org/PaulWise