2016-03-17 | Adam Boliński

SQL Translation Framework

Quick Introduction to SQL Translation Framework  from Docs

Various client-side applications, designed to work with non-Oracle Databases, cannot be used with Oracle Database without significant alterations. This is because SQL dialect varies among vendors of database technologies and different vendors use different syntaxes to express SQL queries and statements.

Starting with Oracle Database Release 12c, there is a new mechanism called SQL Translation Framework that translates the SQL statements of a client program from a foreign (non-Oracle) SQL dialect into the SQL dialect used by the Oracle Database SQL compiler.

In addition to translating non-Oracle SQL statements, the SQL Translation Framework can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue. In this way, you can address an application issue without patching the client application.

The SQL translation framework consists of the following two components:

There were few words what is Translation Framework and how can be used in real life for example how to easy convert Sybase dialect to Oracle dialect without change code of application

select top 4 *  from emp;  -→ Sybase
to
select * from emp where rownum <5;

To do it we can use prepared by Oracle translation profiles for specific SQL dialect for example for Sybase.

SQL Translation Framework give us also much more powerful and intersting feature which is call Custom Translation where we can implement on-the-fly translation from one SQL query to second one.

All we know the DBMS_Advanced_Rewrite which can do similar job, but using this package is more complicated and have more restriction in real life.

OK so let’s do some testing using Custom SQL Translation, we have databases called Stitch (as in popular cartoon).

First me must do is to create translation profile and some translation which will be used in our tests


exec dbms_sql_translator.create_profile('T01');

BEGIN
DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
     profile_name    => 'T01',
     sql_text        => 'select count(*) from dba_tables',
     translated_text => 'select index_name  from dba_indexes where rownum<10');
END;

 

BEGIN
   DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
     profile_name    => 'T01',
     sql_text        => 'select 888 from dual ',
     translated_text => 'select 11111 from dual);
END;

So let’s check how it’s work on Stitch Database

alter session set sql_translation_profile = T01;
alter session set events = '10601 trace name context forever, level 32';

This must be done to simulate that we are remote client of database , without this translation won’t be apply for the internal SQLPlus connected session.

select 888 from dual;

11111

So let’s check what we can see in SQL area , which SQL query is present Translated or Original ??


SQL_ID 7tr6y6c6ua5df, child number 0
-------------------------------------
select 11111 from dual

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation  | Name | Rows | Cost (%CPU)| Time      |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT | |   |     2 (100)|      |
|   1 |  FAST DUAL     | |  1  |     2   (0)| 00:00:01       |
-----------------------------------------------------------------

13 rows selected.

Next subject how it is organizing match pattern ?

Select 888        from dual;  -→ additional space 

888

So beware that matching must be exactly the same string as in DBMS_SQL_TRANSLATOR sql_text.

Ok so this translation will work but what if we try to translate DDL SQL


BEGIN
   DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
     profile_name    => 'T01',
     sql_text        => 'alter table obj_test enable row movement',
     translated_text => 'drop table obj_test');
END;

alter table obj_test enable row;

Table dropped.


select * from obj_test;
select * from obj_test
             *
ERROR at line 1:
ORA-00942: table or view does not exist

Ok so as you see we can do it and we must have in our mind that this technology and be real security problem in case of Injection.

But let’ s see how Oracle try to minimize this problem:


BEGIN
   DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
     profile_name    => 'T01',
     sql_text        =>  'select * from test2 ' ,
     translated_text => 'delete from test_obj');
END;


select * from test2;

ORA-00900: invalid SQL statement

Ok. so we see that Oracle accept translation command to command so select to select ,

delete to delete etc.

So I wondering if we can cheat Oracle ?? I’m pretty sure that it is possible with no additional big effort , I got idea in few seconds.

Let’s do it select into select translation :


BEGIN
   DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
     profile_name    => 'T01',
     sql_text        => 'select (*) from objects_test',
     translated_text => 'select PURGEDATA from dual');
END;

So what is that mystery PURGEDATA ??

create or replace function PURGEDATA return number is

 

pragma autonomous_transaction;

begin
 DELETE FROM objects_test;
 COMMIT;
 return 1;
 EXCEPTION WHEN OTHERS THEN
 return 0;
end PURGEDATA;
/

So let run


select count(*) from objects_test;

95672 rows



select (*) from objects_test;

select count(*) from objects_test;

0 rows

So it is working like a charm , so let’s see what will tell us plan of this statement :


SQL_ID bwx3wf3a54p0m, child number 0
-------------------------------------
select PURGEDATA from dual

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT | | |     2 (100)| |
|   1 |  FAST DUAL | |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

So as you see Translation Framework is great tool but it can be great hole to compromise our database in case of injection, so you can used it but please use it very careful.

Much more about this translation and other security threat will be covered in one of next post.