'No such column' in sqlite when parsing string as a parameter

Question

For better understanding: This is a program to manage the inventory of a green house, so its about plants treated as products. I'm getting a weird error when trying to update a value in my sqlite data base. I'm using a function I called sqUpdateOne that updates a value in my db according to the given parameters. Here it is:

# Sqlite database value updater
def sqUpdateOne(table, column, newValue, refColumn, refValue):
    try:
        with conn:
            c.execute(f'''UPDATE {table} SET "{column}" = {newValue} WHERE {refColumn} = {refValue}''')
    except Exception as e:
        logging.error(f'No se pudo actualizar la tabla {table} - {e}')

In this case "sqUpdateOne" is called by this function:

def modifValue(column, id, prevValue, modValue):
if modValue != prevValue:
    idCol = 'id'
    invCol = 'inventario'
    sqUpdateOne(invCol, column, modValue, idCol, id)
    return True
else:
    return False

Also worth to note: when "modValue" (which later is "newValue" in "sqUpdateOne") is an integer or float it all works fine. As an example: when i do:

sqUpdateOne('inventory','product','newPlant",'idColumn',001)

So this shoud update the column "product" in the table "inventory", specifically the value where "idColumn" is 001, and set that value to "newPlant" (right??) Well... if I do this it gives me an error like

No such column: "newPlant"

So.. in general: The error im geting is when I give it a string in the "newValue" parameter, I dont know why but it takes that value as the "column" value, and gives me the "no such column" error.

sqUpdateOne is used in many other situatuions in this program and I dont have any problems when the value I give to the "newValue" parameter is an integer or float

You're getting error because string values needs quotes around them in a sql query. Better to use a parameterised query, which will protect against SQL injection attacks. https://stackoverflow.com/questions/1005552/sqlite-parameter-substitution-and-quotes — jignatius, May 23 '20 at 14:50
@jignatius thanks a lot, that solved it and i'll look into parameterized query's right now (I may have to re-write a lot of code haha). One more question: the quotes around the parameter affect the fact that I may try to put an *integer* there? in the case I try to do this in a column defined as integer — Lucas Fernandez, May 23 '20 at 16:35
Try this: `c.execute(f'''UPDATE {table} SET "{column}" = ? WHERE {refColumn} = ?''', (newValue, refValue))` This is a parameterised query, which should work for different types of values. — jignatius, May 24 '20 at 03:33

'No such column' in sqlite when parsing string as a parameter

0 Answers0