Filter SQL statement against malicious injection in Python

Question

Would like to allow a client application to execute SQL queries against our database.

The queries will be requests for data, the client should never be able to modify data.

Is there a way to allow a client to send in a SQL statement, then screen it for malicious injection, then pass it through the the database?

We are using the SQLAlchemy library for Python against a PostgreSQL database.

Thanks!

score 4 · Accepted Answer · edited May 23 '17 at 12:34

4

An easier option than trying to interpret queries for malice would be to create a db user with read-only privilege. Your end-users would then use that account to run SELECT queries. You would not need to worry about malicious inserts and deletes because "write" queries would not be allowed. You could also modify permissions further to not allow access to data that you do not want your clients to see etc.

See this SO question and answers for some info on creating "read only" users.

edited May 23 '17 at 12:34

Community

1
1

answered Jan 13 '13 at 16:42

Paul Sasik

79,492
20
149
189

But this dows not prevent a malicious user of having access to more of the database than he is supposed to. – Mikael May 05 '17 at 20:08

MBarsi · Answer 2 · 2013-01-13T17:42:45.240

1

use prepared statement when execute sql queries, using sqlalchemy.sql.expression.text method

from sqlalchemy.sql.expression import text

t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)

refer to sqlalchemy docs for full coverage of text method.

but protect your application against user-defined sql statements is really hard even if the DBA block create and writing roles from users. consider reading this blog post before start.

edited Jan 13 '13 at 17:42

answered Jan 13 '13 at 17:03

MBarsi

2,417
1
18
18

If I understand correctly, this does not allow the client to specify their own queries? – Chris Dutrow Jan 13 '13 at 17:11
1

If you're going to allow users to create their own queries, you have no security at all. – Andy Lester Jan 13 '13 at 19:39

score 0 · Answer 3 · answered May 24 '18 at 11:27

You always can improve your security system with a second database in slave mode that accept only read connections.

https://www.postgresql.org/docs/9.2/static/high-availability.html

This strategy allows you to scale your database in read mode two, three or four times.

Filter SQL statement against malicious injection in Python

3 Answers3