CODEWITHSUNDEEP
perlpostgresqldbi
Kostiuk Aleksandr
Kostiuk Aleksandr

Reputation: 507

DBD::Pg::st execute failed: ERROR: column "***" does not exist

That's part of my code. I do SELECT using message-ID received from gmail. Msg_id stored in the database in the form of base64 and without simbols "< >".

my $inbox = $imap->select("Inbox") or die "Select error: ", $imap->LastError, "\n";
my @mails = ( $imap->unseen );
foreach my $msgid (@mails) {
    my $m_id = $imap->parse_headers( $msgid, "Message-id" )->{"Message-id"}->[0] . "\n";
    $m_id =~ s/[<\>]//g;
    $m_id = encode_base64($m_id);
    $m_id =~ s/\r?$//;
    my $q1 = "select id from mails.mails_in where user_id=$param[5] and message_id=$m_id and user_remote_id=$param[6]";
    $sth = $dbh->prepare($q1);
    $rv  = $sth->execute();
    my @array;

    while ( @array = $sth->fetchrow_array() ) {
        foreach my $i (@array) {
            print "$i\t";
        }
        print "\n";
    }
}

But getting this error.

DBD::Pg::st execute failed: ERROR:  column "zdjkot..." does not exist
LINE 1: ...mails.mails_in where user_id=15206 and message_id=ZDJkOTQ1NT...
                                                             ^ at ./script.pl line 65.

I tried to use an existing msg_id, from the base - the result is similar. Another SELECT's work correctly. Similar SELECT work correctly on php.

I use: Perl v5.18.2, PostgreSQL v8.4.14

Upvotes: 3

Views: 1995

Answers (1)

mpapec
mpapec

Reputation: 50637

You're missing single quotes for $m_id

my $q1 = "select id from mails.mails_in where user_id=$param[5] and message_id='$m_id' and user_remote_id=$param[6]";

but it is always better to use ? placeholders,

my $q1 = "select id from mails.mails_in where user_id =? and message_id =? and user_remote_id =?";
$sth = $dbh->prepare($q1);
$rv = $sth->execute($param[5], $m_id, $param[6]);

as you don't have to worry about quotes, parameter escaping, nor SQL injection attacks.

Upvotes: 7

Related Questions