Forums PostgreSQL.fr

massup

Membre

Automic replacement in SQL query

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?

Marc Cousin

Membre

Re : Automic replacement in SQL query

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.

massup

Membre

Re : Automic replacement in SQL query

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.

Marc Cousin

Membre

Re : Automic replacement in SQL query

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.

massup

Membre

Re : Automic replacement in SQL query

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.

Marc Cousin

Membre

Re : Automic replacement in SQL query

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.

massup

Membre

Re : Automic replacement in SQL query

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');

Marc Cousin

Membre

Re : Automic replacement in SQL query

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.

massup

Membre

Re : Automic replacement in SQL query

Apologize for this.
I might have missed an update when doing my test, you are right this is working fine.
Thanks for your help.

Marc Cousin

Membre

Re : Automic replacement in SQL query

No need to apologize… I'm happy your problem's solved

---

Marc.