Python String Formats with SQL and LIKE Wildcards

Question

It is hard for me to load some sql in python in order to navigate correctly through MySQLdb. This is the pythons string formatting that kills me.

My sql statement uses the LIKE keyword with wildcards. I tried several different things in Python. The problem is that one day I will get one of them, there is a line of code in MySQLdb that comes off in a string format.

Attempt 1:

"SELECT tag.userId, count (user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '%% s%'"% (query)

This is not an option. I get a value error:

ValueError: unsupported character '' '' (0x27) with index 128

Attempt 2:

"SELECT tag.userId, count (user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '\ %% s \%'"% (Request)

I get the same result from attempt 1.

Attempt 3:

like = "LIKE '%" + str (query) + "%'" totalq = "SELECT tag.userId, count (user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username "+ like

This correctly creates the totalq variable, but now when I go to run the query, I get errors from MySQLdb:

File "build / bdist.macosx-10.6-universal / egg / MySQLdb / cursors.py", line 158, in execute query = query% db.literal (args) TypeError: not enough arguments for format string

Attempt 4:

like = "LIKE '\%" + str (query) + "\%'" totalq = "SELECT tag.userId, count (user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user .username "+ like

This is the same result as attempt 3.

It all seems really strange. How can I use wildcards in sql operations with python?

Answer 1

It's not about string formatting, but the problem is how queries should be executed in accordance with the requirements for db operations in Python ( PEP 249 )

try something like this:

sql = "SELECT column FROM table WHERE col1=%s AND col2=%s" params = (col1_value, col2_value) cursor.execute(sql, params) 

here are some examples for psycog2 where you have some explanations that should also be valid for mysql (mysqldb also follows the PEP249 dba api guide 2.0: here are examples for mysqldb )

Answer 2

All of these queries seem vulnerable to SQL injection attacks.

Try something like this:

 curs.execute("""SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE %s""", ('%' + query + '%',)) 

If two arguments are passed to execute() .

Answer 3

To avoid ampersands in Python string formatting expressions, double the ampersand:

 '%%%s%%' % search_string 

Edit: But I definitely agree with another answer. Direct string replacement in SQL queries is almost always a bad idea.

Answer 4

We could try to avoid the percentage symbol by doubling them as follows:

 query_to_get_user_name = """ SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '%%%s%%' """ % (user_name,) cursor.execute(query_to_get_user_name) 

Answer 5

I have a solution to your problem:

You cannot use:

 "SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '%%s%'" % (query) 

you can change it using a string template, for example:

 import MySQLdb import string # string module ....... value = {'user':'your value'} sql_template = string.Template(""" SELECT tag.userId, count(user.id) as totalRows FROM user INNER JOIN tag ON user.id = tag.userId WHERE user.username LIKE '%$user%' """) sql = sql_template.substitute(value) try: cursor.execute(sql) ........... except: ........... finally : db.close()