Python Sqlite Escape String Select Column

Question

I'm trying to find a way to insert a value into my Sqlite query for selecting a column.

What I have working now is;

def ChangeItemQuantity(self, item_name, incrament_quantity):
    try:
        # Change given item quantity in database
        self.c.execute('''
             SELECT quantity
             FROM items
             WHERE itemName=?
             ''',(item_name,))
        current_quantity = self.c.fetchone()
        new_quantity = current_quantity[0] + incrament_quantity
        self.c.execute('''
             UPDATE items
             SET quantity = ?
             WHERE itemName=?
             ''',(new_quantity, item_name))
        self.conn.commit()

This works for changing a value in the quantity column, but I would like to reuse this method for changing the value in another column also, alertLevel.

So I would like to pass in the column name, something like this;

def ChangeItemQuantity(self, column_name, item_name, incrament_quantity):
    try:
        self.c.execute('''
             SELECT ?
             FROM items
             WHERE itemName=?
             ''',(column_name, item_name))

I've also tried;

        self.c.execute('''
             SELECT {}
             FROM items
             WHERE itemName={}
             '''.format(column_name, item_name))

Thank you for all your help.

alecxe · Accepted Answer · 2016-02-20 14:42:32Z

1

You cannot parameterize the column or table names. You have to use string formatting:

def ChangeItemQuantity(self, column_name, item_name, incrament_quantity):
    try:
        self.c.execute('''
             SELECT {column_name}
             FROM items
             WHERE itemName=?
             '''.format(column_name=column_name), (item_name,))

Make sure though that you either trust your source, or validate the column name before inserting into the query.

answered Feb 20, 2016 at 14:42

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

6 Comments

JohnBoy Over a year ago

Sorry for the confusion, but how is this any different than the .format that I have tried?

alecxe Over a year ago

@JohnBoy it's different. You have to use formatting for column or table names, but the rest of the query should still be parameterized.

JohnBoy Over a year ago

Ok, thank you! Would the .format() used here be vulnerable to SQL injection?

alecxe Over a year ago

@JohnBoy exactly, you have to validate/escape the column name.

JohnBoy Over a year ago

by validate you mean colum_name=colum_name? Thank you again for your help.

|

Collectives™ on Stack Overflow

Python Sqlite Escape String Select Column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related