Oracle SQL Injection Block with DBMS_ASSERT

Question

this code is firing errors

  query_string := 'SELECT '||dbms_assert.sql_object_name(trim(both ' ' from return_field))|| 
                   ' FROM '||dbms_assert.schema_name(trim(both ' ' from from_schema))||
                        '.'||dbms_assert.sql_object_name(trim(both ' ' from from_table))||  
                  ' WHERE '||dbms_assert.sql_object_name(key_field) || ' = '||key_value;



 EXECUTE IMMEDIATE query_string into return_result;

invalid sql object.

from the documentation i feel any object in table is an sql object??
whats wrong here ?

consider following function in oracle 10g

Consider the following function in 10g context

    CREATE OR REPLACE FUNCTION scott.tab_lookup (key_field CHAR,
                                       key_value CHAR,
                                       from_schema CHAR,
                                       from_table CHAR,
                                       return_field CHAR,
                                       return_type CHAR)
    RETURN VARCHAR2 IS
    result_a varchar2(1000);
    query_string VARCHAR2(4000);

    /*version 0.5*/
    BEGIN

    query_string := 'SELECT '||dbms_assert.qualified_sql_name(trim(from_table||'.'||return_field))|| 
                       ' FROM '||dbms_assert.schema_name(trim(from_schema))||
                            '.'||dbms_assert.sql_object_name(trim(from_table))||  
                      ' WHERE '||dbms_assert.qualified_sql_name(from_table||'.'||key_field) || ' = '||key_value;

      IF(return_type = 'SQL') THEN
         result_a := query_string;
      ELSE
         EXECUTE IMMEDIATE query_string 
         --USING key_value  
         into result_a;
      END IF;

      RETURN (result_a);
    EXCEPTION 
    WHEN 
        NO_DATA_FOUND THEN 
           RETURN(NULL);
    WHEN
        TOO_MANY_ROWS THEN 
           RETURN('**ERR_DUPLICATE**');
    WHEN OTHERS
    THEN 
    /*
    ORA-44001   INVALID_SCHEMA_NAME 
    ORA-44002   INVALID_OBJECT_NAME
    ORA-44003   INVALID_SQL_NAME
    ORA-44004   INVALID_QUALIFIED_SQL_NAME
    */
        IF    SQLCODE = -44001 THEN 
              RETURN('*ERR_INVALID_SCHEMA*');
        ELSIF SQLCODE = -44002 THEN 
              RETURN('*ERR_INVALID_OBJECT*');
        ELSIF SQLCODE = -44003 THEN 
              RETURN('*ERR_INVALID_SQL_NAME*');
        ELSIF SQLCODE = -44004 THEN 
              RETURN('*ERR_INVALID_QALIFIED_SQLNAME*');
        end if;         
        return ('*ERR_'||sqlcode);
    END;
    /

i am getting ERR_INVALID_OBJECT

--to get the Genrated SQL as Value  

    Select scott.tab_lookup('ID',1,'TEST','TEST_TABLE','TEST_DESC','SQL') from dual;

-- -or-

-- to get the value returned from database field

    Select scott.tab_lookup('ID',1,'TEST','TEST_TABLE','TEST_DESC','') from dual;

my table is like


    TEST_TABLE  
    ====================
    ID   , TEST_DESC
    ====================
    '11' , 'TEST 1'
    '12' , 'TEST 5000'
    '13' , 'TEST INPUT VALUE'
    '14' , 'JUNK VALUE'
    '50' , 'TEST VALUE 50'

this table is in 'TEST' schema and i am connected with SCOTT and SCOTT has 'GRANT SELECT on TEST.TEST_TABLE to scott'

still i get error

ERR_INVALID_OBJECT

A general tip: Oracle provides very clear and specific error messages, which start with a ORA-##### code. Please don't discard that as irrelevant. — Álvaro González, Jan 28 '14 at 12:58
means what ever you gave as parameters to tht function is invalid. Column name cant be checked this way I guess — Maheswaran Ravisankar, Jan 28 '14 at 13:06

Maheswaran Ravisankar · Accepted Answer · 2014-01-28T13:40:40.450

6

query_string := 'SELECT '||dbms_assert.qualified_sql_name(trim(from_schema||'.'||from_table||'.'||return_field))|| 
                   ' FROM '||dbms_assert.schema_name(trim(from_schema))||
                        '.'||dbms_assert.sql_object_name(trim(from_table))||  
                  ' WHERE '||dbms_assert.qualified_sql_name(from_schema||'.'||from_table||'.'||key_field) || ' = '||key_value;



 EXECUTE IMMEDIATE query_string into return_result;

From Docs..

ENQUOTE_LITERAL - Enquotes a string literal
ENQUOTE_NAME - Encloses a name in double q- uotes
NOOP - Returns the value without any checking
QUALIFIED_SQL_NAME - Verifies that the input string is a qualified SQL name
SCHEMA_NAME - Function Verifies that the input string is an existing schema name
SIMPLE_SQL_NAME - Verifies that the input string is a simple SQL name
SQL_OBJECT_NAME - Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object

edited Jan 28 '14 at 13:40

answered Jan 28 '14 at 13:12

Maheswaran Ravisankar

17,652
6
47
69

So schema name is needed in column name as well. – Maheswaran Ravisankar Jan 28 '14 at 13:39
thank you for your support. After solving this problem my next step would be to limit the maximum input by 30 bytes for schema and column name and i am also considering to add return type to be native datatype like date number varchar. – shinobi92 Jan 28 '14 at 13:54
@shinobi92 so what ever the error you posted in Question is solved? Just want to confirm. The one you mention is something different from it ? – Maheswaran Ravisankar Jan 28 '14 at 14:04
no its not different i just added exception handling and still i get the same exception as of the original one here i can say the function is not throwing an error but returning and error+reason value. my problem is with correct and clean input the function should return value instead it is throwing exception **err_invalid_object** what i feel is you were right when you said "column name can not be checked this way" any solution ? – shinobi92 Jan 29 '14 at 05:17
@shinobi92 yes, schema name needed in column name as well. i editted my answer yesterday itself. I tested it and worked for me. – Maheswaran Ravisankar Jan 29 '14 at 05:19
i got it fixed and its working good. one more thing can i do somthing for return type of this function. like if i have numeric keys its ok, if i have charachter keys i will only work if the key don't have space, and in case the key is Date ...? ! is there any posiblity to 1) overload function or 2) use somthing like return_type%rowtype ? – shinobi92 Jan 30 '14 at 07:15
Yes you can overload and yes you can return a row type but It cannot be a dynamic rowtype.. It has to be strongly coupled. – Maheswaran Ravisankar Jan 30 '14 at 07:39
whats your recommendation of considering PIPLINE functions ? – shinobi92 Jan 30 '14 at 07:42
Yup table functions are good.. I personaly liked pipelined functions. Still the return type cannot be dynamic and are you going to deal with bigger resultsets?? – Maheswaran Ravisankar Jan 30 '14 at 07:48
[my answer on a question](http://stackoverflow.com/questions/21171349/difference-between-table-function-and-pipelined-function/21172153#21172153) over pipelined functions – Maheswaran Ravisankar Jan 30 '14 at 07:52
no my result set will be most 1 row per call rarely it will get to 50+ – shinobi92 Jan 30 '14 at 08:25
So you dont need to pipeline – Maheswaran Ravisankar Jan 30 '14 at 08:29
my main concern is about the return type of data that my function has selected RESULT_A in some cases, i will be returning numbers, or date till now the function is good to handle numeric key and varchar2 return type. – shinobi92 Jan 30 '14 at 08:41
1

Yup, i unserstood tht, hence i was stressing that it is not possible at all. When it is ran in SQL engine it has to be prequalified to return somethings. Cant be dynamic.. One advice is mke it Varchar always and parse it using some logic in caller module – Maheswaran Ravisankar Jan 30 '14 at 08:47

Oracle SQL Injection Block with DBMS_ASSERT

1 Answers1

Linked