Why Is Escaping Data Considered So Magical?

[Lawrence D'Oliveiro]

Just been reading this article
<http://www.theregister.co.uk/2010/06/23/xxs_sql_injection_attacks_testing_remedy/>
which says that a lot of security holes are arising these days because
everybody is concentrating on unit testing of their own particular
components, with less attention being devoted to overall integration
testing.

Fair enough. But it’s disconcerting to see some of the advice being offered
in the reader comments, like “force everyone to use stored procedures”, or
“force everyone to use prepared/parametrized statements”, “never construct
ad-hoc SQL queries” and the like.

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—for example,
<http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html>—and then write a
routine that takes arbitrary data and turns it into a valid string literal,
like this <http://www.codecodex.com/wiki/Useful_MySQL_Routines#Quoting>.

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)
      )