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