Store player informations with MySQL.

[ebusiangamers]

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

[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.

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]

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]

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]

Point 1,2,3,5 still stand.

[ebusiangamers]

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]

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]

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

[IzNoGoD]

		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]

The problem is solved! Thanks IzNoGod!
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?