PostgreSQL 美元引用字符串常量以防止 SQL 注入
我可以使用 PostgreSQL 的美元引用字符串常量安全地防止 SQL 注入吗？
我知道处理动态查询的最佳方法是使用参数化查询在应用程序层中生成它们，这不是这个问题的目的。所有业务逻辑都在存储过程中。
我有一个存储过程，它接受参数并生成查询，运行它，格式化结果并将其作为文本块返回。该函数传递一个表名、列名和 WHERE 参数。传递给函数的 WHERE 参数来自用户在数据库中输入的数据。我想确保刺痛已被净化，以便构建的查询是安全的。
使用 PostgreSQLs Dollar-Quoted Strings Constants，我应该能够安全地清理除“$$”之外的所有字符串输入。但是，如果我对“$”进行字符串替换以转义它，我应该能够进行安全的字符串比较。
存储过程：
function_name(tablename text, colnames text[], whereparam text)
--Build dynamic query...
函数调用：
SELECT
function_name('tablename', ARRAY['col1', 'col2', 'col3'], 'AND replace(col1, ''$'', ''/$'') = $$' || replace(alt_string_col, '$', '/$') || '$$ ')
FROM alttable
WHERE alt_id = 123;
生成查询：
SELECT col1, col2, col3 FROM tablename WHERE 1=1 AND replace(col1, '$', '/$') = $$un/safe'user /$/$ data;$$
由于在将 col1 字段与转义的用户数据进行比较之前，我对它进行了转义，即使用户输入“un/safe'user $$ data;”在 alt_string_col 字段中，双美元符号不会破坏查询并且比较会通过。
这是在 PostgreSQL 存储过程中转义字符串的安全方法吗？
编辑1
感谢 Erwin Brandstetter。使用 EXECUTE 的 USING 子句我打算创建一个可以像这样调用的函数：
SELECT function_name(
'tablename',
ARRAY['col1', 'col2', 'col3'],
ARRAY[' AND col1 = $1 ', ' OR col2 = $5 '],
quote_literal(alt_string_col)::text, --Text 1-4
NULL::text,
NULL::text,
NULL::text,
alt_active_col::boolean, --Bool 1-4
NULL::boolean,
NULL::boolean,
NULL::boolean,
NULL::integer, --Int 1-4
NULL::integer,
NULL::integer,
NULL::integer
)
FROM alttable
WHERE alt_id = 123;
它为可以传入的 WHERE 子句提供了一定的灵活性
。存储过程 我有类似这样的 EXECUTE 语句。
FOR results IN EXECUTE(builtquery) USING
textParm1,
textParm2,
textParm3,
textParm4,
boolParm1,
boolParm2,
boolParm3,
boolParm4,
intParm1,
intParm2,
intParm3,
intParm4
LOOP
-- Do some stuff
END LOOP;
Can I safely prevent SQL Injection using PostgreSQL's Dollar-Quoted String Constants?
I know the best was to handle dynamic queries is to have them generated in a application layer with a parametrized query, that's not what this question is about. All of the business logic is in stored procedures.
I have a stored procedure that takes parameters and generates a query, runs it, formats the results and returns it as a chunk of text. This function is passed a table name, column names and WHERE parameters. The WHERE parameters passed to the function are from user entered data in the database. I would like to make sure that the stings are sanitized so the query that is built is safe.
Using PostgreSQLs Dollar-Quoted Strings Constants, I should be able to safely sanitize all string input other than ' $$ '. However, if I do a string replace on "$" to escape it, I should be able to do a string comparison that is safe.
Stored Procedure:
function_name(tablename text, colnames text[], whereparam text)
--Build dynamic query...
Function Call:
SELECT
function_name('tablename', ARRAY['col1', 'col2', 'col3'], 'AND replace(col1, ''
Query Generated:
SELECT col1, col2, col3 FROM tablename WHERE 1=1 AND replace(col1, '
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
SELECT function_name(
'tablename',
ARRAY['col1', 'col2', 'col3'],
ARRAY[' AND col1 = $1 ', ' OR col2 = $5 '],
quote_literal(alt_string_col)::text, --Text 1-4
NULL::text,
NULL::text,
NULL::text,
alt_active_col::boolean, --Bool 1-4
NULL::boolean,
NULL::boolean,
NULL::boolean,
NULL::integer, --Int 1-4
NULL::integer,
NULL::integer,
NULL::integer
)
FROM alttable
WHERE alt_id = 123;
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
FOR results IN EXECUTE(builtquery) USING
textParm1,
textParm2,
textParm3,
textParm4,
boolParm1,
boolParm2,
boolParm3,
boolParm4,
intParm1,
intParm2,
intParm3,
intParm4
LOOP
-- Do some stuff
END LOOP;
', ''/
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
') = $' || replace(alt_string_col, '
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
, '/
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
) || '$ ')
FROM alttable
WHERE alt_id = 123;
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
, '/
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
', ''/
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
') = $' || replace(alt_string_col, '
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
, '/
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
) || '$ ')
FROM alttable
WHERE alt_id = 123;
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
Query Generated:
Since I'm escaping the col1 field before I compare it to escaped user data, even if the user enters, "un/safe'user $$ data;" in the field, alt_string_col, the double dollar sign does not break the query and the comparison passes.
Is this a safe way to escape strings in PostgreSQL stored procedure?
Edit1
Thanks to Erwin Brandstetter. Using the USING clause for EXECUTE I was about to create a function that can be called like this:
It gives some flexibility to the WHERE clauses that can be passed in.
Inside the stored procedure I have something like this for the EXECUTE statement.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用
quote_ident()来防范连接标识符时的 SQL 注入。或者format()< /a> 在 Postgres 9.1 或更高版本中。使用
USINGPL/pgSQL 代码中的EXECUTE子句用于传递值。或者至少是quote_literal()。要确保表名存在（并且在连接时必要时自动引用和模式限定），请使用 特殊数据类型
regclass。有关使用 PL/pgSQL 执行动态 SQL 的更多信息：
从 PostgreSQL 9.0 开始，您可以还可以使用 匿名带有
DO语句的代码块来执行动态SQL。Use
quote_ident()to safeguard against SQL injection while concatenating identifiers. Orformat()in Postgres 9.1 or later.Use the
USINGclause forEXECUTEin PL/pgSQL code to pass values. Or at leastquote_literal().To make sure a table name exists (and is quoted and schema-qualified automatically where necessary when concatenated), use the special data type
regclass.More about executing dynamic SQL with PL/pgSQL:
Since PostgreSQL 9.0 you can also use anonymous code blocks with the
DOstatement to execute dynamic SQL.