Use variables when formulating query to filter data from a table

Question

Use variables when formulating query to filter data from a table

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I am working with Python and MySQL in a Tkinter application. I'm trying to do a search filter to only show me the entries in my table that correspond to what I entered with the keyboard but I do not know how to set it in the SQL statement.

The function I'm using for it is:

def buscar():
    conn = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "unitec", db = "efrias002")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM base17 WHERE usuario = 'fduran006'")
    data = cursor.fetchall()
    for item in data:
        list1.insert(END,item)
    conn.close()

In this case the previous sentence will search in the field "user" everything that contains "fduran006" but how can I modify it so that it looks for what I indicate by means of keyboard. How should I modify the% share% share to achieve this?

mysql python python-2.7 tkinter

asked by Erick Finn 26.12.2017 в 19:46

source

2 answers

1

To do this you must not forget that the query you send is a String so you can do something like that

cursor.execute(QueryConFiltro(filtro));

public String QueryConFiltro(String filtro){

return "SELECT * FROM base17 WHERE usuario = '"+filtro+"'";

}

answered by 26.12.2017 в 19:54

How to change the color of the Toolbar menu icon on Android? [duplicate] Authenticate in PHP with array search

score 1 · Accepted Answer

Apparently you use tkinter although you do not show how you get the name from the user in your app. You can get the text of a StringVar ( usuario = variable.get() ), of a Entry , etc.

Never use Python string formatting ( % , str.format , f"" , concatenation, etc) to create the query from your variables as you expose yourself to attacks SQL injection, instead do something like this:

usuario = busqueda_text.get() 
cursor.execute("SELECT * FROM base17 WHERE usuario = %s;", (usuario,))

Where busqueda_text is a StringVar . The second argument is an iterable, in this case a tuple, pay attention to the comma after the variable, since missing parentheses are treated as such and not as indicative of a tuple.

There are multiple secure ways to pass the variables, for example:

cursor.execute("SELECT * FROM base17 WHERE usuario = %(usr)s;", {"usr": usuario})

cursor.execute("SELECT * FROM base17 WHERE usuario = (%s);", usuario)

cursor.execute("SELECT * FROM base17 WHERE usuario = ?);", usuario)

That is, it would be something like this:

def buscar():
    usuario = busqueda_text.get()
    conn = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "unitec", db = "efrias002")
    cursor = conn.cursor() 
    cursor.execute("SELECT * FROM base17 WHERE usuario = %s;", (usuario,))

    for item in cursor:
        list1.insert(END,item)

    conn.close()