BUG #2228: escaped single quotes choke spi_exec_query in plperlu - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2228
Logged by:          Michael Lush
Email address:      michael@galton.ucl.ac.uk
PostgreSQL version: 8.1.2 and 8.0.3
Operating system:   freeBSD 4.9
Description:        escaped single quotes choke  spi_exec_query in plperlu
Details:

I've written a plperlu (1) script that checks to see if inserted data is
already in the table and if it it marks the new entry as a duplicate.

This works fine for everything except input data that contains a single
quote mark which crashes the transaction regardless of how I escape the
quote.

I've tested it on Postgres 8.1.2 and 8.0.3

(1) We had some issues getting plperl working (I don't recall the details)
given the server enviroment was secure and the need urgent we ended up using
plperlu.

Here is a demo script along with the output I get.

CREATE TABLE test (foo text);
CREATE OR REPLACE FUNCTION quote_bug(text) RETURNS text AS
$$
   $data = $_[0];

   $sql = "SELECT *
             FROM test
            WHERE foo = '$data'";

   $rv_unique = spi_exec_query($sql);

   if ($rv_unique->{processed}) {
        $data .= " dupe";
   }
   return ($data);
$$
LANGUAGE plperlu;

CREATE OR REPLACE FUNCTION do_quote_bug() RETURNS TRIGGER AS
$$
    BEGIN
        NEW.foo := quote_bug(NEW.foo);
        RETURN NEW;
    END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER demo_bug
BEFORE INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE do_quote_bug();

INSERT INTO test VALUES ('No problem here');
INSERT INTO test VALUES ('It''s a problem here');
INSERT INTO test VALUES ('It\'s also a problem here');
INSERT INTO test VALUES ('It''''s inserted a doubled quote');


output
bash-2.05b$ psql -d sandpit  -h dbhost <
/home/genew/Genew4_.triggers/sandpit/quote_bug.sql
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
ERROR:  error from Perl function: syntax error at or near "s" at line 8.
CONTEXT:  PL/pgSQL function "do_quote_bug" line 2 at assignment
ERROR:  error from Perl function: syntax error at or near "s" at line 8.
CONTEXT:  PL/pgSQL function "do_quote_bug" line 2 at assignment
INSERT 0 1

sandpit=# SELECT * FROM test;
          foo
--------------------------------
 No problem here
 It''s inserted a doubled quote
(2 rows)