Is the SQLAlchemy text function exposed to SQL Injection?-CodePudding

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

CodePudding user response：

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)

# 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.