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: