Prohibit case insensitivity for usernames

[hakusaro]

For some reason, the database allows players to make usernames that are insensitive, meaning its easy to impersonate an admin and do other malicious things.

Foo != foo != FOo != FOO, when they should all be the same account.

Obviously, this would only affect new account creation, but its a good feature to have.

[Simon311]

Not sure whetever this is a good idea.
I remember bringing up a similar concern regarding warp names, and most people told me to fuck off because apparently it is a useful feature.
By this logic, letting users have different accounts for foo and FOO is also a useful feature.

[AxisKriel]

The thing is, we need consistent behaviour between different storage types. The current behaviour is faulted because MySQL is case insensitive but SQLite is case sensitive, which means one type will allow this while the other won't.

[Simon311]

@Enerdy I see.
Well then, this would also apply to warp names, no?
I honestly don't know whetever we should allow having different accounts depending on letter case.
Doing so forces people to change name more often, but the impersonation bit sounds right...

[hakusaro]

@Simon311 well, to be honest, if the issue also affects warps, it should be fixed there too (for all things going forward).

Enerdy's point I actually forgot, but it's perhaps the most important: MySQL's behavior for case sensitivity is dependent on the operating system in which MySQL is configured on. Installing on UNIX results in case sensitivity (but not on OS X if you're using Mac OS Extended+, because that's insensitive) whereas Windows results in insensitivity by default. Moving between operating systems for hosting the database can affect the operation of TShock.

[QuiCM]

+1 for insensitivity

[hakusaro]

@WhiTexz so you want it to be insensitive then?

[QuiCM]

Foo == foo == fOo

[hakusaro]

@WhiTexz what we're saying here is that MySQL does not properly support insensitivity on sensitive filesystems. The current behavior is that, but it doesn't work on Linux, for instance, and switches behavior if you change operating systems.

[QuiCM]

Can we not simply lower usernames etc before inserting them?

[hakusaro]

As nicatronTg I dislike that idea.

[AxisKriel]

If we want to retain case sensivity, we can set the relevant rows on mysql to Binary. That assures it'll be case sensitive even in non-UNIX systems.

[hakusaro]

@Enerdy it isn't case sensitive now, so we wouldn't be retaining anything...? This issue is to make it case sensitive?

[AxisKriel]

Yes, it wasn't clear to me if we wanted to make it globally case sensitive or not. I might've not been clear: when I said "retain", I meant take the case sensitive part of it (sqlite, unix mysql) and make it the only behaviour.

[rautamiekka]

It seems §Binary§ doesn't mean the data is binary, since I can INSERT and SELECT freely, but case-sensitivity is strict and there appears to be no difference between §LIKE§ and §=§ as far as results go, but it could be MySQL goes through another mile when §LIKE§ is given even if it's §Binary§. Although phpMyAdmin shows the data as hex (§rautamiekka§=§72617574616d69656b6b61§), §mysql§ shows it directly as text, but I wouldn't use §Binary§ since it likely means automatic translation from/to hex or binary, which is additional cycle before the data exits MySQL Server. Checking the source code would be necessary to be sure.

§ascii_bin§ is closest when English alphabet is the solely used one -and- case-sensitivity is needed, otherwise §utf8_bin§ (3 bytes per character max) for wider support of characters, or for complete support, §utf8mb4_bin§ (4 bytes per character), but complete UTF8 is 99.999...% of times unnecessary.

If case insensitivity on database end is desired, in the same order, §ascii_general_ci§, §utf8_general_ci§ and §utf8mb4_general_ci§.

Of course, case-insensitive collates are slowest, but whether they're slower than auto-translation from/to hex or binary I have no clue, and how do I test I have no clue either.

[hakusaro]

I opened this issue. I'm closing it. If anyone wants it open again free feel to comment.