Page MenuHomeWildfire Games
Differential D628

Escape underscore(and other characters) in like clauses in echelon ratings bot.
Needs ReviewPublic
Actions

Authored by user1 on Jun 11 2017, 4:55 PM.

Details

Reviewers
scythetwirler
elexis
Itms
Group Reviewers
Restricted Owners Package(Owns No Changed Paths)
Trac Tickets
#4540
Summary

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.

This uses the method escape_like from sqlalchemy_utils which will be a new dependency for running the ratings bot. This will require installing the package python3-sqlalchemy-utils on the server and also updating the lobby readme.

Test Plan

Step 1. Apply the patch and install python3-sqlalchemy-utils on the server.
Step 2. Test rated games with names that contain the following characters_, % (possibly others like [ and ] and anything else that could be a special character in a LIKE clause but it's probably testing overkill)
Step 3. ?????
Step 4. Profit

Diff Detail

Lint
Lint Skipped
Unit
Unit Tests Skipped

Event Timeline

user1 created this revision.Jun 11 2017, 4:55 PM
user1 added reviewers: scythetwirler, Itms.Jun 11 2017, 5:19 PM
Vulcan added a subscriber: Vulcan.Jun 12 2017, 8:37 PM
Executing section Default...
Executing section Source...
Executing section JS...
Executing section XML GUI...
Executing section Python...

source/tools/XpartaMuPP/EcheLOn.py
| 189| ····#·TODO:·Support·draws.·Since·it's·impossible·to·draw·in·the·game·currently,
|    | [NORMAL] PyLintBear (W0511):
|    | W0511 - TODO: Support draws. Since it's impossible to draw in the game currently,

source/tools/XpartaMuPP/EcheLOn.py
|  40| ··def·__init__(self,·room):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  41| ····self.room·=·room
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  42| ····self.lastRated·=·""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  44| ··def·getProfile(self,·JID):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  45| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  48| ····stats·=·{}
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  49| ····player·=·db.query(Player).filter(Player.jid.ilike(escape_like(str(JID)),·"*"))
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  51| ····if·not·player.first():
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  52| ······return
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  54| ····queried_player·=·player.first()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  55| ····playerID·=·queried_player.id
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  56| ····if·queried_player.rating·!=·-1:
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  57| ······stats['rating']·=·str(queried_player.rating)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  58| ······rank·=·db.query(Player).filter(Player.rating·>=·queried_player.rating).count()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  59| ······stats['rank']·=·str(rank)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  61| ····if·queried_player.highest_rating·!=·-1:
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  62| ······stats['highestRating']·=·str(queried_player.highest_rating)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  64| ····gamesPlayed·=·db.query(PlayerInfo).filter_by(player_id=playerID).count()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  65| ····wins·=·db.query(Game).filter_by(winner_id=playerID).count()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  66| ····stats['totalGamesPlayed']·=·str(gamesPlayed)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  67| ····stats['wins']·=·str(wins)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  68| ····stats['losses']·=·str(gamesPlayed·-·wins)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  69| ····return·stats
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  71| ··def·getOrCreatePlayer(self,·JID):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  72| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  78| ····players·=·db.query(Player).filter_by(jid=str(JID))
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  79| ····if·not·players.first():
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  80| ······player·=·Player(jid=str(JID),·rating=-1)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  81| ······db.add(player)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  82| ······db.commit()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  83| ······return·player
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  84| ····return·players.first()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  86| ··def·removePlayer(self,·JID):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  87| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  92| ····players·=·db.query(Player).filter_by(jid=JID)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  93| ····player·=·players.first()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  94| ····if·not·player:
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  95| ······return·None
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  96| ····players.delete()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  97| ····return·player
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  99| ··def·addGame(self,·gamereport):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
| 100| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
| 109| ····if·any(map(lambda·state:·state·==·'active',
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
| 111| ······return·None
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentat

http://jw:8080/job/phabricator_lint/185/ for more details.

Itms edited edge metadata.Jun 12 2017, 8:43 PM

Hi, I took the liberty of running our experimental Python linter on this file. As you can see there are a lot of messages (the output was even truncated, I'm retrying now). If you want to take a look at the various reported errors, and see whether you see false positives (warnings you disagree with), that would be a good test for the linter and we could refine some things, on top of fixing the numerous issues in our Python scripts ?

If you don't want to look into that that's fine too ? Thanks for your work!

Itms resigned from this revision.Jun 12 2017, 8:43 PM

Also, I'd rather have @scythetwirler review that one, but I'm staying around.

Vulcan added a comment.Jun 12 2017, 8:45 PM
Executing section Default...
Executing section Source...
Executing section JS...
Executing section XML GUI...
Executing section Python...

source/tools/XpartaMuPP/EcheLOn.py
| 189| ····#·TODO:·Support·draws.·Since·it's·impossible·to·draw·in·the·game·currently,
|    | [NORMAL] PyLintBear (W0511):
|    | W0511 - TODO: Support draws. Since it's impossible to draw in the game currently,

source/tools/XpartaMuPP/EcheLOn.py
|  40| ··def·__init__(self,·room):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  41| ····self.room·=·room
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  42| ····self.lastRated·=·""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  44| ··def·getProfile(self,·JID):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  45| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  48| ····stats·=·{}
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  49| ····player·=·db.query(Player).filter(Player.jid.ilike(escape_like(str(JID)),·"*"))
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  51| ····if·not·player.first():
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  52| ······return
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  54| ····queried_player·=·player.first()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  55| ····playerID·=·queried_player.id
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  56| ····if·queried_player.rating·!=·-1:
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  57| ······stats['rating']·=·str(queried_player.rating)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  58| ······rank·=·db.query(Player).filter(Player.rating·>=·queried_player.rating).count()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  59| ······stats['rank']·=·str(rank)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  61| ····if·queried_player.highest_rating·!=·-1:
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  62| ······stats['highestRating']·=·str(queried_player.highest_rating)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  64| ····gamesPlayed·=·db.query(PlayerInfo).filter_by(player_id=playerID).count()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  65| ····wins·=·db.query(Game).filter_by(winner_id=playerID).count()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  66| ····stats['totalGamesPlayed']·=·str(gamesPlayed)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  67| ····stats['wins']·=·str(wins)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  68| ····stats['losses']·=·str(gamesPlayed·-·wins)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  69| ····return·stats
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  71| ··def·getOrCreatePlayer(self,·JID):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  72| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  78| ····players·=·db.query(Player).filter_by(jid=str(JID))
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  79| ····if·not·players.first():
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  80| ······player·=·Player(jid=str(JID),·rating=-1)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  81| ······db.add(player)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  82| ······db.commit()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  83| ······return·player
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  84| ····return·players.first()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  86| ··def·removePlayer(self,·JID):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
|  87| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  92| ····players·=·db.query(Player).filter_by(jid=JID)
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  93| ····player·=·players.first()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  94| ····if·not·player:
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  95| ······return·None
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 6 spaces, expected 12

source/tools/XpartaMuPP/EcheLOn.py
|  96| ····players.delete()
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  97| ····return·player
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
|  99| ··def·addGame(self,·gamereport):
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 2 spaces, expected 4

source/tools/XpartaMuPP/EcheLOn.py
| 100| ····"""
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
| 109| ····if·any(map(lambda·state:·state·==·'active',
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentation. Found 4 spaces, expected 8

source/tools/XpartaMuPP/EcheLOn.py
| 111| ······return·None
|    | [NORMAL] PyLintBear (W0311):
|    | W0311 - Bad indentat

http://jw:8080/job/phabricator_lint/186/ for more details.

elexis added a reviewer: elexis.Jul 4 2017, 5:57 PM
elexis added a subscriber: elexis.
  • sqlalchemy_utils is a good choice since we already use sqlalchemy and because we shouldn't hardcode a whitelist of characters to escape (bound to fail). HOWEVER: We use debian wheezy which supports only python 3.2 and python 3.2 dropped support for unicode literals (for example u'foo') and python 3.3 added that support back again! So without finally upgrading the lobby server, it is impossible to use the patch currently. It is also not sanely possible to support those characters (we can add a custom u function somewhere, but thats ugly as hell and it would have to be documented in the README). The lobby readme should eventually state that python 3.2 support is dropped when using this module.

Other than that:

  • The patch is correct, because player JIDs are escaped and we can assume that sqlalchemy is doing a better job at keeping track of which characters need to be escaped rather than some custom regex from stackoverflow.
  • The patch is complete (at least for like queries) since a grep for like in that directory yields exactly the patched lines.

All db interaction:

EcheLOn.py:from LobbyRanking import session as db, Game, Player, PlayerInfo
EcheLOn.py:    player = db.query(Player).filter(Player.jid.ilike(str(JID)))
EcheLOn.py:      rank = db.query(Player).filter(Player.rating >= queried_player.rating).count()
EcheLOn.py:    gamesPlayed = db.query(PlayerInfo).filter_by(player_id=playerID).count()
EcheLOn.py:    wins = db.query(Game).filter_by(winner_id=playerID).count()
EcheLOn.py:    players = db.query(Player).filter_by(jid=str(JID))
EcheLOn.py:      db.add(player)
EcheLOn.py:      db.commit()
EcheLOn.py:    players = db.query(Player).filter_by(jid=JID)
EcheLOn.py:    players = map(lambda jid: db.query(Player).filter(Player.jid.ilike(str(jid))).first(),
EcheLOn.py:    game.winner = db.query(Player).filter(Player.jid.ilike(str(winning_jid))).first()
EcheLOn.py:    db.add(game)
EcheLOn.py:    db.commit()
EcheLOn.py:    db.commit()
EcheLOn.py:    players = db.query(Player).filter(Player.rating != -1).order_by(Player.rating.desc()).limit(100).all()
EcheLOn.py:    players = db.query(Player.jid, Player.rating).filter(func.upper(Player.jid).in_([ str(JID).upper() for JID in list(nicks) ]))

We probably shouldn't save JIDs in the database to begin with, since we recently had the issue that fpre was in the players table with multiple JIDs that only differed in the resource part. Not saving the JID but only the username would solve that. But that's a somewhat different issue.
About the upper in the last line, I guess that's due to rP15888 and I wouldn't be surprised if that still causes issues in some other places.

elexis edited edge metadata.Jul 7 2017, 1:28 PM

What I didn't mention yet:

The underscore is a wildcard for a single character, so for many players with the underscore Foo_Bar matches the actual Foo_Bar, so many people are not affected by this bug.
However if there is a Foo_Bar and a FooLBar in the db, the latter might be matched first, which must have been the case for Moe__.
So both players would report the game, but the game would be accounted for the wrong player and the rating is only applied if the same game is reported by the same players.

Dunedan added a subscriber: Dunedan.Jan 12 2018, 6:34 PM

Step 1. Apply the patch and install python3-sqlalchemy-utils on the server.

That should be added to the installation instructions in the README as well.

elexis mentioned this in rP15813: Makes the bot more lenient on case sensitivity due to inconsistencies in case….Oct 28 2018, 2:46 PM
elexis mentioned this in rP21718: Lobby Bot Optimizations and Upgrade to recent SleekXMPP/ejabberd versions..Nov 2 2018, 12:23 PM
elexis added a comment.Nov 2 2018, 1:08 PM

rP15813 and rP15888 were the first commits messing with case-insensitiveness.
rP21718 added the same change in other places. So requires a rebase for that (if not the different implementation proposed below).

The patch is correct, because player JIDs are escaped and we can assume that sqlalchemy is doing a better job at keeping track of which characters need to be escaped rather than some custom regex from stackoverflow.

Actually 'better' doesn't imply correct or ideal.

The language definition is sufficiently easy (only 3 escaped characters, assuming sqlalchemy implements the language correctly) to maybe being able to rely on it:
https://sqlalchemy-utils.readthedocs.io/en/latest/_modules/sqlalchemy_utils/functions/database.html#escape_like
But escaping still seems like a workaround, since it relies on some language definition and an implementation of the language being free of bugs (no matter which library or implementation).

It is possible to do exact playername matching if the JIDs are always stored as lowercase in the database. Then there is no need to escape user input to begin with.
It would require a database transformation, but that should be a matter of running a query or two.

Revision Contents
Changeset List

PathSize
source/
tools/
XpartaMuPP/
7 lines

Diff 2521

View Options

source/tools/XpartaMuPP/EcheLOn.py

Loading...
Wildfire Games · Phabricator