CODEWITHSUNDEEP
pythonsqlquotes
Robert Sturdevant
Robert Sturdevant

Reputation: 35

How to retrieve values with apostrophes from Sqlite

I have a Sqlite3 table that I am trying to access with Python. All is working well except one table column where the value contains an apostrophe (eg. George's).

I have worked out the SQL

"SELECT * FROM table WHERE column1 = '" + value1 + "' and column2 = '" + value2 + "'"

but a syntax error results when value2 = George's.

What is the correct Python syntax for this?

Upvotes: 2

Views: 891

Answers (3)

msturdy
msturdy

Reputation: 10794

A good way to do this, is to use placeholders in your query to really avoid problems with SQL injection

here's a simple example:

code

#!/usr/bin/env python

import sqlite3

dbc = sqlite3.connect('test.db')
c   = dbc.cursor()

name  = "george's"
query = "select * from names where name = ?"

c.execute(query, (name,))

for row in c.fetchall():
    print row

which outputs a tuple containing the row from the DB:

(1, u"george's")

the sqlite3 table: 

sqlite> .schema names
CREATE TABLE names(id integer primary key asc, name char);

sqlite> select * from names;
1|george's
2|dave
3|george

Upvotes: 0

SingleNegationElimination
SingleNegationElimination

Reputation: 156158

first: obligitory link: Exploits of a Mom

To save yourself from this sort of thing, you should be using placeholders. This is a python question, so python answer:

>>> import sqlite3
>>> con = sqlite3.connect(':memory:')
>>> cur = con.cursor()
>>> cur.execute("select ?", ['this contains quotes: "\'"']).fetchall()
[(u'this contains quotes: "\'"',)]

For non-dynamic portions of your query, though, the 'escape' mechanism is to double up quotes:

>>> cur.execute("select ''''").fetchall()
[(u"'",)]

? is the placeholder for sqlite, and many others, But the correct placeholder may be different for the database you are actually using. Other common syntax is :param (PostgreSQL) and %s (MySQL)

Upvotes: 1

mti2935
mti2935

Reputation: 12027

Use the backslash to escape the apostrophe. For example:

select * from tablename where columnname='George\'s'

Upvotes: 1

Related Questions