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
– Álvaro González, Commented Jan 28, 2014 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
– Maheswaran Ravisankar, Commented Jan 28, 2014 at 13:06

Maheswaran Ravisankar · Accepted Answer · 2014-01-28 13:40:40Z

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, 2014 at 13:40

answered Jan 28, 2014 at 13:12

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Maheswaran Ravisankar Over a year ago

So schema name is needed in column name as well.

shinobi92 Over a year ago

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.

Maheswaran Ravisankar Over a year ago

@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 ?

shinobi92 Over a year ago

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 ?

Maheswaran Ravisankar Over a year ago

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

|

Collectives™ on Stack Overflow

Oracle SQL Injection Block with DBMS_ASSERT

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related