Developing Successful Oracle Applications - Expert Oracle Database Architecture - page 19

The prior USER_PW table looks like a pretty important table, but remember, users do not know it exists. However,
they (users with minimal privileges) do have access to the INJ routine:
EODA@ORA12CR1> create user devacct identified by foobar;
User created.
EODA@ORA12CR1> grant create session to devacct;
Grant succeeded.
EODA@ORA12CR1> grant execute on inj to devacct;
Grant succeeded.
So the evil developer/user, can simply execute:
EODA@ORA12CR1> connect devacct/foobar;
Connected.
DEVACCT@ORA12CR1> alter session set
2 nls_date_format = '"''union select tname from tab--"';
Session altered.
DEVACCT@ORA12CR1> exec eoda.inj( sysdate )
select username
from all_users
where created =
''union select tname from tab--'
USER_PW.....
PL/SQL procedure successfully completed.
In the prior code, the select statement executes this statement (which returns no rows):
select username from all_users where created =''
And unions that with:
select tname from tab
Take a look at the last --' bit. In SQL*Plus, a double dash is a comment; so this is commenting out the last quote
mark, which is necessary to make the statement syntactically correct.
Now, that NLS_DATE_FORMAT is interesting—most people don't even know you can include character string literals
with the NLS_DATE_FORMAT . (Heck, many people don't even know you can change the date format like that even
without this “trick.” Nor do they know that you can alter your session (to set the NLS_DATE_FORMAT ) even without the
ALTER SESSION privilege!) What the malicious user did here was to trick your code into querying a table you did not
intend him to query using your set of privileges. The TAB dictionary view limits its view to the set of tables the current
schema can see. When users run the procedure, the current schema used for authorization is the owner of that