Sqlalchemy: SQLAlchemy 1.3.x breaks ORDER BY on PostgreSQL

[seanthegeek]

Upgrading SQLAlchemy from 1.2.18 to 1.3.1 breaks the following query on postgres:

session.query(Task).filter_by(status=TASK_PENDING).filter_by(machine=machine).order_by("priority desc, added_on").first()

in https://github.com/ctxis/CAPE/blob/master/lib/cuckoo/core/database.py#L617

Results in

Can't resolve label reference for ORDER BY / GROUP BY. Textual SQL expression 'priority desc, added_on' should be explicitly declared as text('priority desc, added_on')

Software versions:

OS: Ubuntu Cosmic (18.10)
PostgreSQL: 10.6-0ubuntu0.18.10.1
libpq5: 10.6-0ubuntu0.18.10.1
Python: 2.7.15 (Unfortunately, CAPE sandbox is not compatible with Python 3 - I'm going to work on that)
psycopg2: 2.7.7

See also: https://github.com/ctxis/CAPE/issues/311

[zzzeek]

The message is explainatory, use text() as it recommends:

from sqlalchemy import text

session.query(Task).filter_by(status=TASK_PENDING).filter_by(machine=machine).order_by(text("priority desc, added_on")).first()

see https://docs.sqlalchemy.org/en/latest/changelog/migration_13.html#coercion-of-string-sql-fragments-to-text-fully-removed for background

[zzzeek]

also that usage pattern has been raising a warning since version 1.0 first released four years ago, so they've had time to be aware of it ...

[seanthegeek]

Thanks!

[oumar90]

Thanks Mr. @zzzeek

The message is explainatory, use text() as it recommends:

from sqlalchemy import text