Vous n'êtes pas identifié(e).
Pages : 1
Hello,
In order to audit all changes made on a database and reapply them when necessary on an other copy of this db, i have created triggers on related tables, an audit table and a function which writes in audit table each time a change occures on other tables.
I'm reconstructing the SQL query when writting it in audit.
The problem is with text strings containing apastrophe. I would like to automaticaly replace single apastrophe by double apastrophe in order to have a correct syntax.
Below example of the syntax i'm using:
CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $audit$
BEGIN
--
-- Create a row in auditv2 to reflect the operation performed on tablename,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_TABLE_NAME = 'tablename') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO auditv2 (stamp,query) VALUES(now(), CONCAT('DELETE FROM tablename WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO auditv2 (stamp,query) VALUES( now(), CONCAT('UPDATE tablename SET "typeId"=', NEW."typeId", ',subgroup=''', NEW.subgroup, ''',name=''', NEW.name, ''',"imageName"=''', NEW."imageName", ''' WHERE id=', NEW.id,';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO auditv2 (stamp,query) VALUES( now(), CONCAT('INSERT INTO tablename VALUES(',NEW.id,',''', NEW."typeId",''',''',NEW.subgroup,''',''',NEW.name,''',''',NEW."imageName",''');'));
RETURN NEW;
If name field contains for example "It's the example" i would like to replace it by "It''s the example".
I tried with REPLACE command ( REPLACE (NEW.name, "'", "''") but when inserting a record SQL tells me column "'" doesn't exist!
Any idea?
Hors ligne
Hi,
I think what you are looking for is quote_nullable: https://www.postgresql.org/docs/current … tring.html
This will protect everything in you strings. Because there is a lot of room for SQL injections in what you are doing…
Marc.
Hors ligne
Hello,
I tried with quote_nullable and quote_literal but it doesn't produces expected result.
For example i have a variable new.text containing txt : doesn't.
quote_nullable(new.text) returns 'doesn't' while i would have expected to get 'doesn''t'
I obtain same result with quote_literal.
Hors ligne
It does:
select E'doesn\'t';
?column?
----------
doesn't
select quote_nullable(E'doesn\'t');
quote_nullable
----------------
'doesn''t'Something else must be wrong. Please provide a complete test case.
Marc.
Hors ligne
It works in your example.
But how can i manage it in the case of a variable new.text containing << doesn't >> with no backslash?
quote_nullable(new.text) doesn't return expected result.
Hors ligne
It works exactly the same. There must be something wrong in the way you do it. Post an example function where you have a problem.
Marc.
Hors ligne
Let's consider:
-table test containing fields id and description.
-Table audit containing fields id, timestamp, query
-The audit_function:
CREATE OR REPLACE FUNCTION audit_function() RETURNS TRIGGER AS $audit$
BEGIN
--
-- Create a row audit to reflect the operation performed on test,
--
IF (TG_TABLE_NAME = 'test') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit (stamp,query) VALUES(now(), CONCAT('DELETE FROM test WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('UPDATE test SET id=', NEW.id, ',description=', quote_literal(NEW.description)';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO test VALUES(',NEW.id,',',quote_literal(NEW.description),');'));
RETURN NEW;
END IF;
RETURN NULL;
END;
$audit$ LANGUAGE plpgsql;
-Trigger
CREATE TRIGGER test_audit
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE audit_function();
If i insert record id=1, description=Here's the example (INSERT INTO test (id,description) VALUES (1,'Here''s the example');)
I will get in audit table a record with query=INSERT INTO test (stamp,query) VALUES (yyyymmdd-hhmmss,'Here's the example');
Hors ligne
Ok, first thing: please don't post untested code… this one didn't run as is (the first IF has no END IF)
Second: once corrected, everything works here:
test=# create table audit (stamp timestamp with time zone, query text);
test=# create table test (id int, description text);
test=# CREATE TRIGGER test_audit
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE audit_function();
test=# CREATE OR REPLACE FUNCTION audit_function() RETURNS TRIGGER AS
$audit$
BEGIN
--
-- Create a row audit to reflect the operation performed on test,
--
IF (TG_TABLE_NAME = 'test') THEN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit (stamp,query) VALUES(now(), CONCAT('DELETE FROM test WHERE id=',OLD.id,';'));
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('UPDATE test SET id=', NEW.id, ',description=', quote_literal(NEW.description)';'));
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit (stamp,query) VALUES( now(), CONCAT('INSERT INTO test VALUES(',NEW.id,',',quote_literal(NEW.description),');'));
RETURN NEW;
END IF;
END IF;
RETURN NULL;
END;
$audit$
LANGUAGE plpgsql;
test=# select * from audit ;
stamp | query
-------------------------------+------------------------------------
2016-07-20 15:21:29.036159+02 | INSERT INTO test VALUES(1,'toto');
(1 ligne)
test=# insert into test values (1,'toto');
test=# insert into test values (2,'Here''s the example');
INSERT 0 1
test=# select * from audit ;
stamp | query
-------------------------------+---------------------------------------------------
2016-07-20 15:21:29.036159+02 | INSERT INTO test VALUES(1,'toto');
2016-07-20 15:21:53.053199+02 | INSERT INTO test VALUES(2,'Here''s the example');
(2 lignes)So I think you have another problem in your code.
Marc.
Hors ligne
Apologize for this.
I might have missed an update when doing my test, you are right this is working fine.
Thanks for your help.
Hors ligne
No need to apologize… I'm happy your problem's solved 
Marc.
Hors ligne
Pages : 1