DBD::Pg::st execute failed: ERROR: syntax error at or near "$1"

Question

Trying to set a value in PostgreSQL using Perl and DBI, and thus DBD::Pg.

I'm getting an odd error.

2013-05-23 19:02:36.641139500 updating status to 0
2013-05-23 19:02:36.641410500 DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
2013-05-23 19:02:36.641418500 LINE 1: UPDATE instances SET $1 = $2
2013-05-23 19:02:36.641423500                              ^ at /usr/lib/perl5/vendor_perl/Mitel/MslRest/mbg.pm line 161.
2013-05-23 19:02:36.642425500 [Thu May 23 19:02:36 2013] [error] DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
2013-05-23 19:02:36.642438500 LINE 1: UPDATE instances SET $1 = $2
2013-05-23 19:02:36.642443500                              ^ at /usr/lib/perl5/vendor_perl/Mitel/MslRest/mbg.pm line 161.
2013-05-23 19:02:36.642447500

The relevant code is

my $sql = "UPDATE instances SET ? = ?";
my $dbh = Mitel::tug::getdbh();
$dbh->begin_work;
my $sth = $dbh->prepare($sql);
unless ($sth) {
    return $self->internal_error("prepare failed: " . $dbh->errstr);
}
foreach my $propname (sort keys %{ $raw_data }) {
    my $propval = $raw_data->{$propname};
    print STDERR "updating $propname to $propval\n";
    if (! $sth->execute($propname, $propval)) {
        $dbh->rollback;
        $sth->finish;
        return $self->internal_error("execute: " . $dbh->errstr);
    }
}
$dbh->commit;
$sth->finish;

So I'm trying to update "status" to 0, using the execute method to prevent sql injection, but I'm getting a syntax error for some reason.

Anyone run into this?

root@miketug2 ~]# perl -v

This is perl, v5.10.1 (*) built for i386-linux-thread-multi

perl-DBD-Pg-2.15.1-4.el6_3.i686
postgresql84-server-8.4.14-1PGDG.rhel6.i686

root@miketug2 ~]# uname -a
Linux miketug2 2.6.32-279.22.1.el6.i686 #1 SMP Wed Feb 6 00:31:03 UTC 2013 i686 i686 i386 GNU/Linux

Answer 1

PostgreSQL prefers that you use numbered placeholders ($1, $2, ...) over the positional ? placeholders so someone is translating your ? placeholders to number placeholders; that's why your SQL:

UPDATE instances SET ? = ?

ends up as:

UPDATE instances SET $1 = $2

in the error message.

Now the real problem is that you can't use placeholders for identifiers (table names, column names, ...), you can only use placeholders for values. You can't say SET ? = ?, you have to supply the column name some other way, probably through string interpolation. This means that you have to move your prepare inside your loop to something like this:

foreach my $propname (sort keys %{ $raw_data }) {
    my $prop    = $dbh->quote_identifier($propname);
    my $propval = $raw_data->{$propname};
    my $sth     = $dbh->prepare("UPDATE instances SET $prop = ?");
    $sth->execute($propval);
    $sth->finish();
}

Your real code would, of course, include error handling. Note the use of quote_identifier to make the column name safe for interpolation. If you're going to end up with this simple prepare, execute, finish sequence then you might want to just use do instead.