Python String Formats with SQL Wildcards and LIKE

Question

I'm having a hard time getting some sql in python to correctly go through MySQLdb. It's pythons string formatting that is killing me.

My sql statement is using the LIKE keyword with wildcards. I've tried a number of different things in Python. The problem is once I get one of them working, there's a line of code in MySQLdb that burps on 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 a no go. I get value error:

ValueError: unsupported format character ''' (0x27) at 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\%'" % (query)

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 output as attempt 3.

This all seems really strange. How can I use wildcards in sql statements with python?

To echo @bernie below: From psycopg docs: http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters : "Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." In addition to SQL injection attacks, a second benefit is the driver "can automatically convert Python objects to and from SQL literals: using this feature your code will be more robust and reliable". This vs."a naïve approach to the composition of query strings, e.g. using string concatenation." — Matthew Cornell, Jun 10 '13 at 13:21

score 35 · Answer 1 · answered Jun 28 '10 at 17:42

35

Those queries all appear to be vulnerable to SQL injection attacks.

Try something like this instead:

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 + '%',))

Where there are two arguments being passed to execute().

answered Jun 28 '10 at 17:42

mechanical_meat

163,903
24
228
223

1

That also looks vulnerable to SQL injection. Using parameterized queries through whatever library is used to connect to the database is the way to go. – jrdioko Jun 28 '10 at 17:45
6

@jrdioko: thanks for your comment. Note that there is no actual string interpolation, and that the query *is* prepared by the `execute()` method. – mechanical_meat Jun 28 '10 at 17:49
1

I take that back, it appears that syntax is how the library in question does parameterized queries. – jrdioko Jun 28 '10 at 17:51

score 11 · Answer 2 · answered Jun 28 '10 at 17:41

11

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

'%%%s%%' % search_string

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

answered Jun 28 '10 at 17:41

jrdioko

32,230
28
81
120

How do I use same and send the search_string as a parameter to the cursor.execute() function. So that would prevent me from any type of SQL injections. – shreesh katti Mar 28 '19 at 05:28

score 10 · Accepted Answer · answered Jun 28 '10 at 17:53

10

It's not about string formatting but the problem is how queries should be executed according to db operations requirements 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 PEP249 dba api guidance 2.0: here are examples for mysqldb)

answered Jun 28 '10 at 17:53

dzida

8,854
2
36
57

Thanks. that did it. For some reason I thought MySQL db would figure out how to escape parameters even inside of the sql statement. But now it makes sense to throw parameters in like this. – gngrwzrd Jun 28 '10 at 18:07
Glad it helped you! I spent a lot of time until I figured that some time ago:) – dzida Jun 28 '10 at 21:12
3

How does this work for wildcards and LIKE statements in SQL. I have asked same question here. I want to send the params in cursor.execute() where %s would be used as wildcard and LIKE statement. – shreesh katti Mar 28 '19 at 05:26

score 0 · Answer 4 · answered Jun 15 '20 at 15:10

0

import mysql.connector
mydatabase = mysql.connector.connect(host="localhost", user="root", passwd="1234", database="databaseName")
mycursor = mydatabase.cursor()
user_input =[]
item = str("s%")
user_input.append(item)
mycursor.execute("SELECT * FROM employees WHERE FIRST_NAME LIKE %s ESCAPE ''",user_input )
result = mycursor.fetchall()
for row in enumerate(result):
    print(row)

answered Jun 15 '20 at 15:10

Saeed Ali khan

1

It would be much better if you can describe a little bit about your code and how it's solving the issue. – parisssss Jun 15 '20 at 15:53

score 0 · Answer 5 · answered Feb 22 '23 at 20:22

0

I used the following and it worked:

my_str = 'abc'
query = f"""select * from my_table where column_a like '%%{my_str}%%' """
df=pandas.read_sql_query(query, engine)

answered Feb 22 '23 at 20:22

MOAR

15
6

1

doesn't prevent injection, the input has to be sanitized – ykhrustalev Feb 27 '23 at 13:23

score -1 · Answer 6 · answered Mar 28 '19 at 05:47

We could try escaping the percentage character by doubling them like this:

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)

Dhruv Joshi · Answer 7 · 2020-12-07T08:17:53.400

So I tried this and I think I have got the answer which is simpler for me to understand , a school student . In my code the table name is "books" and the column I'm Searching for is "Name". If you need more xplaination , then feel free to drop a mail at dhruv2003.joshi@gmail.com and I will try my best to answer ASAP

def S():

        n=str(input('Enter the name of the book: '))

        name='%'+n+'%'

        NAME=name

        query="select * from books where Name like '"+NAME+"' "

        c.execute(query)

        ans=c.fetchall()
        
        if len(ans)>0:
            print('')

            for i in ans:

                print(i)

             print('')

        else:
            print('')

            print('An error occured')

            print('Name you gave does not exist :( ')

            print('')

score -2 · Answer 8 · answered Dec 25 '14 at 12:50

I have a solution to your problem :

You can not 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 with string template, such as :

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()

This is no better than direct substitution which you shouldn't be doing anyway. Placeholders as describe above and in the PEP 249 doc's describe how to do this correctly — Mike, Apr 10 '17 at 20:51

Python String Formats with SQL Wildcards and LIKE

8 Answers8

Linked

Related