Обсуждение: Trouble Escaping Quotes

Hello,

I'm using a perl function in my PosgreSQL database to send an email.
However, field data containing single quotes causes the function to fail.
I'm having trouble escaping the quotes.  Sample function definition follows.


If company_name is Bob's Fixit, the function will fail.

CREATE OR REPLACE FUNCTION public.send_rma(rma_info)
  RETURNS void AS
'
    use Mail::Sender;
    my ($rma_data) = @_;
    my ($sender) = new Mail::Sender {
      smtp => \'192.168.1.1\',
      from => \'supp@cog.com\',
     on_errors => \'die\'
    };

    $sender->Open({
      to => \'pgsql-admin@postgresql.org\',
      cc => \'supp@cog.com\',
      subject => "RMA Request $rma_data->{\'rma\'}"
    });

    $sender->SendLineEnc("[RMA]");
    $sender->SendLineEnc("$rma_data->{\'rma\'}");

    $sender->SendLineEnc("[CASE NUMBER]");
    $sender->SendLineEnc("$rma_data->{\'case_number\'}");

    $sender->SendLineEnc("[COMPANY NAME]");
    $sender->SendLineEnc("$rma_data->{\'company_name\'}");

    $sender->Close();

'
  LANGUAGE 'plperlu' STABLE;


Thanks,
Chuck

***********************

Confidentiality notice:  This electronic transmission message is intended only for the use of the individual or entity
towhom it is addressed.  This information should be treated as proprietary, confidential, legally privileged and exempt
fromdisclosure under applicable law.  If the reader of this message is not the intended recipient, (or the employee or
agentresponsible for delivering the message to the intended recipient), you are hereby notified that any use,
dissemination,distribution, or copying of this message is strictly prohibited.  If you have received this communication
inerror, please immediately notify us by telephone (720) 221-9421 or by return e-mail and delete this message.  Thank
youfor your cooperation. 

Haron, Charles wrote:
> Hello,
>
> I'm using a perl function in my PosgreSQL database to send an email.
> However, field data containing single quotes causes the function to fail.
> I'm having trouble escaping the quotes.  Sample function definition follows.

You escape single quotes in PostgreSQL with 2 single quotes... e.g;

print 'foo';

Should be:

print ''foo''; (not double quotes, two single quotes)

Sincerely,

Joshua D. Drake


>
>
> If company_name is Bob's Fixit, the function will fail.
>
> CREATE OR REPLACE FUNCTION public.send_rma(rma_info)
>   RETURNS void AS
> '
>     use Mail::Sender;
>     my ($rma_data) = @_;
>     my ($sender) = new Mail::Sender {
>       smtp => \'192.168.1.1\',
>       from => \'supp@cog.com\',
>      on_errors => \'die\'
>     };
>
>     $sender->Open({
>       to => \'pgsql-admin@postgresql.org\',
>       cc => \'supp@cog.com\',
>       subject => "RMA Request $rma_data->{\'rma\'}"
>     });
>
>     $sender->SendLineEnc("[RMA]");
>     $sender->SendLineEnc("$rma_data->{\'rma\'}");
>
>     $sender->SendLineEnc("[CASE NUMBER]");
>     $sender->SendLineEnc("$rma_data->{\'case_number\'}");
>
>     $sender->SendLineEnc("[COMPANY NAME]");
>     $sender->SendLineEnc("$rma_data->{\'company_name\'}");
>
>     $sender->Close();
>
> '
>   LANGUAGE 'plperlu' STABLE;
>
>
> Thanks,
> Chuck
>
> ***********************
>
> Confidentiality notice:  This electronic transmission message is intended only for the use of the individual or
entityto whom it is addressed.  This information should be treated as proprietary, confidential, legally privileged and
exemptfrom disclosure under applicable law.  If the reader of this message is not the intended recipient, (or the
employeeor agent responsible for delivering the message to the intended recipient), you are hereby notified that any
use,dissemination, distribution, or copying of this message is strictly prohibited.  If you have received this
communicationin error, please immediately notify us by telephone (720) 221-9421 or by return e-mail and delete this
message. Thank you for your cooperation. 
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match


--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage,  and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com

Yes, but how do you escape the quote when the value of the string isn't know
until the function is run?

If $rma_data->{\'company_name\'} is returning Bob's Fixit at run time, how
do escape the quote BEFORE Perl interprets the string and generates an
error?

Regards,
Chuck

> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Thursday, January 20, 2005 3:12 PM
> To: Haron, Charles
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Trouble Escaping Quotes
>
> Haron, Charles wrote:
> > Hello,
> >
> > I'm using a perl function in my PosgreSQL database to send an email.
> > However, field data containing single quotes causes the
> function to fail.
> > I'm having trouble escaping the quotes.  Sample function
> definition follows.
>
> You escape single quotes in PostgreSQL with 2 single quotes... e.g;
>
> print 'foo';
>
> Should be:
>
> print ''foo''; (not double quotes, two single quotes)
>
> Sincerely,
>
> Joshua D. Drake
>
>
> >
> >
> > If company_name is Bob's Fixit, the function will fail.
> >
> > CREATE OR REPLACE FUNCTION public.send_rma(rma_info)
> >   RETURNS void AS
> > '
> >     use Mail::Sender;
> >     my ($rma_data) = @_;
> >     my ($sender) = new Mail::Sender {
> >       smtp => \'192.168.1.1\',
> >       from => \'supp@cog.com\',
> >      on_errors => \'die\'
> >     };
> >
> >     $sender->Open({
> >       to => \'pgsql-admin@postgresql.org\',
> >       cc => \'supp@cog.com\',
> >       subject => "RMA Request $rma_data->{\'rma\'}"
> >     });
> >
> >     $sender->SendLineEnc("[RMA]");
> >     $sender->SendLineEnc("$rma_data->{\'rma\'}");
> >
> >     $sender->SendLineEnc("[CASE NUMBER]");
> >     $sender->SendLineEnc("$rma_data->{\'case_number\'}");
> >
> >     $sender->SendLineEnc("[COMPANY NAME]");
> >     $sender->SendLineEnc("$rma_data->{\'company_name\'}");
> >
> >     $sender->Close();
> >
> > '
> >   LANGUAGE 'plperlu' STABLE;
> >
> >
> > Thanks,
> > Chuck
> >
> > ***********************
> >
> > Confidentiality notice:  This electronic transmission
> message is intended only for the use of the individual or
> entity to whom it is addressed.  This information should be
> treated as proprietary, confidential, legally privileged and
> exempt from disclosure under applicable law.  If the reader
> of this message is not the intended recipient, (or the
> employee or agent responsible for delivering the message to
> the intended recipient), you are hereby notified that any
> use, dissemination, distribution, or copying of this message
> is strictly prohibited.  If you have received this
> communication in error, please immediately notify us by
> telephone (720) 221-9421 or by return e-mail and delete this
> message.  Thank you for your cooperation.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an
> index scan if your
> >       joining column's datatypes do not match
>
>
> --
> Command Prompt, Inc., your source for PostgreSQL replication,
> professional support, programming, managed services, shared
> and dedicated hosting. Home of the Open Source Projects
> plPHP, plPerlNG, pgManage,  and pgPHPtoolkit.
> Contact us now at: +1-503-667-4564 - http://www.commandprompt.com
>
>

On Fri, Jan 21, 2005 at 08:43:00AM -0700, Haron, Charles wrote:
> Yes, but how do you escape the quote when the value of the string isn't know
> until the function is run?
>
> If $rma_data->{\'company_name\'} is returning Bob's Fixit at run time, how
> do escape the quote BEFORE Perl interprets the string and generates an
> error?

Your quote-escaping looks fine to me; \' is just as good as '', although
less standard. I can't reproduce your problem here; your function works
for me under 8.0.0 and perl 5.8.6 . What's your error message when you
call it? Are you sure that your problem isn't happening earlier, when
you put the data into the database in the first place?


Richard

Below is logging information from a client-side SQL monitor, as well as what
I found in <posgresqlhome>/data/serverlog:

I this particular instance the offending quote is in the problem_desc field.
The value of problem_desc being "Bob's Problem" (double quotes excluded).

*** Client-Side SQL monitor data ***
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET DATESTYLE TO ISO;BEGIN;
insert into "rmas"
  ("id", "company_id", "repair_types_id", "printer_models_id",
"serial_number", "problem_desc")
values
  (1240916, 126, 3, 595, '45845384568', 'Bob''s Problem')
Command executed sucessfully
1 row(s) affected
Commiting transaction
END;
SELECT send_rma(rma_info) FROM rma_info WHERE rma = 1240916
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

*** Information From <posgresqlhome>/data/serverlog ***
server closed the connection unexpectedly
         This probably means the server terminated abnormally
         before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:
Message from PostgreSQL backend:
         The Postmaster has informed me that some other backend
         died abnormally and possibly corrupted shared memory.
         I have rolled back the current transaction and am
         going to terminate your database system connection and exit.
         Please reconnect to the database system and repeat your query.
Failed.

> -----Original Message-----
> From: Richard Poole [mailto:rp@guests.deus.net]
> Sent: Sunday, January 23, 2005 11:59 AM
> To: pgsql-admin@postgresql.org
> Cc: Haron, Charles
> Subject: Re: Trouble Escaping Quotes
>
> On Fri, Jan 21, 2005 at 08:43:00AM -0700, Haron, Charles wrote:
> > Yes, but how do you escape the quote when the value of the string
> > isn't know until the function is run?
> >
> > If $rma_data->{\'company_name\'} is returning Bob's Fixit
> at run time,
> > how do escape the quote BEFORE Perl interprets the string and
> > generates an error?
>
> Your quote-escaping looks fine to me; \' is just as good as
> '', although less standard. I can't reproduce your problem
> here; your function works for me under 8.0.0 and perl 5.8.6 .
> What's your error message when you call it? Are you sure that
> your problem isn't happening earlier, when you put the data
> into the database in the first place?
>
>
> Richard
>

"Haron, Charles" <charles.haron@cognitive.com> writes:
> *** Information From <posgresqlhome>/data/serverlog ***
> server closed the connection unexpectedly
>          This probably means the server terminated abnormally
>          before or while processing the request.
> The connection to the server was lost. Attempting reset: WARNING:
> Message from PostgreSQL backend:
>          The Postmaster has informed me that some other backend
>          died abnormally and possibly corrupted shared memory.

Hmm.  That moves it out of the realm of "user error", which is what
I think we'd all been assuming, and into the realm of "server bug".
Can you supply a self-contained test case that causes this?  Or at
least a debugger back trace from the point of the core dump?

            regards, tom lane

This the the SQL statement that causes the error:

******
SELECT Problem_Funct(TestTable1) FROM TestTable1 WHERE TestField1 ~*
'Fixit';
******

This is the dump of the "test case":

*******
--
-- PostgreSQL database dump
--

\connect - postgres

SET search_path = public, pg_catalog;

--
-- TOC entry 6 (OID 352064)
-- Name: plpgsql_call_handler (); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
     AS '$libdir/plpgsql', 'plpgsql_call_handler'
     LANGUAGE c;


--
-- TOC entry 3 (OID 352065)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;


--
-- TOC entry 7 (OID 352066)
-- Name: plperl_call_handler (); Type: FUNCTION; Schema: public; Owner:
postgres
--

CREATE FUNCTION plperl_call_handler () RETURNS language_handler
     AS '$libdir/plperl', 'plperl_call_handler'
     LANGUAGE c;


--
-- TOC entry 4 (OID 352067)
-- Name: plperlu; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
--

CREATE PROCEDURAL LANGUAGE plperlu HANDLER plperl_call_handler;


--
-- TOC entry 5 (OID 352067)
-- Name: plperlu; Type: ACL; Schema: public; Owner:
--

REVOKE ALL ON LANGUAGE plperlu FROM PUBLIC;


--
-- TOC entry 2 (OID 352088)
-- Name: testtable1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE testtable1 (
     testfield1 character varying(25),
     testfield2 text
);


--
-- TOC entry 8 (OID 352094)
-- Name: problem_funct (testtable1); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION problem_funct (testtable1) RETURNS void
     AS '
     use Mail::Sender;
     my ($problem_data) = @_;

     my ($Test1) = $problem_data->{''TestField1''};

'
     LANGUAGE plperlu STABLE;


--
-- Data for TOC entry 9 (OID 352088)
-- Name: testtable1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY testtable1 (testfield1, testfield2) FROM stdin;
Bob's Fixit     Bob's Problem
\.
*******

BTW, I'm using v7.3.3

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, January 24, 2005 12:14 PM
> To: Haron, Charles
> Cc: pgsql-admin@postgresql.org; Richard Poole
> Subject: Re: [ADMIN] Trouble Escaping Quotes
>
> "Haron, Charles" <charles.haron@cognitive.com> writes:
> > *** Information From <posgresqlhome>/data/serverlog ***
> server closed
> > the connection unexpectedly
> >          This probably means the server terminated abnormally
> >          before or while processing the request.
> > The connection to the server was lost. Attempting reset: WARNING:
> > Message from PostgreSQL backend:
> >          The Postmaster has informed me that some other backend
> >          died abnormally and possibly corrupted shared memory.
>
> Hmm.  That moves it out of the realm of "user error", which
> is what I think we'd all been assuming, and into the realm of
> "server bug".
> Can you supply a self-contained test case that causes this?
> Or at least a debugger back trace from the point of the core dump?
>
>             regards, tom lane
>

***********************

Confidentiality notice:  This electronic transmission message is intended only for the use of the individual or entity
towhom it is addressed.  This information should be treated as proprietary, confidential, legally privileged and exempt
fromdisclosure under applicable law.  If the reader of this message is not the intended recipient, (or the employee or
agentresponsible for delivering the message to the intended recipient), you are hereby notified that any use,
dissemination,distribution, or copying of this message is strictly prohibited.  If you have received this communication
inerror, please immediately notify us by telephone (720) 221-9421 or by return e-mail and delete this message.  Thank
youfor your cooperation. 

"Haron, Charles" <charles.haron@cognitive.com> writes:
> BTW, I'm using v7.3.3

Ah so.  It turns out that pre-8.0 versions of plperl fail to cope with
quote marks in fields of rowtype arguments.  I've patched 7.3 and 7.4
for this.  I would recommend updating to 7.3.9 as soon as it comes out
(should be Friday).

            regards, tom lane

I upgraded to 7.3.9, and that fixed the problem.

Thanks,
Chuck

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, January 26, 2005 10:12 AM
> To: Haron, Charles
> Cc: pgsql-admin@postgresql.org; Richard Poole
> Subject: Re: [ADMIN] Trouble Escaping Quotes
>
> "Haron, Charles" <charles.haron@cognitive.com> writes:
> > BTW, I'm using v7.3.3
>
> Ah so.  It turns out that pre-8.0 versions of plperl fail to
> cope with quote marks in fields of rowtype arguments.  I've
> patched 7.3 and 7.4 for this.  I would recommend updating to
> 7.3.9 as soon as it comes out (should be Friday).
>
>             regards, tom lane
>

***********************

Confidentiality notice:  This electronic transmission message is intended only for the use of the individual or entity
towhom it is addressed.  This information should be treated as proprietary, confidential, legally privileged and exempt
fromdisclosure under applicable law.  If the reader of this message is not the intended recipient, (or the employee or
agentresponsible for delivering the message to the intended recipient), you are hereby notified that any use,
dissemination,distribution, or copying of this message is strictly prohibited.  If you have received this communication
inerror, please immediately notify us by telephone (720) 221-9421 or by return e-mail and delete this message.  Thank
youfor your cooperation.