Using Python to access SQL with a variable column name

Question

I'm to link my code to a MySQL database using pymysql. In general everything has gone smoothly but I'm having difficulty with the following function to find the minimum of a variable column.

def findmin(column):
    cur = db.cursor()
    sql = "SELECT MIN(%s) FROM table"
    cur.execute(sql,column)
    mintup = cur.fetchone()

if everything went smoothly this would return me a tuple with the minimum, e.g. (1,).

However, if I run the function:

findmin(column_name)

I have to put column name in "" (i.e. "column_name"), else Python sees it as an unknown variable. But if I put the quotation marks around column_name then SQL sees

SELECT MIN("column_name") FROM table

which just returns the column header, not the value.

How can I get around this?

khampson · Accepted Answer · 2022-09-22 02:20:56Z

2

The issue is likely the use of %s for the column name. That means the SQL Driver will try to escape that variable when interpolating it, including quoting, which is not what you want for things like column names, table names, etc.

When using a value in SELECT, WHERE, etc. then you do want to use %s to prevent SQL injections and enable quoting, among other things.

Here, you just want to interpolate using pure Python (assuming a trusted value; please see below for more information). That also means no bindings tuple passed to the execute method.

def findmin(column):
    cur = db.cursor()
    sql = "SELECT MIN({0}) FROM table".format(column)
    cur.execute(sql)
    mintup = cur.fetchone()

SQL fiddle showing the SQL working:

http://sqlfiddle.com/#!2/e70a41/1

In response to the Jul 15, 2014 comment from Colin Phipps (September 2022):

The relatively recent edit on this post by another community member brought it to my attention, and I wanted to respond to Colin's comment from many years ago.

I totally agree re: being careful about one's input if one interpolates like this. Certainly one needs to know exactly what is being interpolated. In this case, I would say a defined value within a trusted internal script or one supplied by a trusted internal source would be fine. But if, as Colin mentioned, there is any external input, then that is much different and additional precautions should be taken.

edited Sep 22, 2022 at 2:20

answered Jul 15, 2014 at 2:37

khampson

15.5k4 gold badges45 silver badges44 bronze badges

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

2 Comments

Colin Phipps Over a year ago

This solution does assume that the column name is a trusted input (no escaping/validation performed). If you are writing, say, a website and this input comes from the user then you should validate it against a whitelist before including it in SQL in this way.

George Over a year ago

Thanks thats really helpful, worked a treat. Also @Colin thanks for the tip. Cheers all!

Collectives™ on Stack Overflow

Using Python to access SQL with a variable column name

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related