Why Is Escaping Data Considered So Magical?

[Roy Smith]

In article <i00t2k$l07$1 at lust.ihug.co.nz>,
 Lawrence D'Oliveiro <ldo at geek-central.gen.new_zealand> wrote:

> I construct ad-hoc queries all the time. It really isn’t that hard to do
> safely. All you have to do is read the documentation

I get worried when people talk about how easy it is to do something 
safely.  Let me suggest a couple of things you might not have considered:

1) Somebody is running your application (or the database server) with 
the locale set to something unexpected.  This might change how numbers, 
dates, currency, etc, get formatted, which could change the meaning of 
your constructed SQL statement.

2) Somebody runs your application with a different PYTHONPATH, which 
causes a different (i.e. malicious) urllib module to get loaded, which 
makes urllib.quote() do something you didn't expect.
 
> I’ve done this sort of thing for MySQL, for HTML and JavaScript (in both
> Python and JavaScript itself), and for Bash. It’s not hard to verify you’ve
> done it correctly. It lets you easily create table-updating code like the
> following, which makes it so easy to update the code to track changes in the
> database structure:
> 
>      sql.cursor.execute \
>       (
>             "update items set "
>         +
>             ", ".join
>                 (
>                     tuple
>                         (
>                             "%(name)s = %(value)s"
>                         %
>                             {
>                                 "name" : field[0],
>                                 "value" : SQLString(Params.getvalue
>                                   (
>                                     "%s[%s]" % (field[1], 
>                                 urllib.quote(modify_id))
>                                   ))
>                             }
>                         for field in
>                             (
>                                 ("class_name", "modify_class"),
>                                 ("make", "modify_make"),
>                                 ("model", "modify_model"),
>                                 ("details", "modify_details"),
>                                 ("serial_nr", "modify_serial"),
>                                 ("inventory_nr", "modify_invent"),
>                                 ("when_purchased", "modify_when_purchased"),
>                                 ... you get the idea ...
>                                 ("location_name", "modify_location"),
>                                 ("comment", "modify_comment"),
>                             )
>                         )
>                 +
>                     (
>                         "last_modified = %d" % int(time.time()),
>                     )
>                 )
>         +
>             " where inventory_nr = %s" % SQLString(modify_id)
>       )