Python character formatting with mySQL raw db query giving me problems

Question

I'm having a problem with trying to install a search function on my web application. The problem is with the following code. I've tried the following two queries but python gives me an error that I will list below:

def searchBox(user_id, searchparams):

try:
    cursor = connection.cursor()

    if cursor:
        sql = "SELECT * FROM db_email WHERE user_id = %d AND deleted = 0 AND subject LIKE '%%%s%%';"
        cursor.execute(sql % (user_id, searchparams)

I have also tried:

try:
    cursor = connection.cursor()

    if cursor:
        sql = "SELECT * FROM db_email WHERE user_id = %d AND deleted = 0 AND subject LIKE " + "'%" + searchparams + "%';"
        cursor.execute(sql % (user_id))

Both return this error for me:

TypeError: not enough arguments for format string

This is the only raw queries that I have had any trouble with and it's related to the way I need to call LIKE. I could write a stored procedure instead to bypass python but I feel like I'm doing something dumb and overlooking an issue. Any help would be appreciated

Thank you. I think a part of the problem is with my LIKE query I need my searchparams to be enclosed with percentage signs and the below answer doesn't work like so -

subject LIKE '%somestringimlookingfor%'

with the code above it seems to come out to 'test'%''. Any ideas?

score 3 · Accepted Answer · edited May 23 '17 at 10:32

3

Don not use string interpolation for SQL queries, it's completely unsafe.

Instead, use query parameters:

sql = """SELECT 
             * 
         FROM 
             db_email 
         WHERE
             user_id = %s AND 
             deleted = 0 AND 
             subject LIKE '%%%s%%'"""
cursor.execute(sql, (user_id, searchparams))

Percentage sign should be escaped with %.

UPD:

Slightly different option:

sql = """SELECT 
             * 
         FROM 
             db_email 
         WHERE
             user_id = %s AND 
             deleted = 0 AND 
             subject LIKE %s"""
cursor.execute(sql, (user_id, "%" + searchparams + "%"))

Python character formatting with mySQL raw db query giving me problems

1 Answers1