问题描述

此代码触发错误

无效的 sql 对象.

从文档中我觉得表中的任何对象都是 sql 对象??
这里有什么问题吗?

考虑在 oracle 10g 中的以下功能

在 10g 上下文中考虑以下函数

我收到ERR_INVALID_OBJECT

我的桌子就像

此表在测试"模式中，我与 SCOTT 建立了联系并且 SCOTT 有对 TEST.TEST_TABLE 的 GRANT SELECT 进行 scott"

我仍然收到错误

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;

来自文档..

• ENQUOTE_LITERAL - 引用字符串文字
• ENQUOTE_NAME - 用双引号将名称括起来
• NOOP - 不做任何检查就返回值
• QUALIFIED_SQL_NAME - 验证输入字符串是否为限定的 SQL 名称
• SCHEMA_NAME - 函数验证输入字符串是否为现有模式名称
• SIMPLE_SQL_NAME - 验证输入字符串是一个简单的 SQL 名称
• SQL_OBJECT_NAME - 验证输入参数字符串是现有 SQL 对象的合格 SQL 标识符

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

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

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