DBD::Pg problem

**Martijn van Oosterhout** · Nov 12 '05, 12:58 AM

Re: 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:[color=blue]
> 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@somed omain.com>": can't parse "<support@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[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "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[/color]

-----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** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

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: pgsql-general-owner@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Martijn van
Oosterhout
Sent: Wednesday, December 03, 2003 3:52 PM
To: Ausrack Webmaster
Cc: pgsql-general@postgre sql.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:[color=blue]
> 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@somed omain.com>": can't parse "<support@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[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "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[/color]
[color=blue]
> than themselves." - Plato[/color]

---------------------------(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** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

Ausrack Webmaster wrote:[color=blue]
> The thing is...I am not. I am inserting it into a varchar field.[/color]

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** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

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

Jason

-----Original Message-----
From: pgsql-general-owner@postgresq l.org
[mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Alex Satrapa
Sent: Wednesday, December 03, 2003 6:31 PM
To: Ausrack Webmaster
Cc: pgsql-general@postgre sql.org
Subject: Re: [GENERAL] DBD::Pg problem

Ausrack Webmaster wrote:[color=blue]
> The thing is...I am not. I am inserting it into a varchar field.[/color]

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** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

On Wednesday 03 December 2003 09:43, Ausrack Webmaster wrote:[color=blue]
> Tried that ...it is definetely the to_email field, not any others that
> is causing
> the problem.[/color]

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('dev@arc honet.com','dev @archonet.com', 'Blah2');
do_ins('<dev@ar chonet.com>','d ev@archonet.com ','Blah3');
do_ins('<dev@ar chonet.com>','< dev@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@turnstep.com** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

[color=blue]
> 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@somed omain.com>": can't parse "<support@somed omain.com>"[/color]

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.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 majordomo@postg resql.org)

**Barbara Lindsey** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

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:[color=blue]
> 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: pgsql-general-owner@postgresq l.org
> [mailto:pgsql-general-owner@postgresq l.org] On Behalf Of Martijn van
> Oosterhout
> Sent: Wednesday, December 03, 2003 3:52 PM
> To: Ausrack Webmaster
> Cc: pgsql-general@postgre sql.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:
>[color=green]
>>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@som edomain.com>": can't parse "<support@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[/color]
>
>[/color]

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

---------------------------(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** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

Barbara Lindsey <blindsey@cog.u fl.edu> writes:
[color=blue]
> 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.[/color]

If you do this in production (rather than just for debugging) you may
be vulnerable to an SQL injection attack...
[color=blue]
> 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.[/color]

....but it is useful for debugging.

-Doug

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

**Harald Fuchs** · Nov 12 '05, 12:58 AM

Re: DBD::Pg problem

In article <3FCDE437.90603 09@cog.ufl.edu> ,
Barbara Lindsey <blindsey@cog.u fl.edu> writes:
[color=blue]
> 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."')";[/color]
[color=blue]
> Then you dont have to bind params. You can just prepare and execute.[/color]

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

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Joshua D. Drake** · Nov 12 '05, 12:59 AM

Re: DBD::Pg problem

Hello,

I don't have your code but I think you are using "do" instead of
"prepare".

Sincerely,

Joshua D. Drake

Ausrack Webmaster wrote:
[color=blue]
>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@some domain.com>": can't parse "<support@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
>
>[/color]

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
Postgresql support, programming, shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com

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