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