PostgreSQL:如何在数据库触发器中转义单引号?

编程入门行业动态更新时间:2024-10-26 03:37:58

本文介绍了PostgreSQL:如何在数据库触发器中转义单引号?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我创建了一个数据库触发器，用于将行数据存储在审计表中. 在更新操作期间，此触发器从主表中获取数据并将其插入到历史表中. (历史记录表包含列:日期，操作类型(例如更新/删除)，实际行数据) 但是由于输入数据中带引号的文本，触发器在某些情况下会失败.

I created a database trigger to store the row data in an auditing table. During the update operation, this trigger takes data from the main table and inserts it to a history table. (history table has columns: date, operation type say Update/Delete, actual row data) But the trigger fails in some cases because of the quoted text in input data.

如何在触发器中转义引用的文本?

How can I escape the quoted text in my trigger?

--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数据具有单引号引起来的文本，则无法将数据加载到历史记录表中.

This works fine for normal use cases but if the varchar data has single quoted text, then it fails to load data into history table.

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的新手.我尝试了类似的选项

I am new to Postgresql. I tried with options like

regexp_replace() API

和

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

等等，但是我不明白如何遍历ROWTYPE数据并创建正确的插入记录.

etc but I couldn't understand how to loop through the ROWTYPE data and create the correct insert record.

请分享您对如何修改触发器以单引号插入文本的想法.

Please share your thoughts on how can I edit my trigger to insert text with single quotes.

谢谢

推荐答案

通常，单引号通过加倍来转义.

In general single, quotes are escaped by doubling them.

要将变量连接到SQL字符串中，应使用quote_literal()-该函数负责正确转义单引号，例如:

To put concatenate your variables into a SQL string, you should use quote_literal() - that function takes care of properly escaping single quote, e.g:

quote_literal(temp_row.row_data)

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

Having said that: the better (and safer) solution is to use parameters combined with 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()函数:

The %I placeholder usually takes care of properly escaping an identifier, although in this case it would not work. If you want to be 100% sure that even non-standard table names work properly, you need to first put the target table name into a variable and use that for the format() function:

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

此部分:

This part:

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

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

is going to fail after the first row as well. execute .. into ... expects the query to return a single. The statement you are using will return all rows from the history table.

我也不明白您为什么首先这样做.

I also don't understand why you do that in the first place.

您根本不需要从历史记录表中进行选择.

You don't need to select from the history table at all.

这样的东西就足够了(未体验！):

Something like this should be enough (untested!):

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;

最后:之前已经编写了审计触发器，并且为此有很多现成的解决方案:

Finally: audit triggers have been written before, and there are a lot of ready-made solutions for this: