I'm using MSSQL Express 2019.

There doesn't seem to be much documentation on this part from what I can tell. I'm mulling over the best way to tackle this. I can either piece out the data and just send text/IDs or I can store everything in .uassets and just read/write those files. I'm not really worried about performance since it's not like I'm expecting to have a bunch of players, whatever is going to be the most simple/direct/fastest approach will be fine for my needs.

For any SQL/networking gurus: I'm trying to avoid giving clients access to the DB for obvious reasons, but I'm running into what are probably some basic stumbling blocks. I'm considering letting the game server connect to the DB and giving clients a guest-readonly account that can pull info from the DB that the game server can write to. Can I have multiple connections via the same “guest” account to the DB? As in all clients connecting to the same account without cluttering up my DB with guest users.

Then setting up the login page so the client can ask the server to create an account and then read back those credentials when needed. Trying to mirror the DB into the game server and have a way for clients to access the servers local memory seems to be impossible and if it is possible feels like it would just introduce the same issue that giving clients full access to a DB would in the first place.

Is it safe to let clients have read-only access to my DB? It would simplify things greatly.

The server should be the only connection to the database. Clients should be asking the server to do anything DB-related, and the server should return the relevant data.

Additional tip, since I see you plan to have accounts: do not store passwords in cleartext. Hash the value of the password field, and store that instead. On login, compare the hashed value of the input field to the value in the database.

Edit: To elaborate a bit on this process, hash the field client-side, and send the hash to the server. The password itself should never leave the client machine.

Considering I'm using a plugin that uses .net code to send queries to sql and I only have the option of sending strings and images… I'm not sure if that's possible. I considered modifying the plugin to add functionality, but that's beyond my scope.

Given GDPR is an EU thing, I will just have to block EU users until I have someone that is more experienced in networking secure the backend properly.

I'm using UE4 and blueprints, talking to the server as a client doesn't seem to be possible or that I"m aware of anyways. I can issue script to run only on the server or only on the client. I can't think of any nodes that let me “Hey server, I need this info” “hang on, let me check” “here you go”. Perhaps I'm just having a brain fart, even if I copy in the data from my DB to the server I have no way of having the client pull/write to the server itself. I've slugged through enough C++ to get the ability system running, that's it. It would be helpful if a game dev chimed in.

WGS_Stillwater said:

Considering I'm using a plugin that uses .net code to send queries to sql and I only have the option of sending strings and images… I'm not sure if that's possible. I considered modifying the plugin to add functionality, but that's beyond my scope.

Not sure if what's possible?

…and I am a gamedev. There are several ways you can go about this, and the “best” one will depend on a few factors.

Here's an extremely simplistic example of a login system using an external authentication server and VaREST:

I already have a functioning login system with account creation as well. What I don't have is the ability to call a hashing function right now, it's something that can wait till later in development.

I get the concept of the client getting it's info only from the server, I fail to see how to do it right now. Custom events are the only blueprints that you can specify RPC functionality and I don't quite understand how to work that into blueprint logic.

Right now the server connects to the DB via windows authentication and then opens the world map, client connects to a limited guest account and then character selection/creation and then joins the world map from there. How could I change this so the client is only asking the server to interact with the DB rather than having the client talk to the DB directly? Can you provide some visual example please.

It'll take a little time to put a mockup together, but a few things I can say right now are that you really shouldn't be doing this in a widget, those queries look VERY injectable, and also that you're running queries you don't really need to run. How are you establishing initial communication with the server?

Looks like you're using the MSSQL integration from the marketplace, which does not mention prepared statements anywhere in its store page or documentation. I don't own it, so I can't speak to how anything actually gets done behind the scenes, but I'd be concerned about that. I do own NetDB, which works with a PostgreSQL database, and does support prepared statements. Might want to look into that before you get too far in the process. It shouldn't be too painful to migrate, as they're generally very similar.

Your widget will not exist on the server. I'd recommend handling database operations via the controller and (server) game instance, and storing anything you need to keep on-hand in the (client) game instance. This will allow you to respect authority.

Also consider changing your query to something like `SELECT AccountPassword FROM Accounts WHERE AccountName = accountName;` as this will only return one row if you're enforcing account name uniqueness correctly. This will be gentler on your database, and you won't have to do so much looping to handle the response.

I started reworking the script this morning. I've now separated the client from the DB and only the server has a connection. I've got the client logging in through an RPC call. Now I just need to setup a hashing solution.

Thanks for the tip on the “where” specification… that makes a lot more sense. RPC calls can only be made from a player controller or player state, but I can pass whatever I need to onto the game instance for easy access later.

edit: although it appears the game instance isn't accepting variables for some reason (no debug data).

edit2: while no debug data appears, I am able to read/write to the GI properly… shrug

WGS_Stillwater said:

edit: although it appears the game instance isn't accepting variables for some reason (no debug data).

edit2: while no debug data appears, I am able to read/write to the GI properly… shrug

One of the oddities of startup, it would seem. A short delay before your print should get what you're looking for.

@Scotty Khan print / accessing it works fine from the get go, it's just the editors “watch value” debug tool seems to take issue with the game instance when looking at anything stored in there

Ladies and gentlemen… we have hashing!