Is the SQLAlchemy text function exposed to SQL Injection?

Question

I'm learning how to use SQL Alchemy, and I'm trying to re-implement a previously defined API but now using Python.

The REST API has the following query parameter:

myService/v1/data?range=time:2015-08-01:2015-08-02

So I want to map something like field:FROM:TO to filter a range of results, like a date range, for example.

This is what I'm using at this moment:

 rangeStatement = range.split(':')
                if(len(rangeStatement)==3):
                    query = query.filter(text('{} BETWEEN "{}" AND "{}"'.format(*rangeStatement)))

So, this will produce the following WHERE condition:

WHERE time BETWEEN "2015-08-01" AND "2015-08-02"

I know SQL Alchemy is a powerful tool that allows creating queries like Query.filter_by(MyClass.temp), but I need the API request to be as open as possible.

So, I'm worried that someone could pass something like DROP TABLE in the range parameter and exploit the text function

If you are building queries using string formatting then the damage has already been done. Use placeholders and bindparams. — snakecharmerb
– snakecharmerb, Commented Sep 21, 2021 at 23:09
It is a learning project, I'm too used to Java and Spring framework for the data access layer and I'm trying to find out how to do the same in Python (without relying on Django and Django Framework), so, I can change the queries. What I don't want to change is the API Spec — Rafael Carrillo
– Rafael Carrillo, Commented Sep 21, 2021 at 23:37

snakecharmerb · Accepted Answer · 2021-09-22 02:23:15Z

9

If queries are constructed using string formatting then sqlalchemy.text will not prevent SQL injection - the "injection" will already be present in the query text. However it's not difficult to build queries dynamically, in this case by using getattr to get a reference to the column. Assuming that you are using the ORM layer with model class Foo and table foos you can do

import sqlalchemy as sa
...
col, lower, upper = 'time:2015-08-01:2015-08-02'.split(':')

# Regardless of style, queries implement a fluent interface,
# so they can be built iteratively

# Classic/1.x style
q1 = session.query(Foo)
q1 = q1.filter(getattr(Foo, col).between(lower, upper))
print(q1)

or

# 2.0 style (available in v1.4+)
q2 = sa.select(Foo)
q2 = q2.where(getattr(Foo, col).between(lower, upper))
print(q2)

The respective outputs are (parameters will be bound at execution time):

SELECT foos.id AS foos_id, foos.time AS foos_time 
FROM foos 
WHERE foos.time BETWEEN ? AND ?

and

SELECT foos.id, foos.time 
FROM foos 
WHERE foos.time BETWEEN :time_1 AND :time_2

SQLAlchemy will delegate quoting of the values to the connector package being used by the engine, so you're protection against injection will be as good as that provided by the connector package^*.

^* In general I believe correct quoting should be a good defence against SQL injections, however I'm not sufficiently expert to confidently state that it's 100% effective. It will be more effective than building queries from strings though.

edited Sep 22, 2021 at 2:23

answered Sep 22, 2021 at 0:29

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

snakecharmerb Over a year ago

Assuming that the time column` is a Date type, you could convert lower and upper to datetime.date instances before querying. Then you can be confident both that the column name is correct and the dates don't include any injections.

Rafael Carrillo Over a year ago

Awesome, I will give it a try. Thanks!

Ilja Everilä Over a year ago

And some connectors might not even quote values, but send them to the DBMS separately from the query.

Collectives™ on Stack Overflow

Is the SQLAlchemy text function exposed to SQL Injection?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related