Re: IP regex

I don't believe a regular expression is the right way to go here.  (Note 
that neither of the 2 regex solutions previously given do what you want -- 
they would both accept 999.999.00.00 as valid, for example.)  You could 
write a horribly complicated regular expression to do what you want, but it 
would be hard to make sure you got it right.  You'd also be reinventing the 
wheel, as there are already functions designed to work with IPs.

Spenser is on the right track, but INET_ATON() does not always return NULL 
for invalid IPs.  Using Gregg's other example, INET_ATON('65.23') returns 
1090519063, which is really 65.0.0.23.  The trick is to use both INET_ATON() 
and INET_NTOA(), because

   ip = INET_NTOA(INET_ATON(ip))

only if ip is valid.

My advice: It makes no sense to keep IPs in a VARCHAR(30) column.  (Even if 
a string was the right way to go, you'd need at most 15 characters.)  The 
better way is to use an INT UNSIGNED.  Then you store an IP using 
INET_ATON(IP) and retrieve it with INET_NTOA(ip_column).  That takes 4 bytes 
instead of 8 to 16, and reduces the likelihood of garbage values in the 
column.  Thus, I'd suggest

   ALTER TABLE clients ADD new_ip INT UNSIGNED;

   UPDATE clients SET new_ip = INET_ATON(old_ip)
   WHERE old_ip = INET_NTOA(INET_ATON(old_ip));

Sample results:

   SELECT old_ip, INET_NTOA(new_ip) AS ip FROM clients;
   +-----------------+-----------------+
   | old_ip          | ip              |
   +-----------------+-----------------+
   | 192.168.24.20   | 192.168.24.20   |
   | 255.255.255.255 | 255.255.255.255 |
   | 65.23           | NULL            |
   | 277.22.49.75    | NULL            |
   | junk text       | NULL            |
   | 192.168.0.7     | 192.168.0.7     |
   +-----------------+-----------------+
   6 rows in set (0.00 sec)

Then you could drop column old_ip and rename new_ip to old_ip.  (Of course, 
you'd change "clients" and "old_ip" to fit your actual table.)  Going 
forward, you should modify your apps to only store valid IPs in the first place.

Michael

Spenser wrote:
> You could use the MySQL function, INET_ATON() to check for a valid
> address.  This first statement below produces a valid number or string. 
> The second statement below returns NULL because the IP address given
> isn't valid.
>    SELECT INET_ATON('192.168.24.20');
>    SELECT INET_ATON('277.22.49.75');
> 
> Based on this, you could write a Perl script with error checking; have
> it check for NULL or not.  Actually, you could put the field for the IP
> address inside the function of the SQL statement:
> 
>    SELECT INET_ATON(ip_address_column);
> 
> 
>>--- "Gregg R.Allen" <[email protected]> wrote:
>>
>>
>>>I'm sure this is very simple, I just can't seem to learn Regexes:
>>>
>>>I have been given the chore of taking a DB table of client records, 
>>>which has a field called IP address.  Whenever a client logs onto our 
>>>site, I'm supposed to display the IP address they logged on from THE 
>>>LAST TIME they visited, as a security measure.
>>>
>>>This field is a MySQl varchar(30). Since this field was added at table 
>>>creation, but not used until recently, many of the fields contain NULLs 
>>>or junk text.
>>>
>>>Can someone point me in the direction of creating REGEX to test whether 
>>>an IP address is valid. (Valid in the sense of syntax i.e. 
>>>192.168.24.20 is  valid, but
>>>277.22.49.75, or 65.23 is not)?
>>>
>>>Thanks in advance
>>>
>>>Gregg Allen
>>>
>>>
>>>-- 
>>>MySQL Perl Mailing List
>>>For list archives: http://lists.mysql.com/perl
>>>To unsubscribe:    http://lists.mysql.com/[email protected]
>>>
>>>
>>
>>
>>
>>		
>>__________________________________
>>Do you Yahoo!?
>>Take Yahoo! Mail with you! Get it on your mobile phone.
>>http://mobile.yahoo.com/maildemo
> 
> 

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]