具有DBMS_ASSERT的Oracle SQL注入块

问题描述

此代码触发错误

  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;

无效的sql对象.

从文档中我感觉表中的任何对象都是sql对象?
这里怎么了?

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

考虑oracle 10g中的以下功能

consider following function in oracle 10g

在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;
    /

我正在获取 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;

我的桌子就像

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

此表处于'TEST'模式，并且我与SCOTT相关联并且SCOTT具有在TEST.TEST_TABLE上进行GRANT SELECT进行匹配"

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

仍然出现错误

ERR_INVALID_OBJECT

ERR_INVALID_OBJECT

推荐答案

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-字符串原义
• ENQUOTE_NAME-用双引号括起一个名字
• NOOP-不做任何检查就返回值
• QUALIFIED_SQL_NAME-验证输入字符串是否为合格的SQL名称
• SCHEMA_NAME-函数验证输入的字符串是现有的架构名称
• SIMPLE_SQL_NAME-验证输入的字符串是否是简单的SQL名称
• SQL_OBJECT_NAME-验证输入参数字符串是否是现有SQL对象的合格SQL标识符

这篇关于具有DBMS_ASSERT的Oracle SQL注入块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！