SQLAlchemy: like and security (sql injection attacks)

[Felix Schwarz]

Hi,

I have a question related to sql injection when using a clause like
this: "User.c.username.like('%' + userinput + '%')"

What restrictions do I have to put on the variable userinput? Of course,
I will ensure that is no percent character ('%') in userinput. Is that
enough (assuming that SQLAlchemy will do the rest by applying
database-specific quoting rules) or do I need to filter more characters?
Is this specific for database used?

Thank you very much
fs

[Rick Morrison]

Don't build SQL strings up from fragments that contain user input -- it's what makes the application subject to SQL injection in the first place.

Safest would be to use a bound parameter for the literal. See here for details:

http://www.sqlalchemy.org/docs/04/sqlexpression.html#sql_everythingelse_bindparams

[Paul Johnston]

Hi,

>I have a question related to sql injection when using a clause like
>this: "User.c.username.like('%' + userinput + '%')"
>
>

SQLAlchemy uses a bind parameter for the value, so there's no chance of
full-blown SQL injection. There is, as you've identified, a risk of
"like pattern injection". That's probably not a security issue, but it
could be a usability issue. To protect, you need to escape any character
with special meaning in the like clause. That is % and _ and any
database-specific extension (I think some dbs allow character sets with []).

Paul

[jason kirtland]

In terms of sql injection, SQLAlchemy builds literal value comparisons
as bind params, so the db-api is receiving that as a query of 'username
LIKE ?' with '%expr%' provided separately as a bind value. The db-api
then executes that safely in a database-dependent manner.

"Pattern injection" is your responsibility though. So removing or
escaping '%' and '_' from user input is in order. And probably the
escape character as well...

I don't believe that SA currently has direct support for specifying the
escape character with LIKE 'expr' ESCAPE '\', though it probably should.
Other than specifying it at the expression-level, there's no universal
way I know of to deduce what the database connection's configured escape
character is (if any).

[Felix Schwarz]

Hi,

thank you very much for your fast and helpful comments!

fs