mysql query from python returning no results

Question

So I've already taken a look at the documentation and other questions but I've had no luck.

When I run c.execute and have the first %s, coded with the value, it works. Yet, if I try to allow that to be a variable, I get no results. From my understanding, I put use %s as a place holder and then pass a list of those variables as the second parameter. Am I missing something? The cursor from db_connect is conn.cursor(cursors.DictCursor) in this case because I want a dictionary of the results. Either way, I get nothing in a or b, but if I run the same query with "SELECT * FROM users where username = %s", then I get a result.

from MySQLdb import escape_string
from db_connect import connection
import gc

def get_user(parameter, value):
    c, conn = connection(dict_cursor = True)
    data = c.execute("SELECT * FROM users where %s = %s",[escape_string(parameter), escape_string(value)])
    data = c.fetchone()
    conn.close()
    gc.collect()
    return data

a = get_user("username","testuser")
b = get_user("email","[email protected]")
c = get_user("username","doesntexist")

Gordon Linoff · Accepted Answer · 2017-10-11 02:30:29Z

2

Am I missing something?

Yes. You can replace constants in a query with parameters. However, you cannot replace identifiers. This includes:

table names
column names
function names
operators
SQL keywords

What you can do is replace the column name with a string substitution and then pass the comparison value as a parameter.

answered Oct 11, 2017 at 2:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

George Lingo Over a year ago

In that case, can I do something like: data = c.execute("SELECT * FROM users where "+parameter+"= %s",[escape_string(value)]) ? Is this secure since there's no escape_string or anything around it?

Gordon Linoff Over a year ago

@GeorgeLingo . . . Alas, it is not secure. Replacing identifiers in query strings is a security hole in parameterized SQL.

Collectives™ on Stack Overflow

mysql query from python returning no results

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related