formating query with empty parameter

[Tim Chase]

Aahz wrote:
> Tim Chase  <python.list at tim.thechases.com> wrote:
>> To stave off this problem, I often use:
>>
>>   values = [
>>    data['a'],
>>    data['b'],
>>    data['c'],
>>    data['d'],
>>    data['e'],
>>    data['f'],
>>    data['g'],
>>    ]
>>   params = ', '.join('%s' for _ in values)
>>   query = """
>>     BEGIN;
>>       INSERT INTO table
>>         (a,b,c,d,e,f,g)
>>       VALUES (%s);
>>     COMMIT;
>>     """ % params
>>   self.db.execute(query, values)
> 
> How do you handle correct SQL escaping?

If you dump "query", you see that "params" (possibly a better 
name would be "place_holders") is merely a list of "%s, %s, %s, 
..., %s" allowing the "execute(query, ***values***)" to properly 
escape the values.  The aim is to ensure that 
"count(placeholders) == len(values)" which the OP mentioned was 
the problem.

My second round of code (in my initial post) ensures that

the number of items in the column definition (in this case the 
"a,b,c,d,e,f,g")
is the same as
the number of placeholders
is the same as the number of values.

The column-names should be code-controlled, and thus I don't 
worry about sql escaping them (my own dumb fault here), whereas 
the values may come from an untrusted source and need to be 
escaped.  So the code I use often has a dictionary of

  mapping = {
    "tablefield1": uservalue1,
    "tablefield2": uservalue2,
    ...
    }

which I can then easily add/remove columns in a single place if I 
need, rather than remembering to adjust the query in two places 
(the table-fieldnames and add the extra placeholder) AND the 
building of the "values" parameter.  It also makes it harder to 
mis-sequence them, accidentally making the table-fieldnames 
"a,b,c" and the values list "a,c,b" (which, when I have 20 or so 
fields being updated has happened to me on more than one occasion)

-tkc