SQLite: Parameter and field name substitution with Python

Question

I am relatively new to SQL and sqlite3 in general, and have a question about using question mark or named style in executions.

I have several databases with a table called structures, and I have made a method in a class in Python that extracts the n'th value of some column using

def get_nth(self, col_name, n):
    cursor = self.con.cursor()
    if n == -1:
        cursor.execute(f"SELECT {col_name} FROM structures ORDER BY id DESC LIMIT 1")
    else:
        cursor.execute(f"SELECT {col_name} FROM structures WHERE id == {n}")

    try:
        val = cursor.fetchall()[0][0]
    except IndexError as e:
        print("Faulty database - no 'structures' table found")
        raise e
    return val

Having googled about other sqlite3-related problems, I found that executing with f-strings or str.format will be vulnerable to injection attacks, and therefore I tried altering the execution, according to Cursor Objects, to:

if n == -1:
    cursor.execute("SELECT ? FROM structures ORDER BY id DESC LIMIT 1", (col_name, ))
else:
    cursor.execute("SELECT ? FROM structures WHERE id == ?", (col_name, n))

print(cursor.fetchall()[0][0])

This outputs the column name itself instead of the actual value, since .execute uses 'id' and not id if col_name='id'.

Question:

Is there a way to use question mark or named style in this way?

Use a hybrid: cursor.execute("SELECT {col_name} FROM structures WHERE id == ?", (col_name, n)). The bit to protect against injection is the value, not nessecarily the column name. Protect the value, and the column name is no longer an issue. — s3dev
– s3dev, Commented Apr 3, 2020 at 11:08

s3dev · Accepted Answer · 2020-04-03 12:00:02Z

1

In agreement with the answer from @SergeBallesta, you can do something like:

sql = 'SELECT {col_name} FROM structures WHERE id = ?'.format(col_name=colname)
cursor.execute(sql, [n])

Which uses a hybrid of the two conventions.

answered Apr 3, 2020 at 12:00

s3dev

9,8813 gold badges34 silver badges49 bronze badges

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

Comments

Serge Ballesta · Accepted Answer · 2020-04-03 11:33:46Z

1

No. You can only pass data in parameterized queries, but neither SQL words like INSERT, SELECTor UPDATE, nor names like table or field names.

answered Apr 3, 2020 at 11:33

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Collectives™ on Stack Overflow

SQLite: Parameter and field name substitution with Python

Question:

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Question:

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related