Direct MySQL Database Connection

Development Programming & Scripting C++
1

Hey guys,

I am trying to get familiar with C++ inside Unreal, but I only have limited programming experience in general. I am trying to achieve the same thing that I accomplished in a simple C++ application using Visual Studio.

In this case, I am trying to connect to a MySQL database from my Unreal project. I am aware that connecting directly to a database from a client application is a terrible idea in general, and a layer that communicates between the client and the database should be used, but I only want to establish a direct connection first to experiment a bit.

In a Visual Studio project, I had to download the C++ Connector (https://dev.mysql.com/downloads/connector/cpp/1.1.html) then choose Tools->Connect To Database and then set a few things. After that, I could use code like this:


String^ constring = L"datasource=127.0.0.1;port=1234;username=AdminUser;password=Password";
			 MySqlConnection^ conDataBase = gcnew MySqlConnection(constring);
			 MySqlCommand^ cmdDataBase = gcnew MySqlCommand("TEST COMMAND;", conDataBase);
			 MySqlDataReader^ myReader;

Now I imagine this is not the case with Unreal, and I have to do something different. I spent several hours trying to find a solution but I could only find anything regarding the standard method (Client<->Server<->Database). Any ideas about how should I get started with this?

Any help would be really appreciated.
Thanks.

2

That library looks like a C++ .NET wrapper, since it has the managed pointers syntax.

You will need to use the standard C MySQL library, or a pure C++ wrapper for it. Here is a quick tutorial on the C lib (it is rather simple): http://zetcode.com/db/mysqlc/

You can check if Role is the Authority (or do it in your Game Mode, which only runs on the server) and do the MySQL connection there, preferably reading from a config file instead of hardcoding the credentials (CWE - CWE-798: Use of Hard-coded Credentials (4.10)). You can do a API layer in Unreal using Server RPC’s which would let you validate easily on server. I think using any other the API layer route from clients (through something like HTTP/JSON) would be a bad idea, since it would be hard to perform validation.

  1. If you REALLY want to directly connect to the MySQL server on clients, you’ll need to configure the MySQL server to allow external connections and forward port 3306 through any NAT. (Degrades game integrity and network security)
  2. If you REALLY want to use that managed C++ .NET library, the easiest way would probably be to spawn a second process with a SQL statement command line argument, or spawn a daemon and use interprocess communication. (Degrades server/game performance)
  3. Best option - Write a separate SQL data model class with C library you can call into (API’s e.g. Connect(), GetCharInfo(), RecordKill(), whatever kind of stuff you want to store/retrieve). Use it to connect to database from AYourGameMode after reading config file. Handle all notable client –> server events with UFunction(Server, Reliable, WithValidation), detect cheating/bad calls in ServerEvent_Validation, perform SQL queries through the model in ServerEvent_Implementation(). If you need to pass any SQL query results data back from server –> client do it through UFUNCTION(Client, Reliable).
3

Wow, thank you very much for your detailed answer, it is very helpful!
Since none of the solutions seem easier than any other for a beginner like me, I will try and do it the proper way you suggested (option 3). I started learning programming with C, so writing some simple stuff shouldn’t cause much trouble (especially with the helpful link you"ve provided).

However I would like to ask for some help regarding how to actually implement it. I imagine that the SQL data model class would be a class that handles the database connection, writing/querying within the game? And whenever I want to do something in my database I do it using this class (but only server can use it)? If so, this solution seems very convinient and understandable for me. The only part I am really struggling with is actually setting up the environment to be able to use MySQL commands in any form. I understand that I need to use something different than I used in my VS project, you mentioned the Standard C MySQL Library: https://dev.mysql.com/doc/refman/5.7/en/c-api.html -> is it this one?

Okay, so after I acquired the correct library how do I link it to my project? Is it a static or dynamic library (sorry I am just getting familiar with these)? Do I have to do what this tutorial says about how to link static libraries? https://wiki.unrealengine.com/Linking_Static_Libraries_Using_The_Build_System

I would really appreciate some further assistance, but I am really thankful with the help you have already provided, it gave me some perspective on how to get started. I am learning these things only for a few months and I feel like if I could accomplish this that could provide some really useful experience for the future, so I want to accomplish it, even if it seems to require more knowledge and experience than I currently have.

Thanks.

4

Right, I recommended it because it is best practice to separate concerns. It will make your code cleaner (you won’t have a bunch of SQL all over your codebase, it will all be in 1 place). If you ever decide to change from MySQL, you can also just write a new model with the same interface and swap it in.

Yea, only server should use it. I would make the model a gettable variable in your GameMode. You can use it anytime safely in GameMode. If you need to do some SQL stuff in PlayerController or wherever, check if Role == Authority and then grab the pointer from the GameMode.

If this is for something like an MMO with a dedicated server, you might consider some custom #if switches instead of a normal if. This way you won’t compile anything related to the model in the clients. The less clients know about the server the harder it is to cheat.

That is it. It’s been awhile but I think when you install MySQL in its install folder is /include/ and /libs/ which will have everything you need.

I’m not really sure if Unreal’s build system will get in your way or if that is necessary. Normally when I’ve linked against MySQL in the past I follow a procedure similar to this one: MySQL Connector/C++: Guide on building a windows client using Visual Studio (for Dummies) - Ulf Wendel

That will only configure the build for when compiling on Windows though. I think Unreal’s might be for cross platform support so I’d try it.

5

Using mysql inside your game is very unsafe!
Use an php webserver with json api and use json to transfer data …

6

Using MySQL on the server is not unsafe (providing you are aware of standard pitfalls like SQL injection).

As described above an HTTP API is an inferior approach. Performing the API functionality in Unreal will result in better performance, add no additional network attack surface, and make it easier to validate cheating.

7

Personally I prefer HTTP with JSON. Most people do it that way I think.

8

I’m interested in exploring this method and have attempted to use the MySql C++ connector.

However, because UE4 C++ projects are built as NMake projects, you are unable to just set a reference to external .libs and I was not able to figure out how to write the build rules to do so.

Also, it was not possible at the time that I was working on this, a couple of months ago, to build the C++ connector with VS2015.

Any suggestions?

9

You can also use VaRest, here is a working example with MySQL and PHP and JSON requests from the VaREST plugin (you can compile it with 4.11.2 and VB 2015 Community edition - needs Component Tools installed) [FREE] VaRest Login System - With PHP and MySQL - Community Content, Tools and Tutorials - Unreal Engine Forums

So i have no idea about security, but theoretically you should be able to use SSL or SSH or a SQL Proxy, and or with salting passwords additionally (bcrypt), API key. One approach could be to use Google Cloud Compute Engine, maybe even with App Engine, and or Cloud SQL or Cloud Datastore, or with a MEAN stack even. Or a container approach with Docker.

I currently try to set up something secure and scalable, i thought to use a standard SQL db for user data, then another (possibly NoSQL, i.e. MongoDB, for item and progress data) …

10

I am aware of this. I do not wish to use PHP.

11

Did you look into using SQLite instead of MySQL? It sounds like you want to run a local database, and that would work without the need for network connections at all (even localhost).