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
– hagello, Commented Feb 23, 2016 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
– hagello, Commented Feb 23, 2016 at 20:17

Yajnas · Accepted Answer · 2016-02-28 15:25:29Z

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, 2016 at 15:25

Yajnas

531 gold badge1 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hagello Over a year ago

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 Over a year ago

Your second try equals x = c.execute("""SELECT * FROM users WHERE username = ("john",) """) . Normal string interpolation.

hagello Over a year ago

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.

Yajnas Over a year ago

agreed.. thanks for the clarification. I found the following link quite helpful bobby-tables.com/python.html

Community · Accepted Answer · 2017-05-23 12:07:30Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered Feb 23, 2016 at 20:34

hagello

3,3052 gold badges31 silver badges39 bronze badges

2 Comments

Yajnas Over a year ago

>>> 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 "<stdin>", line 1, in <module> TypeError: must be impossible<bad format char>, not str >>>

hagello Over a year ago

Sorry, the parameters have to be passed as a tuple (or a hash if you use "%(name)s"). Edited.

Collectives™ on Stack Overflow

mysqldb: error with Select/execute/escape_string

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related