𝗗𝗼𝗻'𝘁 𝗣𝗮𝗿𝘀𝗲 𝗦𝗤𝗟 𝘁𝗼 𝗠𝗮𝗸𝗲 𝗮 𝗤𝘂𝗲𝗿𝘆 𝗥𝘂𝗻𝗻𝗲𝗿 𝗥𝗲𝗮𝗱-𝗢𝗻𝗹𝘆
Stop trying to secure your database by checking SQL strings for keywords.
If you build a tool to run SQL, you want a read-only mode. You want to prevent an accidental UPDATE from deleting your data. Your first thought might be to block words like DELETE or DROP.
Do not do this.
String checks are easy to bypass. A user can use a WITH clause to hide a DELETE. They can use comments to hide commands. They can call a function that writes to a table. You end up playing a losing game of whack-a-mole.
Let the database handle security.
Postgres has a built-in feature for this. You can declare a transaction as read-only. The server then refuses any write command. This covers CTEs, functions, and DDL.
Here is how to implement it correctly in Python:
- Use a real transaction by setting autocommit to False.
- Run SET TRANSACTION READ ONLY as the first command.
- Set a statement_timeout to prevent long-running queries from locking your system.
- Use rollback() at the end to release locks and snapshots.
This approach does not inspect the SQL text. The query goes to the server exactly as written. You are telling the engine to enforce the rule.
Safety requires two parts:
- Protection from writes: Use read-only transactions.
- Protection from resource abuse: Use timeouts and row limits.
A read-only query can still crash your system with a massive join. A read-only transaction stops writes, but it does not stop heavy resource use. You need both to make ad-hoc SQL safe.
Stop parsing SQL. Ask the database to do its job.
Source: https://dev.to/hitoshi1964/dont-parse-sql-to-make-a-query-runner-read-only-b62