{"id":53,"date":"2004-11-02T02:42:13","date_gmt":"2004-11-02T06:42:13","guid":{"rendered":"http:\/\/blogs.law.harvard.edu\/rlucastemp\/2004\/11\/02\/fix-perl-dbi-dbdpg-bind-values-rel"},"modified":"2004-11-02T02:42:13","modified_gmt":"2004-11-02T06:42:13","slug":"fix-perl-dbi-dbdpg-bind-values-rely-on-perls-automatic-numericstrin","status":"publish","type":"post","link":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/2004\/11\/02\/fix-perl-dbi-dbdpg-bind-values-rely-on-perls-automatic-numericstrin\/","title":{"rendered":"[FIX] Perl DBI \/ DBD::Pg bind values rely on Perl’s automatic numeric\/string scalar conversion"},"content":{"rendered":"

<\/a><\/p>\n

Scenario: you are using DBD::Pg to interface with your database (perhaps directly through DBI, or through an abstraction layer like Class::DBI or DBIx::ContextualFetch) when you get an odd result:<\/p>\n

DBD::Pg::st execute failed: ERROR: parser: parse error at or near [your string, or the part of your string that doesn’t begin with leading digits] at …<\/p>\n

or<\/p>\n

DBD::Pg::db selectrow_array failed: ERROR: Attribute “yourstring” not found at …<\/p>\n

If you look at the PostgreSQL query log, you’ll see that “yourstring” was not properly quoted as a literal in the SQL delivered to the parser.<\/p>\n

Since you’ve either been relying upon your abstraction layer or personally doing the Right Thing and binding your values with the “WHERE thing=? AND otherthing=?” syntax, you’re quite confused — this should all be quoted.<\/p>\n

The problem is that Perl has flagged that scalar as a numeric value, possibly because you used a numeric operator on it (like > or == instead of gt or eq). The solution is to upgrade to DBD::Pg 1.32 or to explicitly stringify your string as “$yourstring”.<\/p>\n

Below is the bug filed with CPAN.
\n—
\nMac OS X 10.2, Perl 5.6.0, DBD::Pg 1.22, DBI 1.45<\/p>\n

Bind values appear to rely upon Perl’s automatic numeric\/string scalar conversion in order to determine whether or not to quote.<\/p>\n

This bug was discussed on
\nhttp:\/\/aspn.activestate.com\/ASPN\/Mail\/Message\/perl-DBI-dev\/1607287<\/p>\n

my $dbh = DBI->connect(…); #connect to Postgres; no errors with SQLite
\nmy $scalar = “abc”;
\nwarn “scalar is greater than zero (and now considered numeric)” if $scalar > 0;
\nwarn “dbh->quote(scalar) works ok: ” . $dbh->quote($scalar);
\nwarn “but bind values do not:” . $dbh->selectrow_array(
\n “SELECT 1 WHERE 1=?”,
\n undef,
\n ($scalar)
\n);<\/p>\n

Using a numeric operator on the scalar makes Perl auto-convert it to a number; this is interpreted by the magic in Pg.xs as rendering the scalar ineligible for quoting.<\/p>\n

One solution is to bind those variables that must be text but might have been numberified with “$varname”, thereby stringifying them in the eyes of Perl.<\/p>\n

[FIX] Perl DBI \/ DBD::Pg bind values rely on Perl’s automatic numeric\/string scalar conversion …<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Scenario: you are using DBD::Pg to interface with your database (perhaps directly through DBI, or through an abstraction layer like Class::DBI or DBIx::ContextualFetch) when you get an odd result: DBD::Pg::st execute failed: ERROR: parser: parse error at or near [your string, or the part of your string that doesn’t begin with leading digits] at … […]<\/p>\n","protected":false},"author":1180,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1460],"tags":[],"class_list":["post-53","post","type-post","status-publish","format-standard","hentry","category-rlucasstories"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/posts\/53","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/users\/1180"}],"replies":[{"embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/comments?post=53"}],"version-history":[{"count":0,"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/posts\/53\/revisions"}],"wp:attachment":[{"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/media?parent=53"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/categories?post=53"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/archive.blogs.harvard.edu\/rlucastemp\/wp-json\/wp\/v2\/tags?post=53"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}