How to specify multiple "where" or "order_by" conditions?

Question

It's not straight forward to find information on this so wondering if there are some docs I can look at but basically I want to achieve passing multiple conditions to either .where() or .order_by() that is safe from SQL injection.

Here's how I am currently doing this: Two tables: Archive and Backup, and I am trying to filter by archive.city, archive.zip, and backup.serial and then I am ordering by all of those fields. The values are coming from the user via URL parameters so I need to make sure these are safe from SQL injection and sanitized.

filters = []
sorts = []

    if 'city' in query:
            city = query['city']
            filters.append(text(f'archive.city = {city}')) 
            sorts.append(text(f'archive.city = {city}')) 

    if 'zip' in query:
            zip = query['zip']
            filters.append(text(f'archive.zip > {zip}'))    
            sorts.append(text(f'archive.zip DESC'))  

    if 'serial' in query:
            serial = query['serial']
            filters.append(text(f'backup.serial IN {serial}'))    
            sorts.append(text(f'backup.serial ASC')) 

    with Session(engine) as session:
        results = session.exec(select(Archive, Backup)
                               .join(Backup)
                               .where(and_(*filters))
                               .order_by(*sorts).all()

as I understand the text() is not safe from sql injection, so how do I transform this so that it does what I want and is safe from sql injection?

score 3 · Accepted Answer · answered Jun 03 '22 at 15:15

3

You can invoke .where() and .order_by() on a select() multiple times and SQLAlchemy will logically "and" them for you:

qry = select(Task)
qry = qry.where(Task.description == "foo")
qry = qry.where(Task.priority < 2)
qry = qry.order_by(Task.priority)
qry = qry.order_by(Task.description)

print(qry)
"""
SELECT task.id, task.description, task.priority 
FROM task 
WHERE task.description = :description_1 AND task.priority < :priority_1 
ORDER BY task.priority, task.description
"""

answered Jun 03 '22 at 15:15

Gord Thompson

116,920
32
215
418

what if instead of `foo` the user sends some funky SQL injection? Will this sanitize automatically? – johojojoj Jun 03 '22 at 18:19
"Will this sanitize automatically?" - Yes. – Gord Thompson Jun 03 '22 at 18:35
thanks @Gord, what if you need to throw in `OR` statements? – johojojoj Jun 06 '22 at 16:07
1

You can use [or_()](https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.or_) – Gord Thompson Jun 06 '22 at 16:21
Could you give an example of where the "or_" would fill in such sintax? – FábioRB Aug 07 '22 at 04:31
@FábioRB - There is an example in the docs I linked in my previous comment. – Gord Thompson Aug 07 '22 at 12:11

How to specify multiple "where" or "order_by" conditions?

1 Answers1

Linked

Related