Filtering breaks for IPv6 addresses with alphabetic characters

[oysteingy]

Issue type

[ ] Feature request
[X ] Bug report
[ ] Documentation

Environment

• Python version: 3.4.5 -->
• NetBox version: 2.2.6 -->

Description

Hi, I filed this as #1680 previously. Problem appears to be related to the RESTAPI, not to pynetbox.
Seems that I can't re-open the previous ticket.

I'm bumping this again, I do not believe that the problem is related to pynetbox but with the Netbox RESTAPI. Upon upgrade 2.2.4 => 2.2.6, my manual backport of #1620 was overwritten.

This looks fine with nbshell

# python3 manage.py nbshell                                                                                                                                                                                                                                                                        
### NetBox interactive shell (netbox)                                                                                                                                                                                                                                                                       
### Python 3.4.5 | Django 1.11.8 | NetBox 2.2.6                                                                                                                                                                                                                                                                              
### lsmodels() will show available models. Use help(<model>) for more info.                                                                                                                                                                                                                                                  
>>> IPAddress.objects.filter(address="2a12:2b48:0:1001::1/127")                                                                                                                                                                                                                                                              
<QuerySet [<IPAddress: 2a12:2b48:0:1001::1/127>]>                

Same query with the API returns nothing:
curl -H "Authorization: Token $TOKEN" -H "Accept: application/json; indent=4" "http://netbox/netbox/api/ipam/ip-addresses/?q=2a12:2b48:0:1001::1/127"
{
"count": 0,

}

Looking up the prefix works fine with the API:
curl -H "Authorization: Token $TOKEN " -H "Accept: application/json; indent=4" "http://netbox/netbox/api/ipam/prefixes/?q=2a12:2b48:0:1001::1/127"
{
"count": 2,
}

By looking at the commit from #1620, this looks reasonable, istartswith seems not to work particularly well with IPv6

 >>> IPAddress.objects.filter(address__istartswith='2a12:2b48:0:1001::1/127')                                                                                                                                                                                                                                                 
<QuerySet []>       

Trying to lookup with address__net_contains_or_equals which is used for prefix works fine:

>>> IPAddress.objects.filter(address__net_contains_or_equals="2a12:2b48:0:1001::1/127")                                                                                                                                                                                                                                      
<QuerySet [<IPAddress: 2a12:2b48:0:1001::1/127>]>     

[jeremystretch]

Well, this is an interesting one. It seems that istartswith is ironically causing issues with capitalization. This is because values are filtered in SQL not with ILIKE value but rather LIKE UPPER(value). Here's an example.

First, create an IPv6 address with a letter in it:

>>> IPAddress.objects.create(address=netaddr.IPNetwork('2001:9999::a1/64'))
<IPAddress: 2001:9999::a1/64>

We can search with istartswith up to the last digit/colon with no problem:

>>> IPAddress.objects.filter(address__istartswith='2001:9999::')
<QuerySet [<IPAddress: 2001:9999::a1/64>]>

But when we add the "a", we get no results:

>>> IPAddress.objects.filter(address__istartswith='2001:9999::a')
<QuerySet []>

A capital "A" doesn't work either:

>>> IPAddress.objects.filter(address__istartswith='2001:9999::A')
<QuerySet []>

However, using the case-sensitive startswith filter and a lowercase "a" does work:

>>> IPAddress.objects.filter(address__startswith='2001:9999::a')
<QuerySet [<IPAddress: 2001:9999::a1/64>]>

This because istartswith forces UPPER(value) in the SQL query but not on the column (due to our custom process_lhs() method on NetFieldDecoratorMixin).

We should be able to fix this by forcing all query values to lowercase. I just need to figure out how best to accomplish that.