PLPGSQL：将参数传递给函数会破坏我的引号

发布于 2024-12-16 15:30:58 字数 918 浏览 1 评论 0原文

如果没有函数，我可以这样做：

DELETE FROM table1
WHERE something='hello'

并且我的值为 some='hello' 的行被删除，但是一旦我实现函数，我就开始遇到引号问题。

CREATE OR REPLACE FUNCTION somefunc(varchar)
RETURNS varchar AS $$
BEGIN

  DELETE FROM table1
  WHERE something='$1';

  DELETE FROM table2
  WHERE something='$1';

  RETURN $1;

END;
$$ LANGUAGE plpgsql;`

似乎什么都不起作用。我已经尝试过（我在SO或其他地方看到的所有变体）：

something=$1   <-- says column "hello" doesn't exist (because no quotes are given)
something=''$1''
something='''$1'''
something=''''$1''''
something='''||$1||'''
something=$Q$$1$Q1$   <--- gives syntax error
something=$Q1$ $1 $Q1$
something=$$ $1 $$
something=quote_literal($1)

以及许多其他变体。我该如何解决这个问题？

顺便说一句，我正在使用 python 脚本来运行该函数。这是运行它的行。我也尝试在这一行中添加引号，但无济于事：

cur.execute("SELECT somefunc(%s);" % (sys.argv[2]))

谢谢！

原文

Without functions, I can do:

DELETE FROM table1
WHERE something='hello'

And my rows with the value of something='hello' get deleted, but as soon I implement functions, I begin to have problems with quotation marks.

CREATE OR REPLACE FUNCTION somefunc(varchar)
RETURNS varchar AS $
BEGIN

  DELETE FROM table1
  WHERE something='$1';

  DELETE FROM table2
  WHERE something='$1';

  RETURN $1;

END;
$ LANGUAGE plpgsql;`

Nothing seems to work. I have tried (all variations that I saw on SO or elsewhere):

something=$1   <-- says column "hello" doesn't exist (because no quotes are given)
something=''$1''
something='''$1'''
something=''''$1''''
something='''||$1||'''
something=$Q$1$Q1$   <--- gives syntax error
something=$Q1$ $1 $Q1$
something=$ $1 $
something=quote_literal($1)

And many other variations. How do I get around this??

Btw, I am using a python script to run the function. Here's the line that runs it. I've also tried adding quotes into this line as well to no avail:

cur.execute("SELECT somefunc(%s);" % (sys.argv[2]))

Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌吟 2024-12-23 15:30:58

此行为基于准备语句的隐式使用。当使用准备好的语句时，查询和参数分别传递到数据库服务器。在这种情况下请勿引用值。
PL/pgSQL 使用准备好的语句，psycopg2 也使用准备好的语句：

...
DECLARE myvar int;
BEGIN
   DELETE FROM mytab WHERE column = myvar; -- quietly using prepared statement

与

DECLARE myvar int;
BEGIN
   -- using dynamic SQL is similar to classic languages, quoting is necessary
   -- but use the quote_literal() function to protect against SQL injection
   EXECUTE 'DELETE FROM mytab WHERE column = ' || quote_literal(myvar);

   -- or dynamic SQL with "USING" clause 
   EXECUTE 'DELETE FROM mytab WHERE column = $1' USING myvar;

This behavior is based on the implicit use of prepare statements. When prepared statements are used, query and parameters are passed to the database server separately. Do not quote values in that scenario.
PL/pgSQL uses prepared statements, psycopg2 uses prepared statements, too:

...
DECLARE myvar int;
BEGIN
   DELETE FROM mytab WHERE column = myvar; -- quietly using prepared statement

versus

DECLARE myvar int;
BEGIN
   -- using dynamic SQL is similar to classic languages, quoting is necessary
   -- but use the quote_literal() function to protect against SQL injection
   EXECUTE 'DELETE FROM mytab WHERE column = ' || quote_literal(myvar);

   -- or dynamic SQL with "USING" clause 
   EXECUTE 'DELETE FROM mytab WHERE column = $1' USING myvar;

回复收藏 0 原文

~没有更多了~