Thread: DBD::Pg problem
Hi I am trying to insert a simple email address into a text field, and I get the below error: DBD::Pg::st execute failed: ERROR: pg_atoi: error in "<support@somedomain.com>": can't parse "<support@somedomain.com>" I figure it is because of the < and @ in the value, but why does it take these as operators even when the value has single quotes around it? I have even tried binding the values and PG_TEXT beforehand and still not luck. Any help would be greatly appreciated. Jason Frisch
pg_atoi is the string to int converter. You're trying to insert it into an integer field. On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote: > Hi > > I am trying to insert a simple email address into a text field, > and I get the below error: > > DBD::Pg::st execute failed: ERROR: pg_atoi: error in > "<support@somedomain.com>": can't parse "<support@somedomain.com>" > > I figure it is because of the < and @ in the value, but why does it take > these as operators even > when the value has single quotes around it? > I have even tried binding the values and PG_TEXT beforehand and still > not luck. > > Any help would be greatly appreciated. > > Jason Frisch > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Attachment
The thing is...I am not. I am inserting it into a varchar field.
$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),'$parent')";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(2, $to, {pg_type => DBD::Pg::PG_TEXT});
$sth->bind_param(3, $subject, {pg_type => DBD::Pg::PG_TEXT});
$sth->execute;
\d it_contact;
Table "public.it_contact"
Column | Type |
Modifiers
------------+-----------------------------+-----------------------------
---------------------------------------
contact_id | integer | not null default
nextval('public.it_contact_contact_id_seq'::text)
email | character varying(100) |
to_email | character varying(100) |
subject | text |
fname | character varying(30) |
lname | character varying(30) |
kafname | character varying(30) |
kalname | character varying(30) |
details | text |
modify | timestamp without time zone |
status | smallint |
parent | integer |
Jason
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DBD::Pg problem
pg_atoi is the string to int converter. You're trying to insert it into
an integer field.
On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote:
> Hi
>
> I am trying to insert a simple email address into a text field,
> and I get the below error:
>
> DBD::Pg::st execute failed: ERROR: pg_atoi: error in
> "<support@somedomain.com>": can't parse "<support@somedomain.com>"
>
> I figure it is because of the < and @ in the value, but why does it
> take these as operators even when the value has single quotes around
> it? I have even tried binding the values and PG_TEXT beforehand and
> still not luck.
>
> Any help would be greatly appreciated.
>
> Jason Frisch
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough
> good men to do nothing." - Edmond Burke "The penalty good people pay
> for not being interested in politics is to be governed by people worse
> than themselves." - Plato
Ausrack Webmaster wrote: > The thing is...I am not. I am inserting it into a varchar field. Are there any single quotes in the message body? They will wreak havoc with the rest of the query. And why are you putting single quotes around '$parent'? What happens if you move the '$body' to the end: $sql="insert into it_contact (email, to_email, subject,modify,parent,details) values(?,?,?,now(),$parent,'$body')"; Alex
Tried that ...it is definetely the to_email field, not any others that
is causing
the problem.
Jason
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alex Satrapa
Sent: Wednesday, December 03, 2003 6:31 PM
To: Ausrack Webmaster
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DBD::Pg problem
Ausrack Webmaster wrote:
> The thing is...I am not. I am inserting it into a varchar field.
Are there any single quotes in the message body? They will wreak havoc
with the rest of the query. And why are you putting single quotes around
'$parent'?
What happens if you move the '$body' to the end:
$sql="insert into it_contact (email, to_email,
subject,modify,parent,details) values(?,?,?,now(),$parent,'$body')";
Alex
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
joining column's datatypes do not match
On Wednesday 03 December 2003 09:43, Ausrack Webmaster wrote:
> Tried that ...it is definetely the to_email field, not any others that
> is causing
> the problem.
With the table schema you gave, the following seems to work fine for me. Only
changes from your example are to remove quoting on $parent and let
bind_param() sort out types by itself.
#!/usr/bin/perl -w
use DBI;
$dbh = DBI->connect("dbi:Pg:dbname=DBNAMEHERE", "", "");
do_ins('alpha','beta','Blah1');
do_ins('dev@archonet.com','dev@archonet.com','Blah2');
do_ins('<dev@archonet.com>','dev@archonet.com','Blah3');
do_ins('<dev@archonet.com>','<dev@archonet.com>','Blah4');
$dbh->disconnect;
exit;
sub do_ins {
my ($from,$to,$subject) = @_;
my $body = 'BBB';
my $parent = 0;
print STDERR "Trying f/t = $from / $to\n";
$sql="insert into it_contact (email, to_email, subject,
details,modify,parent) values(?,?,?,'$body',now(),$parent)";
$sth = $dbh->prepare($sql);
$sth->bind_param(1, $from);
$sth->bind_param(2, $to);
$sth->bind_param(3, $subject);
$sth->execute;
print STDERR "Ending f/t = $from / $to\n\n";
}
--
Richard Huxton
Archonet Ltd
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I am trying to insert a simple email address into a text field, > and I get the below error: > > DBD::Pg::st execute failed: ERROR: pg_atoi: error in > "<support@somedomain.com>": can't parse "<support@somedomain.com>" Try a $dbh->trace(1) and see if that gives you any additional information. Perhaps $parent is being inadvertantly set to a string inside of the application? - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200312030733 -----BEGIN PGP SIGNATURE----- iD8DBQE/zdiRvJuQZxSWSsgRAu0RAJ4/b9QmzZs7/w9n/Ta58Ba6ZqffHQCg/mSm l/qPB/RNfyhtXeRKEQksQwg= =CXfi -----END PGP SIGNATURE-----
When I have problems like this, I do something like this:
$sql="insert into it_contact (email, to_email,
subject,details,modify,parent) values(
'".$from."','".$to,"','".$subject."','".$body."', now(),'".$parent."')";
Then you dont have to bind params. You can just prepare and execute.
Besides that, you can print the $sql string with the expanded variables
and copy and paste it into psql to see if that gets you a different
error to help debug if it is not already obvious when you see the whole SQL.
Hope this helps.
Barb
usrack Webmaster wrote:
> The thing is...I am not. I am inserting it into a varchar field.
>
> $sql="insert into it_contact (email, to_email, subject,
> details,modify,parent) values(?,?,?,'$body',now(),'$parent')";
> $sth = $dbh->prepare($sql);
> $sth->bind_param(1, $from, {pg_type => DBD::Pg::PG_TEXT});
> $sth->bind_param(2, $to, {pg_type => DBD::Pg::PG_TEXT});
> $sth->bind_param(3, $subject, {pg_type => DBD::Pg::PG_TEXT});
> $sth->execute;
>
> \d it_contact;
> Table "public.it_contact"
> Column | Type |
> Modifiers
> ------------+-----------------------------+-----------------------------
> ---------------------------------------
> contact_id | integer | not null default
> nextval('public.it_contact_contact_id_seq'::text)
> email | character varying(100) |
> to_email | character varying(100) |
> subject | text |
> fname | character varying(30) |
> lname | character varying(30) |
> kafname | character varying(30) |
> kalname | character varying(30) |
> details | text |
> modify | timestamp without time zone |
> status | smallint |
> parent | integer |
>
>
> Jason
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martijn van
> Oosterhout
> Sent: Wednesday, December 03, 2003 3:52 PM
> To: Ausrack Webmaster
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] DBD::Pg problem
>
>
> pg_atoi is the string to int converter. You're trying to insert it into
> an integer field.
>
> On Wed, Dec 03, 2003 at 03:45:53PM +0900, Ausrack Webmaster wrote:
>
>>Hi
>>
>>I am trying to insert a simple email address into a text field,
>>and I get the below error:
>>
>>DBD::Pg::st execute failed: ERROR: pg_atoi: error in
>>"<support@somedomain.com>": can't parse "<support@somedomain.com>"
>>
>>I figure it is because of the < and @ in the value, but why does it
>>take these as operators even when the value has single quotes around
>>it? I have even tried binding the values and PG_TEXT beforehand and
>>still not luck.
>>
>>Any help would be greatly appreciated.
>>
>>Jason Frisch
>>
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>
>
--
Barbara E. Lindsey,
COG RDC
Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162
----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
Barbara Lindsey <blindsey@cog.ufl.edu> writes: > When I have problems like this, I do something like this: > $sql="insert into it_contact (email, to_email, > subject,details,modify,parent) values( > '".$from."','".$to,"','".$subject."','".$body."', > now(),'".$parent."')"; > > Then you dont have to bind params. You can just prepare and execute. If you do this in production (rather than just for debugging) you may be vulnerable to an SQL injection attack... > Besides that, you can print the $sql string with the expanded > variables and copy and paste it into psql to see if that gets you a > different error to help debug if it is not already obvious when you > see the whole SQL. ...but it is useful for debugging. -Doug
In article <3FCDE437.9060309@cog.ufl.edu>,
Barbara Lindsey <blindsey@cog.ufl.edu> writes:
> When I have problems like this, I do something like this:
> $sql="insert into it_contact (email, to_email,
> subject,details,modify,parent) values(
> '".$from."','".$to,"','".$subject."','".$body."', now(),'".$parent."')";
> Then you dont have to bind params. You can just prepare and execute.
... and get interesting results if one of the variables contains
quotes or backslashes.
I often use something like that:
$dbh->do (q{
INSERT INTO it_contact (email, to_email, subject, details, modify, parent)
VALUES (?, ?, ?, ?, ?, ?)
}, undef, $from, $to, $subject, $body, now(), $parent);
This lets DBI do the proper quoting for you.
Hello, I don't have your code but I think you are using "do" instead of "prepare". Sincerely, Joshua D. Drake Ausrack Webmaster wrote: >Hi > >I am trying to insert a simple email address into a text field, >and I get the below error: > >DBD::Pg::st execute failed: ERROR: pg_atoi: error in >"<support@somedomain.com>": can't parse "<support@somedomain.com>" > >I figure it is because of the < and @ in the value, but why does it take >these as operators even >when the value has single quotes around it? >I have even tried binding the values and PG_TEXT beforehand and still >not luck. > >Any help would be greatly appreciated. > >Jason Frisch > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com