I am trying to trick an application into executing a DML statement AFTER starting a SELECT statement without using stacked queries so I cannot use the ; character anywhere in my query. I have resorted in using the WITH keyword but while it works OK in the

select 2, 
    case when (WITH a as (select 1 as b) select b from a)=1 
    then 'one' 
    else 'two' end

scenario it fails when I try an insert into such as

select 2, 
    case when (
        WITH a as (insert into mytable(fld) values('b from with')) select fld from a)=1 
    then 'one' 
    else 'two' end

because DML statements in WITH need to be at the top layer. I have also tried using it in a UNION statement in hopes that the UNION will cancel out everything and it will be assumed as a first level statement like

select null union all (
    WITH a as (insert into mytable(fld) values('b from with')) select fld from a)

but I get the same top-level error again.

Can anyone think of a way to achieve my goal either using the WITH keyword or any other way. My requirements are

  • must start with select and
  • can't contain ;

I know I can use the SELECT INTO to put data on tables, I just want more freedom as in UPDATE and DELETE.

The application imposes a filter that says the SQL command must start with a SELECT clause and not contain a ; in it. For me to wrap my DML in a function and call that I would have to use CREATE FUNCTION somehow and that would break the rules.

I cannot connect to the database directly, only through an app. My main use case is trying to circumvent the filter imposed by that app. The app is a CMS like application; however someone decided it was a good idea to add a SQL client to it as well so an application admin can query the database directly. To keep users from trashing the database behind it, a filter was added on that SQL client. Initially it was a must start with SELECT only and later on the ; character was added as well to enhance it. The app is not Open Source.

As a process it is called security research. At the end of it the devs will be notified and someone will ship a better product - for free - and I will have learned something in the process. Just as they have been notified of the stacked queries and closed that door as well and as they will be notified of the select into shortcoming.

  • 1
    By the way, there are 3 ways to start a query in Postgres: SELECT ..., WITH ... and TABLE .... For example TABLE x ; is a valid query (where x is either a base table or a view). Correction, 4 ways if you include parenthesis. – ypercubeᵀᴹ Oct 21 '15 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.