ipv6 ip of user doesn't go into MySQL database

[vophamthanhan]

error while trying to store ipv6 address for authenticated user into a MySQL database.

[AuthMe] Error during SQL operation: [MysqlDataTruncation]: Data truncation: Data too long for column 'ip' at row 1

for example, a user may join with an ipv6 address of [d82e:b902:18db:2bf3:9f59:cfda:75af:8584], compared to an example ipv4 address of 54.175.226.192, the address is different in size and formatting.

Versions:
AuthMeReloaded v5.4.0 (build: 1877)
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper

[vophamthanhan]

from a security point of view, keep in mind that ipv6 addresses have a prefix and a suffix.
the prefix is given out by the ISP, while the suffix can be changed at will by device.
if an IP addresses is used to limit(say registration), i suggest only the prefix of ipv6 be used.
the prefix can be gotten by some regex.

there are multiple different ipv6 representations to go along.

[Xephi]

I think the problem is : with your example, we maybe try to add '[' and ']' characters onto database too (maybe? we need to do some tests about that)
The ip columns current type are all VARCHAR(40), since ipv4 are 15 characters long (max) and ipv6 are 39 characters long (max)

Take a look into :
https://github.com/AuthMe/AuthMeReloaded/blob/master/src/main/java/fr/xephi/authme/datasource/MySQL.java#L193
https://github.com/AuthMe/AuthMeReloaded/blob/master/src/main/java/fr/xephi/authme/datasource/MySQL.java#L211

By the way, it seems we can have a problem when the type is ipv4 mapped ipv6, we could have : 0000:0000:0000:0000:0000:ffff:255.255.255.255 which could be 45 characters long (max), i'll take a look if we try to store that kind of ip and check if we can do a fix for this specific case

Thanks for the report, we'll update this issue when we have some news

[vophamthanhan]

i've got some new flashy regex that can be used.

the output of the ^\[?(?:(?:(?:0000:){5}|::)ffff:)?([\w:.]*) can be considered the "real ip", and can be stored in sql. (reads ipv4-6 mappings and strips [brackets])

the output of the ^\[?(?:(?:(?:0000:){5}|::)ffff:|fe80:(?:(?:0000:){3}|:))?((?:\w{1,4}[:.]){0,3}\w{1,4}|[\w:.]*) can be used for limiters, such as registration/etc. (same as filter 1, only prefix of global ipv6 and suffix of link-local ipv6)

the wanted output will be in regex match group 1.

[cofob]

@Xephi

The ip columns current type are all VARCHAR(40), since ipv4 are 15 characters long (max) and ipv6 are 39 characters long (max)

Why keep 15 (!) Bytes for storing ip in the base for storing ipv4, when you can fit into a blob in 4 (5, taking into account the header of the ip version). It's the same with ipv6.

[TuxCoding]

@cofob Good point. I considered something like that too. The same procedure applies to the UUID. However there multiple downsides to this approach, that's likely also the reason it wasn't implemented.

The most obvious one is that database administrators can't read a binary IP. There are users that inspect the database, search for entries, edit them, etc. This could be on a PHPMyAdmin instance or something else. Handling binary data requires to manually convert them during a query SELECT * FROM users WHERE UUID=CONV(string, 16, 2) - IP. This could too much complexity for users. An alternative is using MySQL's views for user-facing data, where this data gets converted to a human readable format.

Personally I didn't looked into the matter much. So it's still open whether the user could edit the data then and how portable this solution is for other SQL stores.

[cofob]

Please fix this