MySQL connector/python difficulties using %s

Question

I'm using MySQL connector - a Python driver for MySQL In an attempt to eliminate duplicate code within a function with two parameters: title, author. I'm trying to dynamically modify the query to fit User input. Although the query is legit, apparently the wild card %s doesn't work with it.

Can someone please point to a possible fix I'm missing, or an alternative solution that doesn't require creating 4 different queries for all four possible variations: Both are equal to "ALL", neither do, or XOR.

The simplified piece of code that I've tried implementing:

    if title == 'ALL':
        title = 'ANY (SELECT title FROM book)'
    if author == 'ALL':
        author = 'ANY (SELECT author FROM book)'
    cursor.execute("SELECT * FROM book where title = %s and author = %s",(title, author))

Thanks to anyone willing to help.

GMB · Accepted Answer · 2020-09-21 21:48:40Z

1

You cannot pass a piece of SQL as a parameter. This mechanism is built to pass literal values only. I would recommend boolean logic:

sql = "select * from book where (%s = 'ALL' or title = %s) and (%s = 'ALL' or author = %s)"
cursor.execute(sql, (title, title, author, author))

answered Sep 21, 2020 at 21:48

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

pho Over a year ago

In fact, parameters exist specifically to prevent people from injecting SQL into your queries.

Joshua Over a year ago

Thank a lot mate! I wasn't aware that: " select * from table_name where (Truth statement) " would result in that. Wish i could upvote that comment, but I don't have enough reputation.

Collectives™ on Stack Overflow

MySQL connector/python difficulties using %s

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related