Thread: 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

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



On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> Hey Alexander,
>
> 2010/11/20 Alexander Farber <alexander.farber@gmail.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>

--
Sent from my mobile device

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

On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.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.farber@gmail.com>
>
>> I'm actually hoping to use inet (or cidr?) instead of strings...
>>
>>
>>
>> On 11/20/10, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>> > Hey Alexander,
>> >
>> > 2010/11/20 Alexander Farber <alexander.farber@gmail.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@postgresql.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> --
> // Dmitriy.
>

--
Sent from my mobile device

Alexander Farber <alexander.farber@gmail.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@sss.pgh.pa.us> wrote:
> Alexander Farber <alexander.farber@gmail.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)
>|

On 2010-11-20, Alexander Farber <alexander.farber@gmail.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