在 PL/SQL 中引用动态 SQL 的标识符

发布于 2024-09-28 08:57:31 字数 456 浏览 4 评论 0原文

是否有 PL/SQL 函数或一般技术来引用非限定标识符（例如，mytable）以在动态构造的 SQL 查询中使用？部分或完全限定标识符 (ab@c) 怎么样？

考虑这个人为的示例：

CREATE PROCEDURE by_the_numbers(COL_NAME VARCHAR, INTVAL INTEGER) IS
  ...
BEGIN
  -- COL_NAME is interpolated into SQL string
  -- INTVAL gets bound to :1
  stmt := 'SELECT * FROM tbl WHERE ' || COL_NAME || ' = :1';
  ...
END

...我们不想允许在 COL_NAME 中进行简单的 SQL 注入（例如，值“1=1 或 1”）。

原文

Is there a PL/SQL function or general technique to quote unqualified identifiers (e.g., mytable) for use in a dynamically constructed SQL query? How about partially or fully qualified identifiers (a.b@c)?

Consider this contrived example:

CREATE PROCEDURE by_the_numbers(COL_NAME VARCHAR, INTVAL INTEGER) IS
  ...
BEGIN
  -- COL_NAME is interpolated into SQL string
  -- INTVAL gets bound to :1
  stmt := 'SELECT * FROM tbl WHERE ' || COL_NAME || ' = :1';
  ...
END

... where we don't want to permit naive SQL injection in COL_NAME (e.g., a value of '1=1 or 1').

分享到QQ

分享到微博