[Juvanii]

I recently decided to switch scripting database saving system from INI to MySQL, I installed MySQL R39-4 and worked fine with me of loading and saving data. Should i install another version of MySQL because i am new using it?

How to delete a row in this version?
I saw people using mysql_query("DELETE FROM ...") to delete, but this function is undefined in MySQL R39-4, so how to delete ?

[jlalt]

Код:

mysql_query(connection handle,"DELETE FROM `tabel` WHERE `whattofind:d` = 'whatisit[string/numeric]'");

example:

PHP код:

mysql_query(connection handle,"DELETE FROM `accounts` WHERE `Name` = '%s'",pName(playerid)); 


[PrO.GameR]

It's mysql_tquery (or mysql_pquery, It moves each of your queries into a thread so it might break your code since stuff won't happen in the order you did them so if you want to use pquery use with caution) in R39-4 (threaded version of the same function), and your query is right, you should use "DELETE ..."

[Juvanii]

What about the version? should i install another version or not ?

[AndySedeyn]

Use the latest version; R39-4. You also have to escape strings to avoid exploits. The example of jlalt is wrong.
MySQL +R33 wiki page: https://sampwiki.blast.hk/wiki/MySQL/R33

mysql_format and mysql_tquery are key.

[Juvanii]

MySQL quits creating new rows when a new player register, what's the reason?
I started a new blank page of pawn to detect what is causing the problem and i didn't figure it out!

Here is the script of registering dialog:

PHP код:

case DIALOG_REGISTER:
{
    if(!response) return Kick(playerid);
    new day,month,year,ip[16],date[11]; GetPlayerIp(playerid, ip, sizeof(ip)); getdate(year,month,day);
    format(date, sizeof(date), "%i/%i/%i",  day, month, year);
    mysql_format(ConnectionHandle, query, sizeof(query), "INSERT INTO accounts (username, password, registerdate, ip) VALUES ('%s','%s','%s','%s')", GetName(playerid), inputtext, date, ip);
    mysql_format(ConnectionHandle, query, sizeof(query), "SELECT * FROM accounts WHERE username = '%s' LIMIT 1", GetName(playerid));
    mysql_tquery(ConnectionHandle, query, "LoadUserData", "i", playerid);
    format(string, sizeof(string), "{FFFFFF}Account Has Been Registered Successflly\n\n{E8E654}Account: {FFFFFF}%s\n\nType Your Password Below To Login.", GetName(playerid));
    ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD,"{F03A3A}Juvanii's Cops And Robbers",string,"Login","Quit");
    return 1;
} 


LoadUserData Callback:

PHP код:

forward LoadUserData(playerid);
public LoadUserData(playerid)
{
    new rows, fields;
    cache_get_data(rows, fields, ConnectionHandle);
    printf("rows: %d | fields: %d", rows, fields);
    if(rows)
    {
        cache_get_field_content(0, "password", PlayerInfo[playerid][Password], ConnectionHandle, 64);
        cache_get_field_content(0, "registerdate", PlayerInfo[playerid][RegisterDate], ConnectionHandle, 11);
        cache_get_field_content(0, "ip", PlayerInfo[playerid][IP], ConnectionHandle, 16);
        PlayerInfo[playerid][Money] = cache_get_field_content_int(0, "money");
        PlayerInfo[playerid][Score] = cache_get_field_content_int(0, "score");
        PlayerInfo[playerid][Warrents] = cache_get_field_content_int(0, "warrents");
        PlayerInfo[playerid][RegularPlayer] = cache_get_field_content_int(0, "regularplayer");
     }
    return 1;
} 


debug returns 0 rows and 8 fields, it's okay for the fields but why 0 rows ?


MySQL Log when a player join:

Код:

[01:34:34] [DEBUG] mysql_format - connection: 1, len: 128, format: "SELECT * FROM `accounts` WHERE `username` = '%e' LIMIT 1"
[01:34:34] [DEBUG] mysql_tquery - connection: 1, query: "SELECT * FROM `accounts` WHERE `username` = 'Juvanii' LIMIT 1", callback: "AccountChecking", format: "i"
[01:34:34] [DEBUG] CMySQLQuery::Execute[AccountChecking] - starting query execution
[01:34:34] [DEBUG] CMySQLQuery::Execute[AccountChecking] - query was successfully executed within 0.964 milliseconds
[01:34:34] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[01:34:34] [DEBUG] Calling callback "AccountChecking"..
[01:34:34] [DEBUG] cache_get_data - connection: 1
[01:34:34] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called

MySQL Log when a player register:

Код:

[01:34:55] [DEBUG] mysql_format - connection: 1, len: 128, format: "INSERT INTO accounts (username, password, registerdate, ip) VALUES ('%s','%s','%s','%s')"
[01:34:55] [DEBUG] mysql_format - connection: 1, len: 128, format: "SELECT * FROM accounts WHERE username = '%s' LIMIT 1"
[01:34:55] [DEBUG] mysql_tquery - connection: 1, query: "SELECT * FROM accounts WHERE username = 'Juvanii' LIMIT 1", callback: "LoadUserData", format: "i"
[01:34:55] [DEBUG] CMySQLQuery::Execute[LoadUserData] - starting query execution
[01:34:55] [DEBUG] CMySQLQuery::Execute[LoadUserData] - query was successfully executed within 0.340 milliseconds
[01:34:55] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[01:34:55] [DEBUG] Calling callback "LoadUserData"..
[01:34:55] [DEBUG] cache_get_data - connection: 1
[01:34:55] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called

Any help ?

[AndySedeyn]

Escape your strings in mysql_format to avoid exploits! (Use the %e specifier).
Secondly, you should NEVER store passwords in plain text! Hash them and put a salt on them. I hereby refuse to help you until you secure the passwords.

[Juvanii]

Quote:

Originally Posted by AndySedeyn Escape your strings in mysql_format to avoid exploits! (Use the %e specifier). Secondly, you should NEVER store passwords in plain text! Hash them and put a salt on them. I hereby refuse to help you until you secure the passwords.

I was hashing it when i was using INI, i recently switched to MySQL from 2 days only so i kinda don't know everything about it plus this is not the final script, it will be changed after everything works fine, it's just me join the server and no one else. Anyway here what it looks like after hashing:

PHP код:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch(dialogid)
    {
        case DIALOG_REGISTER:
        {
            if(!response) return Kick(playerid);
               new day,month,year,ip[16],date[11]; GetPlayerIp(playerid, ip, sizeof(ip)); getdate(year,month,day);
               format(date, sizeof(date), "%i/%i/%i",  day, month, year);
            mysql_format(ConnectionHandle, query, sizeof(query), "INSERT INTO accounts (username, password, registerdate, ip) VALUES ('%e','%i','%e','%e')", GetName(playerid), udb_hash(inputtext), date, ip);
            mysql_format(ConnectionHandle, query, sizeof(query), "SELECT * FROM accounts WHERE username = '%e' LIMIT 1", GetName(playerid));
            mysql_tquery(ConnectionHandle, query, "LoadUserData", "i", playerid);
            format(string, sizeof(string), "{FFFFFF}Account Has Been Registered Successflly\n\n{E8E654}Account: {FFFFFF}%s\n\nType Your Password Below To Login.", GetName(playerid));
            ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD,"{F03A3A}Juvanii's Cops And Robbers",string,"Login","Quit");
            return 1;
        }
    }
    return 1;
}
forward LoadUserData(playerid);
public LoadUserData(playerid)
{
    new rows, fields;
    cache_get_data(rows, fields, ConnectionHandle);
    printf("rows: %d | fields: %d", rows, fields);
    if(rows)
    {
        cache_get_field_content(0, "registerdate", PlayerInfo[playerid][RegisterDate], ConnectionHandle, 11);
        cache_get_field_content(0, "ip", PlayerInfo[playerid][IP], ConnectionHandle, 16);
        PlayerInfo[playerid][Password] = cache_get_field_content_int(0, "password");
         PlayerInfo[playerid][Money] = cache_get_field_content_int(0, "money");
         PlayerInfo[playerid][Score] = cache_get_field_content_int(0, "score");
         PlayerInfo[playerid][Warrents] = cache_get_field_content_int(0, "warrents");
        PlayerInfo[playerid][RegularPlayer] = cache_get_field_content_int(0, "regularplayer");
     }
    return 1;
}
stock udb_hash(buf[])
{
    new length=strlen(buf), s1 = 1, s2 = 0, n;
    for(n=0; n<length; n++)
    {
       s1 = (s1 + buf[n]) % 65521;
       s2 = (s2 + s1)     % 65521;
    }
    return (s2 << 16) + s1;
} 


[AndySedeyn]

You can reverse the algorithm used for udb_hash in the blink of an eye. Use something more secure (Whirlpool, MD5 or SHA256).

- Only string values should be between single quotation marks.
- You first format a query but you don't send it.

PHP код:

    mysql_format(ConnectionHandle, query, sizeof(query), "INSERT INTO accounts (username, password, registerdate, ip) VALUES ('%e', %i,'%e','%e')", GetName(playerid), udb_hash(inputtext), date, ip);
    mysql_tquery(ConnectionHandle, query, "", "");
    mysql_format(ConnectionHandle, query, sizeof(query), "SELECT * FROM accounts WHERE username = '%e' LIMIT 1", GetName(playerid));
    mysql_tquery(ConnectionHandle, query, "LoadUserData", "i", playerid); 


Read this tutorial: https://sampforum.blast.hk/showthread.php?tid=574714

[Juvanii]

Quote:

Originally Posted by AndySedeyn You can reverse the algorithm used for udb_hash in the blink of an eye. Use something more secure (Whirlpool, MD5 or SHA256). - Only string values should be between single quotation marks. - You first format a query but you don't send it. PHP код:     mysql_format(ConnectionHandle, query, sizeof(query), "INSERT INTO accounts (username, password, registerdate, ip) VALUES ('%e', %i,'%e','%e')", GetName(playerid), udb_hash(inputtext), date, ip);     mysql_tquery(ConnectionHandle, query, "", "");     mysql_format(ConnectionHandle, query, sizeof(query), "SELECT * FROM accounts WHERE username = '%e' LIMIT 1", GetName(playerid));     mysql_tquery(ConnectionHandle, query, "LoadUserData", "i", playerid);  Read this tutorial: https://sampforum.blast.hk/showthread.php?tid=574714

I thought one mysql_tquery will send the both formats. Anyway, it worked fine! Thank you.