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:
and several other solutions outside stackoverflow, already tried wrapping the concatenated value in different type of ways.
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()