Writing a query using like with python and mysql connector

Question

How do you write a query using like with the mysql connector and python. I am trying to avoid sql injections and using an ORM is not an option.

       param = 'bob'
       select_query = mysql_conn.query_db("select * from table_one where col_name like '%?%' order by id asc limit 5", param)

No matter what I send in when the query gets executed I get the same results. I should be getting nothing.

When I use the below query I get an error.

        select_query = mysql_conn.query_db("select * from table_one where col_name like %s order by id asc limit 5", param)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s order by id asc limit 5' at line 1

mata · Accepted Answer · 2017-05-04 11:16:33Z

5

You need to add the % wildcards to the param you're passing before escaping, e.g like this:

select_query = mysql_conn.query_db(
    "select * from table_one where col_name like %s order by id asc limit 5",
     ("%{}%".format(param),)
)

Also the parameters should be passed as a tuple like above, or as a dict when using named parameters:

select_query = mysql_conn.query_db(
    "select * from table_one where col_name like %(p)s order by id asc limit 5",
     {"p": "%{}%".format(param)}
)

answered May 4, 2017 at 11:16

mata

69.3k10 gold badges168 silver badges162 bronze badges

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

3 Comments

user3525290 Over a year ago

That worked. Thanks. I do have another question. I set the param as a tuple then send the query and the tuple to my connection class. But that fails? I have to set the param as a tuple in the connection class.

user3525290 Over a year ago

param = (request.form['txt'],) on my connection class <class 'tuple'> class get_connectioned(): def query_db(self, query, param): self.curr.execute(query, (param) return self.curr The above fails. But if I do the following it works. param = request.form['txt'] <class 'str'> class get_connectioned(): def query_db(self, query, param): self.curr.execute(query, ("%{}%".format(param),)) return self.curr Why does making the param a tuple in the class work. Yet sending the param in as a tuple fails?

mata Over a year ago

@user3525290 it's hard to say with the code like this in a comment. It should do approximately the same, except that the first one uses the parameter as is while the second encloses it in wildcards..

e4c5 · Accepted Answer · 2017-05-04 10:36:35Z

1

Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%

http://mysql-python.sourceforge.net/MySQLdb.html

Which is pretty much the same thing as in old style python string formatting, c printf etc etc

answered May 4, 2017 at 10:36

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Comments

goforAC · Accepted Answer · 2020-11-18 10:48:05Z

0

You need to add the % wildcards to the param you're passing before escaping, e.g like this:

select_query = mysql_conn.query_db( "select * from table_one where col_name like %s order by id asc limit 5", ("%{}%".format(param),) )

Threw me an error arror a la

TypeError: a bytes-like object is required, not 'tuple'

so what worked for me was explicitly coding the quotations marks into the selection quote like this:

  "select * from table_one where col_name like \"%"+param+"%\" order by id asc limit 5"

answered Nov 18, 2020 at 10:48

goforAC

1

Collectives™ on Stack Overflow

Writing a query using like with python and mysql connector

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related