How can I get all entries where field matches IPv6 format in Ruby on Rails?-CodePudding

I have been trying to figure out how to make a query to obtain all entries in a table who's 'ip' field contains a valid ipv6 address. This is due to the fact that there may be intermingled ipv4 address entries. I have been trying to use the ipaddr library. In a controller, I can use IPAddress.valid_ipv6? "1.2.3.4" to check if the given string is an ipv6 address. However, I cannot figure out how to use this same code in a .where( ) query.

Using a model with an 'ip' field, I have tried things like:

valid_ip_entries = Table.where("IPAddress.valid_ipv6? ip")
valid_ip_entries = Table.where("IPAddress.valid_ipv6? :ip")
valid_ip_entries = Table.where("IPAddress.valid_ipv6 ip")

but I've had no luck so far.

CodePudding user response：

The string argument you're providing to where is just sent to the DB as SQL. If you want to run a ruby method on each field from the DB, then you need to return all the records, and then use a ruby iterator to check each result, something like this:

Table.all.select {|r| IPAddress.valid_ip6? r.ip}

Caveat: I have no idea about the ipaddress gem, so I'm just accepting that you knew how to use it. You could also use the IPAddr library which is built into ruby: IPAddr.new(r.ip).ipv6?

Update

I just noticed that MySQL seems to have a function specifically for checking whether a string value is an IPv6 address: IS_IPV6(), so I believe you could just do this:

Table.where( "IS_IPV6(ip) = 1")

CodePudding user response：

The query Table.where() requires an argument that can be cast as an SQL snippet. Unfortunately you can't use the IPAddress (Ruby) method within an SQL snippet.

The solution is not as elegant as you might hope, you can use something like:

# matches 7 groups of (4 hex digits followed by ":") and then one last group of 4 hex digits

Table.where("ip_addr regexp 
'^([[:xdigit:]]{1,4}:){7}[[:xdigit:]]{1,4}$'")

CAUTION the regexp shown is just a rough approximation of IPV6 address matching. There are legitimate addresses that don't match. I showed it here just to illustrate the query syntax.