Embded a delete in select statement in postgres

Question

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!

The returning clause as written does not make sense. It does not accept a sub-query. You can only specify a list of columns to be returned. What exactly are you trying to achieve with that? — user330315
– user330315, Commented Dec 8, 2022 at 13:44
I'm trying to do SQL injection testing on the form which I have built using Nest.js. This form queries the backend using select * from tasks where title like '%${filter}%'. Here the filter is the query param sent from front end — Ashwin
– Ashwin, Commented Dec 8, 2022 at 13:47

JustMe · Accepted Answer · 2022-12-08 13:48:58Z

1

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%'

answered Dec 8, 2022 at 13:48

JustMe

2,3833 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashwin Over a year ago

Yea, I wanted to use the SQL injection to test the form, I wanted to make it secure as it would have a direct impact on the table performance.

jian · Accepted Answer · 2022-12-08 14:51:25Z

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');

user330315 · Accepted Answer · 2022-12-08 15:10:42Z

1

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 = '%'

answered Dec 8, 2022 at 15:10

user330315

Collectives™ on Stack Overflow

Embded a delete in select statement in postgres

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related