56

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/<ipython console> in <module>()

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]: <sqlite3.Cursor object at 0x9666e90>

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.

Lesmana
  • 25,663
  • 9
  • 82
  • 87
rajat banerjee
  • 1,256
  • 2
  • 12
  • 21

4 Answers4

133

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.

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
  • 7
    absolutely. quoting and escaping are last-resource kludges. if parameters are available, use them _always_ – Javier Jul 10 '10 at 17:06
  • 2
    BTW, the same idea works just as well for pretty much every other database worth the name out there too, and in virtually every other practical programming language. *Everyone* does it this way because it is *right*. – Donal Fellows Jul 10 '10 at 17:27
  • Awesome thanks Donal. All working well now. I'd used similar methods with RoR, where it is well documented. But hours of searching for "python sqlite escape characters" yielded nothing. python docs leave a lot to be desired. Thanks Donal and all – rajat banerjee Jul 10 '10 at 20:20
  • @rajat: No idea what I searched for (might have even just been `python sqlite example` and working through the top hit) since I've had a browser crash since then. :-/ – Donal Fellows Jul 10 '10 at 20:23
  • 10
    Note that these parameters only work for values. They don't work for column names, for instance. Quoting might be a kludge but it's still necessary sometimes. – Kos Aug 16 '12 at 12:04
  • Wouldn't this throw an error when a user submits something in double quotes ? Something like - 'He said,"yo who goes there? "' (without the single quotes?) – fixxxer Aug 13 '16 at 08:06
  • Here's a [nice trick](http://stackoverflow.com/a/283801/3491991) for passing a list of strings into a parameter – zelusp Oct 25 '16 at 01:30
  • 1
    It's usually an indication of something terribly wrong if your database interface is passing user-supplied names as table or column identifiers. (Or you are making a real expert-only interface, in which case you're very much not doing ordinary database code!) – Donal Fellows Oct 10 '18 at 14:34
  • How do I generalize this? For example `c.execute(f"UPDATE results SET code = ? SET job = ? SET description = ?, ({code},{job_title},{description})")` returns the error `OperationalError: near "SET": syntax error` - so clearly I'm missing something. –  Dec 26 '19 at 22:56
14

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.

Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
10

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.

dan04
  • 87,747
  • 23
  • 163
  • 198
-7

I've one simple tip you could use to handle this problem: When your SQL statement string has single quote:', then you could use double quote to enclose your statement string. And when your SQL statement string has double quotes:", then you could use single quote:" to enclose your statement string. E.g.

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

Or,

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

This solution works for me in Python environment.

Clock ZHONG
  • 875
  • 9
  • 23
  • 2
    And what if the value you are search has a double-quote in it? Use parameters as per Donal's answer. Or escape (only as a last resort) against the delimiting character (single quote in the OP question; or double-quotes in your example) – colm.anseo Feb 21 '17 at 16:00
  • colminator, my string has no double-quote. If your string has double-quote, just use "\"". – Clock ZHONG Feb 23 '17 at 02:28
  • 3
    You have a static example. In the real world - the WHERE clause would be dynamic. Dynamic content can not be trusted to have delimiter conflicts without escaping - whether you use single or double-quotes. – colm.anseo Feb 23 '17 at 14:30