Looking for an example of PHP Using mySQL Update command during game runtime.

Looking for an example of PHP Using mySQL Update command during game runtime. I been trying all night to get it working. I have Log-In and registration working fine. Thanks to some examples on this forum.

But their must be 9 different ways to write a php page that updates a mySQL data bases and I can't get any of them working. . I'm trying to use the players name and password to determine that the correct data is updated.

Thanxs

 

If you save the user's ID (if you have a UserID coloumn) you could use that instead of name and password to keep track of the user and then update where UserID = $user , ect.

 

The Main Reason I'm not using the user ID is there are going to be a multitude of tables by the time we are done. You have no guarantee that if 50 people register at the same time that all of their Table user Id's will match.
Also "UPDATE" and "Where" seem to be fairly common mySQL commands.

But I find it strange out of 17 Unity tutorials I found, Not a single one covers using the UPDATE command in your PHP file for your Unity game.

Last but not least I just switched to a Windows Server using IIS7 and there does seem to be multiple issues on Google about using form variables in mySQL commands in PHP.

 

I finally muddled through it and figured out the proper Syntax. PHP is not very forgiving to horrible typist like myself.

Here is an example PHP function that updates an mySQL database:

Code (csharp):

1. function PutAbilitiez()
2. {
3.  
4.  
5.     $gameName  = $_POST[ 'gameName' ];
6.  
7.     $playerPassword  = $_POST[ 'playerPassword' ];
8.  
9.     $sex  = $_POST[ 'sex' ];
10.  
11.     $bodyType  = $_POST[ 'bodyType' ];
12.  
13.  
14.     $skinType  = $_POST[ 'skinType' ];
15.  
16.  
17.     $firstName  = $_POST[ 'firstName' ];
18.  
19.     $lastName  = $_POST[ 'lastName' ];
20.  
21.     $age  = $_POST[ 'age' ];
22.  
23.     $playerEmail  = $_POST[ 'playerEmail' ];
24.  
25.  
26.     ////////////////////////////////////////////////////////////////////////////
27.     //
28.     //        ABILITY SCORES
29.     //
30.     ////////////////////////////////////////////////////////////////////////////
31.  
32.     $strength  = $_POST[ 'strength' ];
33.     $strengthMax  = $_POST[ 'strengthMax' ];
34.  
35.     $agility  = $_POST[ 'agility' ];
36.     $agilityMax  = $_POST[ 'agilityMax' ];
37.  
38.     $dexterity  = $_POST[ 'dexterity' ];
39.     $dexterityMax  = $_POST[ 'dexterityMax' ];
40.  
41.     $stamina  = $_POST[ 'stamina' ];
42.     $staminaMax  = $_POST[ 'staminaMax' ];
43.  
44.     $health  = $_POST[ 'health' ];
45.     $healthMax  = $_POST[ 'healthMax' ];
46.  
47.     $intelligence  = $_POST[ 'intelligence' ];
48.     $intelligenceMax  = $_POST[ 'intelligenceMax' ];
49.  
50.     $comprehension  = $_POST[ 'comprehension' ];
51.     $comprehensionMax  = $_POST[ 'comprehensionMax' ];
52.  
53.     $psyche  = $_POST[ 'psyche' ];
54.     $psycheMax  = $_POST[ 'psycheMax' ];
55.  
56.     $mana  = $_POST[ 'mana' ];
57.     $manaMax  = $_POST[ 'manaMax' ];
58.  
59.     $lastPosX = $_POST[ 'lastPosX' ];
60.     $lastPosY = $_POST[ 'lastPosY' ];
61.     $lastPosZ = $_POST[ 'lastPosZ' ];
62.  
63.  
64.     ////////////////////////////////////////////////
65.  
66.     $anchorHead = $_POST[ 'anchorHead' ];
67.     $anchorLeftHand = $_POST[ 'anchorLeftHand' ];
68.     $anchorRightHand = $_POST[ 'anchorRightHand' ];
69.     $anchorChest = $_POST[ 'anchorChest' ];
70.     $anchorPelvis = $_POST[ 'anchorPelvis' ];
71.     $anchorRightBicep = $_POST[ 'anchorRightBicep' ];
72.     $anchorRightForeArm = $_POST[ 'anchorRightForeArm' ];
73.     $anchorRightWrist = $_POST[ 'anchorRightWrist' ];
74.     $anchorLeftBicep = $_POST[ 'anchorLeftBicep' ];
75.     $anchorLeftForeArm = $_POST[ 'anchorLeftForeArm' ];
76.     $anchorLeftWrist = $_POST[ 'anchorLeftWrist' ];
77.     $anchorRightThigh = $_POST[ 'anchorRightThigh' ];
78.     $anchorRightCalf = $_POST[ 'anchorRightCalf' ];
79.     $anchorRightAnkle = $_POST[ 'anchorRightAnkle' ];
80.     $anchorLeftThigh = $_POST[ 'anchorLeftThigh' ];
81.     $anchorLeftCalf = $_POST[ 'anchorLeftCalf' ];
82.     $anchorLeftAnkle = $_POST[ 'anchorLeftAnkle' ];
83.     $defaultHair = $_POST[ 'defaultHair' ];
84.  
85.     ////////////////////////////////////////////////
86.  
87.  
88.     $XPstrength = $_POST[ 'XPstrength' ];
89.     $XPagility = $_POST[ 'XPagility' ];
90.     $XPdexterity = $_POST[ 'XPdexterity' ];
91.     $XPstamina = $_POST[ 'XPstamina' ];
92.     $XPhealth = $_POST[ 'XPhealth' ];
93.     $XPintelligence = $_POST[ 'XPintelligence' ];
94.     $XPcomprehension = $_POST[ 'XPcomprehension' ];
95.     $XPpsyche = $_POST[ 'XPpsyche' ];
96.     $XPmana = $_POST[ 'XPmana' ];
97.  
98.  
99.     ////////////////////////////////////////////////
100.  
101.  
102.     $hitPoints = $_POST[ 'hitPoints' ];
103.     $hitPointsMax = $_POST[ 'hitPointsMax' ];
104.     $basePhyAtk = $_POST[ 'basePhyAtk' ];
105.     $basePhyDef = $_POST[ 'basePhyDef' ];
106.     $baseShotAtk = $_POST[ 'baseShotAtk' ];
107.     $baseMenAtk = $_POST[ 'baseMenAtk' ];
108.     $baseMenDef = $_POST[ 'baseMenDef' ];
109.     $speedMod = $_POST[ 'speedMod' ];
110.  
111.     ////////////////////////////////////////////////////////////////////////////
112.  
113.     // The DATABASE NAME is fw
114.  
115.     //    $SQL = "SELECT * FROM players WHERE gameName = ' " . $gameName . " ' AND  playerPassword = ' " . $playerPassword . " ';";
116.  
117.     //    mysql_query( $SQL ) or die( mysql_error() );
118.  
119.     $query = "UPDATE players SET strength = '" . $strength . "', strengthMax = '" . $strengthMax . "',
120.    agility = '" . $agility . "',  agilityMax = '" . $agilityMax . "',
121.    dexterity = '" . $dexterity . "', dexterityMax = '" . $dexterityMax . "',
122.    stamina = '" . $stamina . "', staminaMax = '" . $staminaMax . "',
123.    health = '" . $health . "', healthMax = '" . $healthMax . "',
124.    intelligence = '" . $intelligence . "', intelligenceMax = '" . $intelligenceMax . "',
125.    comprehension = '" . $comprehension . "', comprehensionMax = '" . $comprehensionMax . "',
126.    psyche = '" . $psyche . "',    psycheMax = '" . $psycheMax . "',
127.    mana = '" . $mana . "', manaMax = '" . $manaMax . "',
128.    lastPosX = '" . $lastPosX . "', lastPosY = '" . $lastPosY . "',
129.    lastPosZ = '" . $lastPosZ . "', anchorHead = '" . $anchorHead . "',
130.    anchorLeftHand = '" . $anchorLeftHand . "', anchorRightHand = '" . $anchorRightHand . "',
131.    anchorChest = '" . $anchorChest . "', anchorPelvis = '" . $anchorPelvis . "',
132.    anchorRightBicep = '" . $anchorRightBicep . "', anchorRightForeArm = '" . $anchorRightForeArm . "',
133.    anchorRightWrist = '" . $anchorRightWrist . "', anchorLeftBicep = '" . $anchorLeftBicep . "',
134.    anchorLeftForeArm = '" . $anchorLeftForeArm . "',    anchorLeftWrist = '" . $anchorLeftWrist . "',
135.    anchorRightThigh = '" . $anchorRightThigh . "', anchorRightCalf = '" . $anchorRightCalf . "',
136.    anchorRightAnkle = '" . $anchorRightAnkle . "', anchorLeftThigh = '" . $anchorLeftThigh . "',
137.    anchorLeftCalf = '" . $anchorLeftCalf . "', anchorLeftAnkle = '" . $anchorLeftAnkle . "',
138.    defaultHair = '" . $defaultHair . "', XPstrength = '" . $XPstrength . "',
139.    XPagility = '" . $XPagility . "', XPdexterity = '" . $XPdexterity . "',
140.    XPstamina = '" . $XPstamina . "', XPhealth = '" . $XPhealth . "',
141.    XPintelligence = '" . $XPintelligence . "', XPcomprehension = '" . $XPcomprehension . "',
142.    XPpsyche = '" . $XPpsyche . "', XPmana = '" . $XPmana . "',
143.    hitPoints = '" . $hitPoints . "', hitPointsMax = '" . $hitPointsMax . "',
144.    basePhyAtk = '" . $basePhyAtk . "', basePhyDef = '" . $basePhyDef . "',
145.    baseShotAtk = '" . $baseShotAtk . "', baseMenAtk = '" . $baseMenAtk . "',
146.    baseMenDef = '" . $baseMenDef . "',    speedMod = '" . $speedMod . "'
147.    WHERE gameName = '" . $gameName . "' AND  playerPassword = '" . $playerPassword . "'";
148.    
149.  
150.  
151.     mysql_query( $query ) or die( mysql_error() );
152.  
153.  
154.  
155. }

 

If you use auto increment then they will not match.

Your PHP example is full of SQL injection holes. At the 'very least' you should be casting(cleansing) the variables into their correct expected datatypes.

 

Actually I'm eager to learn everything I can do to prevent security leaks. Any chance you can show us AbsoluteBreeze?

 

Well your passing variables directly into your sql - without even checking that they are what they say they are.

If your variable is expecting to be a float then cast it as such....

Code (csharp):

1.  
2.     $lastPosX = (float) $_POST[ 'lastPosX' ];
3.     $lastPosY = (float) $_POST[ 'lastPosY' ];
4.     $lastPosZ = (float) $_POST[ 'lastPosZ' ];
5.  

If its text such as

Code (csharp):

1.  $gameName  = $_POST[ 'gameName' ];

Then you should make sure that only alphabetic characters are used and no apotrophes etc.

A function like this is minimal (Its untested - and not perfect)

Code (csharp):

1. function Wash($str) {
2.         $str = @trim(stripslashes($str));
3.         return mysql_real_escape_string($str);
4.     }
5.  

could be called like

Code (csharp):

1.  $gameName = Wash($gameName);

Also using regular expressions to make sure things are formatted correctly (email addresses etc).

Its a whole subject and well worth spending a few days researching

 

Thanxs that's an excellent start. The Log-In code I used is a modified one from this forum :

http://forum.unity3d.com/threads/24721-Tutorial-Unity-and-PHP-login-script-simple-but-useful

I think it had some security which I am planning to port to the other PHP files.

 

I think the main rule with php (or any web app) is not to trust anything and check everything is exactly what it says it is.

 

Since you know a Lot more about this then I do, What do you think of the " anti_injection_login " function in the link I provided above? I was curious how secure it actually was. To be honest I'm not even sure all that it does. PHP is very new to me.

 

Its ok, and will do the job, I suspect there are much better scripts/functions online. Its definitely a good step forward for you though.

I'd also not use MD5 for passwords - and instead choose SHA1.

 

Why SHA1 ??

 

Because md5 is insecure. If any one managed to expose your data (through sql injection) then all you're users passwords would be crackable by using the rainbow tables.

 

Thank you you have saved me a lot of time.

 

I can't seem to find Unity code for SHA1 do you have a link? Looking for Java script and C# if possible.

 

I've only used it for PHP apps (I develop finance apps in my day job), but this should do it....

http://www.kindblad.com/2009/03/24/how-to-generate-the-sha-1-hash-of-a-string-using-c/

So, you store the encrypted hash as a password in your database, then when a user typesa password into your game, the web player encrypts the newly typed password with the hash and then compares it against the stored hash in your db.

 

Thanks Again.

Seems I might be out of luck as far as a java script version. Found a few online but they all give errors in Unity Editor.

 

Well I'm currently working on a leader board management system, I wasn't intending on having it store passwords - but if I get time I'll put some effort into a SHA1 routine (js) and give you a shout.

Probably wont be for a couple of weeks though.

 

That would be great !!!! Because it would be near impossible for me to port it myself.

 

Well no promises - but I'll take a shot at it

 

Since we are talking about basic security. One thing I haven't seen mentioned on the forum or wiki is this PHP command:

Code (csharp):

1. require ($_SERVER["DOCUMENT_ROOT"] . "mygame/config/db_config.php"); // get connection variables

You make a sub-directory called "config" and put all your connection and hash variables in a PHP file called

"db_config.php" .

Then you shut off all outside permissions on your Server for that sub-directory and file. I know it's basic, But all the examples have it on the Main PHP page.

 

Yeah that makes sense - that way your not copying your connection strings etc into each php script separately.... just do it once and include it.