Use a Single Connection in Oracle- Developing Successful Oracle Applications-4

So, they don’t know that the following table in particular exists:

SQL> create table user_pw

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:

SQL> conn system/foo@PDB1 Connected.

In the prior code, the select statement executes this statement (which returns no rows):select username from all_users where created =”

And it 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 them 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 procedure (you, in short, not them). They can now see what tables reside in that schema.

They see that table USER_PW and say, “Hmmm, sounds interesting.” So, they try to access that table:

SQL> select * from pwd_mgr.user_pw; select * from pwd_mgr.user_pw

ERROR at line 1: ORA-00942: table or view does not exist

The malicious user can’t access the table directly; they lack the SELECT privilege on the table. Not to worry, however, there is another way. The user wants to know about the columns in the table. Here’s one way to find out more about the table’s structure:

SQL> alter session set nls_date_format = ‘””union select tname||”/”||cname from col–“‘; Session altered.

SQL> exec pwd_mgr.inj( sysdate ) select username

from all_users where

created = ”union select tname||’/’||cname from col–‘ USER_PW/PW…..

USER_PW/UNAME…..

PL/SQL procedure successfully completed.

There we go, we know the column names. Now that we know the table names and the column names of tables in that schema, we can change the NLS_DATE_FORMAT one more time to query that table—not the dictionary tables. So the malicious user can next do the following:

SQL> alter session set nls_date_format = ‘””union select uname||”/”||pw

from user_pw–“‘; Session altered.

And there we go—that evil developer/user now has your sensitive username and password information. Going one step further, what if this developer has the CREATE PROCEDURE privilege? It is a safe assumption that they would (they are a developer after all). Could they go further with this example? Absolutely. That innocent-looking stored procedure gives guaranteed read access to everything the PWD_MGR schema has read access to, at a minimum; and if the account exploiting this bug has CREATE PROCEDURE (which it does), that stored procedure allows them to execute any command that PWD_MGR could execute!

Note  This example assumes that the user PWD_MGR has been granted the DBA role with the ADMIN OPTION.

And then as the developer, we’ll create a function that grants DBA. There are two important facts about this function: it is an invoker rights routine, meaning that it will execute with the privileges granted to the person executing the routine, and it is a pragma autonomous_transaction routine, meaning that it creates a subtransaction thatwill commit or roll back before the routine returns, therefore making it eligible to be called from SQL. Here is that function:

SQL> create or replace function foo

return varchar2

authid CURRENT_USER as

pragma autonomous_transaction; begin execute immediate ‘grant dba to dev’; return null; end;

Function created.

Now all we have to do is “trick” PWD_MGR (a DBA that can grant DBA to others) into running this function. Given what we’ve done to exploit the SQL injection flaw, this is easy. We’ll set our NLS_DATE_FORMAT to include a reference to this function and grant execute on it to PWD_MGR:

Now we need to reconnect to the database so that the freshly granted privileges are instantiated for the session:

SQL> conn dev/dev_pwd@PDB1

SQL> select * from session_roles;

ROLE PDB_DBA CONNECT RESOURCE SODA_APP SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE

AQ_ADMINISTRATOR_ROLE AQ_USER_ROLE ADM_PARALLEL_EXECUTE_TASK GATHER_SYSTEM_STATISTICS

So, how could you have protected yourself? By using bind variables. For example:

SQL> conn pwd_mgr/pwd_mgr_pwd@PDB1

PL/SQL procedure successfully completed.

Notice the prior output does not contain any injected malicious code. It is a plain and simple fact that if you use bind variables you can’t be subject to SQL injection. If you do not use bind variables, you have to meticulously inspect every single line of code and think like an evil genius (one who knows everything about Oracle, every single thing) and see if there is a way to attack that code. I don’t know about you, but if I could be sure that 99.9999 percent of my code was not subject to SQL injection, and I only had to worry about the remaining 0.0001 percent (that couldn’t use a bind variable for whatever reason), I’d sleep much better at night than if I had to worry about 100 percent of my code being subject to SQL injection.

In any case, on the particular project I began describing at the beginning of this section, rewriting the existing code to use bind variables was the only possible course of action. The resulting code ran orders of magnitude faster and increased many times the number of simultaneous users that the system could support. And the code was more secure—the entire codebase did not need to be reviewed for SQL injection issues. However, that security came at a high price in terms of time and effort, because my client had to code the system and then code it again. It is not that using bind variables is hard, or error-prone, it’s just that they did not use them initially and thus were forced to go back and revisit virtually all of the code and change it. My client would not have paid this price if the developers had understood that it was vital to use bind variables in their application from day one.

About the author

Leave a Reply

Your email address will not be published. Required fields are marked *