PDA

View Full Version : Store player informations with MySQL.



ebusiangamers
3rd July 2015, 17:39
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:

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:

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!89go0LcvuO-dC5I74W8yBOebI4taGlCgvQvkTEz_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

IzNoGoD
3rd July 2015, 18:42
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.

ebusiangamers
4th July 2015, 03:03
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.

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.

ebusiangamers
4th July 2015, 18:39
I got the function get_db_field(fieldname) to work:

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.

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.

IzNoGoD
4th July 2015, 19:11
Point 1,2,3,5 still stand.

ebusiangamers
5th July 2015, 03:45
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

IzNoGoD
5th July 2015, 12:28
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)

ebusiangamers
5th July 2015, 17:04
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:

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);
}


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!ETkfTGzCL0iQiclo6qscw3-Zv6rJUrFMxLHjuq6XbwM

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 877

IzNoGoD
5th July 2015, 18:37
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



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

ebusiangamers
6th July 2015, 10:27
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):


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?

serthy
6th July 2015, 12:05
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. (http://killtube.org/showthread.php?2084-Tutorial-Add-libcod-support-for-your-cod-version)
[Tutorial] How to create your own libcod function (http://killtube.org/showthread.php?2083-Tutorial-How-to-create-your-own-libcod-function)


post some extensions on killtube.org ("http://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)