Entry

Is there a way to escape special characters when inserting a string into a database?

May 11th, 2000 07:09
unknown unknown, Lars Hoeyrup Jensen, Paul Boddie

You can use:

>>> import MySQLdb
>>> MySQLdb.escape_string("""some '"' quotes""")
'some \\\'\\"\\\' quotes'

Alternate:

Rather than using Python's value substitution in strings as suggested, 
it should be possible to use "bind variables" in MySQLdb like this:

  cursor.execute("SELECT * FROM MY_TABLE WHERE MY_COLUMN = %s", 
(my_column,))

Note the use of the Python/C style "%s", but note also that we aren't 
using such "placeholders" with Python's % operator - an additional tuple 
parameter is required. As a result, the database module should process 
the values in the tuple and hopefully get the database to bind the 
values to the query (or action) accordingly.

This is all just speculation on my part, however, as I have never really 
done any work with MySQL, but I do remember something about this in the 
MySQLdb documentation. Other database modules use different syntax for 
the placeholders; for example, Sybase products prefer "?", and replacing 
the placeholders in the above example would indeed make it work on 
Sybase products.

I would discourage the use of simple string substitution when building 
queries. After all, the database modules are there to make the handling 
of all kinds of values, and the issues surrounding them (such as quote 
escaping) transparent. If your database module doesn't support this kind 
of functionality then I would recommend either improving it so that it 
does, or finding another which does; I wouldn't consider modules which 
are deficient in this respect as being suitable for serious work.