Thread: [GENERAL] [OT] Postgresql and PHP

[GENERAL] [OT] Postgresql and PHP

From

Alessandro Baggi

Date:

23 December 2016, 21:44:33

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but
changed to see if this solves my problem.

 From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

and query is performed.

 From php I'm trying to do the same with this code:

$ipsrc = "192.168.1.1";

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have searched
error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG:  execute <unnamed>: SELECT count(*) from bs_ipsource where srcaddr = $1
DETAIL:  parameters: $1 = '192.168.1.1'


I've tried also to use pg_last_error and errors are not printed.

Can someone put me on the right way?

Thanks in advance.

Re: [GENERAL] [OT] Postgresql and PHP

From

Adrian Klaver

Date:

23 December 2016, 21:51:09

On 12/23/2016 07:44 AM, Alessandro Baggi wrote:
> Hi list,
> sorry for this OT.
>
> I have a table on postgresql like this:
>
> id serial not null,
> srcaddr varchar(16) not null
>
> I use this table to store ip address. I've used also inet type but
> changed to see if this solves my problem.
>
> From psql, I run:
>
> select count(*) from bs_ipsource where srcaddr = '192.168.1.1';
>
> and query is performed.

And what is the count?

>
> From php I'm trying to do the same with this code:
>
> $ipsrc = "192.168.1.1";
>
> $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
> srcaddr = $1", array($ipsrc));
> if(!$query) { print error...}
>
> I don't understand why this query fails without error. I have searched

How do you know it fails?

> error in httpd logs and postgresql log without success.
>
> from postgresql logs i get:
>
> LOG:  execute <unnamed>: SELECT count(*) from bs_ipsource where srcaddr
> = $1
> DETAIL:  parameters: $1 = '192.168.1.1'
>
>
> I've tried also to use pg_last_error and errors are not printed.
>
> Can someone put me on the right way?
>
> Thanks in advance.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] [OT] Postgresql and PHP

From

"David G. Johnston"

Date:

23 December 2016, 21:52:25

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi <alessandro.baggi@gmail.com> wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG: execute <unnamed>: SELECT count(*) from bs_ipsource where srcaddr = $1
DETAIL: parameters: $1 = '192.168.1.1'

You might wish to explain how you've defined "fail" since there is no actual error.

One common explanation is that the database psql is talking to, which has the relevant data, is not the same database that PHP is talking to, and which lacks matching data.

You might want to fire off one or more "host machine identification" queries in both just to confirm whether this is the case.

David J.

Re: [GENERAL] [OT] Postgresql and PHP

From

Raymond O'Donnell

Date:

23 December 2016, 21:52:31

On 23/12/16 15:44, Alessandro Baggi wrote:
> Hi list,
> sorry for this OT.
>
> I have a table on postgresql like this:
>
> id serial not null,
> srcaddr varchar(16) not null
>
> I use this table to store ip address. I've used also inet type but
> changed to see if this solves my problem.
>
> From psql, I run:
>
> select count(*) from bs_ipsource where srcaddr = '192.168.1.1';
>
> and query is performed.
>
> From php I'm trying to do the same with this code:
>
> $ipsrc = "192.168.1.1";
>
> $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
> srcaddr = $1", array($ipsrc));
> if(!$query) { print error...}

Are you sure it's failing? Try this:

if ($query === false) {
   ....
}

PHP has funny ideas about what's true and false.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: [GENERAL] [OT] Postgresql and PHP

From

Alessandro Baggi

Date:

23 December 2016, 21:58:58

Hi Adrian,

>
> And what is the count?
>

Reported is 1

>
> How do you know it fails?
>

Really I don't know if it fails but

$query = pg_query_params(....);
if(!$query)
{
    echo pg_last_error($dbcon);
    echo "ERROR";
} else {
    $row = pg_fetch_assoc($query);
    if(!$row)
    {
        echo "some error";
    }

}

does not help to check if there are errors?

With this code I block in the first statement with "ERROR" string
printed and not real error. I've also tried to see if pg_last_error()
works with query keywords errors  (Select * from nonexisttable) and works.

Re: [GENERAL] [OT] Postgresql and PHP

From

Alessandro Baggi

Date:

23 December 2016, 22:03:28

Il 23/12/2016 16:52, David G. Johnston ha scritto:
> On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
> <alessandro.baggi@gmail.com <mailto:alessandro.baggi@gmail.com>>wrote:
>
>     $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
>     where srcaddr = $1", array($ipsrc));
>     if(!$query) { print error...}
>
>     I don't understand why this query fails without error. I have
>     searched error in httpd logs and postgresql log without success.
>
>     from postgresql logs i get:
>
>     LOG:  execute <unnamed>: SELECT count(*) from bs_ipsource where
>     srcaddr = $1
>     DETAIL:  parameters: $1 = '192.168.1.1'
>
>
> You might wish to explain how you've defined "fail" since there is no
> actual error.
>
> One common explanation is that the database psql is talking to, which
> has the relevant data, is not the same database that PHP is talking to,
> and which lacks matching data.
>
> You might want to fire off one or more "host machine identification"
> queries in both just to confirm whether this is the case.
>
> David J.
>

Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other
tables and works without problem.

Then I've only this db and only this host.

Re: [GENERAL] [OT] Postgresql and PHP

From

Adrian Klaver

Date:

24 December 2016, 01:06:37

On 12/23/2016 08:03 AM, Alessandro Baggi wrote:
> Il 23/12/2016 16:52, David G. Johnston ha scritto:
>> On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
>> <alessandro.baggi@gmail.com <mailto:alessandro.baggi@gmail.com>>wrote:
>>
>>     $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
>>     where srcaddr = $1", array($ipsrc));
>>     if(!$query) { print error...}
>>
>>     I don't understand why this query fails without error. I have
>>     searched error in httpd logs and postgresql log without success.
>>
>>     from postgresql logs i get:
>>
>>     LOG:  execute <unnamed>: SELECT count(*) from bs_ipsource where
>>     srcaddr = $1
>>     DETAIL:  parameters: $1 = '192.168.1.1'
>>
>>
>> You might wish to explain how you've defined "fail" since there is no
>> actual error.
>>
>> One common explanation is that the database psql is talking to, which
>> has the relevant data, is not the same database that PHP is talking to,
>> and which lacks matching data.
>>
>> You might want to fire off one or more "host machine identification"
>> queries in both just to confirm whether this is the case.
>>
>> David J.
>>
>
> Hi David,
> thanks for your answer.
> I don't think that this is the problem.
> Before the reported query I've other queries to the same db on other
> tables and works without problem.

Might want to set log_statement to 'all' at least temporarily:

https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

>
> Then I've only this db and only this host.
>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] [OT] Postgresql and PHP

From

Adrian Klaver

Date:

24 December 2016, 01:15:15

On 12/23/2016 08:03 AM, Alessandro Baggi wrote:
> Il 23/12/2016 16:52, David G. Johnston ha scritto:
>> On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
>> <alessandro.baggi@gmail.com <mailto:alessandro.baggi@gmail.com>>wrote:
>>
>>     $query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
>>     where srcaddr = $1", array($ipsrc));
>>     if(!$query) { print error...}
>>
>>     I don't understand why this query fails without error. I have
>>     searched error in httpd logs and postgresql log without success.
>>
>>     from postgresql logs i get:
>>
>>     LOG:  execute <unnamed>: SELECT count(*) from bs_ipsource where
>>     srcaddr = $1
>>     DETAIL:  parameters: $1 = '192.168.1.1'
>>
>>
>> You might wish to explain how you've defined "fail" since there is no
>> actual error.
>>
>> One common explanation is that the database psql is talking to, which
>> has the relevant data, is not the same database that PHP is talking to,
>> and which lacks matching data.
>>
>> You might want to fire off one or more "host machine identification"
>> queries in both just to confirm whether this is the case.
>>
>> David J.
>>
>
> Hi David,
> thanks for your answer.
> I don't think that this is the problem.
> Before the reported query I've other queries to the same db on other
> tables and works without problem.
>
> Then I've only this db and only this host.

Another thought, do you have this table in more then one schema and
maybe you are running into a search_path issue:

https://www.postgresql.org/docs/9.5/static/runtime-config-client.html

>
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] [OT] Postgresql and PHP

From

rob stone

Date:

24 December 2016, 01:21:51

Hello,
On Fri, 2016-12-23 at 16:44 +0100, Alessandro Baggi wrote:
> Hi list,
> sorry for this OT.
>
> I have a table on postgresql like this:
>
> id serial not null,
> srcaddr varchar(16) not null
>
> I use this table to store ip address. I've used also inet type but 
> changed to see if this solves my problem.
>
>  From psql, I run:
>
> select count(*) from bs_ipsource where srcaddr = '192.168.1.1';
>
>

I store and retrieve IP Addresses as follows:-

id serial not null,
srcaddr inet not null

select count(*) from bs_ipsource where srcaddr = '192.168.1.1'::inet;

Cast the textual representation to inet.

Never had a problem.

HTH,
Rob