Safe way to parameterize substring query with AND operator in MySQL + Python

Question

I'm trying to safely build an SQL query from a given string. The string can contain whitespaces.

string = "chair"

query = """SELECT *
             FROM albums
             WHERE title LIKE %s"""
values = ('%' + string + '%',)
cur.execute(query,values)
# >> SELECT * FROM albums WHERE title LIKE '%chair%'

But I can't figure out how to safely extend this to search for multiple words with the AND operator.

string = "big blue chair"
# >> SELECT * FROM albums WHERE title LIKE '%big%' AND
#                               title LIKE '%blue%' AND
#                               title LIKE '%chair%'

(I know this is what FULLTEXT searches are for, etc ... but this is for a very small dataset and is fast enough.)

What's unsafe about doing the obvious: values = ["%{}%".format(val) for val in string.split()]? Not being snarky, I'm just not sure what your concern is. — dano
– dano, Commented Jul 11, 2014 at 15:06

dano · Accepted Answer · 2014-07-11 15:21:20Z

3

If you want to dynamically expand both the query and values based on string, you can do it like this:

string = "big blue chair"
query = "SELECT * FROM albums WHERE title LIKE %s"
values = ["%{}%".format(val) for val in string.split()]
for _ in values[1:]:  # iterate over len(values) - 1
    query += " AND title LIKE %s"
print(query)
print(values)

Output:

SELECT * FROM albums WHERE title LIKE %s AND title LIKE %s AND title LIKE %s
['%big%', '%blue%', '%chair%']

edited Jul 11, 2014 at 15:21

answered Jul 11, 2014 at 15:12

dano

95.5k21 gold badges234 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott · Accepted Answer · 2014-07-11 15:12:25Z

0

Split your string on spaces.

Then:

cursor.execute("SELECT * FROM albums WHERE title LIKE %s AND title LIKE %s AND title LIKE %s", (var1 , var2, var3))

edited Jul 11, 2014 at 15:12

answered Jul 11, 2014 at 15:06

Scott

151 silver badge4 bronze badges

Collectives™ on Stack Overflow

Safe way to parameterize substring query with AND operator in MySQL + Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related