mxODBC and apostrophe's in strings

[Paul Boddie]

benc1 at today.com.au (Ben C) wrote in message news:<8beb91f5.0109112328.1a2c0c5c at posting.google.com>...
> 
> ...
> _text = "This is some really 'extraordinary' text read from a file
> somewhere"
> 
> _date = 10000232
> 
> SQLExpress = "INSERT INTO table (Date, Time) VALUES (" + _date + ",'"
> + _text + "')"
> 
> cursor.execute(SQLExpress)
> ...

It is bad form to "paste in" values into query or action strings...

> The probelm occurs if an apostrophe appears in the text (I assume
> because it has been used as a delimiter in the SQL Expression).

As you have seen, you need to wrap up your value in a way that
satisfies the SQL parser. First of all, that means that you have to do
"special" things just to pass simple strings as values - above, you're
using apostrophes around your string values, for example. Then, you
have to make sure that your values satisfy the encoding requirements
of the SQL parser - strings containing apostrophes are probably the
most common case of error in this case, but I could imagine a naive
conversion of other values for "pasting in" could also produce errors.

> Does anyone know of a workaround for this keeping in mind that any
> combination of ASCI characters could appear in the _text string.

The "proper" way of doing this is to use parameters or "bind
variables". Rewriting your example...

  cursor.execute("INSERT INTO table (Date, Time) VALUES (?, ?)", \
    (_date, _text))

Note that this is *not* anything like a Python-style string
substitution. Instead, the database module will attempt to send the
given values using the underlying database system's API. For those of
us who do not concern ourselves with the details, what this means is
that the values are sent *separately* from the query/action text and
therefore do not suffer from the restrictions experienced in your
example.

A side note: some database modules may try to secretly perform some
kind of "encoding" in the same fashion as you were trying to achieve.
I would argue that the better database systems encourage "proper"
treatment of parameters.

Finally, some database systems use a different syntax for parameters -
the mxODBC documentation explains this in more detail. Try also
searching comp.lang.python (via Google Groups, for example) or the
python-list for more about parameters, "bind variables", mxODBC and
the DB-API.

Paul