Monday, August 20, 2012

Handle user accounts with MySql

Hello

    Now we have a solid way to store and request data the next step is going to be the creation of user accounts.

First we are going to prepare the database, to do so we will connect to the mysql server:
mysql -h localhost -u root -p

Create a database
crete database rpgdata;

Use the newly created database
use rpgdata;

Then create a table, that will store the user accounts
 CREATE TABLE accounts (     id INT AUTO_INCREMENT,     fullname VARCHAR(60),     email VARCHAR(120),     password VARCHAR(30), 
    lastlogin TIMESTAMP,
 PRIMARY KEY (id));


We want to login into the game with an existent account or to create a new one, after that step we will get into game character creation (the next step), to simplify things I'm gonna make a text-only client , and a simplified server too.

The logic will be:
  1. Client connect to the server socket
  2. Client displays two options:2a-create a new character, 2b-login with an existing character
  3. If we choose to create a new character the client will show list of text fields we need to fill to create the account
  4. If we choose to login with an existing account we will fill our username (email) and password. 
In the first case, it asks for:
    • Full user name
    • email(we will use it like our user account)
    • password
    • password (another time), to prevent mistakes when typing
            cout << "NEW ACCOUNT CREATION" << endl;
            cout << "Full name?: ";
            cin  >> fullname;
            cout << "e-mail?: ";
            cin  >> username;
            cout << "Password?: ";
            cin  >> password;
            cout << "Repeat Password?: ";
            cin  >> password2;
    
            if(password.compare(password2)==0)
                {
                    done=true;
                }else{
                    cout << "Passwords don't match" << endl;
                }

    After filling all the data, it will pack it and sent it to the server
    std::stringstream Msg;
    Msg << fullname << "#"<< username << "#" << password << "#";
    NET.SockSend(CREATE_NEW_ACCOUNT,Msg.str().c_str());

    After we receive the message in the server we are going to unpack the data and get the email.
    switch (command){
                case CREATE_NEW_ACCOUNT:
                    cout << "New account request" << endl;
                    data2.clear();
                    split(data[3],'#',data2);//I split it into strings
    
                    fullname = data2[0];
                    username = data2[1];
                    password = data2[2];
    
                    cout << "Fullname " << fullname << endl;
                    cout << "Username " << username << endl;
                    cout << "Password " << password << endl;

     We will make a query to the MySql server with that email account
                    aquery.clear();
                    aquery = "select * from accounts where email = '";
                    aquery+= username;
                    aquery += "'";
                    cout << aquery << endl;
                    aSQL.executequery(aquery.c_str());//executing select query


    If there is already an account with the same email we will reject the data and send a "duplicate account" message to the client, if we don't find it, then we save a new account to the server and send a "account created properly" message to the client
     if (aSQL.getrow())
                        {
                            //Username duplicate..
                            cout << "Duplicate username.." << endl;
                            aNET.Clients[atoi(data[0].c_str())]->SockSend(DUPLICATE_ACCOUNT,"");
                        }else{
                            //We have not found username, ok to insert..
                            cout << "We have not found username, ok to insert.." << endl;
                            aquery.clear();
                            aquery = "insert into accounts values (NULL,'";
                            aquery+= fullname;
                            aquery+="' , '";
                            aquery+= username;
                            aquery+="' , '";
                            aquery+= password;
                            aquery+="', NULL)";
                            cout << aquery << endl;
                            aSQL.executequery(aquery.c_str());//executing select query
                            cout << "New user inserted" << endl;
                            aNET.Clients[atoi(data[0].c_str())]->SockSend(NEW_ACCOUNT_ACCEPTED,"");
                            }
                        break;

    After we create a new user, it will return to the main menu.
    In the second case, it asks for:
    • email
    • password

    After filling the data, it will pack and sent it to the server
        cout << "ACCOUNT LOGIN" << endl;
        cout << "e-mail?: ";
        cin >> username;
        cout << "Password?: ";
        cin >> password;
        std::stringstream Msg;
        Msg << username << "#" << password << "#";
        NET.SockSend(LOGIN_WITH_ACCOUNT,Msg.str().c_str());

    After we receive the message in the server we are going to unpack the data and get the email.
                case LOGIN_WITH_ACCOUNT:
                    cout << "Login request" << endl;
                    data2.clear();
                    split(data[3],'#',data2);//I split it into strings
    
                    username = data2[0];
                    password = data2[1];
                    cout << "Username " << username << endl;
                    cout << "Password " << password << endl;

    With the email we are going to make a query to the server asking for accounts with that email.
                    aquery.clear();
                    aquery = "select * from accounts where email = '";
                    aquery+= username;
                    aquery += "'";
                    cout << aquery << endl;
                    aSQL.executequery(aquery.c_str());//executing select query

    If we receive no accounts, obviously, there is no such account.
     if (aSQL.getrow())
                        {
            /... 
                        }else{
                        //We have not found username..
                        cout << "Username not found.." << endl;
                        aNET.Clients[atoi(data[0].c_str())]->SockSend(UNKNOW_USERNAME,"");
                        }

    If we have found that account we check the password match the one the user has typed.
    //Username found
                        cout << aSQL.row[0] << " " << aSQL.row[1] << " " << aSQL.row[2] << " " << aSQL.row[3] << " " << aSQL.row[4] << endl;
                        std::string pass;
                        pass = aSQL.row[3];
                        if (pass.compare(password)==0)
                            {
                            cout << "Username/password match, login ok" << endl;
                            aNET.Clients[atoi(data[0].c_str())]->SockSend(ACCOUNT_ACCEPTED,"");
                            aNET.Clients[atoi(data[0].c_str())]->Login  = true;
                            }else{
                            //wrong password
                            cout << "Password mismatch.. " << endl;
                            aNET.Clients[atoi(data[0].c_str())]->SockSend(BAD_PASSWORD,"");
                            }

    If the password is right, it will send an ACCOUNT_ACCEPTED message.

    In the client side, after sending data to creating a new account or login with an existing account we will wait to the server to send us a reply
    switch(command)
                {
                case ACCOUNT_ACCEPTED:
                cout << "ACCOUNT_ACCEPTED" << endl;
                reply = true;
                logedin = true;
                break;
    
                case NEW_ACCOUNT_ACCEPTED:
                cout << "NEW_ACCOUNT_ACCEPTED" << endl;
                reply = true;
                break;
    
                case DUPLICATE_ACCOUNT :
                cout << "DUPLICATE_ACCOUNT " << endl;
                reply = true;
                break;
    
                case UNKNOW_USERNAME:
                cout << "UNKNOW_USERNAME" << endl;
                reply=true;
                break;
    
                case BAD_PASSWORD:
                cout << "BAD_PASSWORD" << endl;
                reply=true;
                break;
                }//switch

    And basically that's all, We have now authenticated users, ready for the next step: Player creation process!

    Notes:
    1. Don't look for encryption: it is not yet implemented so the server / client are not secure, the reason for that is 1-Simplicity 2-Easier to understand incoming / outgoing packets and 3-It is very easy to add encryption later.
    2. Messages are not encoded: no binary numbers, just plain text, the reason the same in point 1.1 and 1.2 : making things simpler to understand.
    The full code can be downloaded at https://code.google.com/p/jbfprj/downloads/list or at the svn

    See you!

    No comments: