Aahz wrote:
Tim Chase <python.l...@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
--
http://mail.python.org/mailman/listinfo/python-list
