Problem Using SQL Placeholder In MySQL Query

[AdeS]

Hi all, I'm very new to Python (7 days and counting) and seem to be having problems with passing in variables into my SELECT query.

The intention is to use the value selected from a combo box as the condition in the WHERE clause.

Please note that I'm using VS Code 1.5.8.2, MySQL 8.0.20, Python3

I have tried the ? method and it didn't work, I then tried the %s but nothing was returned. Below is the snippet
in question (sincere apologies for the dire quality of my coding):

#note that mycmb holds the value selected from the combo box

sql = "SELECT * FROM tbl_colours WHERE pc_name = %s"
mycmb = (cmb1.get(), )

mycursor = mydb.cursor();
mycursor.execute(sql, mycmb)
myresult = mycursor.fetchall();

I don't have a clue what the problem is and have tried all solutions found on the web.
Any help would be appreciated.

[ndc85430]

What have you done to debug the problem? For example:

- Are you sure you're reading the value from the box correctly?
- Does the query work if you hardcode a value?
- Is the table name correct?
- Are there actually rows that match what you're selecting on?

[AdeS]

Hi ndc85430 many thanks for replying. In response to your questions:

• Yes: the query works when hardcoded
  Yes: the table name is correct
  Yes: I test the retrieval of the combo box value using cmb1.get() in another file
  

[AdeS]

One last thing; the combo box is populated by a query on a table that is related to the tbl_colours table

[Pedroski55]

This query works great for me, try it with your query.

cur = conn.cursor()
    
# Select query 
cur.execute(f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = '{weeknr}'") 
output = cur.fetchall() 

Print your mycmb to make sure it is what it should be. Looks funny to me, but I have no experience using comboboxes

Try your select query directly in MySQL with a specific value for pc_name, like pc_name = 'Lenovo' .

You need to get mycmb before you call cur.execute with placeholders, you know that right? (Don't get mad, just checking!)

So, when you have a correct value in mycmb, yours would be:

cur = conn.cursor()
    
# Select query 
cur.execute(f"SELECT * FROM tbl_colours WHERE pc_name = '{mycmb}'") 
output = cur.fetchall() 

[ndc85430]

cur = conn.cursor()
    
# Select query 
cur.execute(f"SELECT studentnr, score FROM allstudentsAnswers{clas} WHERE weeknr = '{weeknr}'") 

...

cur = conn.cursor()
    
# Select query 
cur.execute(f"SELECT * FROM tbl_colours WHERE pc_name = '{mycmb}'") 
output = cur.fetchall() 

Please don't advise people to use string interpolation (or concatenation) in SQL queries as that is vulnerable to SQL injection. Parameterised queries are the correct way to do it, as they give the database a chance to validate the input.

[Pedroski55]

I think OP will be happy if it works first. Later make it better!

Can you do PDO prepare queries from within Python?

I thought that would only work in PHP.

What about the remote login? Also vunerable to attack? How is that protected in Python?

[Pedroski55]

ndc85430 made me think.

Normally, I don't worry about security much, I only run a little homework webpage, no important data at all. Nothing to steal, and everything backed-up on my laptop and only me issuing such select queries.

But I suppose it is wise to consider security. I found this page about parameterized queries. Looks

a. understandable and
b. fairly easy

This advice is for mysql.connector

Not sure if that will work with pymysql, which I use. I'll try tomorrow.

Something new I learned!!

[ndc85430]

Hi ndc85430 many thanks for replying. In response to your questions:

• Yes: the query works when hardcoded
  Yes: the table name is correct
  Yes: I test the retrieval of the combo box value using cmb1.get() in another file
  

This suggest something is wrong with the query then. Which library are you using to connect to the database? Can you at least post the schema for the table and some insert statements, so people can perhaps try to replicate the problem locally? I can't see anything obviously wrong really.

Also, why do some of your lines have semi-colons at the end?

[AdeS]

Hi ndc85430

After reading some MySQL documentation I realised that MySQL uses the %s placeholder in a different way both in the SQL query and in the cursor execution statement. See below:

mysql = "SELECT * FROM tbl_colours WHERE pc_name = %(pc_name)s"

mycursor.execute(mysql, {'pc_name': mycmb})

This works perfectly.

However, when I execute the same query with a different value for the pc_name variable (and the row count is less than the previous result) both the new and old results appear in the frame (using entry widgets in a frame to display the results). That’s my next challenge. Will keep you posted if you’re interested.

The semicolons are a result SQL habits and a Python novice