对使用动态SQL的几点建议

NDS建议

对共享程序使用调用者权限

在动态SQL中使用AUTHIDCURRENT_USER子句，来实现调用者模型。

例子：

PROCEDUR Eexec_DDL(ddl_string IN VARCHAR2)

AUTHID CURRENT_USER

IS

BEGIN

EXECUTE IMMEDIATE ddl_string;

END;

 

预测并处理动态错误

当我们进行大量的动态SQL时，很容易引起混乱，而且在调试代码上会浪费很多时间——除非我们在编写动态SQL时采取预防措施。

建议如下：

1、在调用EXECUTEIMMEDIATE和OPEN FOR语句时，总是准备一个异常处理单元；

2、在每个异常处理程序中，记录和/或显示发生错误时的错误消息和SQL语句；

3、 我们可能ahi需要考虑在这些语句前面添加一个“跟踪机制”，这样就可以在动态SQL构建和执行时方便观察了。

例如，下面例子添加了一个错误处理部分来显示所出现的问题：

PROCEDURE exec_DDL(ddl_string IN VARCHAR2)

AUTHID CURRENT_USER

IS

BEGIN

   EXECUTE IMMEDIATE ddl_string;

EXCEPTION

WHEN OTHERS THEN

                   DBMS_output.put_line(‘DynamicSQL Failure: ’ || DBMS_UTILITY.FORMAT_ERROR_STACK);

                   DBMS_OUTPUT.PUT_LINE(‘ onstatement: “’ || ddl_string || ‘”’);

                   RAISE;

END;

当我们试图用一个错误的语法创建一个表的时候，会看到如下内容：

SQL> EXEC exec_DDL(‘CREATE TABLE x’)

Dynamic SQL Failure: ORA-00906: missingleft parenthesis on statement:”CREATE TABLE x”

 

构建PL/sql程序来实现用户在运行时指定的任何表的内容：

 

使用绑定而非拼接

绑定

拼接

EXECUTE IMMEDIATE ‘UPDATE ’ || tab ||

‘SET sal = :new_sal ’ USING v_sal;

EXECUTE IMMEDIATE ‘UPDATE ’ || tab ||

‘SET sal = ’ || v_sal

绑定包括使用占位符和USING子句；拼接则通过直接在SQL字符串内添加值而缩短处理过程。

建议尽可能地使用绑定，原因如下：

1、绑定通常更快

2、绑定更容易编写和维护

3、绑定有助于避免隐式转换

4、绑定避免了发生代码注入的机会

如果依靠大量的拼接，写出的语句可能是：

EXECUTE IMMEDIATE

         ‘UPDATE emp SET empsal = ’ || val_in ||

         ‘WHERE hiredate BETWEEN ’ ||

‘ TO_DATE(‘’’ ||TO_CHAR(v_start) || ‘’’)’ ||

‘ AND ’ ||

‘ TO_DATE(‘’’ ||TO_CHAR(v_end) || ‘’’)’;

而切换到绑定时我们的代码更容易理解：

EXECUTE IMMEDIATE

         ‘UPDATE emp SET empsal = :v_sal

WHERE hiredate BETWEEN :v_satrt AND :v_end’

USING v_sal,v_start, v_end;

 

减少代码注入（SQL注入）的危险

考虑下面的示例：

PROCEDURE GET_ROWS(TABLE_IN IN VARCHAR2,WHERE_IN IN VARCHAR2) IS

 BEGIN

   EXECUTE IMMEDIATE 'DECLARE

     l_row ' || TABLE_IN || '%ROWTYPE;

     BEGIN

       SELECT * INTO l_row

       FROM ' || TABLE_IN || ' WHERE ' || WHERE_IN || ';

     END;';

 END;

以下是对GET_ROWS的SQL注入，如果有所有者schema具有的权限，就可以创建一个“后门”程序，

它将执行我们通过动态字符串传递的任何语句。

 BEGIN

 GET_ROWS('emp',

          'empno = 7369;

          EXECUTE IMMEDIATE

          ''CREATE PROCEDURE backdoor (str VARCHAR2)

          AS BEGIN EXECUTE IMMEDIATE str; END;''' );

 END;*/

为了把发生代码注入的可能性降到最低，提供一下几点建议：

1、 严格限制用户schema权限，如不允许该schema创建数据库对象、移除数据库对象或直接访问表；

不允许与操作系统交互（或可交互）的包的执行，如UTL_SMTP、UTL_FILE、UTL_TCP和DBMS_PIPE。

2、尽量使用绑定变量

3、检查动态文本中的危险文本，如分号

4、使用DBMS_ASSERT来验证输入(在11g及以后的版本中才有)