Escaping chars in Python and sqlite

Question

Hi, I have a python script that reads raw movie text files into an sqlite database.

I use re.escape(title) to add escape chars into the strings to make them db safe before executing the inserts.

Why does this not work:

In [16]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='\'Allo\ \'Allo!\"\ (1982)'") --------------------------------------------------------------------------- OperationalError
Traceback (most recent call last)

/home/rajat/Dropbox/amdb/ in ()

OperationalError: near "Allo": syntax error

Yet this works (removed \' in two places) :

In [17]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='Allo\ Allo!\"\ (1982)'") Out[17]:

I can't figure it out. I also can't ditch those leading quotes because they're actually part of the movie title. Thank you.

Answer 1

You're doing it wrong. Literally. You should be using parameters, like this:

c.execute("UPDATE movies SET rating = ? WHERE name = ?", (8.7, "'Allo 'Allo! (1982)"))

Like that, you won't need to do any quoting at all and (if those values are coming from anyone untrusted) you'll be 100% safe (here) from SQL injection attacks too.

Answer 2

I use re.escape(title) to add escape chars into the strings to make them db safe

Note that re.escape makes a string re-safe -- nothing to do with making it db safe. Rather, as @Donal says, what you need is the parameter substitution concept of the Python DB API -- that makes things "db safe" as you need.

Answer 3

SQLite doesn't support backslash escape sequences. Apostrophes in string literals are indicated by doubling them: '''Allo ''Allo! (1982)'.

But, like Donal said, you should be using parameters.