使用EXECUTE IMMEDIATE的动态SQL语法 [英] Dynamic SQL syntax using EXECUTE IMMEDIATE

问题描述

动态SQL更新语句如下:

Dynamic SQL update statement as below:

EXECUTE IMMEDIATE 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID;

l_prefix是保留表名前缀的参数，分配的值为T_ i.CUSTOMER_REF_ID和i.CUSTOMER_ID是从游标获取的字段.

l_prefix is the parameter hold the prefix of table name, the value assigned is T_ i.CUSTOMER_REF_ID and i.CUSTOMER_ID are the fields fetched from cursor.

动态语句在获取数据时遇到错误ORA-00904: invalid identifier.

The dynamic statement encounter error ORA-00904: invalid identifier when data fetched.

以静态方式重写时，查询工作正常:

When rewrite it in static way, the query is working fine:

UPDATE T_CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = i.CUSTOMER_REF_ID WHERE CUSTOMER_ID = i.CUSTOMER_ID;

UPDATE T_CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = i.CUSTOMER_REF_ID WHERE CUSTOMER_ID = i.CUSTOMER_ID;

我知道在动态SQL的连接上一定出了点问题，只是无法查明原因，因为编译很好.

I know it must be something wrong on the concatenation of dynamic SQL, just couldn't pinpoint any as the compilation is fine.

推荐答案

警告:像这样的动态SQL容易受到SQL注入攻击的攻击.尽可能重写动态SQL来使用绑定变量.

WARNING: Dynamic SQL like this is susceptible to SQL Injection attacks. Wherever possible rewrite your dynamic SQL to use bind variables instead.

而不是像这样构造动态SQL:

Instead of constructing your dynamic SQL like this:

L_SQL := 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID;
EXECUTE IMMEDIATE L_SQL;

使用此:

L_SQL := 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = :REF_ID WHERE CUSTOMER_ID = :CUST_ID';
EXECUTE IMMEDIATE L_SQL USING i.CUSTOMER_REF_ID, i.CUSTOMER_ID;

这仍然可以通过l_prefix进行SQL注入，但是如果您以编程方式控制该值，则可以.另外，将SQL的构造和SQL的执行分为两个步骤，使您可以更轻松地将EXECUTE IMMEDIATE替换为DBMS_OUTPUT.PUT_LINE(SQL);来检查查询中的语法错误.您还可以DBMS_OUTPUT.PUT_LINE您的参数i.CUSTOMER_REF_ID和i.CUSTOMER_ID来检查它们的值.

This is still subject to SQL injection at the l_prefix, but if you control that value programatically it may be OK. Also splitting the construction of the SQL and execution of the SQL into two steps allows you to more easily replace the EXECUTE IMMEDIATE with DBMS_OUTPUT.PUT_LINE(SQL); to check your query for syntax errors. You can also want to DBMS_OUTPUT.PUT_LINE your parameters i.CUSTOMER_REF_ID and i.CUSTOMER_ID to check their values.

这篇关于使用EXECUTE IMMEDIATE的动态SQL语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！