Re: Comparing first 3 numbers of a IPv4 address?

Hello,

I'm trying to program a PHP-script, where users
can rate the "goodness" of the other players:

create table pref_rep (
id varchar(32) references pref_users(id) check (id <> author),
author varchar(32) references pref_users(id),
author_ip inet,
good boolean,
last_rated timestamp default current_timestamp
);

To (try to) prevent tampering I'd like to delete
entries for the same id coming
from the same IP in the course of last hour:

create or replace function pref_update_rep(_id varchar,
_author varchar, _author_ip inet,
_good boolean) returns void as $BODY$
begin

delete from pref_rep
where id = _id and
author_ip = _author_ip and
age(to_timestamp(last_rated)) < interval '1 hour';

update pref_rep set
author = _author,
author_ip = _author_ip,
good = _good,
last_rated = current_timestamp
where id = _id and author = _author;

if not found then
insert into pref_rep(id, author, author_ip, good)
values (_id, _author, _author_ip, _good);
end if;
end;
$BODY$ language plpgsql;

I have 2 questions please:

1) if I'd like to compare just the first 3 numbers of
the IP address instead of the 4, how can I do it?
(yes, I know about the A,B,C type of IPv4 networks...)

2) Do I need to add an index to my table
or are id and author indexed already?

Thank you!
Alex

Hey Alexander,

2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>

> Hello,
>
> I'm trying to program a PHP-script, where users
> can rate the "goodness" of the other players:
>
> create table pref_rep (
> id varchar(32) references pref_users(id) check (id <>
> author),
> author varchar(32) references pref_users(id),
> author_ip inet,
> good boolean,
> last_rated timestamp default current_timestamp
> );
>
> To (try to) prevent tampering I'd like to delete
> entries for the same id coming
> from the same IP in the course of last hour:
>
> create or replace function pref_update_rep(_id varchar,
> _author varchar, _author_ip inet,
> _good boolean) returns void as $BODY$
> begin
>
> delete from pref_rep
> where id = _id and
> author_ip = _author_ip and
> age(to_timestamp(last_rated)) < interval '1 hour';
>
> update pref_rep set
> author = _author,
> author_ip = _author_ip,
> good = _good,
> last_rated = current_timestamp
> where id = _id and author = _author;
>
> if not found then
> insert into pref_rep(id, author, author_ip, good)
> values (_id, _author, _author_ip, _good);
> end if;
> end;
> $BODY$ language plpgsql;
>
> I have 2 questions please:
>
> 1) if I'd like to compare just the first 3 numbers of
> the IP address instead of the 4, how can I do it?
> (yes, I know about the A,B,C type of IPv4 networks...)
>
You may try something like this (this solution can be better):
SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
to get first 3 bytes of IP in array (ready to compare with another
array).

> 2) Do I need to add an index to my table
> or are id and author indexed already?
>
Foreign keys columns does not indexed. You should create them
manually (if you need).

>
> Thank you!
> Alex
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.

I'm actually hoping to use inet (or cidr?) instead of strings...

On 11/20/10, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> Hey Alexander,
>
> 2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
>
>> Hello,
>>
>> I'm trying to program a PHP-script, where users
>> can rate the "goodness" of the other players:
>>
>> create table pref_rep (
>> id varchar(32) references pref_users(id) check (id <>
>> author),
>> author varchar(32) references pref_users(id),
>> author_ip inet,
>> good boolean,
>> last_rated timestamp default current_timestamp
>> );
>>
>> To (try to) prevent tampering I'd like to delete
>> entries for the same id coming
>> from the same IP in the course of last hour:
>>
>> create or replace function pref_update_rep(_id varchar,
>> _author varchar, _author_ip inet,
>> _good boolean) returns void as $BODY$
>> begin
>>
>> delete from pref_rep
>> where id = _id and
>> author_ip = _author_ip and
>> age(to_timestamp(last_rated)) < interval '1 hour';
>>
>> update pref_rep set
>> author = _author,
>> author_ip = _author_ip,
>> good = _good,
>> last_rated = current_timestamp
>> where id = _id and author = _author;
>>
>> if not found then
>> insert into pref_rep(id, author, author_ip, good)
>> values (_id, _author, _author_ip, _good);
>> end if;
>> end;
>> $BODY$ language plpgsql;
>>
>> I have 2 questions please:
>>
>> 1) if I'd like to compare just the first 3 numbers of
>> the IP address instead of the 4, how can I do it?
>> (yes, I know about the A,B,C type of IPv4 networks...)
>>
> You may try something like this (this solution can be better):
> SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
> to get first 3 bytes of IP in array (ready to compare with another
> array).
>
>
>> 2) Do I need to add an index to my table
>> or are id and author indexed already?
>>
> Foreign keys columns does not indexed. You should create them
> manually (if you need).
>
>
>>
>> Thank you!
>> Alex
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>

--
Sent from my mobile device

You can. host() just extract IP address from a value of inet
type as text, string_to_array() converts this text to text[], and
it makes it possible to compare with another text[]... I see no
problem here. It works just fine.
But probably there is another (better) solution...

2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>

> I'm actually hoping to use inet (or cidr?) instead of strings...
>
>
>
> On 11/20/10, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> > Hey Alexander,
> >
> > 2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
> >
> >> Hello,
> >>
> >> I'm trying to program a PHP-script, where users
> >> can rate the "goodness" of the other players:
> >>
> >> create table pref_rep (
> >> id varchar(32) references pref_users(id) check (id <>
> >> author),
> >> author varchar(32) references pref_users(id),
> >> author_ip inet,
> >> good boolean,
> >> last_rated timestamp default current_timestamp
> >> );
> >>
> >> To (try to) prevent tampering I'd like to delete
> >> entries for the same id coming
> >> from the same IP in the course of last hour:
> >>
> >> create or replace function pref_update_rep(_id varchar,
> >> _author varchar, _author_ip inet,
> >> _good boolean) returns void as $BODY$
> >> begin
> >>
> >> delete from pref_rep
> >> where id = _id and
> >> author_ip = _author_ip and
> >> age(to_timestamp(last_rated)) < interval '1 hour';
> >>
> >> update pref_rep set
> >> author = _author,
> >> author_ip = _author_ip,
> >> good = _good,
> >> last_rated = current_timestamp
> >> where id = _id and author = _author;
> >>
> >> if not found then
> >> insert into pref_rep(id, author, author_ip, good)
> >> values (_id, _author, _author_ip, _good);
> >> end if;
> >> end;
> >> $BODY$ language plpgsql;
> >>
> >> I have 2 questions please:
> >>
> >> 1) if I'd like to compare just the first 3 numbers of
> >> the IP address instead of the 4, how can I do it?
> >> (yes, I know about the A,B,C type of IPv4 networks...)
> >>
> > You may try something like this (this solution can be better):
> > SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
> > to get first 3 bytes of IP in array (ready to compare with another
> > array).
> >
> >
> >> 2) Do I need to add an index to my table
> >> or are id and author indexed already?
> >>
> > Foreign keys columns does not indexed. You should create them
> > manually (if you need).
> >
> >
> >>
> >> Thank you!
> >> Alex
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
> > --
> > // Dmitriy.
> >
>
> --
> Sent from my mobile device
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.

I think inet is a number internally, there is probably a more effective way...

On 11/20/10, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> You can. host() just extract IP address from a value of inet
> type as text, string_to_array() converts this text to text[], and
> it makes it possible to compare with another text[]... I see no
> problem here. It works just fine.
> But probably there is another (better) solution...
>
> 2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
>
>> I'm actually hoping to use inet (or cidr?) instead of strings...
>>
>>
>>
>> On 11/20/10, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
>> > Hey Alexander,
>> >
>> > 2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
>> >
>> >> Hello,
>> >>
>> >> I'm trying to program a PHP-script, where users
>> >> can rate the "goodness" of the other players:
>> >>
>> >> create table pref_rep (
>> >> id varchar(32) references pref_users(id) check (id <>
>> >> author),
>> >> author varchar(32) references pref_users(id),
>> >> author_ip inet,
>> >> good boolean,
>> >> last_rated timestamp default current_timestamp
>> >> );
>> >>
>> >> To (try to) prevent tampering I'd like to delete
>> >> entries for the same id coming
>> >> from the same IP in the course of last hour:
>> >>
>> >> create or replace function pref_update_rep(_id varchar,
>> >> _author varchar, _author_ip inet,
>> >> _good boolean) returns void as $BODY$
>> >> begin
>> >>
>> >> delete from pref_rep
>> >> where id = _id and
>> >> author_ip = _author_ip and
>> >> age(to_timestamp(last_rated)) < interval '1 hour';
>> >>
>> >> update pref_rep set
>> >> author = _author,
>> >> author_ip = _author_ip,
>> >> good = _good,
>> >> last_rated = current_timestamp
>> >> where id = _id and author = _author;
>> >>
>> >> if not found then
>> >> insert into pref_rep(id, author, author_ip,
>> >> good)
>> >> values (_id, _author, _author_ip, _good);
>> >> end if;
>> >> end;
>> >> $BODY$ language plpgsql;
>> >>
>> >> I have 2 questions please:
>> >>
>> >> 1) if I'd like to compare just the first 3 numbers of
>> >> the IP address instead of the 4, how can I do it?
>> >> (yes, I know about the A,B,C type of IPv4 networks...)
>> >>
>> > You may try something like this (this solution can be better):
>> > SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
>> > to get first 3 bytes of IP in array (ready to compare with another
>> > array).
>> >
>> >
>> >> 2) Do I need to add an index to my table
>> >> or are id and author indexed already?
>> >>
>> > Foreign keys columns does not indexed. You should create them
>> > manually (if you need).
>> >
>> >
>> >>
>> >> Thank you!
>> >> Alex
>> >>
>> >> --
>> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >>
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>>
>> --
>> Sent from my mobile device
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>

--
Sent from my mobile device

I will be glad to see the best solution, so if you find it please share. :-)

But you want to compare IP addresses by 3 first parts. I don't see any
function or operator at SQL level which allow to do it without converting
to text.
Probably, you can do it by manipulating a binary form (from libpq).
But is it worth it ? :-)

2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>

> I think inet is a number internally, there is probably a more effective
> way...
>
> On 11/20/10, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> > You can. host() just extract IP address from a value of inet
> > type as text, string_to_array() converts this text to text[], and
> > it makes it possible to compare with another text[]... I see no
> > problem here. It works just fine.
> > But probably there is another (better) solution...
> >
> > 2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
> >
> >> I'm actually hoping to use inet (or cidr?) instead of strings...
> >>
> >>
> >>
> >> On 11/20/10, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> >> > Hey Alexander,
> >> >
> >> > 2010/11/20 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
> >> >
> >> >> Hello,
> >> >>
> >> >> I'm trying to program a PHP-script, where users
> >> >> can rate the "goodness" of the other players:
> >> >>
> >> >> create table pref_rep (
> >> >> id varchar(32) references pref_users(id) check (id <>
> >> >> author),
> >> >> author varchar(32) references pref_users(id),
> >> >> author_ip inet,
> >> >> good boolean,
> >> >> last_rated timestamp default current_timestamp
> >> >> );
> >> >>
> >> >> To (try to) prevent tampering I'd like to delete
> >> >> entries for the same id coming
> >> >> from the same IP in the course of last hour:
> >> >>
> >> >> create or replace function pref_update_rep(_id varchar,
> >> >> _author varchar, _author_ip inet,
> >> >> _good boolean) returns void as $BODY$
> >> >> begin
> >> >>
> >> >> delete from pref_rep
> >> >> where id = _id and
> >> >> author_ip = _author_ip and
> >> >> age(to_timestamp(last_rated)) < interval '1 hour';
> >> >>
> >> >> update pref_rep set
> >> >> author = _author,
> >> >> author_ip = _author_ip,
> >> >> good = _good,
> >> >> last_rated = current_timestamp
> >> >> where id = _id and author = _author;
> >> >>
> >> >> if not found then
> >> >> insert into pref_rep(id, author, author_ip,
> >> >> good)
> >> >> values (_id, _author, _author_ip, _good);
> >> >> end if;
> >> >> end;
> >> >> $BODY$ language plpgsql;
> >> >>
> >> >> I have 2 questions please:
> >> >>
> >> >> 1) if I'd like to compare just the first 3 numbers of
> >> >> the IP address instead of the 4, how can I do it?
> >> >> (yes, I know about the A,B,C type of IPv4 networks...)
> >> >>
> >> > You may try something like this (this solution can be better):
> >> > SELECT (string_to_array(host('192.168.1.123'), '.'))[1:3];
> >> > to get first 3 bytes of IP in array (ready to compare with another
> >> > array).
> >> >
> >> >
> >> >> 2) Do I need to add an index to my table
> >> >> or are id and author indexed already?
> >> >>
> >> > Foreign keys columns does not indexed. You should create them
> >> > manually (if you need).
> >> >
> >> >
> >> >>
> >> >> Thank you!
> >> >> Alex
> >> >>
> >> >> --
> >> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >> >> To make changes to your subscription:
> >> >> http://www.postgresql.org/mailpref/pgsql-general
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > // Dmitriy.
> >> >
> >>
> >> --
> >> Sent from my mobile device
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >>
> >
> >
> >
> > --
> > // Dmitriy.
> >
>
> --
> Sent from my mobile device
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.

Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:
> 1) if I'd like to compare just the first 3 numbers of
> the IP address instead of the 4, how can I do it?

regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
regression-# network(set_masklen('123.45.67.56'::inet, 24));
?column?
----------
t
(1 row)

regards, tom lane

Thank you. Or maybe also?

(author_ip & '255.255.255.0'::inet) = (_author_ip & '255.255.255.0'::inet)

On Sat, Nov 20, 2010 at 4:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:
>> 1) if I'd like to compare just the first 3 numbers of
>> the IP address instead of the 4, how can I do it?
>
> regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
> regression-#  network(set_masklen('123.45.67.56'::inet, 24));
>  ?column?
> ----------
>  t
> (1 row)
>|

Hey Tom,

Thanks for you solution!

2010/11/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:
> > 1) if I'd like to compare just the first 3 numbers of
> > the IP address instead of the 4, how can I do it?
>
> regression=# select network(set_masklen('123.45.67.42'::inet, 24)) =
> regression-# network(set_masklen('123.45.67.56'::inet, 24));
> ?column?
> ----------
> t
> (1 row)
>
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.

On 2010-11-20, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:

>
> 1) if I'd like to compare just the first 3 numbers of
> the IP address instead of the 4, how can I do it?
> (yes, I know about the A,B,C type of IPv4 networks...)

have you heard of CIDR (what about IPV6, which I'm going to ignore,
but you should consider unless this code is throw-away)

just use set_masklen and the >> operator.

select set_masklen('1.2.3.4'::inet,24) >> '1.2.3.244'::inet;

--
⚂⚃ 100% natural