pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trouble Escaping Quotes
9+ messages / 4 participants
[nested] [flat]

* Trouble Escaping Quotes
@ 2005-01-20 21:13 Haron, Charles <charles.haron@cognitive.com>
  2005-01-20 22:11 ` Re: Trouble Escaping Quotes Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Haron, Charles @ 2005-01-20 21:13 UTC (permalink / raw)
  To: pgsql-admin


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 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.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
  2005-01-20 21:13 Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
@ 2005-01-20 22:11 ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Joshua D. Drake @ 2005-01-20 22:11 UTC (permalink / raw)
  To: Haron, Charles <charles.haron@cognitive.com>; +Cc: pgsql-admin

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

Attachments:

jd.vcf (text/x-vcard, 638B) download begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard
^ permalink raw reply [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
@ 2005-01-21 15:43 Haron, Charles <charles.haron@cognitive.com>
  2005-01-23 18:59 ` Re: Trouble Escaping Quotes Richard Poole <rp@guests.deus.net>
  0 siblings, 1 reply; 9+ messages in thread

From: Haron, Charles @ 2005-01-21 15:43 UTC (permalink / raw)
  To: pgsql-admin

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
> 
> 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
  2005-01-21 15:43 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
@ 2005-01-23 18:59 ` Richard Poole <rp@guests.deus.net>
  0 siblings, 0 replies; 9+ messages in thread

From: Richard Poole @ 2005-01-23 18:59 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Haron, Charles <charles.haron@cognitive.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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
@ 2005-01-24 16:34 Haron, Charles <charles.haron@cognitive.com>
  2005-01-24 19:13 ` Re: Trouble Escaping Quotes Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Haron, Charles @ 2005-01-24 16:34 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Richard Poole <rp@guests.deus.net>

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
> 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
  2005-01-24 16:34 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
@ 2005-01-24 19:13 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2005-01-24 19:13 UTC (permalink / raw)
  To: Haron, Charles <charles.haron@cognitive.com>; +Cc: pgsql-admin@postgresql.org, Richard Poole <rp@guests.deus.net>

"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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
@ 2005-01-26 16:20 Haron, Charles <charles.haron@cognitive.com>
  2005-01-26 17:12 ` Re: Trouble Escaping Quotes Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Haron, Charles @ 2005-01-26 16:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Haron, Charles <charles.haron@cognitive.com>; +Cc: pgsql-admin@postgresql.org, Richard Poole <rp@guests.deus.net>



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 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.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
  2005-01-26 16:20 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
@ 2005-01-26 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2005-01-26 17:12 UTC (permalink / raw)
  To: Haron, Charles <charles.haron@cognitive.com>; +Cc: pgsql-admin@postgresql.org, Richard Poole <rp@guests.deus.net>

"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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Trouble Escaping Quotes
@ 2005-02-12 18:50 Haron, Charles <charles.haron@cognitive.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Haron, Charles @ 2005-02-12 18:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Haron, Charles <charles.haron@cognitive.com>; +Cc: pgsql-admin@postgresql.org, Richard Poole <rp@guests.deus.net>


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 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.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2005-02-12 18:50 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-20 21:13 Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
2005-01-20 22:11 ` Joshua D. Drake <jd@commandprompt.com>
2005-01-21 15:43 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
2005-01-23 18:59 ` Richard Poole <rp@guests.deus.net>
2005-01-24 16:34 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
2005-01-24 19:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-26 16:20 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>
2005-01-26 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-12 18:50 Re: Trouble Escaping Quotes Haron, Charles <charles.haron@cognitive.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox