CREATE FUNCTION之后的代码是字符串意味着什么？ [英] What does the code following CREATE FUNCTION being a string imply?

问题描述

来自很好的答复：

在PostgreSQL中， CREATE FUNCTION 确实是一个 SQL语句，但仅仅是一个
包装器，用于指定要执行的代码块
与SQL查询引擎不同。 Postgres（与其他DBMS不同）
支持多个运行时引擎，它们可以执行传递给 CREATE FUNCTION语句的代码块
-
的一个工件是代码实际上是一个字符串，因此CREATE FUNCTION只有
看到一个字符串，什么都没有。

什么代码实际上是一个字符串，因此CREATE FUNCTION仅看到一个字符串，什么都没有的后果是什么？

这被视为动态SQL吗？与动态SQL相比，它是否防止或引入了SQL注入风险？

与代码不是字符串的其他RDBMS（如果有）有什么不同？

谢谢。

解决方案

PostgreSQL是高度可扩展的，您例如，可以定义自己的过程语言来编写函数。

PostgreSQL对语言一无所知，只不过它必须调用某个语言处理程序

选择执行该功能的方法是简化将代码作为字符串传递。

这只是一个实现细节，并不使PostgreSQL函数比其他RDBMS或多或少容易受到SQL注入的攻击。<​​/ p>

您需要在多个级别上进行操作必须防御注入：

• 函数参数：在这里，您应该尽可能选择非字符串数据类型。

  p>



• 该函数内的SQL语句：在此处应尽可能避免使用动态SQL，并且如果必须使用动态SQL，则应使用％L 格式函数的code>模式。

同样，无论是否将函数体指定为字符串，这都是相同的。

From a great reply:

in PostgreSQL, CREATE FUNCTION is indeed a "SQL statement" but is is merely a "wrapper" to specify a block of code that is executed by something different than the SQL query "engine". Postgres (unlike other DBMS) supports multiple "runtime engines" that can execute the block of code that was passed to the "CREATE FUNCTION" statement - one artifact of that is that the code is actually a string so CREATE FUNCTION only sees a string, nothing else.

What are the consequences of "the code is actually a string so CREATE FUNCTION only sees a string, nothing else"?

Is that considered as dynamic SQL? Does it prevent or introduce SQL injection risk, compared to dynamic SQL?

How is that different from other RDBMS (if any?) where "the code is not a string"?

Thanks.

解决方案

PostgreSQL is highly extensible, and you can for example define your own procedural language to write functions in.

PostgreSQL knows nothing about the language except that it has to call a certain language handler to execute the function.

The way that was chosen to implement this is to simplify pass the code as a string.

This is just an implementation detail and does not make PostgreSQL functions any more or less vulnerable to SQL injection than other RDBMS.

There are several levels on which you have to defend yourself against injection:

• The function arguments: Here you should choose non-string data types whenever possible.

• The SQL statements within the function: Here you should avoid dynamic SQL whenever possible, and if you have to use dynamic SQL, you should insert variables using the %L pattern of the format function.

Again, this is the same if function bodies are specified as strings or not.

这篇关于CREATE FUNCTION之后的代码是字符串意味着什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！