mysqldb: error with Select/execute/escape_string

Question

executing this code on Python 2.7.10 [GCC 5.2.1 20151010] on linux2

import flask
from MySQLdb import escape_string as thwart
username="abc"

conn = MySQLdb.connect(host="localhost",user="root", passwd="xxxxxxx", db="pythonprogramming")
c       = conn.cursor()
x       = c.execute("SELECT * FROM users WHERE username = (%s)",  (thwart(username)))

I get the following error:

Traceback (most recent call last):
File "", line 1, in TypeError: must be impossible, not str

this is MySQL version on my PC

+-------------------------+------------------------------+
| Variable_name | Value

+-------------------------+------------------------------+
| innodb_version | 5.7.11

| protocol_version | 10

| slave_type_conversions |

| tls_version | TLSv1,TLSv1.1

| version | 5.7.11

| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64

| version_compile_os | Linux

+-------------------------+------------------------------+

Is _import flask_ necessary to reproduce the problem? Try to strip all unneeded code (and unrelated tags). And try to remove all unneeded complexity. Is it necessary to remove `escape_string` to `thwart`? — hagello, Feb 23 '16 at 20:09
`escape_string` seems to be very important here, but in the headline, there is only `thwart`, which sounds quite random to me. Your question will be hard to find as it is. — hagello, Feb 23 '16 at 20:17

score 1 · Answer 1 · answered Feb 28 '16 at 15:25

1

The code below worked for me and the query variable x returned "1L"

`param1="john"
 x = c.execute("""SELECT * FROM users WHERE username = %s """, (param1,))
`

though i did not quite understand why the following code below did not work

param1="john"
x = c.execute("""SELECT * FROM users WHERE username = %s """ % (param1,))

and

param1="john"
x = c.execute("""SELECT * FROM users WHERE username = %s """ , (param1))

answered Feb 28 '16 at 15:25

Yajnas

53
1
1
7

1L is the number 1 as a long integer. This seems to be the size of the result set. Probably the database driver generally uses a long because the size of a result set _could_ exceed the range of an integer. – hagello Feb 28 '16 at 21:27
Your second try equals `x = c.execute("""SELECT * FROM users WHERE username = ("john",) """) `. Normal string interpolation. – hagello Feb 28 '16 at 21:37
As for your 3rd try: execute needs a tuple or a hash of values. Just putting parentheses around a single value does _not_ create a tuple. You need the additional comma. – hagello Feb 28 '16 at 21:43
agreed.. thanks for the clarification. I found the following link quite helpful http://bobby-tables.com/python.html – Yajnas Feb 29 '16 at 01:38

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

You are aware of the dangers of SQL injection. That's good.

You even use a very secure form of execute: parametrized query). When you do your query this way, you do not need escaping at all. execute does it all for you. Thus the solution is:

x       = c.execute("SELECT * FROM users WHERE username = %

s", (username,))

You would need escaping if you did something like this (with the needed import):

x       = c.execute("SELECT * FROM users WHERE username = %s" %  escape_string(username))

For further discussion, have a look at Python MySQL Parameterized Queries

edited May 23 '17 at 12:07

Community

1
1

answered Feb 23 '16 at 20:34

hagello

2,843
2
27
37

>>> import MySQLdb
>>> from MySQLdb import escape_string
>>> conn = MySQLdb.connect(host="localhost",user="root", passwd="samsam",db="pythonprogramming")
>>> c = conn.cursor()
>>> username ="abc"
>>> x = c.execute("SELECT * FROM users WHERE username = (%s)" % escape_string(username))

...still getting error

Traceback (most recent call last): File "", line 1, in
TypeError: must be impossible, not str
>>> – Yajnas Feb 28 '16 at 14:56
Sorry, the parameters have to be passed as a tuple (or a hash if you use "%(name)s"). Edited. – hagello Feb 28 '16 at 21:12

mysqldb: error with Select/execute/escape_string

2 Answers2