Skip Menu |
 

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 40440
Status: rejected
Priority: 0/
Queue: DBD-Pg

People
Owner: Nobody in particular
Requestors: lpsolit [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: DBI->quote(quotemeta("foo-bar")) returns invalid string
Date: Tue, 28 Oct 2008 01:40:11 +0100
To: bug-DBD-Pg [...] rt.cpan.org
From: Frédéric Buclin <lpsolit [...] gmail.com>
Download (untitled) / with headers
text/plain 409b
While running the following command: perl -MBugzilla -e '$dbh = Bugzilla->dbh; print $dbh->quote(quotemeta("key-selenium-ktwo"))' it returns E'key\\-selenium\\-ktwo' instead of 'key\\-selenium\\-ktwo' when using either DBD::Pg 2.10.3 or 2.11.2. The problem doesn't occur with DBD::Pg 1.49. This generates invalid strings which, when used in SQL queries, generate crashes. I'm using Perl 5.10.0 on Linux.
Download (untitled) / with headers
text/plain 196b
Why are you using quotemeta here? Please describe in detail what sort of crash occurs. You should in general be using placeholders, and not trying to construct queries from the output of quote().
Subject: Re: [rt.cpan.org #40440] DBI->quote(quotemeta("foo-bar")) returns invalid string
Date: Mon, 27 Oct 2008 18:01:33 -0700
To: bug-DBD-Pg [...] rt.cpan.org
From: "David E. Wheeler" <dwheeler [...] cpan.org>
Download (untitled) / with headers
text/plain 375b
On Oct 27, 2008, at 17:58, Greg Sabino Mullane via RT wrote: Show quoted text
> Why are you using quotemeta here? Please describe in detail what > sort of > crash occurs. You should in general be using placeholders, and not > trying to construct queries from the output of quote().
Also, what version of PostgreSQL are you using and against what version did you compile DBD::Pg? David
Subject: Re: [rt.cpan.org #40440] DBI->quote(quotemeta("foo-bar")) returns invalid string
Date: Tue, 28 Oct 2008 19:12:24 +0100
To: bug-DBD-Pg [...] rt.cpan.org
From: Frédéric Buclin <lpsolit [...] gmail.com>
Download (untitled) / with headers
text/plain 1.2k
Show quoted text
> Why are you using quotemeta here?
quotemeta() is currently in use in the Bugzilla code, which I'm contributing to. The context in which it's used is available here: http://mxr.mozilla.org/mozilla/source/webtools/bugzilla/Bugzilla/Search.pm#878 We use quotemeta() to escape characters before using the string in a regexp. I don't know if $dbh->quote() already escapes everything which is required to be used in regexp, which is why quotemeta() is called first. Show quoted text
> Please describe in detail what sort of crash occurs.
The problem is described here: https://bugzilla.mozilla.org/show_bug.cgi?id=461729 And the error is: DBD::Pg::st execute failed: ERREUR: erreur de syntaxe sur ou près de « foo » LINE 1: ...EOPENED') ) AND (bugs.keywords ~* '(^|[^a-z0-9])E'foo\\-bar(... The relevant part of the SQL query is: WHERE (( bugs.bug_status IN ('NEW','ASSIGNED','REOPENED') ) AND (bugs.keywords ~* '(^|[^a-z0-9])E'foo\\-bar($|[^a-z0-9])')) You should in general be using placeholders, and not Show quoted text
> trying to construct queries from the output of quote().
I know that, and our modern code does this. But in the case of Search.pm, that's pretty hard to do. I use PostgreSQL 8.3.4 and tried both DBD::Pg 2.10.3 and 2.11.2.
Subject: Re: [rt.cpan.org #40440] DBI->quote(quotemeta("foo-bar")) returns invalid string
Date: Tue, 28 Oct 2008 11:20:30 -0700
To: bug-DBD-Pg [...] rt.cpan.org
From: "David E. Wheeler" <dwheeler [...] cpan.org>
Download (untitled) / with headers
text/plain 558b
On Oct 28, 2008, at 11:12, Frédéric Buclin via RT wrote: Show quoted text
> The problem is described here: > > https://bugzilla.mozilla.org/show_bug.cgi?id=461729 > > And the error is: > > DBD::Pg::st execute failed: ERREUR: erreur de syntaxe sur ou près > de « > foo » > LINE 1: ...EOPENED') ) AND (bugs.keywords ~* '(^|[^a-z0-9])E'foo\\- > bar(... > > The relevant part of the SQL query is: > > WHERE (( bugs.bug_status IN ('NEW','ASSIGNED','REOPENED') ) AND > (bugs.keywords ~* '(^|[^a-z0-9])E'foo\\-bar($|[^a-z0-9])'))
Which part of that is $word? Bet, David
Subject: Re: [rt.cpan.org #40440] DBI->quote(quotemeta("foo-bar")) returns invalid string
Date: Tue, 28 Oct 2008 19:33:16 +0100
To: bug-DBD-Pg [...] rt.cpan.org
From: Frédéric Buclin <lpsolit [...] gmail.com>
Download (untitled) / with headers
text/plain 205b
Show quoted text
>> WHERE (( bugs.bug_status IN ('NEW','ASSIGNED','REOPENED') ) AND >> (bugs.keywords ~* '(^|[^a-z0-9])E'foo\\-bar($|[^a-z0-9])'))
> > Which part of that is $word?
E'foo\\-bar. It was originally foo-bar.
Show quoted text
> LINE 1: ...EOPENED') ) AND (bugs.keywords ~* '(^|[^a-z0-9])E'foo\\- > bar(...
There's the problem. You can't just plug the output of $dbh->quote back into a random string. The only guarantee is that passing the output back to the *database* will produce the original string. If you look at the DBI docs for DBI, you'll see this example: === It is valid for the quote() method to return an SQL expression that evaluates to the desired string. For example: $quoted = $dbh->quote("one\ntwo\0three") may return something like: CONCAT('one', CHAR(12), 'two', CHAR(0), 'three') === What you need to do is to quote the final compiled string, and pass that directly in. That's far inferior to using a placeholder in the first place, but it should suffice. Other than that, there is no way around it - E'' is now the official Postgres way. Best: .. 'AND bugs.keywords ~* ?' If you must: $string = $dbh->quote( q{(^|[^a-z0-9])} . quotemeta('foo-bar') . q{($|[^a-z0-9])} ); $SQL .= "AND bugs.keywords ~* $safestring";
Subject: Re: [rt.cpan.org #40440] DBI->quote(quotemeta("foo-bar")) returns invalid string
Date: Wed, 29 Oct 2008 10:40:47 +0100
To: bug-DBD-Pg [...] rt.cpan.org
From: Frédéric Buclin <lpsolit [...] gmail.com>
Download (untitled) / with headers
text/plain 290b
Show quoted text
> What you need to do is to quote the final compiled string, and pass that > directly in.
Thanks a lot, greg. I did that and this indeed fixes our problem. Probably the original code was written with MySQL only in mind. You can close this bug report as invalid. Thanks again! Frédéric


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.