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
