[Resolved] Using MySQL Database with Photon

jnmwizkid
2010-11-03 22:08:22

Hi guys, I have a question about using a MySQL database with Photon.

First of all, would it be better to:

A. Establish a connection with Photon, then have Photon execute the MySQL database commands on the Server localhost.

B. Establish a connection with Photon and the MySQL databases separately with the same external IP but different ports.

I assume that A. is more secure? Also, for either selection, would it be better to keep the connection open to the database constantly, or open and close it for each database command?

Could you explain the security risks associated with A, B, or even a different option that you might think is better? (and ways to increase security)

I am comfortable with MySQL, but still learning how Photon works. I am making a Windows Standalone with Unity3D.

I'd understand how to code B, but how would I go about making the database command execute on localhost for the server computer (if A. is the better choice)?

Not sure if this is relevant, but these questions arose during the process of creating a Login system (It is not a MMO, Login will go to the main menu, and then to a Lobby.)

Thanks

Comments

dreamora
2010-11-03 22:46:56

B is no rational option at all actually because you don't want to offer global wildcard access. B would require that you offer a php layer to talk to the DB securely but as you have photon it makes sense to go through that path

Boris
2010-11-03 22:48:38

A) of course. Giving clients direct access to your database is a huge security breach. Usually .NET will use a connection pool for db connections, when you "close" the connection .NET will just put it back to the connection pool - at least this is how it works for microsoft sql server connections so I assume it's similar with other DBs. If you know how to access the DB from the game client, then you know how to do it from photon - that should be very much the same (at least if you use c# on both).

jnmwizkid
2010-11-03 23:49:03

Alright, that's what I thought. I'm not sure exactly where to put the commands in the server for using the database locally. I assume that an event is raised?

I have a Database class that uses this code for connecting (substituted user/pass): (also, forgive my coding, it may look messy, as I prefer to usually not use indents)

public bool Connect(){

string connectionString = "Server=localhost;"+"Database=account;"+"User ID=user;"+"Password=pass;"+"Pooling=false"; 

dbConnection = new MySqlConnection(connectionString); 

dbConnection.Open(); 

	return true;

}

There's a Query function that returns object[].

In usePhoton.cs:

GameInstance.Login(username,password);

I have some code in Game.cs currently, but I'm positive it's wrong, I'm not raising events with it (except updating the shown username for other players). The remote client freezes when trying to connect to the database (works fine locally). Here it is anyway, just for some extra info:

public Database DB;

public bool Login(string tryuser, string trypass){

object[] Values;

DB = new Database();

DB.Connect();

Values = DB.Query("SELECT `username`,`password` FROM `playerdata` WHERE `username` = '"+tryuser+"' AND `password` = '"+trypass+"';");

if((string)Values[0]==tryuser && (string)Values[1]==trypass){

this.LocalPlayer.username = tryuser;

	return true;

}

else{

this.LocalPlayer = null;

this.Disconnect();

	return false;

}

}

My question is, do I put the Event info in Game.cs, or somewhere else? How would some pseudocode look? Am I looking at it totally wrong?

Also, the project I'm working on now isn't the actual game, I'm doing some work trying to learn photon first before I integrate it.

Boris
2010-11-04 00:14:16

not sure i understand what you mean... you implement an operation on the server, then access the db with the operation handler and send an operation response with the data.

By the way, I would not use user input data and concat it with db queries, someone could execute very different sql code than you would want.. for instance password="'; drop table 'playerdata" the mysql doc talks about it, too: http://dev.mysql.com/doc/refman/5.5/en/ ... parameters

dreamora
2010-11-04 04:46:56

Funnily I've seen systems allowing this ;)

already allowing ' can be enough to fuckup a whole DB actually as a friend of mine a few years ago had to find out the bombing way ^^

jnmwizkid
2010-11-04 16:59:12

Hah, wow! Thanks. I never even thought about that. Here's my refined Database code using Parameters:

//Database.cs Class
public bool Login(string user,string pass){

object[] Values = new object[2];

string Query = "SELECT `username`,`password` FROM `playerdata` WHERE `username` = ?username AND `password` = ?password;";

Connect();

MySqlCommand Command = new MySqlCommand(Query,dbConnection);

Command.Parameters.AddWithValue("?username",user);

Command.Parameters.AddWithValue("?password",pass);

MySqlDataReader reader = Command.ExecuteReader();

while(reader.Read()){

reader.GetValues(Values);

}

reader.Close(); 

reader = null; 

Command.Dispose(); 

Command = null;

Disconnect();



if((string)Values[0]==user && (string)Values[1]==pass){

	return true;

}

else{

	return false;

}

}

What happened to your friend, Dreamora? o.O

Anyway, sorry for not being specific enough, Boris. I've done a lot of experimenting with Photon, and looked at the docs a lot, and I have a basic understanding of how the events are transmitted between server and players, etc. However, I don't understand how/where to code the events, and what they do internally.

In order to implement an operation on the server, do I write a function in "Game.cs" ,or a different script? Which function would that be?

Although, I see a function called "OperationResult" in Game.cs already. Would that be what you referred to as the "Operation Response" with the data of the successful login bool?

What would be the Operation Handler?

Sorry for all the nooby questions! :)

Boris
2010-11-04 17:15:09

Events from the sever (or other clients) arrive at EventAction on the client. OperationResponse from the server arrives at OperationResult on the client. The Operation Handler is the switch-case part on the server that dispatches the operation from the client.

There is a short tutorial in the docs that might help you to understand the basic workflow better:

• BlankPhotonServerSetup.pdf
• BlankPhotonClientSetup.pdf
• PhotonCustomOperation.pdf
• PhotonCustomEvent.pdf

jnmwizkid
2010-11-04 17:24:43

I looked at those before awhile ago, but I didn't understand it. I'll look at it again now that I have more knowledge of Photon. I'll post back later with some examples of my attempts. Thanks.

Boris
2010-11-04 17:33:59

just looking at them won't really help a lot, really follow the instructions. Let me know what you don't understand, I will try to explain. Thanks!

jnmwizkid
2010-11-10 18:27:05

Alright, I've figured it out. Thanks so much for telling me about those blank server setups! I've setup my own blank server based on the instructions, and I was able to make the basic Custom Operation work. I'm stuck on making the MySQL command, however. Through a lot of debugging and log writing, I've found out that the server basically crashes when executing the "command." It can successfully connect to the database, but as soon as it tries to run a command on the database, the Photon server stops processing information. It doesn't actually crash, as I can send the request again, and it will reconnect to the server, but no other information is processed in the code line past the point where the command is executed.

I setup a very simple command for testing purposes:

In MyPeer.cs:

public void SQLTest(){

string Query = "SELECT * FROM `playerdata`";

MySqlCommand Command = new MySqlCommand(Query,dbConnection);

MySqlDataReader reader = Command.ExecuteReader();

while(reader.Read()){

log.Write(reader.GetString(1)); //log is a custom class that writes to a logging file.

}

Command.Dispose();

Command = null;

reader.Dispose();

reader = null;

}

The server successfully connects to the database (with a different function), and then nothing happens here. Even if I put a log.Write() at the first line of that function, nothing will be written.

Also, to demonstrate how the server stops processing information, this code is in OnOperationRequest:

SQLConnect();

SQLTest();

SQLDisconnect();

It successfully connects, "freezes" at SQLTest, and doesn't execute SQLDisconnect or send the reply back to the client (If I take out SQLTest, everything works smoothly. It will connect, disconnect, and send a reply back to the client.)

I know it is not a problem with the database, because the code works fine if I run it outside of the Server code.

I think it may be some kind of security with the database? Maybe it is not allowing access of commands from a 'server'? I'd think that'd make it so it couldn't connect, which it can.. Either that, or maybe there's a restriction on what Photon can send out from the server externally, and I need to make some changes?

Thanks.

Boris
2010-11-10 18:41:28

Photon does not restrict sql connections. I would think it has something to do with the connection, maybe it times out? Or there is exception that isn't caught? Try adding a try/catch block. You can also add a log entry after each line so you know where exactly it stops processing. Or attach the VS debugger to the photon process and step through - just make sure the VS version you are using matches the hosted CLR runtime (VS2008 can debug .net 2.0 only). For example, to run with .net 2.0 modify the photonSocketServer.xml:

<Runtime
   Assembly="PhotonHostRuntime, Version=2.0.0.0, Culture=neutral"
   Type="PhotonHostRuntime.PhotonDomainManager"
   CLRVersion="v2.0">
</Runtime>

jnmwizkid
2010-11-10 23:56:37

The try catch block worked to give the exception, thanks. Here it is:

System.IO.FileLoadException: Could not load file or assembly 'System, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e' or one of its dependencies. Strong name validation failed. (Exception from HRESULT: 0x8013141A)
File name: 'System, Version=2.0.5.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e' ---> System.Security.SecurityException: Strong name validation failed. (Exception from HRESULT: 0x8013141A)

I assume that it is saying that it is trying to access a different version of System.dll. I have System.dll version 3.

I updated my MySql.Data.dll to the most recent version, and the problem still occurs.

Boris
2010-11-11 02:06:51

Maybe MySql requires .net 2.0. Try it again running photon with CLRVersion="v2.0" (see the config snippet above).

Boris
2010-11-11 02:08:53

@jnmwizkid wrote:

I have System.dll version 3.

That sounds odd.. you have an additional system.dll? Shouldn't you use the microsoft system.dll that comes with the .net framework?

jnmwizkid
2010-11-11 03:20:21

Haha... Nice. Ah, I was using the System.dll and the System.Data.dll from the Unity Mono library. The "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727" files work soooo much better.

Thanks for your patience! I'll post back later just to confirm that I'm doing this right. Thanks :)

jnmwizkid
2010-11-11 17:35:03

Okay. Successfully figured out how to use MySQL with Photon. Thanks again, Boris, for pointing out those Blank setups--they helped a lot.

Here's what I have now, just for reference:

//Gameloop.cs
Game.Login(Username,Password);



//MyPhotonGame.cs

public bool LoginSuccess;

public bool LoginResponse; //These two variables used just for determining if Login worked.



public void Login(string Username, string Password){

LoginSuccess = false;

LoginResponse = false;

Hashtable Parameters = new Hashtable();

Parameters[(byte)100]=Username;

Parameters[(byte)101]=Password;

this.Peer.OpCustom(1,Parameters,true);

}



//MyPeer.cs

public void OnOperationRequest(OperationRequest request){

Dictionary<short, object> Dict = new Dictionary<short, object>();

OperationResponse response;

switch (request.OperationCode){

case 1:

string Username = (string)request.Params[(byte)100];

string Password = (string)request.Params[(byte)101];

Dict[100]=DB.Login(Username,Password);

response = new OperationResponse(request,0,"OK",Dict);

this.photonPeer.SendOperationResponse(response);

break;

}

}



//MyPhotonGame.cs (again)

public void OperationResult(byte opCode, int returnCode,Hashtable returnValues, short invocID){

switch (opCode){

case 1:

LoginSuccess = (bool)returnValues[(byte)100];

LoginResponse = true;

break;

}

}

Hopefully this can help others learn, as well. Thanks again.

DavilSin
2011-11-09 05:04:16

Hello! I can not find -BlankPhotonServerSetup.pdf

• BlankPhotonClientSetup.pdf
• PhotonCustomOperation.pdf
• PhotonCustomEvent.pdf Where can I download these documents?

toberg
2019-04-02 10:26:41

Greetings,
Can I use different db management systems for MySQL with Photon? I know that it can be integrated easily with MongoDB. I just need more advanced usage of my db.

chvetsov
2019-04-02 12:18:04

hi, @toberg
Yes, you may use any. there are no limitations

best,
ilya

jamesLi
2022-08-08 08:05:10

I would not suggest connect to database from Photon directly, as you expose the database connections and all the SQL code. your database can be easily cracked if your game code is breached, which is also pretty easy.

The most common solution is using web api as interaction to database, using Photon webRPC call to interact with webapi, and then to the database.

Also, Do not write your SQL code in your game, at least using stored procedures. This is a bad example😂

CLIENT - Photon - WEB SERVER API - DATABASE - this is the right way to do it.