SA-MP Forums Archive
[Tutorial] Creating a MySQL account system (Simple) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] Creating a MySQL account system (Simple) (/showthread.php?tid=162584)


Creating a MySQL account system (Simple) - Carlton - 24.07.2010

Information

Hi, I decided to do something simple instead of my previous tutorials involving confusing variables and difficult MySQL work. We're going to make a account system together that involves.

- Registration
- Login
- Saving

Enjoy and pay attention , also, check out these:

https://sampforum.blast.hk/showthread.php?tid=161726
https://sampforum.blast.hk/showthread.php?tid=161454

Before we begin

Ok, make sure you're connected to a MySQL database and have the tables and fields ready. If you're confused what that's read this http://dev.mysql.com/doc/

You will need the following:

- Brain.
- Common PAWN knowledge.
- Installed MySQL plugin (https://sampforum.blast.hk/showthread.php?tid=56564)
- A proper attitude.

Registration

Prepare your registration method and make a open slot where you want to start the registration. A example is below.

pawn Код:
CMD:register(playerid, params[]) {
     new sscanfinput[128];
     if(sscanf(params, "s[64]", string)) return SendClientMessage(playerid, COLOR_PURPLE, "<> /register (password)");
     if(strlen(sscanfinput) > 64) return SendClientMessage(playerid, COLOR_PURPLE, "Only 64 characters allowed.");
     // Open slot.
     return 1;
}
Lets begin formating the query to check if their account exist already.

pawn Код:
GetPlayerName(playerid, sscanfinput, 24);
format(sscanfinput, 128, "SELECT * Account_Table_Name WHERE Username = '%s'", sscanfinput);
mysql_query(sscanfinput); // This function runs a MySQL query.
mysql_store_result(); // This function stores the result from a SELECT query.
if(mysql_num_rows()) { // This function checks how much rows are from a SELECT query.
    mysql_free_result(); // This function releases the stored result.
    SendClientMessage(playerid, COLOR_PURPLE, "Your account does exist!");
    return 1;
}
mysql_free_result();
// Further coding.
Now, what I just did was select everything from a account table and checked if any of the data's username equals the player's name. I the result of the selection and made it return that their account exists already. This is what our register command should look like now.

pawn Код:
CMD:register(playerid, params[]) {
     new sscanfinput[64], string[128];
     if(sscanf(params, "s[64]", sscanfinput)) return SendClientMessage(playerid, COLOR_PURPLE, "<> /register (password)");
     if(strlen(sscanfinput) > 64) return SendClientMessage(playerid, COLOR_PURPLE, "Only 64 characters allowed.");
     new name[24];
     GetPlayerName(playerid, name, 24);
     format(string, 128, "SELECT * Account_Table_Name WHERE Username = '%s'", name);
     mysql_query(string); // This function runs a MySQL query.
     mysql_store_result(); // This function stores the result from a SELECT query.
     if(mysql_num_rows()) { // This function checks how much rows are from a SELECT query.
          mysql_free_result(); // This function releases the stored result.
          SendClientMessage(playerid, COLOR_PURPLE, "Your account does exist!");
          return 1;
     }
     mysql_free_result();
     // Further coding.
     return 1;
}
Now lets start inserting data.

pawn Код:
new Escape[2][128];
mysql_real_escape_string(name, Escape[0]); // This function makes sure you don't get MySQL injected. Read about it by searching it on ******.
mysql_real_escape_string(sscanfinput, Escape[1]);
format(string, 128, "INSERT INTO Account_Table_Name (Username, Password) VALUES('%s', md5('%s'))", Escape[0], Escape[1]);
mysql_query(string);
Information about the INSERT query I did:

Quote:

To insert data into MySQL table you would need to use SQL INSERT INTO command. You can insert data into MySQL table by using mysql> prompt or by using any script like PHP. Here is generic SQL syntax of INSERT INTO command to insert data into MySQL table:

PHP код:
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES( value1, value2,...valueN ); 
Obviously in PAWN you will need to format the values for specific things. Now as you can see in my query, I inserted a row that contained our username and our password. You can add more fields if you'd like. Here's an example how:

pawn Код:
new Escape[2][128];
mysql_real_escape_string(name, Escape[0]); // This function makes sure you don't get MySQL injected. Read about it by searching it on ******.
mysql_real_escape_string(sscanfinput, Escape[1]);
format(string, 128, "INSERT INTO Account_Table_Name (Username, Password, Faction, Rank) VALUES('%s', md5('%s'), 999, 999)", Escape[0], Escape[1]);
mysql_query(string);
Make sure these fields exist before inserting. Also make sure you md5 your passwords, for password security.

Login

Ok, to login make sure you have your command ready, just like in the previous step.

pawn Код:
CMD:login(playerid, params[]) {
     new sscanfinput[64], string[128];
     if(sscanf(params, "s[64]", string)) return SendClientMessage(playerid, COLOR_PURPLE, "<> /register (password)");
     // Open slot.
     return 1;
}
Now, we're going to use sscanf and explode, not split because it is a failure: it's slow.

sscanf - Slightly faster than explode by ******.
explode - Way faster than split made by Westie.
split - Slow, and overused. I think made by DracoBlue.

Ok, lets get ready, we're going to check the whole database if the account exist then load the account, if the password is correct.

pawn Код:
new name[24],
      Escape[2][128];
GetPlayerName(playerid, name, 24);
mysql_real_escape_string(name, Escape[0]); // This function makes sure you don't get MySQL injected. Read about it by searching it on ******.
mysql_real_escape_string(sscanfinput, Escape[1]);
format(string, 128, "SELECT * FROM Account_Table_Name WHERE Username = '%s' AND Password = md5('%s')", Escape[0], Escape[1]);
mysql_query(string);
mysql_store_result();
if(!mysql_num_rows()) {
    mysql_free_result();
    SendClientMessage(playerid, COLOR_PURPLE, "Your password is wrong or your account doesn't exist.");
    return 1;
}
SendClientMessage(playerid, COLOR_PURPLE, "You're logged in.");
// Login code here.
mysql_free_result();
This query will check if there's an row in the database that contains the same username and password. Then we stored the result and checked it the query found anything. Now lets begin with our login code. The one below is sscanf2.

pawn Код:
new Data[80], MyPassword[64], MyUserName[24], MyAdminLevel;
mysql_fetch_row_format(Data, "|");
sscanf(Data, "p<|>s[24]s[64]i", MyPassword, MyUserName, MyAdminLevel);
Obviously that looked confusing as fuck to you or not. If you're confused about how I did that read this topic: https://sampforum.blast.hk/showthread.php?tid=120356, sscanf goes field by field, that's why I added MyUserName as a variable, you can't skip a field, I think, without making a useless variable?

Now here's the explode way, if you don't have explode, here's the function.

PHP код:
stock explode(aExplode[][], const sSource[], const sDelimiter[] = " ", iVertices = sizeof aExplode, iLength = sizeof aExplode[])
{
   new
      iNode,
      iPointer,
      iPrevious = -1,
      iDelimiter = strlen(sDelimiter);
   while(iNode < iVertices)
   {
      iPointer = strfind(sSource, sDelimiter, false, iPointer);
      if(iPointer == -1)
      {
         strmid(aExplode[iNode], sSource, iPrevious, strlen(sSource), iLength);
         break;
      }
      else
      {
         strmid(aExplode[iNode], sSource, iPrevious, iPointer, iLength);
      }
      iPrevious = (iPointer += iDelimiter);
      ++iNode;
   }
   return iPrevious;
} 
Once again, here's the explode code:

pawn Код:
new Data[80], ExplodeSplit[3][100], MyPassword[64], MyUserName[24], MyAdminLevel;
mysql_fetch_row_format(Data, "|");
explode(ExplodeSplit, Data, "|");
strmid(MyUserName, ExplodeSplit[0], 0, strlen(ExplodeSplit[0]), 255); // https://sampwiki.blast.hk/wiki/Strmid
strmid(MyPassword, ExplodeSplit[1], 0, strlen(ExplodeSplit[1]), 255);
MyAdminLevel = strval(ExplodeSplit[2]);
You made ExplodeSplit's first array 3 because of the blank character that's needed. Now your code should like this in sscanf:

pawn Код:
CMD:login(playerid, params[]) {
     new sscanfinput[64], string[128];
     if(sscanf(params, "s[64]", string)) return SendClientMessage(playerid, COLOR_PURPLE, "<> /register (password)");
     new name[24],
      Escape[2][128];
     GetPlayerName(playerid, name, 24);
     mysql_real_escape_string(name, Escape[0]); // This function makes sure you don't get MySQL injected. Read about it by searching it on ******.
     mysql_real_escape_string(sscanfinput, Escape[1]);
     format(string, 128, "SELECT * FROM Account_Table_Name WHERE Username = '%s' AND Password = md5('%s')", Escape[0], Escape[1]);
     mysql_query(string);
     mysql_store_result();
     if(!mysql_num_rows()) {
           mysql_free_result();
           SendClientMessage(playerid, COLOR_PURPLE, "Your password is wrong or your account doesn't exist.");
            return 1;
     }
     SendClientMessage(playerid, COLOR_PURPLE, "You're logged in.");
     new Data[80], MyPassword[64], MyUserName[24], MyAdminLevel;
     mysql_fetch_row_format(Data, "|");
     sscanf(Data, "p<|>s[24]s[64]i", MyPassword, MyUserName, MyAdminLevel);
     mysql_free_result();
     return 1;
}
With explode it would look like:

pawn Код:
CMD:login(playerid, params[]) {
     new sscanfinput[64], string[128];
     if(sscanf(params, "s[64]", string)) return SendClientMessage(playerid, COLOR_PURPLE, "<> /register (password)");
     new name[24],
      Escape[2][128];
     GetPlayerName(playerid, name, 24);
     mysql_real_escape_string(name, Escape[0]); // This function makes sure you don't get MySQL injected. Read about it by searching it on ******.
     mysql_real_escape_string(sscanfinput, Escape[1]);
     format(string, 128, "SELECT * FROM Account_Table_Name WHERE Username = '%s' AND Password = md5('%s')", Escape[0], Escape[1]);
     mysql_query(string);
     mysql_store_result();
     if(!mysql_num_rows()) {
           mysql_free_result();
           SendClientMessage(playerid, COLOR_PURPLE, "Your password is wrong or your account doesn't exist.");
           return 1;
     }
     SendClientMessage(playerid, COLOR_PURPLE, "You're logged in.");
     new Data[80], ExplodeSplit[3][100], MyPassword[64], MyUserName[24], MyAdminLevel;
     mysql_fetch_row_format(Data, "|");
     explode(ExplodeSplit, Data, "|");
     strmid(MyUserName, ExplodeSplit[0], 0, strlen(ExplodeSplit[0]), 255); // https://sampwiki.blast.hk/wiki/Strmid
     strmid(MyPassword, ExplodeSplit[1], 0, strlen(ExplodeSplit[1]), 255);
     MyAdminLevel = strval(ExplodeSplit[2]);  
     mysql_free_result();
     return 1;
}
Obviously it's suggested you create a variable that says the player is logged in or not. Example:

pawn Код:
new bool: LoggedIn[MAX_PLAYERS];
CMD:login(playerid, params[]) {
   if(LoggedIn[playerid]==true) return SendClientMessage(playerid, COLOR_PURPLE, "You're logged in already.");
   // Code.
}
Saving

Saving accounts are simple, the syntax is below for the MySQL query.

PHP код:
UPDATE table_name
SET column1=value, column2=value2,... etc.
WHERE some_column=some_value 
Here's an example.

PHP код:
mysql_query("UPDATE People SET Cheese='Sucks' WHERE Cheese= 'Rocks'"); 
This is pretty self explanatory but i'll show you a example for the tutorial.

PHP код:
format(string, 128, "UPDATE Account_Table_Name SET AdminLevel=%i, Money=%i WHERE Username = '%s", MyAdminLevel, MyMoney, name);
mysql_query(string); 
Conclusion

Thank you for reading, if you have any errors or questions please post below. Enjoy.

Re: Creating a MySQL account system (Simple) - Flake. - 24.07.2010

Really nice thanks!

Re: Creating a MySQL account system (Simple) - Kar - 24.07.2010

good i'm going to use this when i convert my script from dini to mySQL

Re: Creating a MySQL account system (Simple) - iLinx - 24.07.2010

Great tutorial :3
I'm sure this will help those still using files jump, or at least get near the MySQL way of doing things

Re: Creating a MySQL account system (Simple) - Carlton - 24.07.2010

Thanks.

Re: Creating a MySQL account system (Simple) - Mr187 - 24.07.2010

Now this one is done right, I am glad to see somebody makes a tutorial and know's what there doing.

I hope to see you work on ours like this soon Carlton , Also showing me more on what i did wrong in pawno lol.

Re: Creating a MySQL account system (Simple) - The Scripter - 25.07.2010

Very nice tutorial! This will help me in converting my registration system from Dini to MySQL. Thanks!

Re: Creating a MySQL account system (Simple) - Carlton - 02.08.2010

I didn't see you both replied, thanks anyway. :P

Re: Creating a MySQL account system (Simple) - Kar - 02.08.2010

what do you mean mysql injected?

people can hack your mysql?

Re: Creating a MySQL account system (Simple) - Calgon - 02.08.2010

Quote:
Originally Posted by Kar
Посмотреть сообщение
what do you mean mysql injected?

people can hack your mysql?
http://www.******.co.uk/search?q=mys...ient=firefox-a