Python sqlite3 OperationalError in a simple Select query

Question

i have a strange behavior with a very simple query in sqlite (python sqlite3)

Here is a query which works

cursor = self.con.cursor()
cursor.execute("SELECT * FROM vpro1 ORDER BY :orderby DESC LIMIT 1", {"table": "vpro1", "orderby": self.unit_timestamp})

But this one throw me an error

cursor = self.con.cursor()
cursor.execute("SELECT * FROM :table ORDER BY :orderby DESC LIMIT 1", {"table": "vpro1", "orderby": self.unit_timestamp})

The Exception is

sqlite3.OperationalError: near ":table": syntax error

So when i try to use the qmark for table name it throws an error, if i hardcode it in the query, it works, the :orderby works whatever i use (qmark, hardcoded, named)... Same behavior with qmark style (?,? with a tuple)

Thanks for your advice !

Community · Accepted Answer · 2017-05-23 10:34:55Z

2

You cannot use the DB API to complete table names. I'm not sure what the reason for this is, but I've encountered this same problem in the past. It does not work in SQLite or MySQL and probably others.

See Donald Miner's answer to another question for a workaround.

def scrub(table_name):
    return ''.join( chr for chr in table_name if chr.isalnum() )

scrub('); drop tables --')  # returns 'droptables'

Then pass this in as a format string.

table = "vpro1"
cursor.execute("SELECT * FROM {0} ORDER BY :orderby DESC LIMIT 1".format(scrub(table)), {"orderby": self.unit_timestamp})

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jan 18, 2013 at 15:09

Fredrick Brennan

7,3533 gold badges35 silver badges67 bronze badges

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

1 Comment

Gehasia Over a year ago

Thanks for this solution ! I've searched but never found the Donald Miner's post, so thanks for your (and his) tip !

Collectives™ on Stack Overflow

Python sqlite3 OperationalError in a simple Select query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related