pymysql.err.OperationalError: 1054. "Unknown column 'X' in 'where clause'

Question

I'm getting the following error in console, where the column name actually is the value passed through the query:

pymysql.err.OperationalError: (1054, "Unknown column 'LiqNac83437' in 'where clause'")

This is my function:

sql = f"""
            SELECT 
                detallev.clave,
                detallev.cantidad,
                venta.fecha
            FROM
                detallev
                    INNER JOIN
                venta ON detallev.ven_id = venta.ven_id
            WHERE
                clave = '{codigoBarras}'
            AND (fecha BETWEEN {fecha_inicio} AND {fecha_final});"""
    print(sql)
    with bd:
        with bd.cursor() as cursor:
            cursor.execute(sql)
            resultado = cursor.fetchall()
            cursor.close()

which is called by:

@app.get('/{sucursal}/reporte/articulos/')
def reporte_articulo(sucursal: Origenes, clave: str = '', fecha_inicial: str = '', fecha_final: str = fechaHoy(), username: str = Depends(valida_usuario)):
    return reporte_articulos.reporte_articulo(sucursal, clave, fecha_inicial, fecha_final)

I'm using FastAPI, python and Mysql. I've already tried following these solutions with no luck:

Solution 1 Solution 2

and several other solutions outside stackoverflow, already tried wrapping the concatenated value in different type of ways.

• When running this query directly on Mysql workbench it works perfect, aside from calling it from the API.
• When the column name value passed to the function is only numbers as "47839234215" instead of "LiqNac83437", which is numbers and letters, It works great as expected.

Answer 1

This happens because you are substituting the values yourself, and in this case you have not properly quotes the fields in the BETWEEN clause. It sees LiqNac83437 and thinks it is a column name, because it is not quoted.

For this reason, and to avoid SQL injection problems, you should let the database connector do the quoting:

    sql = """
            SELECT 
                detallev.clave,
                detallev.cantidad,
                venta.fecha
            FROM
                detallev
                    INNER JOIN
                venta ON detallev.ven_id = venta.ven_id
            WHERE
                clave = ?
            AND fecha BETWEEN ? AND ?;"""
    with bd.cursor() as cursor:
        cursor.execute(sql, (codigoBarras, fecha_inicio, fecha_final))
        resultado = cursor.fetchall()
        cursor.close()