Обсуждение: BUG #2228: escaped single quotes choke spi_exec_query in plperlu

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)

"Michael Lush" <michael@galton.ucl.ac.uk> writes:
> 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);

It's hardly a bug that you get a syntax error when $data contains
a single quote.  It's up to you to construct a well-formed query
string to give to spi_exec_query, and this code is not doing that.

            regards, tom lane

michael <michael@galton.ucl.ac.uk> writes:
> On Wed, 1 Feb 2006, Tom Lane wrote:
>> It's hardly a bug that you get a syntax error when $data contains
>> a single quote.  It's up to you to construct a well-formed query
>> string to give to spi_exec_query, and this code is not doing that.

> As I understand it the  input strings are correctly escaped

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

Yeah, but by the time your trigger sees it, the data isn't escaped
anymore.

            regards, tom lane

On Wed, 1 Feb 2006, Tom Lane wrote:
> "Michael Lush" <michael@galton.ucl.ac.uk> writes:
> > 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);
>
> It's hardly a bug that you get a syntax error when $data contains
> a single quote.  It's up to you to construct a well-formed query
> string to give to spi_exec_query, and this code is not doing that.
>
>             regards, tom lane


As I understand it the  input strings are correctly escaped

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Michael John Lush PhD            Tel:44-20-7679-5027
Nomenclature Bioinformatician        Fax:44-20-7387-3496
HUGO Gene Nomenclature Committee    Email:  nome@galton.ucl.ac.uk
The Galton Laboratory
University College London, UK
URL: http://www.gene.ucl.ac.uk/nomenclature/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Wed, 1 Feb 2006, Tom Lane wrote:

> michael <michael@galton.ucl.ac.uk> writes:
> > On Wed, 1 Feb 2006, Tom Lane wrote:
> >> It's hardly a bug that you get a syntax error when $data contains
> >> a single quote.  It's up to you to construct a well-formed query
> >> string to give to spi_exec_query, and this code is not doing that.
>
> > As I understand it the  input strings are correctly escaped
>
> > 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');
>
> Yeah, but by the time your trigger sees it, the data isn't escaped
> anymore.

The light dawns,  Thankyou very much for clearing that up for me!


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Michael John Lush PhD            Tel:44-20-7679-5027
Nomenclature Bioinformatician        Fax:44-20-7387-3496
HUGO Gene Nomenclature Committee    Email:  nome@galton.ucl.ac.uk
The Galton Laboratory
University College London, UK
URL: http://www.gene.ucl.ac.uk/nomenclature/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~