Postgresql – Is it possible to `DELETE` in a subquery

postgresqlpostgresql-9.3

Is it possible to run a DELETE as a subquery of a SELECT? Eg:

SELECT * FROM posts where title = (DELETE FROM posts returning 'whoops');

When I try this, I get syntax error at or near "FROM", pointing to the FROM of the DELETE, so it doesn't seem possible, but maybe I'm just doing it wrong somehow.

Context: I'm trying to understand the possible damage of some SQL injection attacks. In this case, the overall query will be run in a prepared statement with no variable bindings. This means an attacker can't run multiple top-level statements, but I'm trying to assess the possible damage from subqueries.

Edit: I should have said, the querying code is doing something like:

 query_parameter = blindly_read_from_user_input()

 # this is just interpolated into a string
 db_query = "SELECT * FROM posts WHERE title = '#{query_parameter}'"

 connection.prepare("query_name", db_query)
 connection.exec_prepared("query_name")

So:

  • It's using a prepared statement, which means an attacker can't cause multiple top-level queries to run
  • It's not using bound parameters

I'm trying to see whether I can delete records based on SQL injection in this existing query structure.

Best Answer

You can DELETE in a CTE - which is a different kind of subquery.

But you probably don't need it for this simple case, you just need to use RETURNING and fix the syntax errors in your code:

DELETE 
FROM posts AS p
WHERE p.title = 'whoops'
RETURNING p.* ;