FAQ
Hi,

I'm trying to insert following values in one my tables in potgres 8.2.

223, 1, localhost, 127.0.0.1, UnMask1.mshome.net, UnMask1.mshome.net,
Tue, 1 Jan 2002 13:15:35 -0500 (EST), ESMTP, g01IFZlU002773,
abc@abc.mshome.net.

Except for the first two fields (that happend to be int4 datatype),
rest all the fields are of data type text.

When this query gets executed using command

$query_tbl_ul_received->execute;

I get the followring error:

DBD::Pg::st execute failed: execute called with an unbound placeholder
at email_parser.pl line 561, <FILE> line 1.

Can anyone please throw some light on it.

Thanks in advance,
Jas

Search Discussions

  • Andrew Dunstan at Jan 21, 2007 at 10:53 pm

    Jasbinder Singh Bali wrote:
    Hi,

    I'm trying to insert following values in one my tables in potgres 8.2.

    223, 1, localhost, 127.0.0.1, UnMask1.mshome.net, UnMask1.mshome.net,
    Tue, 1 Jan 2002 13:15:35 -0500 (EST), ESMTP, g01IFZlU002773,
    abc@abc.mshome.net.

    Except for the first two fields (that happend to be int4 datatype),
    rest all the fields are of data type text.

    When this query gets executed using command

    $query_tbl_ul_received->execute;

    I get the followring error:

    DBD::Pg::st execute failed: execute called with an unbound placeholder
    at email_parser.pl line 561, <FILE> line 1.

    Can anyone please throw some light on it.
    not with this info - you need to show us the statement prepared and any
    param bindings.

    cheers

    andrew
  • Jasbinder Singh Bali at Jan 22, 2007 at 2:27 pm
    the final statement prepared is

    INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,
    received_via,received_with,received_for,received_date_time,received_comments)
    VALUES S (223, 1, 'localhost',
    '127.0.0.1', 'UnMask1.mshome.net', 'UnMask1.mshome.net',
    'Tue, 1 Jan 2002 13:15:35 -0500 (EST)', 'ESMTP', 'g01IFZlU002773',
    'abc@abc.mshome.net.'
    )


    The table creation script for tbl_ul_received is as follows:-


    CREATE TABLE tbl_ul_header_received
    (
    unmask_id int4 NOT NULL,
    seq_no int4 NOT NULL,
    received_id text,
    received_from text,
    received_by text,
    received_via text,
    received_with text,
    received_for text,
    received_date_time text,
    received_comments text,
    CONSTRAINT pk_received PRIMARY KEY (unmask_id, seq_no)
    )
    WITHOUT OIDS;
    ALTER TABLE tbl_ul_header_received OWNER TO postgres;

    When this insert statement gets executed (in perl) using
    $query_tbl_ul_received->execute; it throws the following error

    DBD::Pg::st execute failed: execute called with an unbound placeholder
    at email_parser.pl line 566, <FILE> line 1.

    In my perl code, query is formed as follows:-

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)
    VALUES (".$unmask_id.",".$count.",'".$id."','".$from_from."','".$by_domain."'',',".$with."','".$for."','".$date_time."','".$rcomm."')");

    I'm kind of badly stuck with this problem and have no clue about its solution.

    A quick help would be highly appreciated.
    Thanks,
    Jas

    On 1/21/07, Andrew Dunstan wrote:
    Jasbinder Singh Bali wrote:
    Hi,

    I'm trying to insert following values in one my tables in potgres 8.2.

    223, 1, localhost, 127.0.0.1, UnMask1.mshome.net, UnMask1.mshome.net,
    Tue, 1 Jan 2002 13:15:35 -0500 (EST), ESMTP, g01IFZlU002773,
    abc@abc.mshome.net.

    Except for the first two fields (that happend to be int4 datatype),
    rest all the fields are of data type text.

    When this query gets executed using command

    $query_tbl_ul_received->execute;

    I get the followring error:

    DBD::Pg::st execute failed: execute called with an unbound placeholder
    at email_parser.pl line 561, <FILE> line 1.

    Can anyone please throw some light on it.
    not with this info - you need to show us the statement prepared and any
    param bindings.

    cheers

    andrew
  • Andrew Dunstan at Jan 22, 2007 at 2:52 pm

    Jasbinder Singh Bali wrote:

    In my perl code, query is formed as follows:-

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)

    VALUES
    (".$unmask_id.",".$count.",'".$id."','".$from_from."','".$by_domain."'',',".$with."','".$for."','".$date_time."','".$rcomm."')");
    That is just horrible, to be honest. I have a very strong opinion that
    interpolating literal data values into SQL is to be avoided if at all
    possible. You should be doing something like this:

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)

    VALUES (?,?,?,?,?,?,?,?,?)";
    my $rv =
    $query_tbl_ul_received->execute($unmask_id,$count,$id,$from_from,$by_domain,$with,$for,$date_time,$rcomm);


    Use numbered placeholders of the $n variety if you prefer (see DBD::Pg
    docs for details).

    Quite apart from saving you all the tiresome bother of getting the
    quoting right, this saves you from the possibility of SQL injection
    attacks. See if doing this resolves your error. If not, at least we'll
    be better able to diagnose it with any luck.

    cheers

    andrew
  • Jasbinder Singh Bali at Jan 22, 2007 at 3:08 pm
    Thanks alot andrew.
    I did it exactly the way you suggested and it solved my problem.
    I have a question here though as I'm new to perl.
    What is the difference between my way and your way.
    What is this placeholder problem all about.
    After all i was just concatenating my query string with different
    values that I intended to insert.
    Could you please throw some light on it?
    Thanks and regards,
    Jas
    On 1/22/07, Andrew Dunstan wrote:
    Jasbinder Singh Bali wrote:

    In my perl code, query is formed as follows:-

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)

    VALUES
    (".$unmask_id.",".$count.",'".$id."','".$from_from."','".$by_domain."'',',".$with."','".$for."','".$date_time."','".$rcomm."')");
    That is just horrible, to be honest. I have a very strong opinion that
    interpolating literal data values into SQL is to be avoided if at all
    possible. You should be doing something like this:

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)

    VALUES (?,?,?,?,?,?,?,?,?)";
    my $rv =
    $query_tbl_ul_received->execute($unmask_id,$count,$id,$from_from,$by_domain,$with,$for,$date_time,$rcomm);


    Use numbered placeholders of the $n variety if you prefer (see DBD::Pg
    docs for details).

    Quite apart from saving you all the tiresome bother of getting the
    quoting right, this saves you from the possibility of SQL injection
    attacks. See if doing this resolves your error. If not, at least we'll
    be better able to diagnose it with any luck.

    cheers

    andrew
  • Andrew Dunstan at Jan 22, 2007 at 3:41 pm

    Jasbinder Singh Bali wrote:
    Thanks alot andrew.
    I did it exactly the way you suggested and it solved my problem.
    I have a question here though as I'm new to perl.
    What is the difference between my way and your way.
    What is this placeholder problem all about.
    After all i was just concatenating my query string with different
    values that I intended to insert.
    Could you please throw some light on it?

    You mean apart from being easier and cleaner? Your problem was that you
    needed to quote various literals. It looks to me like you have one too
    many single quotes following $by_domain. Even if you had got the quotes
    right, your code would have blown up if any of the data values had
    contained a quote mark. The best and safest way to do this is to get out
    of the game of putting literal values in SQL at all. Get into the habit
    of passing values to prepared statements to fill in the placeholders
    (the ? marks in my code). Then you never need to quote them at all. In
    addition, if you need to call the query more than once with different
    values, it only needs to be prepared once, and can be executed many
    times. There are many good reasons to do it this way and none not to ;-)

    HTH

    cheers

    andrew
    Thanks and regards,
    Jas
    On 1/22/07, Andrew Dunstan wrote:
    Jasbinder Singh Bali wrote:

    In my perl code, query is formed as follows:-

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)
    VALUES
    (".$unmask_id.",".$count.",'".$id."','".$from_from."','".$by_domain."'',',".$with."','".$for."','".$date_time."','".$rcomm."')");
    That is just horrible, to be honest. I have a very strong opinion that
    interpolating literal data values into SQL is to be avoided if at all
    possible. You should be doing something like this:

    my $query_tbl_ul_received = $dbh->prepare("INSERT INTO tbl_ul_received
    (unmask_id,seq_no,received_id,received_from,received_by,received_via,received_with,received_for,received_date_time,received_comments)


    VALUES (?,?,?,?,?,?,?,?,?)";
    my $rv =
    $query_tbl_ul_received->execute($unmask_id,$count,$id,$from_from,$by_domain,$with,$for,$date_time,$rcomm);



    Use numbered placeholders of the $n variety if you prefer (see DBD::Pg
    docs for details).

    Quite apart from saving you all the tiresome bother of getting the
    quoting right, this saves you from the possibility of SQL injection
    attacks. See if doing this resolves your error. If not, at least we'll
    be better able to diagnose it with any luck.

    cheers

    andrew

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbd-pg @
categoriesperl
postedJan 21, '07 at 9:33p
activeJan 22, '07 at 3:41p
posts6
users2
websiteperl.org

People

Translate

site design / logo © 2022 Grokbase