Postgresql：如何在数据库触发器中转义单引号？

2018-07-03 17:26:34

我创建了一个数据库触发器来将行数据存储在审计表中。在更新操作期间，该触发器从主表获取数据并将其插入到历史记录表中。（历史表有列：日期，操作类型说更新/删除，实际行数据）但由于输入数据中引用的文本，触发器在某些情况下失败。

我怎样才能避免触发器中引用的文字？

--My trigger
CREATE OR REPLACE FUNCTION audit.if_modified() RETURNS TRIGGER AS $function$
DECLARE
    temp_row RECORD; -- a temporary variable used on updates/deletes
    v_sql text;
BEGIN
    IF TG_WHEN <> 'AFTER' THEN
        RAISE EXCEPTION 'audit.if_modified() may only run as an AFTER trigger';
    END IF;

v_sql = 'select * from ' || TG_TABLE_NAME::regclass || '_history';
execute v_sql into temp_row;

select now() into temp_row.action_tstamp_tx;
temp_row.action = SUBSTRING(TG_OP,1,1);
IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = OLD;
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = OLD;
ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = NEW;
ELSE
    RAISE EXCEPTION '[audit.if_modified] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
    RETURN NULL;
END IF;

EXECUTE 'INSERT INTO audit.' || TG_TABLE_NAME::regclass || '_history VALUES (''' || 
temp_row.action_tstamp_tx || ''',''' ||
temp_row.action  || ''',''' ||
temp_row.row_data  || ''')'; 

RETURN NULL;
END;
$function$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = audit,public,pg_catalog;

这适用于正常用例，但如果varchar数据具有单引号文本，则无法将数据加载到历史记录表中。

ERROR:  syntax error at or near "s"
LINE 1: ...VALUES ('2016-02-22 11:44:43.994295-06','U','(6,Tom's,"2016-02...
                                                               ^
QUERY:  INSERT INTO audit.test_history VALUES ('2016-02-22 11:44:43.994295-06','U','(6,Tom's,"2016-02-22 09:49:32.315543")')
CONTEXT:  PL/pgSQL function if_modified() line 30 at EXECUTE

我是Postgresql的新手。我尝试使用类似的选项

regexp_replace() API

和

SELECT into temp_row.row_data unnest(('{' || trim((temp_row.row_data)::text, '()') || '}')::text[]);

等等，但我无法理解如何遍历ROWTYPE数据并创建正确的插入记录。

请分享您的想法，我如何编辑触发器以插入带有单引号的文本。

谢谢，

一般来说，单引号可以通过加倍来逃脱。

为了把你的变量连接到一个SQL字符串中，你应该使用quote_literal() - 该函数负责正确地转义单引号，例如：

quote_literal(temp_row.row_data)

话虽如此：更好（更安全）的解决方案是将参数与format()结合使用：

EXECUTE 
   format('INSERT INTO audit.%I_history values ($1, $2, $3)', tg_table_name)
   using temp_row.action_tstamp_tx, temp_row.action, temp_row.row_data;

%I占位符通常负责正确转义标识符，尽管在这种情况下它不起作用。如果您想100％确定即使非标准表名称正常工作，您也需要先将目标表名称放入一个变量中，然后将其用于format()函数：

l_tablename := TG_TABLE_NAME || '_history';
EXECUTE 
   format('INSERT INTO audit.%I_history values ($1, $2, $3)', l_tablename)
   using ....

这部分：

v_sql = 'select * from ' || TG_TABLE_NAME::regclass || '_history';
execute v_sql into temp_row;

在第一行之后也会失败。 execute .. into ...期望查询返回一个单一的 。您正在使用的语句将返回历史记录表中的所有行。

我也不明白你为什么这样做。

你根本不需要从历史表中选择。

这样的东西应该足够了（ 未经测试！ ）：

IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
    temp_row := OLD;
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
    temp_row := OLD;
ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
    temp_row := NEW;
ELSE
    RAISE EXCEPTION '[audit.if_modified] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
    RETURN NULL;
END IF;

execute format ('insert ... values ($1, $2, $3') 
   using now(), SUBSTRING(TG_OP,1,1), temp_row;

最后：审计触发器之前已经写好，现在有很多现成的解决方案：

使用hstore

使用jsonb

还有一个来自Postgres Wiki的复杂例子

链接地址: http://www.djcxy.com/p/93955.html

上一篇: Postgresql: How to escape single quotes in Database trigger?

下一篇: How to use triggers to prevent duplicate records in PostgreSQL?