Letting users write SQL against your databases (without letting them break anything)

The problem

The data source service has an unusual feature: users write their own SQL.

A product specialist who needs a new dropdown, say "all oak finishes available this season", doesn't have to ask a developer. They write a query, test it in the UI, map the columns to label and value, and save it as a source. From then on, any application can fetch that list by code.

That's a huge productivity win. It's also a serious risk, because those queries run against real company databases, shared with other applications.

What if a saved query contains:

DELETE FROM products;

or

UPDATE prices SET amount = 0;

or, less dramatic but just as damaging:

DROP TABLE finishes;

It doesn't take a malicious user. A copy-paste from the wrong window, a test query saved by mistake, or a misunderstanding of what "source" means is enough. And because sources are executed automatically (on demand, and by a nightly job), a destructive query wouldn't run once. It would run again every night.

The rule had to be absolute: sources are read-only. Always.

The details

Where queries enter the system

A query reaches the database through four paths:

1. Create: saving a new source,
2. Update: changing an existing source's query,
3. Test: the "try it" button in the editor, which runs a query that isn't saved yet,
4. Execute: running a stored query (on demand, on refresh, nightly).

A guard on only some of these paths is a guard with a hole. "Test" is the easiest to forget, and it's the one an attacker would try first, because it runs unsaved SQL.

What "read-only" means in SQL

It's not just INSERT, UPDATE and DELETE. PostgreSQL has many ways to change things or affect the server:

• data changes: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, COPY,
• schema changes: CREATE, ALTER, DROP, COMMENT, RENAME,
• permissions: GRANT, REVOKE,
• code execution: DO (anonymous code blocks), CALL, EXECUTE, PREPARE,
• session and transaction control: SET, RESET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, LOCK,
• maintenance: VACUUM, REINDEX, CLUSTER, REFRESH (materialized views),
• messaging: LISTEN, NOTIFY.

The challenges

False positives

A naive substring check for UPDATE would reject perfectly innocent queries:

SELECT id, label, updated_at FROM finishes;   -- contains "update"
SELECT * FROM deleted_items_archive;          -- contains "delete"

Users would hit errors on harmless queries and lose trust in the tool.

Hiding keywords in comments

SQL has comments. A check must not be fooled by them, and shouldn't be confused by keywords that appear only inside comments either:

SELECT label, value FROM finishes -- TODO: delete old rows later

Keyword filters have limits

This is the most important challenge, and the one I want to be honest about: a keyword denylist can't fully understand SQL. Functions can have side effects, and some dangerous behavior doesn't need a forbidden keyword at all. A filter is good at stopping mistakes and the obvious attacks; it isn't, by itself, a complete security boundary.

The solution

1. A read-only guard

const FORBIDDEN_KEYWORDS = [
  'INSERT', 'UPDATE', 'DELETE', 'MERGE', 'UPSERT', 'TRUNCATE', 'COPY',
  'DROP', 'CREATE', 'ALTER', 'RENAME', 'COMMENT',
  'GRANT', 'REVOKE',
  'EXEC', 'EXECUTE', 'CALL', 'DO', 'PREPARE', 'DEALLOCATE',
  'SET', 'RESET', 'BEGIN', 'COMMIT', 'ROLLBACK', 'SAVEPOINT', 'LOCK', 'UNLOCK',
  'VACUUM', 'REINDEX', 'CLUSTER', 'REFRESH', 'LOAD', 'IMPORT',
  'LISTEN', 'NOTIFY',
];

// Whole words only, case-insensitive: "updated_at" is NOT "UPDATE"
const FORBIDDEN = new RegExp(`\\b(${FORBIDDEN_KEYWORDS.join('|')})\\b`, 'i');

export function assertReadOnlyQuery(query: string): void {
  // Remove comments first: -- line comments and /* block comments */
  const stripped = query
    .replace(/--.*$/gm, '')
    .replace(/\/\*[\s\S]*?\*\//g, '');

  const match = FORBIDDEN.exec(stripped);
  if (match) {
    throw new BadRequestException(
      `Query contains forbidden keyword "${match[1].toUpperCase()}". Only read-only (SELECT) queries are allowed.`,
    );
  }
}

Three details make it practical:

• Word boundaries (\b) match whole words only. updated_at, deleted_items and created_by pass; UPDATE prices doesn't. The _ counts as a word character, which is exactly why updated_at is safe.
• Comments are stripped before checking. A note like -- delete later doesn't block a valid query.
• The error names the keyword. "Query contains forbidden keyword DELETE" tells the user exactly what to fix, instead of a vague "invalid query".

2. Check at every entry point

The same function is called on all four paths: create, update, test and execute. Checking at execute time too matters even if create and update already checked: it protects against rows that were inserted before the guard existed, edited directly in the database, or changed by a future code path someone forgets to guard.

// create & update: before saving
assertReadOnlyQuery(dto.query);

// test: before running unsaved SQL
assertReadOnlyQuery(dto.query);

// execute: before every run, including the nightly job
assertReadOnlyQuery(record.query);

3. Defense in depth: the guard is one layer

Because a keyword filter can't fully parse SQL, it's designed as one layer of several. The layers I recommend (and that this kind of service needs) are:

The strongest guarantees come from the database: if the role can't write, no clever query can write. The keyword guard's job is to give fast, friendly feedback and to stop the common cases before they ever reach the server.

4. Credentials are data, and sensitive

Connection strings are stored as their own records, and the schema marks the field clearly: it may contain credentials, treat it as sensitive. Connection strings are never returned in public responses or error messages, and a connection string can't be hard-deleted while sources still use it, so a source never silently points at nothing.

5. Test before saving

New connection strings are verified before they're saved: the service opens a connection and closes it immediately, with a timeout. HTTP endpoints get a quick GET with Accept: application/json and a 10-second timeout. Users learn about a typo in a host name when they type it, not at midnight when the nightly job fails.

Lessons learned

• User-written SQL is a feature with a blast radius. Decide the rules (here: strictly read-only) before shipping it.
• Guard every path, including "test". Unsaved SQL is still SQL that runs.
• Check at execution time too. Saved data can change in ways your save-time validation never saw.
• Word boundaries and comment stripping prevent false positives. A guard that blocks updated_at will be bypassed by frustrated users.
• Good errors name the problem. "Forbidden keyword DELETE" is a fix, not a frustration.
• Never trust a single layer. Keyword filters stop mistakes; database roles and read-only transactions stop everything else.
• Let the database enforce what it can. Permissions enforced by PostgreSQL can't be bypassed by clever syntax.

FAQ

How can I allow users to run only SELECT queries in PostgreSQL?

The most reliable way is at the database level: run user queries with a role that only has SELECT privileges, and set default_transaction_read_only = on for that role. An application-level keyword check is a useful extra layer for clear feedback.

Is a keyword blacklist enough to prevent SQL injection or writes?

No. It catches mistakes and obvious attacks, but it can't fully understand SQL. Combine it with read-only database roles, read-only transactions, timeouts and parameterized queries wherever user input is involved.

Why use word boundaries when checking SQL keywords?

So that column or table names containing a keyword, like updated_at or deleted_items, aren't rejected. Matching whole words avoids false positives that would frustrate users.

Should I validate SQL when saving it or when running it?

Both. Validation at save time gives immediate feedback; validation at run time protects against data that changed later, legacy rows, and code paths added after the original check.