Passing user values to WHERE clause

Question

I am trying to pass a combination of two user inputted values into a WHERE clause, using Python and PymySQL.

The code I have at the moment is:

sign_input = input("Please enter <, =, > for population check ")
Pop_Lim = input("Please input a number for population parameter ")

Where_Limit = sign_input +" "+ Pop_Lim

conn = psq.connect("localhost","root","root","City",cursorclass=psq.cursors.DictCursor)

query = "SELECT * FROM city Where Population %s"

with conn:
    cursor = conn.cursor()
    cursor.execute(query, Where_Limit)
    city = cursor.fetchall()
    for row in city:
        print(row["ID"], row["Name"]," :   ",row["CountryCode"]," :   ",row["District"]," :   ",row["Population"])     # insert spacers for legibility purposes

The error says I suggests that there is a problem with the Where_Limit variable.

Any suggestions on how to fix this, or pass a symbol and population variable to the Where function in the SQL command?

Answer 1

If that were allowed, it could be a security risk. First, make sure, on the server, if this is for a web server, that sign_input is one of <, =, and >. Then, you can either use string concatenation (query = "SELECT * FROM city Where Population " + sign_input + " %s") or a set of if/elif statements:

if sign_input == '<':
    query = "SELECT * FROM city Where Population < %s"
elif sign_input == '=':
    query = "SELECT * FROM city Where Population = %s"
elif sign_input == '>':
    query = "SELECT * FROM city Where Population > %s"

Concatenation is shorter and less repeticious, but it is easier to make sure the if/elif chain with constant strings is secure. It is also easier to use a different set of legal values for sign_input with the if/elif chain.