带有 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_schema))||'.'||dbms_assert.sql_object_name(trim(''' from_table))||' WHERE '||dbms_assert.sql_object_name(key_field) ||' = '||key_value;EXECUTE IMMEDIATE query_string into return_result;无效的 sql 对象.
从文档中我觉得表中的任何对象都是 sql 对象??
这里有什么问题吗?
考虑在 oracle 10g 中的以下功能
在 10g 上下文中考虑以下函数
<前>创建或替换功能 scott.tab_lookup (key_field CHAR,键值字符，from_schema CHAR，from_table CHAR，返回字段字符，return_type CHAR)返回 VARCHAR2 是结果_a varchar2(1000);查询字符串 VARCHAR2(4000);/*版本0.5*/开始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') THENresult_a := query_string;别的立即执行 query_string--使用键值进入result_a；万一;返回 (result_a);例外什么时候NO_DATA_FOUND THEN返回(空)；什么时候TOO_MANY_ROWS THENRETURN('**ERR_DUPLICATE**');当别人然后/*ORA-44001 INVALID_SCHEMA_NAMEORA-44002 INVALID_OBJECT_NAMEORA-44003 INVALID_SQL_NAMEORA-44004 INVALID_QUALIFIED_SQL_NAME*/如果 SQLCODE = -44001 那么RETURN('*ERR_INVALID_SCHEMA*');ELSIF SQLCODE = -44002 THENRETURN('*ERR_INVALID_OBJECT*');ELSIF SQLCODE = -44003 THENRETURN('*ERR_INVALID_SQL_NAME*');ELSIF SQLCODE = -44004 THENRETURN('*ERR_INVALID_QALIFIED_SQLNAME*');万一;return ('*ERR_'||sqlcode);结尾;/我收到ERR_INVALID_OBJECT
<前>-- 获取生成的 SQL 作为值选择 scott.tab_lookup('ID',1,'TEST','TEST_TABLE','TEST_DESC','SQL') from dual;- -或者--- 获取从数据库字段返回的值选择 scott.tab_lookup('ID',1,'TEST','TEST_TABLE','TEST_DESC','') from dual;我的桌子就像
<前>TEST_TABLE====================ID , TEST_DESC===================='11' , '测试 1''12', '测试 5000''13', '测试输入值''14' , '垃圾值''50' , '测试值 50'此表在测试"模式中，我与 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
相关文章