How can I create a dynamic order by expression for a query

Question 1

I am testing with PostgreSQL and psycopg and have the following code:

my table is called cars and have columns: brand, model, year, price, id

import psycopg

k = input("")

conn = psycopg.connect(
    dbname="Testing",
    user="postgres",
    password="my_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

cur.execute("SELECT * FROM cars ORDER BY %s DESC;", (k,))  # Corrected the query here


rows = cur.fetchall()


for row in rows:
    print(row)

cur.close()
conn.close()

but when I enter price in terminal I get this

price
('bmw', 'X5', 2000, 1000, 1)
('lada', 'granta', 2020, 1000, 2)
('Audi', 'A8', 2030, 200000, 3)
('audi', 'A6', 2008, 200000, 4)
('a', 'b', 2000, 2000, 5)
('mercedes', 'c-klasse', 2019, 20000, 6)
('mercedes', 'c-klasse', 2019, 20000, 7)
('Audi', 'Q7', 2020, 300000, 8)

so data is not being sorted. How to fix it

tried to replace k by ‘price’ but it didn’t work

Question 2

Parameter binding only exists for values, like integers, strings, dates etc. A column name is not a value; you cannot use parameter binding for identifiers. Since k gets bound to the placeholder as a value, your query is equivalent to

SELECT * FROM cars ORDER BY 'price' DESC;

Since the column 'price' (unlike the column price, or equivalently "price") always contains the same value (namely, the string 'price'), the ordering clause has no effect.

You have to put the identifier into the SQL command manually. However, given that putting user input directly into SQL compromises security, it would be better to find another method (e.g. a dropdown, with dropdown values mapping back to column names, or at least strict validation).

For example, in the toy code you posted,

columns = { "brand", "model", "year", "price", "id" }
while True:
    k = input("Sort field:")
    if k in columns:
        break
    print("Invalid field")

# ...

cur.execute(f'SELECT * FROM cars ORDER BY "{k}" DESC;')

The double quotes in "{k}" are not necessary here, but are a good practice, just in case a valid column name happens to be a keyword like where or include a character not valid in plain identifiers, like First Name.

EDIT: As Adrian Klaver notes in a comment, psycopg2.sql can compose SQL safely, so this should be the prefered method (though it is specific to psycopg2, i.e. limited to PostgreSQL):

query = sql.SQL("SELECT * FROM cars ORDER BY {} DESC")
cur.execute(query.format(sql.Identifier(k)))

Leave a Comment Cancel reply