Embded a delete in select statement in postgres-CodePudding

I am trying to embed a Delete operation in SELECT in postgres. Tried the following command but its not working.. :(

select * from tasks where title ilike '% 
Delete from tasks where title ilike `%Re%` returning ( 
Select title from tasks where title ilike `%smoke%`)%'

where the actual query in TS looks like

select * from tasks where title ilike '%${filter}%'

I'm trying to fit the

Delete from tasks where title ilike '%Re%' returning ( 
Select title from tasks where title ilike '%smoke%')

in the place of '%{filter}%'

I am getting errors all around and not able to perform the operation!

CodePudding user response：

You're trying to execute an invalid SQL statement. If you want to return rows from the delete statement you can use the CTE:

WITH del AS (
   -- it will be better to get the task id here
   DELETE FROM tasks WHERE title ILIKE '%Re%' RETURNING title
)
   SELECT title FROM del WHERE title ILIKE '%smoke%'

CodePudding user response：

You can use function and lateral achieve that.

CREATE temp TABLE tasks (
    title text
    , misc numeric DEFAULT random() ::numeric
);
INSERT INTO tasks (title)
    VALUES ('foo bar');
INSERT INTO tasks (title)
    VALUES ('Re')
    , ('Re_1')
    , ('smoke_1')
    , ('smoke_2')
    , ('smoke_3')
    , ('foo bar')
    , ('brown');'

simple function, use case insensitive regex pattern match to delete rows.

CREATE OR REPLACE FUNCTION delete_tasks_title_pattern (x text)
    RETURNS void
    AS $func$
    DELETE FROM tasks
    WHERE title ~* x;
$func$
LANGUAGE sql
STRICT;

Then the following query will do select (title ILIKE '%smoke%') and delete (title ~* 'foo')

SELECT
    sub.*
FROM (
    SELECT
        *
    FROM
        tasks
    WHERE
        title ILIKE '%smoke%') sub
    , LATERAL delete_tasks_title_pattern ('foo');

CodePudding user response：

If I understand your question correctly, you are trying to exploit SQL injection by replacing '%${filter}%' with some sub-query that deletes rows.

You can't have a sub-query that runs a DML statement, but you can use the well known little bobby tables approach to inject a DELETE statement.

Assuming that ${filter} will be replaced at runtime with the value provided, and you pass the following string as the filter parameter:

';delete from tasks where true or title = '

then the query is turned into two queries:

select * from tasks where title ilike '%';
delete from tasks where true or title = '%'