Python MySQLdb WHERE SQL LIKE

Question

I have recently started to learn Python and MySQL for web purposes and I have run into a following problem :

I want to pull out from a mysql database one record that contains any text that I enter in param section, howerver I am running into following problem when making a query:

traceback (most recent call last):
  File "/Users/Strielok/Desktop/test.py", line 13, in <module>
    c.execute("SELECT * FROM data WHERE params LIKE ('%s%') LIMIT 1"  % (param))
TypeError: not enough arguments for format string

Here is my code:

import MySQLdb



db = MySQLdb.connect (host = "localhost",
                          user = "root",
                          passwd = "root",
                          db = "test")
param = "Test"

par = param

c = db.cursor()

c.execute("SELECT * FROM data WHERE params LIKE ('%s%') LIMIT 1"  % (param))


data = c.fetchall()
print data

c.close()

Thanks in advance.

Allen · Accepted Answer · 2014-03-16 16:28:49Z

55

Directly inserting the data into the SQL string is not the best way to do this, as it is prone to SQL injection. You should change it to this:

c.execute("SELECT * FROM data WHERE params LIKE %s LIMIT 1", ("%" + param + "%",))

edited Mar 16, 2014 at 16:28

Allen

5,11625 silver badges30 bronze badges

answered Jun 5, 2012 at 19:24

univerio

20.7k3 gold badges73 silver badges73 bronze badges

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

2 Comments

Rob Boyle Over a year ago

Oops, you're totally correct @univerio. Deleted my answer, yours is better.

Michał Lach Over a year ago

thanks, your solution was the best, I like this section " ("%" + param + "%",)"

Alisson R. Perez · Accepted Answer · 2021-01-05 14:54:43Z

2

A better aproach (and safer), instead to use a string as "%" + param + "%" would be to escape % char in template string with another % (so, %%), so it'll be:

c.execute("SELECT * FROM data WHERE params LIKE '%%%s%%' LIMIT 1", (param,))

answered Jan 5, 2021 at 14:54

Alisson R. Perez

713 bronze badges

Comments

swasheck · Accepted Answer · 2012-06-05 19:53:08Z

0

Your not using a proper parameter. Python is expecting another qualifier in this area ('%s%___') in order to do a string substitution. This is also vulnerable code. The proper way to do this would look more like:

c.execute("SELECT * FROM data WHERE params LIKE '%%s%' LIMIT 1",(param,))

edited Jun 5, 2012 at 19:53

answered Jun 5, 2012 at 19:22

swasheck

4,7232 gold badges32 silver badges58 bronze badges

1 Comment

Rob Boyle Over a year ago

I don't think this will result in the same query. The final string will a LIKE clause of %param%, rather than param%, as @univerio intends.

Collectives™ on Stack Overflow

Python MySQLdb WHERE SQL LIKE

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related