1

If the first word of a SQL statement is either WITH or SELECT, is the statement guaranteed not to change the state of the database? i.e. is this loop "safe"?

read SQL string
if string begins with SELECT or WITH
    process string
else
    error, do not execute

If the answer is no, what string(s) would allow data to be modified?

(note that this is a very specific question and not a request for general advice regarding database security.)

3

It's possible for a SELECT statement to modify the database, for example:

  • select mysequence.nextval increments a sequence

  • select myfunction executes a PL/SQL function which could have any sort of side effects

The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as

ORA-14551: cannot perform a DML operation inside a query
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

are returned:

SQL> set serveroutput on
SQL> create table t1 (x number);

Table created.

SQL> insert into t1 values (42);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

         X
----------
        42

SQL> create or replace function f1 return varchar2 is
   pragma autonomous_transaction;
begin
   execute immediate 'truncate table t1';
   return 'Table t1 truncated';
end f1;
/
  2    3    4    5    6    7
Function created.

SQL> select f1 from dual;

F1
--------------------------------------------------------------------------------
Table t1 truncated

SQL> select * from t1;

no rows selected
  • I don't think you can call a function in a SELECT statement that changes the database. – a_horse_with_no_name Apr 12 '15 at 20:31
  • @a_horse_with_no_name: I have expanded my answer – Marco Baldelli Apr 13 '15 at 7:09
  • Ah, right. I wasn't thinking of autonomous transactions. – a_horse_with_no_name Apr 13 '15 at 7:14

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.