Python sqlite escape single quote

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.

Lesmana

24.5k8 gold badges80 silver badges86 bronze badges

asked Jul 10, 2010 at 16:41

rajat banerjeerajat banerjee

1,1982 gold badges11 silver badges19 bronze badges

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.

answered Jul 10, 2010 at 16:48

Donal FellowsDonal Fellows

128k18 gold badges141 silver badges209 bronze badges

9

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.

answered Jul 10, 2010 at 17:08

Alex MartelliAlex Martelli

824k163 gold badges1203 silver badges1380 bronze badges

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.

answered Jul 13, 2010 at 5:47

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.

answered Aug 10, 2016 at 8:23

Clock ZHONGClock ZHONG

7357 silver badges22 bronze badges

3

I am using sqlite3 and created tables for the hybrid mobile applications. While executing insert query, it shows below error.

SQL error near line 16: near "s": syntax error

the line is,

INSERT INTO table_name (id, fullname) VALUES (98765, 'Robert O\'neils');

The issue is about escape character for a single quote. 

I also tried double escaping the single quote (using \\\' instead of \' ), but that ain't work either. 

What did I miss?

#sqlite #database