DBD::Pg problem

[Ausrack Webmaster]

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
"<su*****@somed omain.com>": can't parse "<su*****@somed omain.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]

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
"<su*****@somed omain.com>": can't parse "<su*****@somed omain.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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/zYgKY5Twig3Ge+Y RArVDAJ0YqO4Ua0 jeiLz0xyYPLU74y qcoiQCdHq2G
pTH9QrMtdFGDdpY j3DpixAI=
=KGnB
-----END PGP SIGNATURE-----

[Ausrack Webmaster]

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(),'$p arent')";
$sth = $dbh->prepare($sql );
$sth->bind_param(1 , $from, {pg_type => DBD::Pg::PG_TEX T});
$sth->bind_param(2 , $to, {pg_type => DBD::Pg::PG_TEX T});
$sth->bind_param(3 , $subject, {pg_type => DBD::Pg::PG_TEX T});
$sth->execute;

\d it_contact;
Table "public.it_cont act"
Column | Type |
Modifiers
------------+-----------------------------+-----------------------------
---------------------------------------
contact_id | integer | not null default
nextval('public .it_contact_con tact_id_seq'::t ext)
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: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: pg***********@p ostgresql.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
"<su*****@somed omain.com>": can't parse "<su*****@somed omain.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 <kl*****@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

---------------------------(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

[Alex Satrapa]

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(?,?,?,no w(),$parent,'$b ody')";

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

[Ausrack Webmaster]

Tried that ...it is definetely the to_email field, not any others that
is causing
the problem.

Jason
-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org] On Behalf Of Alex Satrapa
Sent: Wednesday, December 03, 2003 6:31 PM
To: Ausrack Webmaster
Cc: pg***********@p ostgresql.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(?,?,?,no w(),$parent,'$b ody')";

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

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:P g:dbname=DBNAME HERE", "", "");

do_ins('alpha', 'beta','Blah1') ;
do_ins('d**@arc honet.com','d** @archonet.com', 'Blah2');
do_ins('<de*@ar chonet.com>','d **@archonet.com ','Blah3');
do_ins('<de*@ar chonet.com>','< de*@archonet.co m>','Blah4');

$dbh->disconnect;
exit;

sub do_ins {
my ($from,$to,$sub ject) = @_;
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(),$pa rent)";
$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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[greg]

-----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
"<su*****@somed omain.com>": can't parse "<su*****@somed omain.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 gr**@turnstep.c om
PGP Key: 0x14964AC8 200312030733

-----BEGIN PGP SIGNATURE-----

iD8DBQE/zdiRvJuQZxSWSsg RAu0RAJ4/b9QmzZs7/w9n/Ta58Ba6ZqffHQCg/mSm
l/qPB/RNfyhtXeRKEQksQ wg=
=CXfi
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Barbara Lindsey]

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,"','".$subj ect."','".$body ."', now(),'".$paren t."')";

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(),'$p arent')";
$sth = $dbh->prepare($sql );
$sth->bind_param(1 , $from, {pg_type => DBD::Pg::PG_TEX T});
$sth->bind_param(2 , $to, {pg_type => DBD::Pg::PG_TEX T});
$sth->bind_param(3 , $subject, {pg_type => DBD::Pg::PG_TEX T});
$sth->execute;

\d it_contact;
Table "public.it_cont act"
Column | Type |
Modifiers
------------+-----------------------------+-----------------------------
---------------------------------------
contact_id | integer | not null default
nextval('public .it_contact_con tact_id_seq'::t ext)
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: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: pg***********@p ostgresql.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
"<su*****@som edomain.com>": can't parse "<su*****@somed omain.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: [phone] ext. 314 Fax: [phone]

----
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.
----
---------------------------(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

[Doug McNaught]

Barbara Lindsey <bl******@cog.u fl.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,"','".$subj ect."','".$body ."',
now(),'".$paren t."')";

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Harald Fuchs]

In article <3F************ **@cog.ufl.edu> ,
Barbara Lindsey <bl******@cog.u fl.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,"','".$subj ect."','".$body ."', now(),'".$paren t."')"; 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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org