Re: DBD::Pg problem

Поиск
Список
Период
Сортировка
Искать

Re: DBD::Pg problem

От:
"Ausrack Webmaster" <webmaster@ausrack.com.au>
Дата:
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
> "": can't parse ""
> 
> 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      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

DBD::Pg problem

От:
"Ausrack Webmaster" <webmaster@ausrack.com.au>
Дата:
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
"": can't parse ""

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

Re: DBD::Pg problem

От:
"Ausrack Webmaster" <webmaster@ausrack.com.au>
Дата:

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

Re: DBD::Pg problem

От:
Martijn van Oosterhout <kleptog@svana.org>
Дата:
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
> "": can't parse ""
> 
> 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      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

Re: DBD::Pg problem

От:
Richard Huxton <dev@archonet.com>
Дата:
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','Blah3');
do_ins('','','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

Re: DBD::Pg problem

От:
Alex Satrapa <alex@lintelsys.com.au>
Дата:
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

Re: DBD::Pg problem

От:
Barbara Lindsey <blindsey@cog.ufl.edu>
Дата:
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
>>"": can't parse ""
>>
>>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.
----

Re: DBD::Pg problem

От:
"Joshua D. Drake" <jd@commandprompt.com>
Дата:
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
>"": can't parse ""
>
>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

Re: DBD::Pg problem

От:
greg@turnstep.com
Дата:

-----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
> "": can't parse ""
 
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-----

Re: DBD::Pg problem

От:
Doug McNaught <doug@mcnaught.org>
Дата:
Barbara Lindsey  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

Re: DBD::Pg problem

От:
Harald Fuchs <nospam@sap.com>
Дата:
In article <3FCDE437.9060309@cog.ufl.edu>,
Barbara Lindsey  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.
FAQ