How can a restricted window for sql injections be abused?

Question

I have a postgres 11 database with no confidential information in it and in the application an intruder can write queries inside the following update statement.

UPDATE table
SET col_a = val_b
WHERE {potentially some other filters}
AND {injected SQL};

I have restricted it such that no strings with ;, /* or -- are allowed.

So how can the intruder do anything to the database outside of table.col_a?

This question is a continuation question of Allowing users to input raw SQL in update statement safely where I explain the requirement to allow such an open system.

Answer 1

Since you are explicitly allowing users to construct raw SQL code, you have to trust them.

However, this doesn't mean that you're defenseless.

Relational databases -- especially those with replication -- have Write-Ahead Log (WAL) files that act as transaction-by-transaction incremental backups. If you perform regular full (offsite) backups, and ship your WAL files offsite frequently, you can recover from malicious users.

One bit of advice, though: Regularly practice restoring your database from your backup... There's nothing worse than thinking you had stable backups, then finding out during a disaster that the backups can't be restored for some reason.

You will also want to log every query that has a custom clause in it, regardless of if it succeeds (in fact, log before attempting to run the query) and especially log custom clauses that are caught by your filter. Be sure to log the user ID along with the clause they tried. People like to play with the obvious stuff first, so seeing a few and 1 = 1';-- clauses appear will let you know who to keep an eye on.

If you're fine with the user knowing that they're being watched very closely, then when a clause trips your filter, you can automatically send an email to yourself with that user CC'd. Don't make the email accusatory, just that potentially problematic input was detected and filtered. Presumably, you still want them to be a user; you just want to make sure they stay a well behaved user.

One additional benefit of this log is, you'll be able to find and fix false positives, i.e., AND text LIKE '%The em dash is often typed out as two dashes, "--".%'

Answer 2

Denial of service

An attacker could construct a query condition that's very resource intensive, and run it repeatedly, making the server so overloaded that it can't serve normal requests.

Information extraction

For starters, even if there are no other options to see the results through your app, an attacker probably can construct a timing-based channel for information extraction. I won't provide specific examples as they're time consuming, but it's reasonable to expect that this would be possible. The original question notes that there's no confidential information in the database, but there's probably some confidential information outside the database obtainable by methods described below.

Escaping your sanitization

Again, I won't construct a specific example, but "no strings with ;, /* or -- are allowed." is generally not sufficient sanitization, so I'll assume that there's a way for the attacker to execute multiple arbitrary SQL commands in this scenario. And this is a bit of a moving target - if someone provides you a counterexample using a UNION subquery or some unicode handling weirdness that allows bypassing the filters and you fix that, you'll still have more holes in it.

Read arbitrary files

select pg_read_file('/etc/passwd', 0, 10000) will return contents of a file on the database server - a penetration test would likely show that there's some convoluted way to extract that data.

Write arbitrary files

If arbitrary SQL can be executed, an attacker might run something like COPY mytable (mycol) TO '/var/www/test.php' which in some cases (depending on the system user under which postgresql is running) can allow further exploitation

Execute system commands

Again, if your sanitization can be somehow broken and arbitrary SQL can be executed, then commands like

DROP TABLE IF EXISTS myoutput;
CREATE TABLE myoutput(filename text);
COPY myoutput FROM PROGRAM 'ps aux';
SELECT * FROM myoutput ORDER BY filename ASC;

(with some obfuscation to avoid naive filtering) would allow an attacker to execute arbitrary system commands on the server.