Store player informations with MySQL.

Hi all, this is my first post on killtube, I frequented at cod1.eu

I used these 2 functions to exchange infos betweeen players and MySQL:

PHP Code:

set_db_field(fieldname, value) { ret = ""; query = "SELECT * FROM `"+level.table+"` WHERE `user_name`= '"+ self.name +"' LIMIT 1"; mysql_query(level.mysql_connection, query); r = mysql_store_result(level.mysql_connection); num = mysql_num_fields(r); if(num != 0) { row = mysql_fetch_row(r); if(isdefined(row)) { mysql_query(level.mysql_connection, "UPDATE `"+ level.table +"` SET `"+fieldname+"`='"+value+"' WHERE `user_guid`='"+self getGuid()+"' LIMIT 1"); } else mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`"+fieldname+"`) VALUES ('"+value+"')"); } mysql_free_result(r); }

and this:

PHP Code:

get_db_field(fieldname) { //should be using JOIN etc eh... ret = ""; //don't need to real_escape assumingly you wouldn't take raw input as argument query = "SELECT * FROM "+level.table+" WHERE `user_name`='"+self.name+"' LIMIT 1"; mysql_query(level.mysql_connection, query); r = mysql_store_result(level.mysql_connection); num = mysql_num_fields(r); if(num != 0) { row = mysql_fetch_row(r); if(isdefined(row)) { mysql_query(level.mysql_connection, "SELECT `"+fieldname+"` FROM `"+level.table+"` WHERE `user_guid`='"+self getGuid()+"' LIMIT 1"); r2=mysql_store_result(level.mysql_connection); ret = r2; mysql_free_result(r2); } else ret = 0; } mysql_free_result(r); return ret; }

And here is my SQL table used with XAMPP: https://mega.co.nz/#!doQR2LoA!89go0L...lCgvQvkTEz_Xq0

The problem is that when the map restart, the database is not saved, thus I believe that there is problem with the table/scripts.
Could you please help me out?

Thanks

1. its easy to exploit your database system, just naming myself something with ' will escape your string-input. User input == evil, so treat it as such.
2. You should look into insert...on duplicate key update....
3. You are using the most basic mysql functions, which can cause your server to lagg. Try looking into the async mysql module i inserted into libcod.
4. Can you confirm by using phpmyadmin or heidisql that the changes are actually in the database? if not, your connection to the database might not even exist.

Edit 5. your insert into doesnt specify the guid of the player, which is the primary key of the table and should always be set.

Thank you for replying.
1. I'm new to MySQL so I'm kinda struggling with it.
2. Will try and make a report on it.
3. I'm testing CoDUOExtended (made by phpcod, not yet released), thus I can just use provided functions/methods.
4. I use phpmyadmin to confirm: no changes were made in the database but still, my connection exists: if not, the game crashes.

PHP Code:

mysql_stuff() { level.host = getCvar("mysql_host"); level.pass = getCvar("mysql_password"); level.database = getCvar("mysql_database"); level.user = getCvar("mysql_user"); level.table = getCvar("mysql_table"); level.mysql_connection = xmysql_connect(level.host, level.user, level.pass, level.database, 3306); if(!isdefined(level.mysql_connection) || level.mysql_connection == 0) { logprint("mysql not defined"); return; } else logprint("MySQL: Connect to "+ level.host +" successfully"); }

5. I forgot it.

I got the function get_db_field(fieldname) to work:

PHP Code:

get_db_field(fieldname) { query = "SELECT * FROM "+level.table+" WHERE `user_name`='"+ self.name +"' LIMIT 1"; mysql_query(level.mysql_connection, query); r = mysql_store_result(level.mysql_connection); ret = r; num = mysql_num_fields(r); if(num != 0) { row = mysql_fetch_row(r); if(isdefined(row)) { mysql_query(level.mysql_connection, "SELECT `"+fieldname+"` FROM `"+level.table+"` WHERE `user_name`='"+self.name+"' LIMIT 1"); r2=mysql_store_result(level.mysql_connection); ret = r2; if(mysql_num_fields(r2) > 0) { row2=mysql_fetch_row(r2); if(isdefined(row2)) { if(isdefined(row2[0])) { //printconsole("row2="+row2[0]+"\n"); ret = row2[0]; } } } mysql_free_result(r2); } } mysql_free_result(r); return ret; }

However, the set_db_field(fieldname, value) still not working: with helps from previous replies, it works OCCASIONALLY.

PHP Code:

set_db_field(fieldname, value) { if(isDefined(self.isbot)) return; query = "SELECT * FROM "+level.table+" WHERE `user_name`='"+ self.name +"' LIMIT 1"; mysql_query(level.mysql_connection, query); r = mysql_store_result(level.mysql_connection); num = mysql_num_fields(r); if(num != 0) { row = mysql_fetch_row(r); if(isdefined(row)) { mysql_query(level.mysql_connection, "UPDATE `"+ level.table +"` SET `"+fieldname+"`='"+value+"' WHERE `user_name` = '"+self.name+"' LIMIT 1"); } else mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`"+fieldname+"`) VALUES ('"+value+"')"); } else mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`"+fieldname+"`) VALUES ('"+value+"')"); mysql_free_result(r); }

Please help.

Point 1,2,3,5 still stand.

Quote:

Originally Posted by IzNoGoD

1. its easy to exploit your database system, just naming myself something with ' will escape your string-input. User input == evil, so treat it as such.
2. You should look into insert...on duplicate key update....
3. You are using the most basic mysql functions, which can cause your server to lagg. Try looking into the async mysql module i inserted into libcod.
4. Can you confirm by using phpmyadmin or heidisql that the changes are actually in the database? if not, your connection to the database might not even exist.

5. your insert into doesnt specify the guid of the player, which is the primary key of the table and should always be set.

1. I dont really understand this point. Please explain.
2. Use "INSERT...ON DUPLICATE UPDATE..." doesn't change anything.
3. This is CoDUOExtended, so it doesn't have it.
5. The where user_guid = self getGuid() doesn't work with insert

1. If i'd name myself "Iz'; SELECT password FROM mysql.user; --" i could potentially get the (encrypted) password. This is called a mysql injection and you might have heard about a lot of sites falling prey to one of those
2. You ofcourse need to specify the key that can be duplicate, for example in this current database structure, the guid() is the primary index, so that can be duplicate (also UNIQUE indexes can be duplicates), so, do something liek this: "INSERT INTO table (guid, fieldname) VALUES (" + getguid() + ", value_for_fieldname) ON DUPLICATE KEY UPDATE fieldname=value_for_fieldname
3. You could manually add it. Async mysql is just a function in libcod which is opensource, just as codextended is open source (right?)
5. see 2. in this answer, but you should have specified it using VALUES (guid, fieldvalue)

1 Attachment(s)

Quote:

Originally Posted by IzNoGoD

1. If i'd name myself "Iz'; SELECT password FROM mysql.user; --" i could potentially get the (encrypted) password. This is called a mysql injection and you might have heard about a lot of sites falling prey to one of those
2. You ofcourse need to specify the key that can be duplicate, for example in this current database structure, the guid() is the primary index, so that can be duplicate (also UNIQUE indexes can be duplicates), so, do something liek this: "INSERT INTO table (guid, fieldname) VALUES (" + getguid() + ", value_for_fieldname) ON DUPLICATE KEY UPDATE fieldname=value_for_fieldname
3. You could manually add it. Async mysql is just a function in libcod which is opensource, just as codextended is open source (right?)
5. see 2. in this answer, but you should have specified it using VALUES (guid, fieldvalue)

1. Understood.
2. I modified the script as below. Please post the corrected script if it is still incorrect (I need a reference to learn).
3. I dont have enough knowledge to do such thing: learning need time which I dont have now.
5. Understood.

Here are the modified functions with the updated table:

PHP Code:

set_db_field(fieldname, value) { /*if(isDefined(self.isbot)) return;*/ query = "SELECT * FROM `"+level.table+"` WHERE `user_guid`= '"+self.guid+"' LIMIT 1"; mysql_query(level.mysql_connection, query); r = mysql_store_result(level.mysql_connection); num = mysql_num_fields(r); if(num != 0) { row = mysql_fetch_row(r); if(isdefined(row)) { mysql_query(level.mysql_connection, "UPDATE `"+ level.table +"` SET `"+fieldname+"`='"+value+"' WHERE `user_guid`= '"+self.guid+"' LIMIT 1"); } else{ mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`user_guid`, `"+fieldname+"`) VALUES ('"+self.guid+"', '"+value+"') ON DUPLICATE KEY UPDATE `"+fieldname+"`='"+value+"'"); } } else{ mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`user_guid`, `"+fieldname+"`) VALUES ('"+self.guid+"', '"+value+"')"); } mysql_free_result(r); }

PHP Code:

get_db_field(fieldname) { if(isDefined(self.isbot)) return 0; if(fieldname == "user_guid"){ secondary = "user_name"; secondary_value = self.name; } else{ secondary = "user_guid"; secondary_value = self.guid; } ret = ""; query = "SELECT * FROM `"+level.table+"` WHERE `user_guid`= '"+self.guid+"' LIMIT 1"; mysql_query(level.mysql_connection, query); r = mysql_store_result(level.mysql_connection); ret = r; num = mysql_num_fields(r); if(num != 0) { row = mysql_fetch_row(r); if(isdefined(row)) { mysql_query(level.mysql_connection, "SELECT `"+fieldname+"` FROM `"+level.table+"` WHERE `user_guid`= '"+self.guid+"' LIMIT 1"); r2=mysql_store_result(level.mysql_connection); ret = r2; if(mysql_num_fields(r2) > 0) { row2=mysql_fetch_row(r2); if(isdefined(row2)) { if(isdefined(row2[0])) { //printconsole("row2="+row2[0]+"\n"); ret = row2[0]; } } } mysql_free_result(r2); } } mysql_free_result(r); return ret; }

New table: https://mega.co.nz/#!M5R30Y5Q!ETkfTG...rFMxLHjuq6XbwM

The funny thing is that the uploaded informations are wrong with human players while it is true with bot players. I doubt this is due to mysql (as showed before), not the variables.
Another Attachment 877

PHP Code:

set_db_field(fieldname, value) { mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`user_guid`, `"+fieldname+"`) VALUES ('"+self.guid+"', '"+value+"') ON DUPLICATE KEY UPDATE `"+fieldname+"`='"+value+"'"); }

Keep in mind that above code does NOT do any sanity checks on input, so mysql injections are still possible. Also it might try to set a non-existing column so check your inputs. Also, it sets values in mysql to a string. If you're storing kills, you might wanna change it to integers/floats

PHP Code:

get_db_field(fieldname) { if(isDefined(self.isbot)) return undefined; mysql_query(level.mysql_connection, "SELECT fieldname FROM `"+level.table+"` WHERE `user_guid`= '"+self.guid+"' LIMIT 1"); r = mysql_store_result(level.mysql_connection); if(isdefined(r)) { row = mysql_fetch_row(result); if(isdefined(row) && isdefined(row[0])) { mysql_free_result(r); return row[0]; } mysql_free_result(r); } return undefined; }

Above code will not check for columnname correctness either, so check your inputs. Also it returns undefined if the player is either a bot or the row doesnt exist at all

The problem is solved! Thanks IzNoGod! :cool: :o
Indeed all informations are uploaded/downloaded as string, I post the final version of the functions (that work):

PHP Code:

get_db_field(fieldname, vartype) { if(isDefined(self.isbot)) return undefined; if(fieldname == "user_guid") mysql_query(level.mysql_connection, "SELECT `"+fieldname+"` FROM `"+level.table+"` WHERE `user_name`= '"+self.name+"' LIMIT 1"); else mysql_query(level.mysql_connection, "SELECT `"+fieldname+"` FROM `"+level.table+"` WHERE `user_guid`= '"+self.guid+"' LIMIT 1"); r = mysql_store_result(level.mysql_connection); if(isdefined(r)) { row = mysql_fetch_row(r); if(isdefined(row) && isdefined(row[0])) { mysql_free_result(r); if(vartype == "int") row[0] = (int)(row[0]); if(vartype == "float") row[0] = (float)(row[0]); return row[0]; } mysql_free_result(r); } if(vartype == "int" || vartype == "float") return 0; else return undefined; } set_db_field(fieldname, value) { if(isDefined(self.isBot)) return; mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`user_guid`, `"+fieldname+"`) VALUES ('"+self.guid+"', "+value+") ON DUPLICATE KEY UPDATE `"+fieldname+"`="+value+""); wait 0.05; } set_db_field_string(fieldname, value) { if(isDefined(self.isBot)) return; mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`user_guid`, `"+fieldname+"`) VALUES ('"+self.guid+"', '"+value+"') ON DUPLICATE KEY UPDATE `"+fieldname+"`='"+value+"'"); wait 0.05; }

I want to learn to create something like CoDExtended or whatsoever, where should I start?

Quote:

Originally Posted by ebusiangamers

I want to learn to create something like CoDExtended or whatsoever, where should I start?

Grab a cold beer
Place your freshly wetted lips onto it and enjoy
Have some programming expirience in C and understand the basics (pointer arithmetics etc.) also know some CoD-Script (its kind of managed and simplified C)
have a look here:
[Tutorial] Add libcod support for your cod version.
[Tutorial] How to create your own libcod function
post some extensions on killtube.org :p

If you want to hook some other games/programs, you can also have a look at ollydbg etc. and watch some easy tutorials on how to simply bypass instructions etc. or inject some dll or have an API hook with 'detours' (windows)