Opened 7 years ago

Closed 10 months ago

Last modified 10 months ago

#4540 closed defect (fixed)

[PATCH] Lobby rating not working if username contains underscore

Reported by: elexis Owned by: user1
Priority: Should Have Milestone: Alpha 27
Component: Multiplayer lobby Keywords: patch rfc
Cc: user1, scythetwirler Patch: Phab:D628

Description (last modified by Langbart)

As reported by moe _, finishing a rated game has no effect on the player who has an underscore or other odd character in the playername.

(04:58:02) scythetwirler: underscore in SQL LIKE clauses is some sort of wildcard

related ticket

#6558

Attachments (2)

echelon_escape_like.patch (1.6 KB ) - added by user1 7 years ago.
This escapes _, %, [, ], and \ in 3 places that sqlalchemy's ilike method is used. (first upload was sloppy. forgot to remove some logging )
echelon_escape_like2.patch (1.6 KB ) - added by user1 7 years ago.
This version uses the method escape_like from sqlalchemy_utils. (note that this will require installing the package python3-sqlalchemy-utils on the server) (reuploaded)

Download all attachments as: .zip

Change History (14)

comment:1 by elexis, 7 years ago

Cc: user1 scythetwirler added

comment:2 by leper, 7 years ago

(04:58:02) scythetwirler: underscore in SQL LIKE clauses is some sort of wildcard

Which makes one wonder why someone is using like there, with unsanitized input.

by user1, 7 years ago

Attachment: echelon_escape_like.patch added

This escapes _, %, [, ], and \ in 3 places that sqlalchemy's ilike method is used. (first upload was sloppy. forgot to remove some logging )

comment:3 by user1, 7 years ago

broken down it's like this:

Player.jid.ilike
(
	str(JID).translate
	(
		str.maketrans
		(
			{"_": r"\_",
			 "[": r"\[",
			 "]": r"\]",
			 "%": r"\%",
			 "\\": r"\\"} 	## backslash needs to be escaped in the left operand but not the right
		)
	), 
	"\\"				## tell ilike to use backslash as an escape character (we also escape this backslash)
)

ref: http://stackoverflow.com/a/18935765

comment:4 by elexis, 7 years ago

Keywords: patch rfc added
Summary: Lobby rating not working if username contains underscore[PATCH] Lobby rating not working if username contains underscore

22:32 < user1> elexis: looks like there is no function in sqlalchemy core but insteadwe would have to install python3-sqlalchemy-utils which has what we need but i don't know if it's worth it

by user1, 7 years ago

Attachment: echelon_escape_like2.patch added

This version uses the method escape_like from sqlalchemy_utils. (note that this will require installing the package python3-sqlalchemy-utils on the server) (reuploaded)

comment:5 by user1, 7 years ago

Patch: Phab:D629
Last edited 7 years ago by user1 (previous) (diff)

comment:6 by user1, 7 years ago

Patch: Phab:D629Phab:D628

Oops, fixed wrong diff.

comment:7 by elexis, 7 years ago

Milestone: Alpha 22Work In Progress

Important to fix, but since we can change the lobby bot code after the release, it shouldn't block this. Also we need to fix the readme and should add the module (Phab:D280)

comment:8 by Silier, 3 years ago

Owner: set to user1

comment:9 by Langbart, 22 months ago

Description: modified (diff)

Adding a link to a related ticket in the description

comment:10 by Langbart, 22 months ago

Description: modified (diff)

comment:11 by Dunedan, 10 months ago

Resolution: fixed
Status: newclosed

This issue is already fixed, as it was caused by the same underlying problem as #6558.

Here is an example where two players with underscores in their name successfully played a rated game today:

2023-06-11T08:57:45+00:00 <Ratings> A rated game has ended. hema_narayanan lost against emperor_loki. Rating Adjustment: hema_narayanan (1091 -> 1059) and emperor_loki (1308 -> 1338)

comment:12 by Stan, 10 months ago

Milestone: Work In ProgressAlpha 27
Note: See TracTickets for help on using tickets.