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
- can't contain
I know I can use the
SELECT INTO to put data on tables, I just want more freedom as in
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.