Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 2F4B49FB386
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  6 Jul 2007 11:14:52 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 45953-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri,  6 Jul 2007 11:14:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3])
	by postgresql.org (Postfix) with ESMTP id DF4089FB358
	for <pgsql-sql@postgresql.org>; Fri,  6 Jul 2007 11:14:47 -0300 (ADT)
Received: from webaccess.indicator.be ([10.100.1.7]) by
	interscan.indicator.com with InterScan Message Security Suite;
	Fri, 06 Jul 2007 15:59:02 +0200
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Fri, 06 Jul 2007 16:14:44 +0200
Message-Id: <468E6A69.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.1 
Date: Fri, 06 Jul 2007 16:14:34 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: <pgsql-sql@postgresql.org>
Subject: Quote problem
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__PartB493715A.0__="
X-imss-version: 2.047
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/36
X-Sequence-Number: 28771

--=__PartB493715A.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

Consider a perl function like this
CREATE OR REPLACE FUNCTION public.test(text) RETURNS VOID AS
$body$
  my $var =3D shift;
  spi_exec_query("INSERT INTO tbltest(field1) VALUES ('$var')");
$body$
LANGUAGE 'plperlu' VOLATILE;
and a table
CREATE TABLE tbltest (
  field1 text
) WITHOUT OIDS;
=20
When I call the function like
SELECT test('Do this')
it works. But when I call it like
SELECT test('Don't do that')
it fails (of course). So I call it like
SELECT test('Don''t do that')
It still fails and I do understand why. But how do I solve it in the =
function?
Using a prepared statement would be a solution, like
  $sel =3D spi_prepare('SELECT test($1)', 'text');
  spi_exec_prepared($sel, $var);
But I need to get it solved without prepared statements.
What is the best approach considering that $var could contain both
single and double quotes?
Thanks
=20
=20
=20
=20
=20

--=__PartB493715A.0__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-15=
">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>Consider a perl function like this</DIV>
<DIV>CREATE OR REPLACE FUNCTION public.test(text) RETURNS VOID AS</DIV>
<DIV>$body$</DIV>
<DIV>&nbsp; my $var =3D shift;</DIV>
<DIV>&nbsp; spi_exec_query("INSERT INTO tbltest(field1) VALUES ('$var')");<=
/DIV>
<DIV>$body$</DIV>
<DIV>LANGUAGE 'plperlu' VOLATILE;</DIV>
<DIV>and a table</DIV>
<DIV>CREATE TABLE tbltest (</DIV>
<DIV>&nbsp; field1 text</DIV>
<DIV>) WITHOUT OIDS;</DIV>
<DIV>&nbsp;</DIV>
<DIV>When I call the function like</DIV>
<DIV>SELECT test('Do this')</DIV>
<DIV>it works. But when I call it like</DIV>
<DIV>
<DIV>SELECT test('Don't do that')</DIV>
<DIV>it fails (of course). So I call it like</DIV>
<DIV>
<DIV>SELECT test('Don''t do that')</DIV>
<DIV>It still fails and I do understand why. But how do I solve it in the =
function?</DIV>
<DIV>Using a prepared statement would be a solution, like</DIV>
<DIV>&nbsp; $sel =3D spi_prepare('SELECT test($1)', 'text');</DIV>
<DIV>&nbsp; spi_exec_prepared($sel, $var);</DIV>
<DIV>But I need to get it solved without prepared statements.</DIV>
<DIV>What is the best approach considering that $var could contain =
both</DIV>
<DIV>single and double quotes?</DIV>
<DIV>Thanks</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV></DIV></DIV>
<DIV>&nbsp;</DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

--=__PartB493715A.0__=--