SQL中是否有转义变量的机制

Question

我将在PostgreSQL中编写一个接受变量的存储过程(我对SQL的了解接近于零，所以如果问题很明显，我很抱歉(。由于这个变量将在调用中一字不差地使用，我希望确保它被正确地转义以避免注入。

有没有一个函数可以封装变量，它可以正确地进行转义

我特别想在SQL中这样做，而不是在调用SQL查询的代码中清除输入(该变量((可以说这会更容易(。

我很惊讶没有发现任何关于这种功能的突出文档，这让我相信这不是一种标准做法。我最接近的是Postgresql的lexer源代码，但这超出了我的能力，无法理解这是否是所提到的正确转义(这将导致string被用作u&’stringuescape’’’，这看起来相当野蛮(

Answer 1

PostgreSQL中有几个引用函数，记录在https://www.postgresql.org/docs/current/functions-string.html

quote_ident(string text)    text    Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. See also Example 40-1.   quote_ident('Foo bar')  "Foo bar"
quote_literal(string text)  text    Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable. See also Example 40-1.    quote_literal(E'O'Reilly') 'O''Reilly'
quote_literal(value anyelement) text    Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled. quote_literal(42.5) '42.5'
quote_nullable(string text) text    Return the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled. See also Example 40-1.    quote_nullable(NULL)    NULL
quote_nullable(value anyelement)    text    Coerce the given value to text and then quote it as a literal; or, if the argument is null, return NULL. Embedded single-quotes and backslashes are properly doubled.   quote_nullable(42.5)    '42.5'

但是，如果您正在设计从字符串准备SQL的过程，则应该使用查询参数。

PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

在中阅读更多信息https://www.postgresql.org/docs/current/sql-prepare.html