Re: execute called with an unbound placeholder

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 <[email protected]> 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
>>
>>
>