C++ game accounts with MySQL and Argon2id

Intro

0:00 Hello and welcome back to episode five, where we're going to give our players accounts, kept in MySQL, with their passwords hashed the way they should be. Let's begin with a quick look at where we're going to be by the end of the episode.

0:13 By the end of this episode, our server's code creates accounts in MySQL, and checks each password against its Argon2id hash, and these tests run it against MySQL, in Docker. And the accounts table they write to has a unique key on the name, so two players can't share one.

0:30 On the website of the series you can find more information about the project. The code for the project is open source on GitHub, tagged at each step of each episode so that you can follow along.

Last time

0:42 Now, before we get to today's work, let's quickly go over what we did last time. Our server started measuring how it runs: a gauge for the players online, a histogram for how long each tick takes, and counters for the bytes and the messages. Prometheus scrapes those numbers two seconds apart, and Grafana draws them on a dashboard that lives in the repo, as a file. And Docker Compose starts the server, Prometheus and Grafana together, with one command. We also wrote a bot that plays like someone at the keys, started three hundred of them, and saw that what we send grows with the square of the players. Before we add anything, let's see what's missing from our game right now.

The problem

1:21 So we're going to run last episode's server again, with a few bots. I've started that server in the background, in this terminal. Then three bots connect, and the server calls them players one, two and three. They start in a row across the middle of the board, and wander off. Now I stop them, and start those three bots again. To the server, they're new players, four, five and six, back in the middle. A player lasts as long as its connection, so when you close the client, your cube is gone with it. And when the server restarts, everybody's gone, as it keeps nothing on disk. There's also no way to tell who's who, so anybody can be anybody.

Today

2:01 Today, we're going to give each player an account, kept in a database, so that a player who comes back is still the player from yesterday. The database is MySQL, which runs in Docker Compose, with its tables in a file in the repo. A password doesn't go into it as it was typed: we keep an Argon2id hash of it, through a library called libsodium. Our server talks to MySQL through MySQL's own C API, with prepared statements, and an accounts class creates an account and finds one by its name. Tests run all of it against MySQL, on my machine and in CI. Nothing logs in with these accounts yet, as logging in is going to get a server of its own, which keeps the slow hashing off the game server. All right, let's get started with which kind of database we want.

SQL or NoSQL

2:52 In the first episode, I said that MySQL is mostly my preference. I also said that we'd come back to it once the game needed a database, which is where we are now. Our data, so far, is accounts, and a player for each account. Later on, there are going to be items, guilds, friends, and trades between players. That data is full of relations, as a player has items, an item belongs to one player, and a trade moves items from one player to another. A relational database such as MySQL keeps those as tables, and it enforces the rules between them for us. A unique key means that two accounts can't share a name. A foreign key means that a player belongs to an account that exists. And a transaction means that a trade happens completely, or not at all. So an item can't be duplicated, or lost halfway through, which is a kind of bug that MMOs are quite famous for. A document database such as MongoDB keeps each record as a document, such as a whole player with its items inside it. A document is flexible, and it's a good fit for data you read and write as a whole. MongoDB has transactions these days too, so a trade could be done safely there as well. However, for a game where players trade, and a lot of things relate to each other, I'm going with MySQL. It's still a choice we can revisit, as the code that talks to the database is going to stay in a few small classes.

MySQL

4:14 We're going to run MySQL from its official Docker image. The image reads its settings from environment variables: the database to create, a user and its password, and what to do about the root password. And when it starts with an empty data folder, it runs the SQL files it finds in a folder called docker entrypoint initdb dot d. So MySQL is going to create our tables from a file in the repo, through that folder.

4:41 We start in compose dot yaml, where MySQL gets a service of its own, at the top. We will use an official mysql image with long-term support. Under environment go the variables we saw on Docker Hub. MySQL database asks the image to create a database called nanommo, the first time it starts. MySQL user and MySQL password create a user, also called nanommo, and assign it that database. We will get a random root password that we will not make use of. On a real server, the password would come from secrets that stay out of git. Under volumes, the first line mounts a named volume, called mysql, at slash var slash lib slash mysql, which is where MySQL keeps its data. A named volume is storage that Docker looks after, apart from any container. So the accounts survive when the container is removed, say by a docker compose down. Without it, they'd live inside the container and be deleted with it. The second line mounts our db folder, and colon r o lets the container have read-only access. Ports publishes MySQL's port, three three oh six, on my machine, so that the servers and the tests we run outside Docker can reach it. Then a healthcheck, which is a command Docker runs inside the container to be able to tell when it's ready. A container can be running for a while before the database accepts connections, so we need servers to stay on hold. Mysqladmin ping asks the server whether it's alive, using the admin tool that comes with MySQL. Dash h one two seven dot oh dot oh dot one makes it ask over TCP, and we're going to come back to why in a moment. Dash dash silent keeps it quiet, as all Docker needs from it is the exit code. It runs two seconds apart, up to thirty times, so MySQL gets a minute to start. At the bottom, volumes declares the named volume, so that Compose creates it the first time. The schema goes into a new folder, db, as schema dot sql. That's the file MySQL is going to run, and as it's in the repo, anyone who clones it gets our tables as we have them. Create table accounts comes first, with a row for each account. The id is an INT UNSIGNED, so it can't be negative, and AUTO INCREMENT makes MySQL number the accounts for us: one, two, three, and so on. So two accounts created at once can't end up with one id. It's the PRIMARY KEY, which is how a row is found, and the account's id is what the rest of the game is going to refer to. The username is a VARCHAR, a string of up to sixteen characters. NOT NULL means it has to be there, and UNIQUE adds a unique key, so that two accounts can't share a name. By default, MySQL compares these names without case, so Alice with a capital A and alice count as one name. That's what we want, as two players with those names would be quite confusing. The password hash is a VARCHAR of up to a hundred and twenty-eight characters. It's not the password, and we're going to look at why right after we start MySQL. A hundred and twenty-eight is the size that libsodium's hashes fit into. And created at is a TIMESTAMP, which defaults to the current time, so we know when each account was made without our code writing it. Then create table players, with where each account's player was when it last left. Its primary key is the account id, so each account has one player at most. Then x and z, as FLOATs, which is how the server keeps positions. And a FOREIGN KEY makes sure that a player's account id is an account that exists. ON DELETE CASCADE deletes the player along with its account, so no player is left behind without one. MySQL runs this file when it starts on an empty volume, and not after that. So once we change the schema, we're going to need migrations, which is a topic for when we get there. In dot dockerignore, the db folder stays out of the Docker build's context, like the compose file and the monitoring folder. So a change to the schema doesn't rebuild the server's image. Let's go back to the healthcheck for a moment, and the dash h we gave it. On its first start, the image runs a temporary server, to set things up and run our schema, and that one doesn't listen on the network. Mysqladmin's default, localhost, would reach it through a socket file, and call MySQL healthy too early. Pinging over TCP waits for the real server, which listens on the network once the setup is done.

9:09 Let's start it with docker compose up dash d, which starts MySQL in the background, and dash dash wait waits until its healthcheck passes. Before the container, Compose creates the network and the mysql volume, which didn't exist yet. The first start takes a little while, as MySQL sets up its data folder and runs our schema. Docker compose exec runs a command in the running container, here MySQL's own command-line client, logged in as nanommo, on the nanommo database. Dash e runs the SQL we give it, which here is two statements. MySQL warns us about the password on the command line, which is fair, and fine for a database on our own machine. Show tables lists our two tables, and describe accounts shows the columns, with the unique key on the username.

Passwords

10:01 Before we write accounts to the database, let's talk about passwords. We don't keep the password in plain text, because if the database leaks, all the passwords are out. A lot of databases have leaked over the years, and people reuse their passwords on other sites too. Instead, we keep a hash of it, from a function that turns the password into a value that can't be turned back. To check a login, we hash what was typed, and compare the two. Each password also gets a salt, which is a few random bytes mixed into its hash. That way, two people with one password get different hashes, and an attacker can't use tables of hashes for common passwords that were worked out in advance. And the hash is slow on purpose, as a fast hash such as SHA-256 lets an attacker try billions of guesses a second on a graphics card. We're going to use Argon2id, which takes tens of milliseconds and sixty-four megabytes of memory for each guess. And the memory is what makes graphics cards a lot less helpful. That's barely noticeable for one login, and a lot for billions of guesses. Argon2 won the Password Hashing Competition in 2015, and it's the first choice in OWASP's advice on storing passwords.

11:15 To hash passwords, we're going to use libsodium, by Frank Denis. It's a crypto library in C, with an API that's hard to use the wrong way, and its password hashing uses Argon2id. Crypto pwhash str hashes a password into a string that holds the algorithm, its settings, the salt and the hash. And crypto pwhash str verify checks a password against that string. Those two, and its random numbers later on, are what we need from it.

11:44 Unlike our other libraries, libsodium builds with autotools, not CMake, so FetchContent can't build it for us. Homebrew and Ubuntu both package it, so we're going to take it from the system, and the README is going to say how to install it. In the top-level CMakeLists, find package PkgConfig loads CMake's support for pkg-config. That's the tool that tells a build where a system library's headers and binaries are, from a small file the package installs. Then pkg check modules looks for libsodium, and REQUIRED stops the configure step with an error if it isn't installed. That's a lot clearer than a missing header halfway through the build. And IMPORTED TARGET makes a target for it, called PkgConfig colon colon sodium, which we can link with like any other. In the server's CMakeLists, the core library gets password dot c c, and links with that target. It's public, like the rest, so whatever links with the core gets libsodium too. The core also links with Abseil's check, which we're going to use in a moment, and the tests get password test. Now the new header, password dot h, with its header guard. It includes string, for the hash we hand back, and string view, for the password we're given. Then Abseil's status or, for a result that can fail. Hash password hashes a password with Argon2id, and returns the string we saw in libsodium's docs, with the salt and the settings in it. So that string is all we need to keep in the database. The hash needs a lot of memory, on purpose, and getting it can fail, which is why it comes back in a status or. It takes a string view, as it reads the password and keeps none of it, so the caller can pass whatever it has without a copy. Verify password takes a hash and a password, and is true if they match. A bool is enough, as there's no error to report. In the source file, our own header comes first, as the style guide orders them. Then sodium dot h, libsodium's header, with the C system headers. Then array, for the buffer the hash is written into, and string. From Abseil, its check, from its logging library, and then status, while status or comes with our header. An unnamed namespace keeps init sodium to this file, as nobody else needs it. The library has to be set up before it's used, which is what sodium init does. It picks the fastest code for this CPU, and gets its random numbers ready. Calling it again does nothing, so it's safe to call it each time, and we don't have to remember to do it in main. A negative result means libsodium couldn't initialise, which points to a broken system that no caller could do anything useful about. So we use Abseil's check, which checks a condition, and if it's false, stops the program with our message. That's for a state the program can't carry on from, while errors a caller can handle go back as a status. Hash password calls it first, before it does anything else. The hash goes into an array of chars, crypto pwhash STRBYTES long, which is the hundred and twenty-eight that our column holds. Crypto pwhash str writes the hash into it, from the password's bytes and their length. The last two arguments are the limits on how hard Argon2 works. Opslimit interactive is how many passes Argon2 makes over its memory, and memlimit interactive is how much memory it uses. If it can't get that memory, it returns non-zero, so we return a resource exhausted error. That's Abseil's code for running out of something, here memory. Then we go back up and add a comment over the call: what libsodium suggests for logins, two passes over sixty-four mebibytes of memory. There are stronger presets, called moderate and sensitive, which take a second or more. They suit things such as unlocking a disk, and not a game's login screen. If it worked, we return the hash, which is a C string inside the array. So we turn it into a std string, up to its zero, and the status or takes it as its value. Verify password sets up libsodium too, then calls crypto pwhash str verify, with the hash and the password's bytes. It reads the algorithm, the settings and the salt from the hash, hashes the password with them, and compares the two. Zero means it matches, and that's what verify password turns into its bool. As the hash says how it was made, we can raise the limits one day, and the old hashes still verify. Then the tests, in password test dot c c, which include our header, string, Abseil's status or, and gtest. The first is about accepting the right password, and refusing a wrong one, which is the whole job of a password check. It starts with a status or, called hash, and fills it by hashing correct horse. Assert true, like in episode one, stops the test there if hashing failed. Then it expects verify password to accept correct horse, the password we hashed. And it expects verify password to refuse correct horse with a capital E at the end. A check that let anything in would pass the first half on its own, so the second half matters as much. The second, salts each hash, hashes correct horse twice, and expects two different results, which is the salt at work. If two players pick one password, their hashes still differ, so a leaked table doesn't show who shares a password. In the Dockerfile, the toolchain gets pkg-config, and libsodium dev, which has the headers, as the build needs both. A new stage, runtime, starts from Ubuntu and installs libsodium twenty-three. That package is the library on its own, without the headers or the compiler, so the image we ship stays small. The server's image now starts from runtime, as the server links with libsodium dynamically, and without it, it wouldn't even start.

17:40 Once it's built, we run the server's tests, and dash dash gtest filter picks the password ones. Both pass, and next to each one, gtest prints how long it took. The first hashes once and checks twice, and takes around a hundred and eighty milliseconds. The second hashes twice, in around a hundred and twenty. So each hash, and each check, costs around sixty milliseconds, which is longer than one of our ticks. Waiting that long is fine for someone at a login screen, and it's the reason the hashing stays away from the game server.

MySQL from C++

18:12 To talk to MySQL from C++, we're going to use MySQL's own client library, through its C API. Before the code, let's look at how we hand it values. The tempting way is to paste the name into the SQL, as text. Then somebody picks a name with a quote in it, followed by some SQL of their own. Now our statement runs what they wrote, which could find an account without knowing its name, or drop a table. That's called SQL injection, and it has been behind a lot of breaches. Prepared statements avoid it, as the SQL goes to MySQL first, with question marks where the values go, and MySQL parses it. Then the values go on their own, and MySQL treats them as data, whatever's in them. So a name with quotes in it is an odd name, and nothing more.

19:01 MySQL's documentation has a chapter on prepared statements in the C API. A statement handle, MySQL stmt, holds a statement once it's prepared. And the values go in MySQL bind structures, one for each value, which say where the value is in memory, its type and its length. The client library comes with MySQL, and it needs OpenSSL, for the password exchange with the server. It comes inside MySQL's whole source tree, which takes a long while to build, so it's the second library we take from the system's package manager.

19:32 MySQL's client library goes through pkg-config too, like libsodium, and on Ubuntu, that works as it is. On a Mac, we want Homebrew's mysql-client package, which is the client library without a whole MySQL server. However, Homebrew keeps it out of the usual places, so that it doesn't clash with a MySQL server you might install next to it. So in the top-level CMakeLists, if we're on APPLE, we ask Homebrew where it is. The reason goes in a comment: Homebrew keeps mysql-client out of the places pkg-config looks in. Execute process runs brew dash dash prefix mysql-client, which prints the folder it lives in, and output variable keeps what it prints. Output strip trailing whitespace drops the newline at the end. Error quiet keeps it silent on a Mac without Homebrew, where the library might still be found somewhere else. Then list append adds that folder to the CMake prefix path, which pkg-config looks in too. And pkg check modules looks for mysqlclient, as required, with an imported target, like libsodium. In the Dockerfile, the toolchain gets libmysqlclient dev, for the headers, and the runtime stage gets libmysqlclient twenty-one, the library the servers load when they run. Now the class that talks to MySQL, in database dot h, which starts with mysql dot h, the header for MySQL's C API. Then cstdint, for the size of an insert id, and memory, for the unique pointer a connection comes in. Then string and string view, for the SQL and its values, then utility, for std move, as the database is going to keep its settings, and vector, for the rows. And Abseil's status, for steps that can fail without a value to hand back, and status or, for the results that can fail. Result is a struct for what a statement gives back. Rows is a vector of rows, and each row is a vector of strings, as we're going to read each value back as text. And insert id is the id that an INSERT gave its new row. The Database class is a connection to MySQL, held open for as long as the object lives. Connecting can fail, say when MySQL isn't running, and a constructor can't hand back an error. So Database has a static create function, like our window in episode two. It takes the host, the user, the password and the database's name. It returns a status or of a unique pointer to a Database. The comment over it says that it connects to MySQL, or returns why it couldn't. So a Database that exists is one that's connected, and nobody has to check. The destructor closes the connection, and copying is deleted, as two copies would share one handle, and close it twice. Run runs a statement, and it takes the SQL as a string view, and the values as a vector of strings. The values are empty by default, for statements that need none. It returns a status or of a Result, so a statement that fails comes back as an error, with MySQL's message. The comment over it says that values go apart from the SQL, one for each question mark, so they stay values. A duplicate key comes back as an already exists error. That's the prepared statement we've just seen, and as run is the one way our code reaches the database, nobody can forget to use one. And some errors are an answer we're expecting, which the caller wants to tell apart from a failure. Say, a row that would break a unique key is how we're going to find out that a name is taken, so that error gets a code of its own. And the comment goes on: and a lost connection as unavailable, as that's an error we can recover from, by re-connecting. So the database is going to keep what it needs to re-connect. Privately, its constructor takes the four settings that create gets. And it moves each of them into a member of its own, with std move, so that the strings aren't copied a second time. Connect is a private function that connects with those settings, and create calls it the first time, while run calls it again when the connection is lost. The last member is a pointer to MYSQL, the C API's connection handle, which stays a null pointer until we connect. In the source file, after our header, we include errmsg dot h, with the error numbers of MySQL's client library, such as for a closed connection. Then mysqld error dot h, which has the numbers of the errors that the MySQL server sends us. Next come array, for fixed-size buffers, and cstddef, for size t, which counts the values we bind. Then memory, for unique pointer, and string, for the values we read back. Type traits is the standard library's header for working with types, and we need it for one of the C API's types, further down. Then utility, for std move, and vector, for the binds we hand MySQL and the rows we read. From Abseil, memory, for wrap unique, and str cat, for building messages. The C API hands us raw handles, and our job in this file is to wrap them, so that they get cleaned up whatever happens. In an unnamed namespace, k value size is how much room each value we read back gets, two hundred and fifty-six bytes. The C API wants a buffer for each column before it fetches a row, so we have to pick a size up front. Its comment gives the reason: room for the longest value we read back, a password hash. Next, a using alias, Length, as MySQL writes each value's length into a variable that we hand it. And its header declares that variable's type as an unsigned long. The style guide asks for int, or a type with its size in its name, such as uint64 t, and not long, whose size changes from one system to another. However, MySQL writes through a pointer, so our variable has to have the C API's type, whatever it is. So we ask the compiler for that type, instead of writing it out by hand. Decltype gives us the declared type of something, here the length field of MYSQL BIND, which is a pointer to a length. Remove pointer t, from type traits, takes the pointer away, and leaves the type it points to. Over it goes a comment: the C API's own type for a length, whatever it is on this system. A Value is a struct for one value of the row being fetched. It has its bytes, how many of them MySQL wrote, as a Length, and whether it's NULL, which in SQL isn't the empty string. Close statement is a deleter, a struct whose call operator calls mysql stmt close. A unique pointer calls its deleter when it lets go of what it holds, so it can look after a C handle too. Statement is a using alias for a unique pointer to a MYSQL STMT that calls it. So a statement gets closed however we leave the function, on any of its returns, and we don't have to remember it on each path. Error turns one of MySQL's errors into a status, from its number and its message. ER DUP ENTRY means a row would break a unique key. So it becomes an already exists error, which is Abseil's code for something that's there already. Three numbers mean a closed connection, and CR server gone error and CR server lost come from the client library. We get them when MySQL has closed the connection, or stops answering halfway through. And ER client interaction timeout is what MySQL sends just before it closes a connection that sat idle for too long. Those three become an unavailable error, which is Abseil's code for a service we can't reach right now, but might reach later. Any other number is an internal error, which is the code for something that broke on our side, and that the caller can't fix. Str cat puts MySQL's error number in front of its message, so the log says which error it was. Statement error is a shortcut for when a statement fails. It reads the statement's error number and message, and hands them to error. Prepare makes a statement and prepares it, and as run is going to need that twice when the connection is lost, it gets a function of its own. Mysql stmt init makes the statement, held by our Statement pointer, and if it can't, we return the connection's error, through error. Mysql stmt prepare sends the SQL to MySQL, which parses it, and tells us if it's wrong, before any values are involved. If the SQL has a typo, prepare fails, and we return the statement's error. Otherwise, we hand back the statement, and the status or holds on to it. Create comes next, with the header's signature, but without static, as that keyword goes on the declaration and nowhere else. The database's constructor is private, so make unique can't reach it. So Abseil's wrap unique takes the object we've made with new, and puts it in a unique pointer, like we did for the window. Then create calls connect, and if that fails, we return its status. The unique pointer deletes the half-made database on the way out, and otherwise, we return it, connected. Connect starts with mysql init, which makes a connection handle. It returns a null pointer when it can't allocate one, so we check for that first, and return a resource exhausted error, out of memory. Mysql options sets the character set to utf8mb4, which is MySQL's name for full UTF-8, with emoji and all. Its older utf8 has three bytes a character at most, and can't store emoji. Mysql real connect connects, with the host, the user, the password and the database we kept, each one as a C string. It waits until it's connected, which is fine, as that happens when a server starts, or after it lost its connection. The zero is the default port, three three oh six, the null pointer is a socket file we don't use, and the last zero means no flags. If it fails, we put MySQL's message in an unavailable error, so that a re-connect that fails comes back from run like the lost connection before it. Then we close the new handle, and return the error. Otherwise, as the comment there says, the old connection goes once the new one works. Mysql close does nothing with the null pointer we start with, and closes a lost connection when we re-connect. So if re-connecting fails, the database still has a handle, and the next run can try again. Then the new handle becomes ours, and the destructor closes it, with mysql close. The destructor closes whatever handle we have, and for a database whose first connect failed, that's still the null pointer, which is safe. Run starts with prepare, on our connection, and an unavailable error means a closed connection. Then we go back up and add a comment: MySQL drops a connection that sits idle for eight hours, and a restart drops them all. Nothing has run yet, so we connect again and retry. Our login server could sit idle overnight, and without this, the first login in the morning would fail, along with all the ones after it. Preparing sends the SQL and no values, so trying it again can't run a statement twice. If connect fails, we return its error, and otherwise, we prepare once more. If preparing still fails, or failed for another reason, we return that error. Then statement takes the prepared statement out of the status or, with std move, as a unique pointer moves and can't be copied. Then we check that the statement has as many question marks as we have values. A mismatch is a bug in our code, so it's an invalid argument error. It's better to hear about it here than as a strange error from MySQL. Next, params, a vector of MYSQL BIND, one for each value. Above it, we write that each value goes in as text, and MySQL converts it to its column's type. That keeps run simple, as we don't need a type for each value, and MySQL converts it like it would a number written in the SQL. Each bind gets the type MYSQL TYPE STRING, a pointer to the value's bytes, and its length. The const cast is there because the C API takes a plain pointer, even though it reads the bytes and doesn't change them. Mysql stmt bind param hands the values over, if there are any, as a statement without question marks has nothing to bind. Then mysql stmt execute runs the statement, and if either fails, we return the statement's error. A duplicate key fails at execute, and that's where it turns into already exists. In the result, insert id comes from mysql stmt insert id. Mysql stmt field count says how many columns the statement selects, and an INSERT selects none, so we return right away. Otherwise, row has a Value for each column, and bound has a MYSQL BIND for each. A loop over the columns fills in each bind, again as a string. This time the binds point the other way, at our Value's bytes, with room for k value size, and at where MySQL writes the length, and whether it's NULL. Mysql stmt bind result hands those over, and if that fails, we return its error. Then mysql stmt fetch fills them in, one row at a time, and returns zero while there are rows. For each row, we copy each value into a string, with its length, or an empty one for NULL. A fetch that returns MySQL data truncated means a value didn't fit in our two hundred and fifty-six bytes. That's an out of range error, and it says so, instead of handing back half a value. Anything else but MySQL no data is the statement's error. Otherwise, we've read all the rows, and we return them. Let's look again at where the values go in, since the SQL and the values reach MySQL separately. So whatever a player types as a name stays a value, and can't turn into SQL.

33:26 With a way to talk to MySQL, we can keep accounts in it. In the server's CMakeLists, the core library gets database dot c c and accounts dot c c, and links with mysqlclient's imported target, while the tests get accounts test. Next comes the header for the accounts, accounts dot h, and after its guard, it includes cstdint, for the ids. Then string and string view, Abseil's status and status or, and our database header. First come two rules for new accounts, and check username says what's wrong with a name. It returns an Abseil status, which is OK for a good name. A bool could say no, but not why, and the player needs to see why. Over it, we write that an invalid argument error says what's wrong, in words for the player. So the caller gets the reason as a value it can pass on, for something as ordinary as a name that's too short. Check password does that for a password, with rules of its own. Account is a struct with what the login server needs from an account: its id, and its password hash. The id is a uint32 t, an unsigned number of thirty-two bits, like the INT UNSIGNED column it comes from. The Accounts class is the accounts table, and the one place that writes SQL for it. Its constructor takes the database, by reference, as it uses a connection that someone else owns. It's explicit, so that a Database can't quietly turn into Accounts where a function expects one. Create creates an account, and returns its id in a status or. Its comment says it returns an already exists error if the name is taken. Find finds an account by its name, for a login. Over it goes a comment too: returns a not found error if there's no account by that name. Not found is Abseil's code for something that isn't there, so the caller can tell a missing account from a database that's down. The member is that reference, so the database has to outlive the accounts, and main is going to see to that. In the source file, after our header, we include algorithm, for all of, and cctype, for is alnum. Then string and vector, and Abseil's numbers, for simple a to i. That one turns text into a number, like in our mains. Check username comes first, and as a name is going to show over a player's cube and in our logs, we keep it short and plain. It refuses a name shorter than three or longer than sixteen characters, as sixteen is what our column holds. The invalid argument error says so in a sentence, ready for the login screen. Then allowed is a lambda that's true for a letter or a digit, from is alnum, or an underscore. The cast to unsigned char is there because is alnum is undefined for negative chars, and a byte from a UTF-8 name can be one. All of checks that each character is allowed, so there are no spaces, no quotes, and no letters that merely look like ours. If one isn't, the error says what names are made of, and otherwise, it returns an OK status. Check password is next, and it works like check username. A password shorter than eight characters is refused, as short passwords are the first ones an attacker tries, and a slow hash doesn't save them. Then a password longer than a hundred and twenty-eight characters is refused too. That upper limit is far more than any password a person types, and keeps somebody from sending us a megabyte to hash. Create comes next, and it takes the username and the hash, as the plain password doesn't get this far. It runs an INSERT INTO accounts, where the username and the hash are two question marks, and their values follow. We go back up and write down why: the unique key on the name decides, with an already exists error. Checking first and inserting after would let two people take one name in between. Two players can sign up at once, and both would see a free name. So the unique key has to be the judge, inside the database, in one step. If run returns an error, create hands its status up to the caller. For a taken name, that's the already exists error that run made from MySQL's duplicate entry, and any other error goes up as well. Otherwise, it returns the insert id, as the account's id. The arrow reaches into the result inside the status or, like a pointer would. Find is for logins, so it takes a name, and selects the id and the password hash for it, with the name as the statement's value. An error from run goes up, like in create, and no rows means no account, so that's a not found error. Otherwise, we take the first row, as a name matches one account at most. The Account is built with a designated initialiser, like in episode two, which sets the hash, and leaves the id to be read from text next. Simple a to i turns the id from text into a number, and returns false if the text isn't one. That would mean a broken table, so it's an internal error. Next comes a small header of helpers, for the tests that need a MySQL to run against. It's called test services dot h, as it's where the tests find the services they run against. It includes cstdlib, for get env, then memory, random and string, and utility, for std move, then Abseil's status or, gtest, and our database header. Its functions go in a namespace of their own, nanommo testing, so they can't clash with the game's. Both are inline, as they're defined in a header, and inline lets each test file that includes it have a copy without the linker complaining. Test database returns a unique pointer to a Database, for a test to run against. Its comment gives the host: MySQL at nanommo MySQL host, or a null pointer if that isn't set. If it's set and we can't connect, the test fails. Inside, it reads nanommo MySQL host from the environment, with get env. When it isn't set, it returns a null pointer, so that a test can skip, instead of reaching for a database by accident, or failing on a machine without one. Otherwise, database create connects to that host, as the nanommo user, with its password and database. If it can't connect, GoogleTest's add failure records a failure with the status, and the test carries on. We fail on purpose here, as once we've said where MySQL is, a MySQL we can't reach is a problem to see, and not a test to skip. It returns a null pointer then, so the test goes no further. Otherwise, star std move takes the database out of the status or, like the window in episode two. The other helper, test name, makes up a name for a test's account. It's test underscore and a random number, from random device, which asks the operating system for it. That way, tests running at once, or a real player called alice, don't get in each other's way. Now the tests, in accounts test, where we include our header, then chrono, for durations such as two seconds. Then cstdint, memory and string, and thread, whose sleep for makes a test wait. From Abseil, its status and status or, then gtest and the helpers. The first two tests need no database, as they check the rules. The test of good names expects alice underscore 7 to be OK, and then come the names that fail. A name of two characters is under the minimum, and seventeen underscore letters, at seventeen characters, is over the maximum. Each is one past its limit, which is where an off-by-one in the check would show up. A name with a space in it fails too, and so does one with a quote and two dashes. That's how SQL injection often starts, even though our prepared statements would cope with it. Refuses short passwords refuses hunter two, which is seven characters, and accepts correct horse. Then a fixture, AccountsTest, for the tests that need MySQL. A fixture is a class that GoogleTest builds afresh for each of its tests, with code to run before and after the test. Set up connects, and if there's no database, GTEST SKIP skips the test, and says how to run it. Tear down deletes the test's account afterwards, so the database doesn't fill up with test accounts. It expects the delete to be OK, as run hands back a status or, and a clean-up that fails should show up in the results. Each test gets a name of its own, from test name. Finds the account it created creates an account with the test's name, and a made-up hash, as what's in the hash doesn't matter here. It asserts that create gave back an id, then finds the account by its name, and asserts that worked. Then it checks that the id and the hash are the ones it created, which is the round trip the login server depends on. Refuses a name that's taken creates the account, and asserts that it worked. Then it tries again with that name, and another hash. Is already exists checks a status's code, so the test can tell that create was refused for the right reason, and not because of some other error. Then it tries with TEST in capitals where the name says test, which gets already exists too. MySQL counts that as the name it already has, so a name can't be taken twice by changing its case. The last test is for the lost connection we handled in run, when MySQL drops a connection that sat idle. It sets wait timeout to one second, with SET SESSION, so for this connection alone. That's the setting behind MySQL's eight hours, so the test waits two seconds instead of a night, with sleep for. By then, MySQL has closed the connection, and creating an account has to work anyway, as run re-connects. To run these against MySQL anywhere, compose dot yaml gets a tests service. It's built from the build stage, which has the compiled tests, and its command runs the server's tests. Its environment sets nanommo MySQL host to mysql, the service's name on Compose's network. Depends on waits until MySQL is healthy, and the test profile keeps it out of a plain docker compose up. And CI gets a step that runs docker compose run dash dash rm tests, so the tests against MySQL run on each push too. Compose run starts MySQL first, through depends on, so the step is a single command. The test stage in the Dockerfile still runs all the tests, and there, without a database, these ones skip. Back in accounts dot c c, the INSERT is where the database decides whether a name is free, instead of us looking the name up first.

43:57 Without a database, the two tests of the rules pass, and the three that need MySQL are skipped. Dash dash gtest brief keeps the output to the tests that didn't pass. Then I start MySQL, and run them again, with nanommo MySQL host set to one two seven dot oh dot oh dot one, which goes over TCP, like the healthcheck. Now all five tests run, and pass, against MySQL in its container, and the one that waits for MySQL to drop its connection takes two seconds.

Docs

44:27 Last, the README, where building now needs two libraries from the system's package manager, MySQL's client library and libsodium, and it lists the commands for Homebrew and for Ubuntu. A new section on accounts says how to start MySQL with Docker Compose, the rules for a name and a password, and that the servers don't log anyone in yet. It shows how to run the tests against MySQL, with nanommo MySQL host set, or all in Docker with compose run. And it warns that docker compose down dash v deletes the volume, and all the accounts with it.

Wrap-up

45:01 So, today we started MySQL with Docker Compose, with its schema in the repo, and we went with SQL, as our data is full of relations. Passwords are hashed with Argon2id, slow on purpose, with a salt each, and the values go to MySQL apart from the SQL, as prepared statements, so a name can't turn into SQL. Our accounts class creates an account, finds one by its name, and refuses a name that's taken, with tests that run against MySQL, in a container, on my machine and in CI.

Playing it

46:30 Let me know in the comments what you'd like to see next. And if you want to follow along with the series, subscribe. I hope you find this useful, and thanks for watching.