Be careful of optional parameters in your APIs

Robert Vandenberg Huang — Sun, 11 Jul 2021 08:52:59 +0000

Recently, a team in my company has been facing a chaotic situation caused by bad API designs from a payment service, giving me an idea to share the story here. Most of the details in the article have been modified due to sensitivities but the big picture is not affected.

The background, to put it roughly, is that the payment service provides a web API that supports offline transactions. After a customer has completed an offline transaction (for example, using a credit/debit card to transfer money), the customer can enter its reference ID on the merchant's website. The website then calls the offline transaction API to verify the reference ID. If the ID is valid, the website increases the user's balance.

Everything was perfect until one day the payment service added two parameters to the API without releasing change notes to callers. Simply put, the new parameters allow its callers to specify the ID of a merchant's subsidiary (if any) so the verification will fail if the reference ID belongs to another subsidiary. In order to maintain backward compatibility, both parameters are optional.

This was where the catastrophe started. For some reason, after the two optional parameters were added, malicious users found that a reference ID of the transaction that does not belong to the merchant can pass the verification. It is like the reference ID of an offline transaction to Steam can be used to increase the balance on Epic Games Store. This caused a huge loss to victim merchants because they actually did not receive the money, yet the API misled them to increase a malicious user's balance.

After a great effort of investigation and lengthy communication with the provider, the team found that the provider's API works if the two parameters are present. However, the damage has been done.

So two morals of the story:

When adding new optional parameters to an old API, testing the new parameters working as expected is equally important as they are omitted. And yet the latter is usually not covered.
Sometimes, a breaking change is acceptable when maintaining backward compatibility becomes expensive.

Thank you for reading this.

Fun interview question: How to update Saint Quartz stones with one line SQL statement in FGO?

Robert Vandenberg Huang — Sat, 22 Feb 2020 16:37:17 +0000

So recently I have been obsessed with the mobile game Fate/Grand Zero(FGO) and spent lots of time probing into every detail of the game. One particular detail that jumps out right in front of me is how the Summoning works. It also becomes one of my fun interview questions that challenge candidates' SQL skills.

In FGO, a player holds two types of "Saint Quartz stones". The first type is Free Saint Quartz which is usually awarded for achievements such as finishing certain levels or daily logging-in to the game. The second type is Paid Saint Quartz which is, as the name has implied, purchased by the player. The player can either pays 3 stones to summon one time or 30 stones, for higher probabilities of getting rare characters, to summon 10 times. The rules of how they are spent are:

Free Saint Quartz stones will be spent first.
If Free Saint Quartz stones are not enough for summoning, Paid Saint Quartz stones will be spent then for the remaining part.

For example, if the player holds 40 Free Saint Quartz stones and 20 Paid Saint Quartz stones, after 10-time summoning, the numbers of stones are 10 and 20 respectively. And if the player holds 15 free and 20 paid stones, the results are 0 and 5.

The interview question is: given that a table player contains two columns free_sant_quartz and paid_sant_quartz, both in integer, write a SQL statement according to the rules with only one UPDATE statement. (Assuming that the DB system is MySQL or MariaDB)

Before we get to the real UPDATE answer, let us take a look at the SELECT statement that allows the player to preview how many remaining stones there would be after 10-time summoning.

/*
CREATE TABLE IF NOT EXISTS players (
  id int(6) NOT NULL,
  free_sant_quartz int(3) NOT NULL,
  paid_sant_quartz int(3) NOT NULL,
  PRIMARY KEY (id)
);
*/

SELECT 
GREATEST(free_sant_quartz - 30, 0) AS free_sant_quartz_remaining, 
LEAST(free_sant_quartz - 30, 0) + paid_sant_quartz AS paid_sant_quartz_remaining
FROM players;
-- The WHERE clause is omitted

The GREATEST() and LEAST() are two very useful functions to solve the problem as they simulate the "priorities" between free_sant_quartz and paid_sant_quartz. If the player holds 30 Free Sant Quartz stones or more, the result of GREATEST(free_sant_quartz - 30, 0) will simply be the remaining Free Sant Quartz stones and the clause LEAST(free_sant_quartz - 30, 0) produces zero. And if the player holds less than 30, the former produces zero and the latter produces a negative number so paid_sant_quartz gets deducted for the remaining part.

Once we get the SELECT statement, it becomes easy to come up with an UPDATE statement for the problem. The only catch is that we need a temporary variable for the deducted free_sant_quartz.

UPDATE players SET 
free_sant_quartz = GREATEST(@temp := free_sant_quartz - 30, 0),
paid_sant_quartz = LEAST(@temp, 0) + paid_sant_quartz
WHERE free_sant_quartz + paid_sant_quartz >= 30 AND player_id = ?;
-- The player_id is omitted.

Case closed.

You can play with the complete example at DB Fiddle.

DEV Community: Robert Vandenberg Huang

Be careful of optional parameters in your APIs

Fun interview question: How to update Saint Quartz stones with one line SQL statement in FGO?