Re: [BUGS] BUG #2228: escaped single quotes choke spi_exec_query in

#1

On Wed, 1 Feb 2006, Tom Lane wrote:

Quote:

"Michael Lush" <michael (AT) galton (DOT) 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 (AT) galton (DOT) ucl.ac.uk
The Galton Laboratory
University College London, UK
URL: http://www.gene.ucl.ac.uk/nomenclature/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#2

On Wed, 1 Feb 2006, Tom Lane wrote:

Quote:

michael <michael (AT) galton (DOT) 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 (AT) galton (DOT) ucl.ac.uk
The Galton Laboratory
University College London, UK
URL: http://www.gene.ucl.ac.uk/nomenclature/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2228: escaped single quotes choke spi_exec_query in

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2228: escaped single quotes choke spi_exec_query in - 02-02-2006 , 05:06 PM

Re: [BUGS] BUG #2228: escaped single quotes choke spi_exec_query in - 02-02-2006 , 05:07 PM