SQL string filters fail to be case sensitive

[silentwings]

for example, in SQLUsers.UsersHandler

def clientFromUsername(self, username):
    entry = self.sess().query(User).filter(User.username==username).first()
    if not entry: return None
    return OfflineClient(entry)

Calling this with username=='[fx]bluestone' returns the offline client with username [Fx]Bluestone

On the Python side (e.g. using == with DataHandler.usernames) there is case sensitivity

This likely causes inconsistencies

[silentwings]

Easiest way to reproduce: send e.g. "GETUSERINFO [fx]bluestone" when [Fx]Bluestone is offline (if the client requested is online, but case is incorrect, there will be a crash due to this bug!)

[abma]

Calling this with username=='[fx]bluestone' returns the offline client with username [Fx]Bluestone

doesn't that mean, that the search is case sensitive but the search should be case insensitive?

[abma]

https://stackoverflow.com/questions/16573095/case-insensitive-flask-sqlalchemy-query

->

.query.filter(Model.column.ilike("ganye"))

-> entry = self.sess().query(User).filter(User.username.ilike(username)).first()

should do the trick (untested)

[abma]

ugh: https://stackoverflow.com/questions/22172727/how-to-force-a-filter-to-match-exact-cases-case-sensitivity-in-sqlalchemy/31788828

from sqlalchemy import func entry = self.sess().query(User).filter(User.username == func.binary(username)).first()

[silentwings]

https://www.oreilly.com/library/view/mysql-cookbook/0596001452/ch04s10.html "String comparisons in MySQL are not case sensitive by default:" This seems a legitimate case in which to blame MS...

Same convention in http://underpop.free.fr/m/mysql/teach-yourself/using-operators-with-strings.html for mariadb

[silentwings]

http://crazedmonkey.com/blog/links/sqlalchemy-does-not-force-case-sensitive-matching-for-mysql-or-any-other-dbs.html

Seems the issue also exists in SQLAlchemy; our col types are wrong, utf8 defaults to utf8_general_ci which is case insensitive, and utf8_general_cs is needed for case sensitivity

Not sure which is best

• enforce case sensitivity on db side and update db col types
• handle case insensitivity on python side e.g. https://github.com/spring/uberserver/blob/1fa91726789dde1dd17b55f965774782c5716764/protocol/Protocol.py#L961

[silentwings]

resolution is that:

• username is case sensitive but case insensitivity on db side prevents registration of a second user with "same" name in different case
• functions which retrieve user by username (from db) and require a case sensitive check must also check the case on python side (primarily clientFromUsername, which almost all routes through)
• user trying to login with incorrect case gets a error msg with username containing correct case

note that whether db is case sensitive or not is OS dependent, we correctly handle either possibility