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?
We need to supply values in placeholders ( %s ) before executing a query. Pass Python variables at the placeholder's position when we execute a query. We need to pass the following two arguments to a cursor. execute() function to run a parameterized query.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Learn Python + JavaScript + Microsoft SQL for Data science There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one or multiple characters. The underscore represents a single number or character.
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()
.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With