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

Question

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.

Answer 1

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")