I need a solution to SQL, saving and loading data and security/antipiracy

Actually I could use some direction on how to setup the gateway that can connect securely to my SQL database and then send that data back to the game client. I actually just emailed my professor about this question too because I'm thinking I need to write a gateway server in php that just takes a set number of commands from the game client then in return talks to the database then sends the correct data back to the client. The concept is clear but the implementation part is a bit hairy for me. Any help you could give would be greatly appreciated! I have this all running on a home desktop just for my test purposes on the same desktop i have running my wiki for my $$anonymous$$ms development collaboration etc... But later I plan on migrating over to googles cloud sql service or perhaps something like smartfox but of course before I migrate I want to get the code running on virtual machines on my own desktop so I can work out all the kinks, again any help would be a huge help to this fledgling developer! :-)

Ok, what follows is some code that should get you started. It basically calls on a script on your server that then connects to a user database. It checks to see if the username and password the user entered into a form in Unity match up to what's in the database. If they match, it sends back the word "CORRECT!" to your game. Otherwise it sends back the word "WRONG". Obviously you will need a database set up and you'll need to enter the details for it in the top section of the php script.

The first code is for a Unity script, and the second is the php script.

I originally got some of this code from someone else, then edited it for my game, then for you I edited it further, so hopefully it all works now as intended. It works fine in my game but the changes I made here might throw up some errors as I haven't tested this particular incarnation. :)

This Answers thing only lets me put a certain number of characters into a reply so I have to split this all up into three comments.

First up, the Unity code (Javascript):

 var hash : String = "myHash"; //must match php script $phpHash variable!
 
 function Login() {
     
         var form = new WWWForm(); //here you create a new form connection
         form.AddField( "myform_hash", hash ); //adds your hash code to the field myform_hash
         form.AddField( "myform_nick", formNick );                    
         form.AddField( "myform_pass", formPassword );
         
         // this now calls on your server-side script...
         var w = WWW("http://www.YOURSIT$$anonymous$$CO$$anonymous$$/scripts/check_user.php", form); //here we create a var called 'w' and we sync with our URL and the form
         yield w; //we wait for the form to check the PHP file, so our game doesn't just hang
         
         if (w.error != null)
         {
             // there was an error getting the result...
             Debug.Log("ERROR!");
         }
         else
         {
           formText = w.text.Trim(); //here we return the data our PHP told us
           
           Debug.Log("RESULT: "+formText);
       
           //if the script says "CORRECT" then we can log this user in...
           if (formText == "CORRECT")
           {
               Debug.Log("username check : CORRECT");
               // log the user in...
               // (Your login code goes here, or call a function, or whatever.
               // In other words, the user checks out, so do whatever you need to do now with that information. :)
           }
           
           w.Dispose(); //clear our form in game
         }
         
         formPassword = ""; // clear password field if not correct...
     passwordWarning = "wrong username or password!";
 
 }

 function OnGUI() {
 
 // this will need to be changed according to your GUI needs, of course...
 
 GUI.skin = customSkin;
 
   GUI.BeginGroup (Rect (((Screen.width / 2) - 512), ((Screen.height / 2) - 384), 1024, 768));
                 
             GUILayout.BeginArea (Rect (400, 180, 300, 120));
             
                 GUILayout.BeginHorizontal();
                 
                     GUILayout.BeginVertical();
                         GUILayout.Label( "username:" );
                                     GUILayout.Space (16);
                         GUILayout.Label( "password:" );
                     GUILayout.EndVertical();
         
                     GUILayout.BeginVertical();                                        
                         formNick = GUILayout.TextField (formNick, 32, GUILayout.Width(180));
                         formPassword = PasswordField (formPassword, "*" );
                         GUILayout.Label( passwordWarning);        
                     GUILayout.EndVertical();
                     
                 GUILayout.EndHorizontal();
 
             GUILayout.EndArea();
             
             
             GUILayout.BeginArea (Rect (300, 310, 420, 110));
                         
                 GUILayout.BeginHorizontal();
                 
                     GUILayout.BeginVertical();
                     
                         if (( GUILayout.Button ("Login", GUILayout.Width(90) ) ) && (loginButtonLive)){
                           Login();
                         }
                     
                     GUILayout.EndVertical();
                 GUILayout.EndHorizontal();
             
             GUILayout.EndArea();
          
      GUI.EndGroup ();
      
 }

Secondly, the php code, named check_user.php:

 <?php
 //error_reporting(E_ALL);
 
 // CONNECTIONS =========================================================
 
     $my_host = "localhost"; // Your mySQL server host address
     $mydb = "yourSQLdatabase"; // The name of the database to use
     $my_user = "yourSQLusername"; // Your mySQL username
     $my_pass = "yourSQLpassword"; // Your mySQL password
 
   $database = mysql_connect($my_host, $my_user, $my_pass) or die('Could not connect: ' . mysql_error());
   mysql_select_db($mydb) or die('Could not select database');
   
 // =============================================================================
 
 // PROTECT AGAINST SQL INJECTION and CONVERT PASSWORD INTO $$anonymous$$D5 formats
 function anti_injection_login_senha($sql, $formUse = true)
 {
 
     $sql = preg_replace("/(from |select |insert |delete |where |drop table|show tables|,|'|#|\*|--|\\\\)/i","",$sql);
     $sql = trim($sql);
     $sql = strip_tags($sql);
 
     if(!$formUse || !get_magic_quotes_gpc())
       $sql = addslashes($sql);
       $sql = md5(trim($sql));
         return $sql;
 }
 
 // THIS ONE IS JUST FOR THE NIC$$anonymous$$NA$$anonymous$$E PROTECTION AGAINST SQL INJECTION
 function anti_injection_login($sql, $formUse = true)
 {
     $sql = preg_replace("/(from |select |insert |delete |where |drop table|show tables|,|'|#|\*|--|\\\\)/i","",$sql);
     $sql = trim($sql);
     $sql = strip_tags($sql);
     if(!$formUse || !get_magic_quotes_gpc())
       $sql = addslashes($sql);
     return $sql;
 }
 
 // =============================================================================
 
 $unityHash = anti_injection_login($_POST["myform_hash"]);
 $phpHash = "myHash"; // same code in here as in your Unity game in the hash variable
 
 $nick = anti_injection_login($_POST["myform_nick"]); // I use this function to protect against SQL injection
 $pass = anti_injection_login_senha($_POST["myform_pass"]);
 
 
 // defaults...
     $userid = "";
     $logins = "";
     $registered = "";
     $forceUpdate = "";
             
     if ((!$nick || !$pass) || ($unityHash != $phpHash))
     {
         echo $nick;
         echo "Sorry, you're not authorized to be here!";
     }
     else
     {
         // get userID
 
         $query = "SELECT user_id, registered FRO$$anonymous$$ login_users WHERE username = '$nick' AND password = '$pass'";
         $result = mysql_query($query) or die('Query failed: ' . mysql_error());
 
         while ($row = mysql_fetch_array($result))
         {
             $userid = $row['user_id'];
             $registered = $row['registered'];
         }
 
             // send info to the game...
             if ($userid != "") && ($registered != "")
             {            
                 echo "CORRECT";
             }
             else
             {
                 echo "WRONG";
             }
 
     }
 
 // Close mySQL Connection
 mysql_close();
 
 ?>

$$anonymous$$y professor responded with:

Yeah, if you have the decryption key on the client machine, then that can be exposed.

So, it sounds like you want to create an application layer to sit between the client and the database. That layer would basically be a collection of web service type stuff that the client would use to get/send data that would go back to the database.

The first line of defense against sql injection is to not use dynamic sql and only use stored procedures, it's far more challenging to inject through parameters. Second line is validation of data to be well formed, which you can do on the client machine as a first step, as well as at the web services as well.

You'll want to put security on the web services, so that the user is required to login to the game and once authenticated by the web server that security token would be sent to those web services that you feel require authenticated access. You wouldn't want the client to have any kind of database awareness, unless you have a local (client side) database that it keeps things in.

With any client-server system, you'll want to think about a caching system to reduce the amount of calls to the server as well as calls to the database. So, the client should remember things and the app server should remember things, the key thing there is you need to make them forget those things that are out of date.