A good way to escape quotes in a database query string?

Question

I've tried all manner of Python modules and they either escape too much or in the wrong way. What's the best way you've found to escape quotes (", ') in Python?

In what context do you want the escaping? For python strings, or into a database? — workmad3, May 22 '09 at 09:21

score 36 · Accepted Answer · edited Nov 08 '18 at 05:32

36

If it's part of a Database query you should be able to use a Parameterized SQL Statement.

As well as escaping your quotes, this will deal with all special characters and will protect you from SQL injection attacks.

edited Nov 08 '18 at 05:32

Samuel Liew

76,741
107
159
260

answered May 22 '09 at 09:30

David Webb

190,537
57
313
299

19

+1: If you're escaping quotes in a database query, you're doing the SQL wrong. – S.Lott May 22 '09 at 09:53
Excellent, now I just need a copy of Python 3.0. – Jonathan Prior May 22 '09 at 10:49
3

Why do you need Python 3.0 to use a parameterized SQL statement? They exist in all releases and all Postgres Python interfaces. – S.Lott May 22 '09 at 10:53
There are other features I need to use in Python 3.0. – Jonathan Prior May 22 '09 at 11:18
All Python 3.0 features are available in Python 2.6. Use that. – S.Lott May 22 '09 at 11:51
2

@S.Lott Can you explain why? Would you have a link for a better/cleaner way to do it than escaping quotes? – Basj Mar 01 '18 at 16:38
2

Not everything can be parameterized. In particular if you are trying to dynamically construct a SQL query. Like dynamic table names, or dynamic operators, using `>` or `<` depending on a boolean. Or having different number of parameters. – CMCDragonkai Sep 22 '20 at 06:07

score 24 · Answer 2 · answered Feb 18 '13 at 06:25

24

Use json.dumps.

>>> import json
>>> print json.dumps('a"bc')
"a\"bc"

answered Feb 18 '13 at 06:25

eddie_c

3,403
1
16
6

9

This fails when Unicode characters are included in the string: `print json.dumps(u"£")` prints `"\u00a3"` – Jason Oster Nov 07 '14 at 23:38
Aside from the issue above this works pretty well, but it's a very heavy solution for a problem that can be addressed more simply. – GregD Aug 20 '18 at 03:05

score 11 · Answer 3 · edited Oct 18 '20 at 13:20

11

The easy and standard way to escape strings, and convert other objects to programmatic form, is to use the built in repr() function. It converts an object into the representation you would need to enter it with manual code.

E.g.:

s = "I'm happy I am \"here\" now"
print repr(s)
>>  'I\'m happy I am "here" now'

No weird hacks, it's built in and it just works for most purposes.

edited Oct 18 '20 at 13:20

Caner

57,267
35
174
180

answered Dec 03 '12 at 03:27

GregD

741
7
15

6

This is probably not what the OP wants. repr does escapes the quotes but it also wraps the string in single quotes. For example: repr("King's Castle") becomes '"King\'s Castle"' (notice the wrapping quotes). – Trasplazio Garzuglio Feb 12 '14 at 21:32
I end up with `u'I\'m happy I am "here" now'`. – luckydonald Jul 20 '18 at 17:30
1

`repr` escapes using Python rules, with backslashes. My impression is OP wants to return an SQL literal which has different rules — single quotes should be doubled, backslashes not significant. – Beni Cherniavsky-Paskin Nov 20 '19 at 12:22

score 5 · Answer 4 · edited Nov 15 '19 at 13:08

5

If using psycopg2, its execute() method has built-in escaping:

cursor.execute("SELECT column FROM table WHERE column=%s AND column2=%s", (value1, value2))

Note, that you are giving two arguments to execute method (string and tuple), instead of using Python's % operator to modify string.

Answer stolen from here: psycopg2 equivalent of mysqldb.escape_string?

edited Nov 15 '19 at 13:08

Dan Getz

8,774
6
30
64

answered Jun 18 '16 at 12:36

ex4

2,289
1
14
21

score 5 · Answer 5 · answered Jan 30 '18 at 23:51

5

Triple single quotes will conveniently encapsulate the single quotes often used in SQL queries:

c.execute('''SELECT sval FROM sdat WHERE instime > NOW() - INTERVAL '1 days' ORDER BY instime ASC''')

answered Jan 30 '18 at 23:51

Roy

272
3
8

score 2 · Answer 6 · edited May 23 '17 at 12:02

2

If you're using psycopg2 that has a method for escaping strings: psycopg2.extensions.adapt() See How to quote a string value explicitly (Python DB API/Psycopg2) for the full answer

edited May 23 '17 at 12:02

Community

1
1

answered Mar 11 '10 at 22:40

EMP

59,148
53
164
220

score 0 · Answer 7 · answered Mar 11 '10 at 22:28

0

For a solution to a more generic problem, I have a program where I needed to store any set of characters in a flat file, tab delimited. Obviously, having tabs in the 'set' was causing problems.

Instead of output_f.write(str), I used output_f.write(repr(str)), which solved my problem. It is slower to read, as I need to eval() the input when I read it, but overall, it makes the code cleaner because I don't need to check for fringe cases anymore.

answered Mar 11 '10 at 22:28

robertlayton

612
1
7
20

3

repr/eval is a bad idea in this case. You could use `csv` module instead. It will take care of embed tabs in the field for you – jfs Dec 03 '12 at 03:47

score 0 · Answer 8 · answered Jan 27 '12 at 01:44

0

Triple-double quotes are best for escaping:

string = """This will span across 'single quotes', "double quotes",
and literal EOLs all in the same string."""

answered Jan 27 '12 at 01:44

3

It won't work in this case: `string = """This is a string "containing a quote""""` – dolma33 Aug 08 '12 at 16:00

score -1 · Answer 9 · answered May 16 '19 at 00:51

For my use case, I was saving a paragraph against the database and somewhere in the paragraph there might have been some text with a single quote (example: Charlie's apple sauce was soggy)

I found this to work best:

database_cursor.execute('''INSERT INTO books.collection (book_name, book_quoted_text) VALUES ('%s', "%s")''' % (book_name, page_text.strip()))

You'll notice that I use "" after wrapping the INSERT statement in '''

A good way to escape quotes in a database query string?

9 Answers9

Linked

Related