Trying to visualize how the backend system is going to be laid out for a small persistent world multiplayer game. Basically the server will connect to the SQL database and the clients will connect to the server, the server will read/write from/to the DB based on client input. Does this keep the DB secure from cheating/hacking since everything is server authoritative?

Not going for some professional setup to emulate commercial setups, this is strictly for development/learning purposes at this time.

“Server authoritative” has nothing to do with keeping the DB secure - it just means that players can't cheat by lying to each other about their own capabilities and state. Instead, the server manages all the gameplay logic and tells the players the result of their actions.

For traditional online games, the server is a separate process that runs the game logic and which only communicates with a database when it absolutely needs to, because the database is slow. Examples of these essential accesses might be during login (to get a player's details), logout (to commit the player's details), when important in-game events occur (e.g. gaining experience, trading items), and so on.

The DB is secure by virtue of being either located in such a way that it can only be contacted from inside the same network, or by having strong authentication so that only your authorised server can connect, read from, and write to it.

Web games have often had to run ‘stateless’, e.g. by processing hits to a PHP page or similar, so they would have to write everything to the DB each time in order to maintain any sort of persistence, but they're not suitable for real-time play. Your other thread mentioned Unreal Engine so I'm assuming this isn't relevant to you.

It's common these days to have a half-way house where people use a more high-performance database (maybe a key-value store or a document DB) and write to it more often, but that requires a fair degree of expertise.

There are also companies that provide ‘Backend As A Service’ where they provide convenient APIs for persistent state and some even let you code up much of your server logic in Javascript or similar, so they handle the server process for you.

The part that is confusing me a little is how I'm going to setup the login on the client to tell the server to check the db for their info. I know how to communicate with my db, but how would the logic go as far as asking the server to query the db without giving the client the ability to query the db directly? Just use a switch on authority? A visual example would be super helpful if anyone has one.

I'll be using windows authentication only to keep the db secure since I have no need for remote admin.

It's simplest to think in separate computer systems connected through a network.

In reality, “computer system” is mostly “a running program”, which thus talks to other programs through a network connection. Modern computer systems have no trouble running several programs at the same time, and have a network inside as well, so in practice, the above can be done on a single computer as well.

In a multi-player setup, there is one server-program that knows everything, and zero or more client programs with a keyboard and display for a user to play the game. With typical home computer systems, you thus have at most one client program due to limitations on the number of available keyboards, mice, and screens, although in a GUI environment, you could open a window for each client program obviously.

A client program doesn't know a lot, it mostly displays the game, reads keyboard etc input, and passes information between the user and the server (program). It's simplest if you see the client as a stand-alone game program, except there is no game information at the local disk, all game information must be brought in from a remote location through the network.

So how does the client work with a login? It depends on how you organize it. In its simplest form, the server needs credentials with every request that you do. The client starts with a display to log in. When the user enters the credentials, the client stores that, and sends it along to the server with every request for information, or with every action performed by the user (ie clicked at something to start a build or whatever). Eg “user X has started action Y”.

The server receives the request, verifies it, probably stores that Y is done, and returns whether or not that is approved. The client then shows the response to the user at the screen.

The first thing a client would do is query the information needed to display the game to the user.

Obviously, sending along credentials with each request eats bandwidth, and causes a lot of DB access at the server, so you may want to check once at the server, and return some ID or so that the client can use as authentication. The downside of that is that if that ID gets hi-jacked (man in-the-middle-attack), someone else could cause a lot of havoc. Likely there are other schemes that you can use, but I am not very knowledgeable in that area.

I was having a brainfart and remembered I can use the game instance to keep anything in memory I need clients to access within the game itself and the server will be the only thing that needs to query the SQL db to update the one mirrored in memory periodically.

I'll be hosting everything from one machine for quite some time, while it would be cool I don't have expectations I'll need to support a bunch of players so I'm not super worried about efficiency though I'm sure there are some good ways to keep redundancy down.

Any recommendations on organizing the data structure? One account→One row? Split it into multiple tables?

Data bases typically work best when you apply normalisation to avoid duplication of entries, apparently ( https://en.wikipedia.org/wiki/Database_normalization ), but I only know it in theory, never made a data base bigger than for a toy problem.

Don't have too much time right now to dig into the link, but it sounds like the approach is to use modularization and avoid any repetition where possible. I suppose that breaking up the data into multiple tables would also improve integrity… if one part fails only that part fails instead of an entire account being affected.

This is a very broad topic, but in general, web application security best practices apply equally to games. Games introduce some unique circumstances that you'll have to develop more specialized solutions for, though.

I haven't found many gamedev-focused application security resources yet, so for now I'm just going to recommend familiarizing yourself with the OWASP Top Ten, if you haven't already.

You'll want to make sure that you're only executing queries via prepared statement as well, and only from the server. If a player is able to inject data or otherwise hijack a command to the database, authority effectively does not matter. Notice that this is #1 on the Top Ten.

Quick hits:

• Validate any data you receive from the client carefully; regex is good for this.
• Do not attempt to clean up bad/invalid data, just log and reject it.
• Detailed logging helps you understand how a bad actor attacks your game.
• Keep player-facing error messages as vague as possible, while still serving their purpose.

In terms of securing the box itself:

• Configure your database and game server application to communicate with each other via internal interfaces (192.x.x.x/10.x.x.x) only, and block traffic to any non-essential ports on their external interfaces. Note that MSSQL has been known to default to the external interface during initial setup.
• Apply updates and patches in a timely fashion. This applies to both the operating system and any middleware you may have in use, such as PHP.
• Harden any remote management services you use (SSH, RDP, etc.), paying special care to use strong passwords and multi-factor authentication wherever possible. Use key pairs instead of passwords for services supporting them (SSH).
• Setup a VPN for remote administration, rather than exposing sensitive services to the internet.

These are by no means exhaustive lists, but they're a good start.

Finally got ability system up and running on my project, now I'm back to setting up the database. It looks like what I need to do is be able to query files (rather than accessing raw data tables) from my database to integrate with blueprints properly.

I believe I need to setup filestream to do this. Any tips or links to some good starter guides (ideally geared towards game development) would be very appreciated.

WGS_Stillwater said:

Finally got ability system up and running on my project, now I'm back to setting up the database. It looks like what I need to do is be able to query files (rather than accessing raw data tables) from my database to integrate with blueprints properly.

I believe I need to setup filestream to do this. Any tips or links to some good starter guides (ideally geared towards game development) would be very appreciated.

Which database are you planning to use? There are solutions for most flavors.