Saturday, December 16, 2023

The Jon Schneider Game of the Year awards: 2023

 

Starting with my favorite video game that I played for the first time this year, and continuing on with the rest of the top 10, these were my personal top games of 2023 (along with the platform(s) on which I played them)!

1. Baldur’s Gate 3 (Windows / Mac)

There are so many facets to this game that, on their own, are exceedingly well done, and joined together, make for an incredible game. The adaptation of D&D 5E combat and mechanics. The overall story. The individual party members, and their stories. The smart way in which the game’s world and characters realistically respond to your actions. It speaks to BG3’s quality that it is the first long-form game in a very long time that I played through from start to finish -- and then immediately made a new character and started a second play-through.

2. Zelda: Tears of the Kingdom (Switch)

It ALSO speaks to Baldur’s Gate 3's quality that when I played Tears of the Kingdom earlier in 2023, I felt that it was the best game I’d played in several years, and a lock for game of the year! A more-than-worthy sequel to Breath of the Wild (my personal 2017 Game of the Year). Hyrule is once again super fun to explore -- this time with the additions of caves, the sky, the underground, and more things to do. The construction system managed to successfully achieve making me feel like “I’m not just a fantasy warrior, I'm a fantasy warrior engineer!”

3. Super Mario Bros. Wonder (Switch)

In my opinion, the best new 2D Super Mario game in 32 years(!!) (Super Mario World, SNES, 1991). The combination of more traditional-style Mario 2D gameplay with the “wonder” sequences in the various levels made for a great combined experience. The pseudo-multiplayer real-time “ghosts” of other players that you can see -- and to a limited extent, interact with, but very importantly, not in any toxic way -- was also a neat (optional) addition which I ended up being quite glad that I had decided to leave enabled.

4. Cobalt Core (Steam Deck / Windows)

This recently-released, spaceship-battle-themed roguelike deckbuilder came out of nowhere for me to claim this high spot on my crowded GOTY list this year. It adds to the traditional Slay-the-Spire-like ability to block incoming damage an ability to move laterally to dodge incoming fire (or to take it on more-armored parts of your ship). Battles are quick and punchy. Even though Cobalt Core is mostly not mechanically similar to FTL, it did give me some of the smartly-designed vibes of that game.

5. Infernax (Steam Deck)

A solid, 8-bit-style platforming action game in the tradition of Castlevania 2 (NES). The tight gameplay and fun progression were enough to keep me engaged straight through from start to credits.

6. Dave the Diver (Steam Deck)

A scuba-speargun-fishing main game mashed together with restaurant management sections, complete with a perfect-beer-pouring minigame? Why does this work so well? Once again, it's because all of the individual pieces are so solid! The charming character design and pixel art certainly don’t hurt, either. The first game in this year’s list that I haven’t actually finished yet: I played mostly in Early Access, decided to set it aside until the full 1.0 release -- and haven’t picked it back up quite yet due to the strength of all of the other new games on offer this year.

7. Octopath Traveler 2 (Switch)

This year’s turn-based JRPG comfort food. An interesting battle system, combined with a story that was decent enough to keep me playing straight through to the end -- albeit for only some of the characters, not all 8. As with the first series entry, my wish would be for better interaction between the 8 protagonist characters.

8. Outer Wilds (Steam Deck)

After having this game on my to-play for a few years, I finally had a good way to play it when I acquired a Steam Deck in the early part of 2023. As I had heard about Outer Wilds prior to playing, this is a game worth playing without being spoiled on what it is all about! I will say to give it a try if a first-person, solo exploration game where you board and fly your own spaceship (while still in the first-person perspective) and explore a small solar system sounds appealing. I did admittedly eventually get stuck on how to progress further (and didn’t want to consult a guide) -- but not until after quite a few hours of really interesting exploration and discovery.

9. Tunic (Steam Deck)

For me, Tunic created a vibe of being a kid back in the pre-Internet days, and having a new Nintendo cartridge to play -- but there's no real in-game help, and the instruction booklet that came with the cartridge (which in Tunic's case is discoverable in pieces within the game itself) is mostly in Japanese (a language I can't read) -- but the booklet's bits of English text, plus illustrations, allow for puzzling out the less obvious parts of game's mechanics and objectives. Add that to some overhead-view Zelda-like combat -- except tricky, with some Souls-like aspects -- and Tunic ends up being pretty cool and unique experience.

10. Alina of the Arena (Mac / Windows)

A last-minute list addition! Another roguelike deckbuilder hybrid -- this time with Into-The-Breach-like tactical positioning, with a solo character you control facing off with one or more enemies on a small hex grid.

Honorable Mentions

A few of the other games I played which were good, but didn’t make this year’s Top 10, in approximate descending order of where they would have appeared on the list, had there been room: Retro Bowl College (iPhone), Diablo 4 (Windows), Pinball FX (Windows), 30XX (Steam Deck), Tinykin (Steam Deck).

Sunday, July 23, 2023

How to fix timestamps on Mac Photos exported files

This is a "Remind my future self how to do this, but hopefully it'll be helpful for the rest of y'all too" post!

To change the timestamps on files exported from the Mac's Photos app to match the dates that the photos and/or videos were actually taken:

1. Install exiftool if it isn't already installed:

brew install exiftool

2. One a a time, run these two commands from the terminal, from the directory where the files are located:

for file in *.jpeg; do touch -t "$(exiftool -p '$CreateDate' -d '%Y%m%d%H%M' "$file")" "$file"; done

for file in *.mov; do touch -t "$(exiftool -p '$CreationDate' -d '%Y%m%d%H%M' "$file")" "$file"; done

When those are done, each file's timestamp should match the actual date that the photo or video was taken.

Any The ExtractEmbedded option may find more tags in the media data warnings can be ignored.

Background

When copying photos and/or videos from an iPhone to a Mac, the copied photos don't end up as individual files in the Mac's filesystem. Instead, they become part of the "Photos Library" on the Mac, in which all photos and movies are stored in a single "blob" file.

Fortunately -- for the purpose of copying and/or backing up photos elsewhere, on non-Apple computers or cloud storage -- the Mac's Photos app provides a capability to "export" photos and videos from the library as individual files. (This is accessed via File menu > Export.)

Two export options are provided: "Unmodified Originals" (which tend to have large file sizes); or as JPG, TIFF, or PNG files (for photos), and .mov files for videos (which produces smaller file sizes).

Unfortunately, the exported photo and image files have a timestamp (shown as "Date Modified" in Finder) of the time the export was performed -- not the time that each individual photo or video was actually taken.

For me, having the date shown for each file in Finder match the date that the photo/video was originally taken is a lot more useful. Hence, the procedure described earlier in this post to make that change.

"CreateDate" versus "CreationDate"

You may have noticed that in the two terminal commands above, the former uses the EXIF tag "CreateDate", and the latter, "CreationDate".

For some reason -- for photos and videos exported using the Photos app on macOS Ventura 13.4, and originally taken on an iPhone running iOS 16.5 -- exported .jpeg and .mov files, respectively, have inconsistent sets of EXIF tags.

The EXIF tags on a paritcular file can be inspected using exiftool via a terminal command like:

exiftool -s my_photo.jpeg

For my exported .jpeg files, this produces output like (with irrelevant tags excluded):

CreateDate: 2023:07:04 09:51:12

There's no "CreationDate" tag present.

For my exported .mov files,  the output is like:

CreateDate: 2023:07:22 14:04:56
CreationDate: 2023:07:04 13:39:20+02:00

So both CreateDate and CreationDate values are present; however, here, "CreateDate" is the timestamp of the Mac Photos app export, and CreationDate is the actual time the video was recorded.

I'm sure there are excellent reasons behind this seemingly-inconsistent state of affairs; I am not aware of what those might be. 😅 In any event, it was easy enough, one I investigated and figured out what was going on, to split the exiftool command into two separate parts, for the EXIF tags that are actually present and correct in the .jpeg and .mov files, respectively.

Credit for the original exiftool command that I adapted here goes to Daniel Schofield on the Ask Different Stack Exchange site.

Friday, July 21, 2023

Recommended for international travel in Switzerland: "Traverlers Wifi"

(Note: I have no affiliation with "Travelers Wifi," nor is this a paid post. I'm just a fan!)

My family and I were fortunate enough to be able to spent the past sixteen days traveling abroad in Switzerland. To cover us while there, our current cell provider, AT&T, wanted US $10 per person per day. For just one person, that of course would work out to $160; for the four of us with cell phones, we'd have gotten hit for $640. Eek!

My wife Melissa, an excellent hobbyist travel planner, researched, and landed on Travelers Wifi as the solution. It's a cell-phone-sized device that provides cellular connectivity in Switzerland (specifically that one country), and allows up to 5 client devices at a time to connect via Wi-Fi to access the Internet.

Travelers Wifi device

The device cost €5.90 (a little over US $6) per day. Quite the cost savings over the up-to-$40-daily that we would have been spending!

Bottom line: It worked great! 

The device connected with no issues and had good Internet speeds everywhere we went (Zurich, Lucerne, Lauterbrunnen and the Berner Oberland region, Vevey and environs, and Geneva). 

Our device was good for about eight hours of battery life on a full (overnight) charge. That's not quite a full day of adventures, but it was not to worry: We had come prepared with a few external battery packs with USB-A outputs; connecting one of those to the Travelers Wifi device was more than enough to get it through an entire day. I'd recommend being similarly prepared to back up the Travelers Wifi device with an external battery.

Pick-up of the device in Zurich was trivial; Travelers Wifi has a dedicated storefront in the Zurich airport in the public (outside of security) area, and they were able to look up our online reservation (made in advance, prior to our trip) and hand over the device with no issues.

Return of the device was similarly easy; although the Geneva airport (from which we were departing) has no Travelers Wifi storefront, it does have several of the yellow "Die Post" mailboxes. The Travelers Wifi comes with a "self-addressed stamped envelope"; we just put the device and it's included cable and wall charger into that envelope, and dropped the resultant thin package into a mailbox at the airport.

I'm happy to be able to cheerfully recommend Travelers Wifi to anyone traveling abroad in Switzerland!

Thursday, June 22, 2023

The mystery of the broken JWT magic link login URLs on iPhone

My team at work was recently facing a problem where "magic link" login URLs being sent out via SMS (text message) were "broken" when received by iPhone users. Only part of the URL's query string portion was properly rendering as part of the link; the remainder -- despite not being separated by a space, or any URL-invalid characters -- was showing up as plain text:


A magic link in this context is an URL that includes a secure, tamper-evident key which identifies the user, and allows them to log in to the application that sent the link, in lieu of having to enter a password. (This has security pros and cons; that linked article provides a nice summary.)

My team is using JWT as the magic link key. JWTs are encoded into three portions, separated by period characters (remember that, it's important later!): A header; the message payload (including things like the user's ID, and the key's expiration time); and the signature verification.

In our case, only the first portion of the JWT value, the header, was being rendered by iPhone recipients of our SMS message as a part of the clickable hyperlink. The remaining two portions were showing up as plain text. This broke the magic link! While it still directed users to our site, it was unable to log them in.

I spent the day yesterday performing an investigation into why this was happening.

For starters, asking Google about the maximum length of an SMS message yields the answer "160 characters." Here in 2023, as far as regular users are concerned, this is no longer really true. (When's the last time you were composing a text message, and your phone stopped you from sending your message because it was longer than about half a Tweet?) All modern providers use "SMS concatenation" to, behind the scenes, break a long SMS message into multiple parts, and then seamlessly stitch those parts back together into a single message for the recipient.

I hypothesized: Perhaps Apple's implementation of SMS concatenation doesn't work when the URL itself is longer than 160 characters (as our magic link login URLs including a JWT token are)? No; I was able to disprove this by sending myself a text message with such an URL; it arrived in one piece, no problems. 

(As an aside, I started out doing these tests by using the web UI of my work's existing Twilio account to send messages to my personal iPhone's number. This worked fine; I pretty quickly determined, though, that I could more expeditiously test by just using my Mac's Messages app to send messages to my own phone number. This produced the same results, as far as the received message ending up broken or not.)

Perhaps SMS concatenation doesn't work when the query string portion of the URL is longer than 160 characters? No; disproved by sending myself such a link, which once again was delivered in one piece, as expected.

Perhaps the problem is when a single query string key-value pair -- or just a query string value -- is longer than 160 characters? No; I was able to successfully send myself messages (using the string "1234567890" repeatedly as the query string value to achieve the target length) in with such query string values excess of 500 characters in length, no problems.

My testing went on like this. I was consistently able to reproduce the broken link behavior using an actual (Dev environment) magic login link; the behavior of any particular URL being broken or not appeared to be consistent/deterministic, at least. Further, by trimming down certain portions of that URL, the link would be correctly delivered in one piece. 

By testing many message and URL variants, and recording for each one whether it succeeded or failed to deliver properly, along with the lengths of the various portions of the message text and the URL, I was finally able to pin down the problematic behavior. Here it is, in plain English:

For a given query string value: If that value contains any URL-valid punctuation characters (i.e. non-alphanumeric characters): If any portion (or "slice") of that query string value beyond the first portion, when separated/sliced by punctuation characters, is 302 characters or longer, the URL will break (on Apple devices). If all such portions are 301 characters or shorter, the URL will render correctly. 

Recall that JWT values consist of 3 portions -- separated by period characters? This meant that if the token's 2nd (payload) or 3rd (signature) encoded portions were in excess of 301 characters, the resulting link would be broken when delivered to an iPhone. 

(Notably: It's only Apple's handling of SMS messages, in their Messages / iMessage app on iPhone and on Mac, where links render as broken in this particular way. In my testing with Android clients, and with Google Voice, all links that I tested with were delivered correctly, regardless of length!)

Here are a few examples of working and broken URLs (when received by an Apple client). To save space (and to make this post less ugly!), instead of actually spelling out URL portions of 300+ characters, I'll represent such portions with the number of characters in that portion. The following links, when delivered to and viewed on an iPhone, or in Apple's Messages app on a Mac:

https://example.com?key=400 (OK; there are no punctuation characters in the query param value)

https://example.com?key=10.302 (BROKEN; the second portion of the query param value is longer than 301 characters)

https://example.com?key=301.301_301 (OK; no portion of the query param value is longer than 301 characters)

https://example.com?key=200~200.400 (BROKEN; the 2nd portion is ok, but the 3rd potion is longer than 301 characters)

https://example.com?key=400-50 (OK; only the first portion of the query param value is longer than 301 characters, and that doesn't manifest the problem)

https://example.com?key1=400&key2=400 (OK; the both query param values here consist only of "first portions", which don't manifest the problem)

To work around this problem -- and to produce links that are some what less nasty-looking on clients that render the entire URL -- I'm planning on making a pair of changes to our magic login tokens:

1. Reducing the payload content to "essential" values only. Namely, the user's email address, and an expiration date/time value. This will cut down on the middle "payload" portion of the JWT.

2. Using HS256 instead of RS256 as the signing algorithm. For our specific application and usage scenario, HS256 will provide sufficient security; but HS256 signature values are significantly shorter in length. 

All of the aforementioned testing was done in June 2023 using an iPhone 12 running iOS 16.5.1; and a MacBook Pro running MacOS Ventura 13.4.  Perhaps Apple will address this issue in future software versions? (But if this particular bug isn't at the top of their priority list, I certainly can understand why not. ☺)

Hopefully this post may be helpful to any of y'all out there who are researching why your SMS messages that include JWT magic link login URLs (or other long URLs including long query string values) being delivered to iPhone clients aren't rendering properly!

Thursday, April 06, 2023

The Mystery of the NULL Values in a NOT NULL DATETIME MySQL Database Column

My development team at work yesterday picked up a task to investigate a error that was periodically showing up in our production logs:

NoMethodError: undefined method `>' for nil:NilClass

This is a common Ruby error that occurs when you try to call a method on a variable with a value of nil. In this case, the "method" in question was the greater-than operator, ">". 

The line of code associated with the error in question was actually a conditional evaluation that involved 3 separate ">" evaluations, which obscured the exact variable that was the source of the error a bit. 

Two of the three variables in question were attributes of an ActiveRecord model object whose associated database column was defined as NOT NULL. Those attributes were not being re-assigned after being read from the database, so we initially ruled them out as being the possible cause of the error.

However, further investigation revealed that none of the three variables on the problematic line of code could -- in theory -- possibly ever be null. Faced with this, I decided to take a closer look at the actual data in the database.

Our production environment is split into multiple MySQL databases. For purposes of conveniently being able to query customer data across databases all at once, we have an ETL process which extracts (non-sensitive) customer data from the source databases, and populates it all into a single central Snowflake SQL database. 

The database structure of the relevant table (Users) was similar to this (simplified for brevity):

  • id: INT, primary key
  • first_name: VARCHAR(255)
  • last_name: VARCHAR(255)
  • updated_at: DATETIME, NOT NULL

Temporarily putting common sense aside, I queried the snowflake database to see if any of the NOT NULL updated_at values were nevertheless null:

SELECT * FROM Users WHERE updated_at IS null

This returned 0 results, as expected. 

Acting on a hunch, I tried searching for unexpectedly old records; this database has been in service since about 2009:

SELECT * FROM Users WHERE updated_at < '2005-01-01'

This query did produce some results! Out of the tens of thousands of records in the Users table, a few hundred records were returned whose updated_at date was '1970-01-01 00:00' -- a value equal to the start of the epoch in Unix time

At this point I questioned: Does Ruby on Rails and/or ActiveRecord somehow treat start-of-epoch date values of '1970-01-01 00:00' at nil? This seemed unlikely, but I tested it anyway, setting the updated_at value for an existing record in my local machine's copy of the database to that start-of-epoch value; and then reading the record into the corresponding ActiveRecord model object. Not unexpectedly, the updated_at attribute on the model did not end up with a nil value; it had the expected value of midnight on January 1, 1970.

I still felt like I might be onto something here, though. At this point, I wanted to inspect the data in the actual MySQL customer database. I don't have access to the live database, but I was able to access a read-only replica of the database.

Having connected to that MySQL database, I verified that the updated_at column was still defined at NOT NULL; it was. I then ran the same query to look for old values:

SELECT * FROM Users WHERE updated_at < '2005-01-01'

As before, this returned a few records -- about 300. However, I noticed that the actual updated_at values were different. This time, they were returned as '0000-00-00 00:00'. 

Year "0", month "0", day "0" -- all nonexistent values. Feeling a bit of a chill, I re-ran my earlier query to look for records with null values:

SELECT * FROM Users WHERE updated_at IS null

MySQL returned the same 300 records

Cool, cool, coolcoolcool. The evidence indicated that (1) MySQL was allowing values of '0000-00-00 00:00' to be set on a DATETIME, NOT NULL column; and (2) despite the aforementioned NOT NULL restriction on the column, was evaluating such values as being null.

To close the loop, back on my local development machine's database, I set an existing record to the '0000-00-00 00:00' value -- which MySQL happily allowed -- and then, in the Rails console, populated a model object from the record. Sure enough: The updated_at attribute on the record had been assigned a value of nil.

Summary of findings

(1) In a Rails application (and possibly in other languages / frameworks as well), it may not be safe to assume that a value read from a DATETIME, NOT NULL column on a MySQL database is actually guaranteed not to be null. MySQL allows a value of '0000-00-00 00:00' to be set in such columns (despite the NOT NULL restriction); such values are treated as null both my MySQL itself, and by Rails / ActiveRecord.

(2) Although I'm not familiar with its inner workings, that the ETL process from the multiple production MySQL databases to consolidate data into a single Snowflake database couldn't 100.0% be trusted not to change data values. In this case, it silently converted MySQL DATETIME values of '0000-00-00 00:00' to values of '1970-01-01 00:00' 

Mitigations

Doing a bit of additional reading after the fact, I found a StackOverflow post that references a MySQL configuration mode of "NO_ZERO_DATE," which when set, prevents such "zero date values" from being set. 

According to the MySQL documentation, not setting that option may be "more convenient" and/or consume less space. Based on my team's experience here, though, I'd certainly be inclined to take advantage of that NO_ZERO_DATE setting whenever feasible!