Best way of gluing strings in Python to compose SQL query

Question

I'm coming from PHP background. When composing SQL queries I tend to do something like this:

$query = '
SELECT
    *
FROM `table` AS t
WHERE 
    t.`categoryID` = '.$categoryID.'
';

if(!empty($recordID))
{
$query .= '
    AND t.`recordID` = '.$recordID.'
';
}

$data = $db->fetchAll($query);

What would be the best/most efficient way of doing this in Python?

What DB library are you using? Probably you should avoid string concatenation altogether as it risks introducing SQL injection vulnerabilities. — Mark Byers, Feb 11 '12 at 00:58
To expand on @MarkByers comment: This is what the Psycopg people call a a naïve approach to the composition of query strings, e.g. using string concatenation - http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters . Instead use query parameters to avoid SQL injection attacks and to automatically convert Python objects to and from SQL literals. http://stackoverflow.com/questions/3134691/python-string-formats-with-sql-wildcards-and-like?rq=1#comment24606225_3134691 — Matthew Cornell, Jun 10 '13 at 13:25

score 10 · Accepted Answer · edited Feb 15 '19 at 12:36

10

There are many way you can achieve it using Python. The simplest would be to either use the format string syntax or the Template object.

The advantage of the format string syntax is that you don't need to use another object. Example:

query = "SELECT * FROM `table` AS t WHERE t.`categoryID`={}".format(category_id)
if record_id:
    query += " AND t.`recordID`={}".format(record_id)

Although most of the time the database Python wrapper will let you make it more secure (preventing SQL injection):

cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Leo Tolstoy", "1"))

You maybe interested in these links:

Here is how it can work for Postgresql:

ps = db.prepare("SELECT * FROM information_schema.tables WHERE table_name = $1 LIMIT $2")
ps("tables", 1)

edited Feb 15 '19 at 12:36

Georgy

12,464
7
65
73

answered Feb 11 '12 at 01:00

charlax

25,125
19
60
71

I like this solution much better. Thanks. – marcin_koss Feb 11 '12 at 01:11
Thanks! Make sure you properly escape though, otherwise you may have SQL injection. – charlax Feb 11 '12 at 01:16
Thanks, I take it escaping method in your second example should be sufficient? – marcin_koss Feb 11 '12 at 06:35
1

Yes! What database are you using? You maybe interested in this discussion too: http://stackoverflow.com/questions/897020/a-good-way-to-escape-quotes-in-python – charlax Feb 11 '12 at 19:27
I suppose `f''` instead of `format` will also prevent sql injections ? Aren't both the same? – Delrius Euphoria Dec 16 '20 at 13:57

score 5 · Answer 2 · answered Feb 11 '12 at 01:01

5

Use the python DB-API parameter substitution:

symbol = 'IBM'

# Do this 
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

From: http://docs.python.org/library/sqlite3.html

answered Feb 11 '12 at 01:01

monkut

42,176
24
124
155

That's a nice solution but it's not really what I was looking for. What I like about the way I do it in this PHP example is that it is very easy to follow the logic for complex queries that change based on some data. – marcin_koss Feb 11 '12 at 01:09

Best way of gluing strings in Python to compose SQL query

2 Answers2