Friday 7 January 2011

SFS1X: Database Login

Hi there! In this tutorial I'll explain how can you make a simple Flash Application that connects and then logs in, after the server verifies the credentials to check if there is a user registered with those credentials. This tutorial will be based in the Simple Login Tutorial, so I suggest you to do it before doing this.

For making the Database Login, I'll use:
- H2 Database as the database that will store the registered user's information;
- An AS1.0 Extension, that will handle the login process;

You can download the source code of this tutorial here.

So, the Client Side

First open the Fla file that you created in the Simple Login Tutorial.
You'll need to create a new Input TextField after the userName (using the Text Tool, shortcut key: Tand give it the Instance Name "passWord" without the quotes and set its Behavior property to Password (Like the following image).


You can optionally add a new Dynamic TextField after the userName and give it the Instance Name "errorMsg" without the quotes. This TextField will show the common login errors like "Username or password wrong", "can't connect to the database", etc.

Finally you need to edit some of the code that is in the first frame, so it will stay like this:
import it.gotoandplay.smartfoxserver.*;
stop();

var ip:String = "127.0.0.1";
var port:Number = 9339;
var zone:String = "TutorZone";
var connected:Boolean = false;

userName._visible = false;
passWord._visible = false;
login_btn._visible = false;

var smartfox:SmartFoxClient = new SmartFoxClient();
smartfox.debug = true;
connect();

function connect() {
 status.text = "Connecting";
 smartfox.connect(ip,port);
}

smartfox.onConnection = function(success) {
 if (success) {
  status.text = "Successfully connected!";
  connected = true;
  userName._visible = true;
  passWord._visible = true;
  login_btn._visible = true;
  login_btn.onRelease = sendLogin;
  Selection.setFocus(userName);
 } else {
  status.text = "Can't connect!";
 }
};

function sendLogin() {
 if (connected) {
  if (userName.text != "") {
   error.text = "";
   status.text = "Logging in...";
   smartfox.login(zone,userName.text,passWord.text);
  }
 }else{
  connect()
 }
}

smartfox.onExtensionResponse = function(resObj:Object) {
 if (resObj._cmd == "logOK") {
  _global.myName = resObj.name;
  smartfox.getRoomList();
 } else if (resObj._cmd == "logKO") {
  error.text = "Error at login: "+resObj.error;
  status.text = "Connected";
 }
};

smartfox.onRoomListUpdate = function(roomList:Object) {
 gotoAndStop("lobby");
 display.text = "Logged in as "+_global.myName;
};

smartfox.onConnectionLost = function() {
 gotoAndStop("Login");
 status.text = "Disconnected";
 connected = false;
 userName._visible = false;
 passWord._visible = false;
};
As you can see, I changed the Zone to TutorZone, added some lines to make the passWord TextField disappearing at the beggining and appearing when it successfully connects, like the userName.


Important Warning: I decided to change the Zone not to interfere with the zones that already come with sfs and that are needed for the examples, and from now I'll be always using the TutorZone in the next tutorials.


So you'll need to create a new Zone. If you don't know how to create a new Zone, you can follow the Basic Server Configuration Tutorial.


But there is one important change that I did to the script. The new connect function and the Extension Response, Connection Lost  and RoomList Update handlers.


So let's explain this code:

function connect() {
 status.text = "Connecting";
 smartfox.connect(ip,port);
}

I only created this function so this way I can connect to the server just by executing connect() (or _root.connect() if the code is inside a movieclip) and i dont need to be always displaying the status and calling the smartfox.connect(ip, port). I've also created a connected Boolean, that is true when the client is connected to the server and is false when the client isn't.
In the sendLogin function, now it verifies if it is connected to the server, and if it isn't, it tries to connect to the server again.
smartfox.onExtensionResponse = function(resObj:Object) {
 if (resObj._cmd == "logOK") {
  _global.myName = resObj.name;
  smartfox.getRoomList();
 } else if (resObj._cmd == "logKO") {
  error.text = "Error at login: "+resObj.error;
  status.text = "Connected";
 }
};
This function handles the onExtensionResponse event fired by the client when it receives a response from the Extension. I'll talk more about the extension in the Server Side part of this tutorial. The resObj returns the data from the server. In this tutorial the Extension will return the command "logOK" if the login succeeded or will return the command "logKO" if the login failed.
If the login succeeded, I set a global variable called myName that will represent our public name (the name that everyone sees). Then it executes the getRoomList() command. This command is only used if the customLogin parameter in the Zone is set to true. If it is true, it tells the server that "you don't need to handle the login, because I have an extension that will handle it for you". When handling the customLogin, you need to manually request the roomList from the server, otherwise you won't be able to join any room. But if you don't have the customLogin set to true, in other words you aren't handling the login with an extension, you don't need to request the roomList, because the server automatically sends it after the login.
If the login failed, it displays the error sent by the extension in the error TextField and displays again the "Connected" in the status TextField.
For more information about the onExtensionResponse you can check the docs (http://www.smartfoxserver.com/docs/index.htm?http://www.smartfoxserver.com/docs/docPages/tutorials_pro/02_simpleExtension/index.htm) and the API (http://www.smartfoxserver.com/docs/docPages/as2/html/it_gotoandplay_smartfoxserver_SmartFoxClient.html#onExtensionResponse).
smartfox.onRoomListUpdate = function(roomList:Object) {
 gotoAndStop("lobby");
 display.text = "Logged in as "+_global.myName;
};
This function handles the onRoomListUpdate event by the client when it receives the roomList from the server. In further tutorials I'll use the received roomList, but for now I don't need it. When it receives the roomList, it just goes to the lobby frame and displays message "Logged in as Rjgtav" for example, in the display TextField.
smartfox.onConnectionLost = function() {
 gotoAndStop("login");
 status.text = "Disconnected";
 connected = false;
 userName._visible = false;
 passWord._visible = false;
};
This function handles the onConnectionLost event fired when the client loses the connection with the server. When it loses the connection, it goes to the login frame (in case it is in the lobby frame), shows the "Disconnected" in the status TextField, sets the connected variable to false and hides the userName and passWord TextFields.

We're done with the Client Side, now to the Server Side.
First you'll need to have a database. If you don't have one, you can create one by simply doing this:

1. Open the adminDb.bat in the [SmartFoxServer Installation Folder]\Server (for Windows users) or run ./adminDb.sh command in a terminal window (it will open a new browser window with the h2 login page - at least in Windows it opens).
2. Now enter "jdbc:h2:~/sfstutor" without the quotes as the JDBC URL and enter your desired User Name and Password.
3. Finally press the Connect button.


Now that you have a database, you need to create a table. For that, you just need to run this SQL Statement (you can run SQL statements by entering them in the big central TextField and by clicking the Run (Ctrl+Enter) button):

CREATE TABLE USERS(ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(255), PASS VARCHAR(255), EMAIL VARCHAR(255));

I'll explain this code very quick:

CREATE TABLE USERS() - Creates a new table called USERS and its collums. The collums are set inside the parentheses ();
ID INT AUTO_INCREMENT PRIMARY KEY - A collum called ID and type INT. The AUTO_INCREMENT will make the collum values automatically increase by 1 (like 1, 2, 3, blabla). It is the primary key of the table (you can only have one primary key per table);
NAME VARCHAR(255) - A collum called NAME and type VARCHAR with a maximum of 255 characters; 
PASS VARCHAR(255) - A collum called PASS and type VARCHAR with a maximum of 255 characters;
EMAIL VARCHAR(255) - A collum called EMAIL and type VARCHAR with a maximum of 255 characters;

Now that you have a table, you only need to do one more thing. You need to insert data, in other words register users, for now, I'll only explain how to do it by a SQL Statement. I'll explain how to make a register in a future tutorial. For registering the first users, you only need to run this SQL Statement:

INSERT INTO USERS (NAME, PASS, EMAIL) VALUES ('User's Username', 'User's Password', 'User's Email');

A quick explanation:

INSERT INTO USERS() - Inserts data into the table USERS. The collum's names that we are going to insert data are set inside the first parentheses () and the data to be inserted is set inside the last parentheses ();

Note: The data position inside the last parentheses must match the correspondent collum's position in the first parentheses.

And you're done with the database. Now the extension.

You need to create a new Extension called DatabaseLoginExt. If you don't know how to create an extension, you can check the Simple Extension Tutorial.

This is the source code of the Extension:

var dbase
var userName;
var passWord;
var SocketChannel;

function init(){
 dbase = _server.getDatabaseManager()
}

function destroy(){
 delete dbase
 delete userName;
 delete passWord;
 delete SocketChannel;
}

function handleInternalEvent(evt)
{
 if(evt.name == "loginRequest"){  
  var error = "";
  
  userName = evt["nick"];
        passWord = evt["pass"];
        SocketChannel = evt["chan"];
  
  var sql = "SELECT COUNT (NAME) FROM USERS WHERE NAME='"+userName+"' AND PASS='"+passWord+"'";
  var queryRes = dbase.executeQuery(sql)
  
  var response = {}
  
  if (queryRes != null){
   var Row = queryRes.get(0)
   var count = Row.getItem("COUNT(NAME)");
    
   if(count == 1){
    trace("SUCCESSFULL LOGIN")
    var obj = _server.loginUser(userName, passWord, SocketChannel)
    
    if(obj.success){
     response._cmd = "logOK";
     response.name = userName;
    }else{
     error = obj.error;
     response._cmd = "logKO";
    }
   }else if(count == 0){
    trace("FAILED LOGIN")
    response._cmd = "logKO";
    error = "Wrong username or password";
   }
  }else{
   response._cmd = "logKO";
   error = "Error connecting to the database"
  }
  
  response.error = error;
  _server.sendResponse(response, -1, null, SocketChannel)
 }
}

Here's the explanation of the Extension code:

function init(){
 dbase = _server.getDatabaseManager()
}
This function handles the init event. When the Extension inits, it sets the dbase variable as the Zone's Database Manager.
function destroy(){
 delete dbase
 delete userName;
 delete passWord;
 delete SocketChannel;
}
This function handles the destroy event. When the Extension is destroyed, it deletes all the used variables.
if(evt.name == "loginRequest"){
Checks if the event received is the loginRequest (the one that is sent when the client is requesting to login).
userName = evt["nick"];
passWord = evt["pass"];
SocketChannel = evt["chan"];
The evt param received from the loginRequest event, is an Object. So we are setting the variables to the correspondent data in the evt Object.
Note: It could be evt.nick instead of evt["nick"]. It's the same
var sql = "SELECT COUNT (NAME) FROM USERS WHERE NAME='"+userName+"' AND PASS='"+passWord+"'";
This code sets the sql variable. It is a prepared statement. This prepared statement counts the number of users in the database that have the received userName and passWord.
var queryRes = dbase.executeQuery(sql)
This code makes the Database Manager execute the Prepared Statement sql and sets the queryRes variable as the result returned from the query. There are two commands to execute a SQL Statement. The executeQuery() and the executeUpdate(). The difference between them is that the executeQuery() only queries the database, in other words, gets data from it and the executeUpdate() only updates the database, for example, insert new data.
var response = {}
Creates a new Object. The response Object will be sent to the client (ExtensionResponse event).
var Row = queryRes.get(0)
var count = Row.getItem("COUNT(NAME)");
The queryRes object is the result returned from the query and the result is made of rows. As the database won't have duplicated users, the result will only return a maximum of one row, and as the queryRes is an Array, we get its first parameter (the 0). The Row has items (corresponding to the collums), and when we do a SELECT COUNT(), it only returns a collum called COUNT(the name of the collum to count). So we get that item and assign it to the count variable.
var obj = _server.loginUser(userName, passWord, SocketChannel)

if(obj.success){
 response._cmd = "logOK";
 response.name = userName;
}else{
 error = obj.error;
 response._cmd = "logKO";
}
If the count equals 1, than that means that there's a registered user with the entered credentials, so we login it and assign a variable to the loginUser function, that will be the return of the function. If it returns true, it means that the user successfully logged in. If it returns false, it means that it failed during the log in proccess. Then we set the _cmd of the response. This property is like the name of the result from the ExtensionRequest sent by the client. In this case, we set it as "logOK" if the login succeeded and "logKO" if the login failed. And if the login failed, we assign the error to a variable, that will be returned inside the response Object.
response.error = error;
_server.sendResponse(response, -1, null, SocketChannel)
In this code we set the error property inside the response Object as the error (As you can see along the code I set some more errors). Then we send the response back to the client. For more information about the sendResponse() function you can check the ServerSide API (http://www.smartfoxserver.com/docs/docPages/serverSideApi/_server/sendResponse.htm)

Now, all you need to do is to modify your Zone in the config.xml, so it will stay like this:


<Zone name="TutorZone" uCountUpdate="true" maxUsers="10000" customLogin="true" roomListVars="true">
        <AutoReloadExtensions>true</AutoReloadExtensions>
        <Rooms>
                <Room name="Login" maxUsers="5000" autoJoin="true" limbo="true" />
                <Room name="Lobby" maxUsers="5000" isPrivate="false" isTemp="false" autoJoin="false" uCountUpdate="true" extensionName=""/>
        </Rooms>
        <DatabaseManager active="true">
                <Driver>sun.jdbc.odbc.JdbcOdbcDriver</Driver>
                <ConnectionString>jdbc:h2:~/sfstutor</ConnectionString>
                <UserName>Your database's UserName</UserName>
                <Password>Your Database's Password</Password>
                <TestSQL><![CDATA[SELECT COUNT(*) FROM USERS]]></TestSQL>
                <MaxActive>100</MaxActive>
                <MaxIdle>100</MaxIdle>
                <OnExhaustedPool>fail</OnExhaustedPool>
                <BlockTime>5000</BlockTime> 
        </DatabaseManager>
        <Extensions>
<extension name="LoginExt"  className="DatabaseLoginExt.as" type="script" />
</Extensions>
</Zone>


For more information about how to create a Zone you can check the Basic Server Configuration Tutorial.

Tip: Don't forget to set the Local playback security to Access network only. For more information about this, you can check this tip.


You can download the source code of this tutorial here.


And that's all. I hope you enjoyed this tutorial. If you have any questions, fell free to ask me (by commenting or by dropping me a pm in the sfs forums).

Stay tuned for the next tutorial ;-)

28 comments:

  1. Hey Rjgtav,

    I just followed your tutorial as a test and it worked, but when I tested it, by default my user variables aren't being set. Is this a cause of your script?

    I ask this because I'm using the avatarChat example file for this test, so it couldn't be a problem in the file.

    Thanks,
    Cool Boy 714

    PS: To make the script more secure, use _server.escapeQuotes(phrase) on the database sql code. :P

    ReplyDelete
  2. thx. I was already tired, and released this tutorial without reviewing it, so it might have some bugs. I.ll test later. And thx for the tip

    ReplyDelete
  3. Hello, will your tutorials work with Actionscript 3.0, and Smartfoxserver pro?
    Thanks

    ReplyDelete
  4. Well, i don't know. Currently I'm only writing tutorials of AS2.0 with sfs1x. Maybe I'll do some as3 in the future. But it's also the same as as3.

    ReplyDelete
  5. When you use this tutorial, how would you be able to pass a text field value to the server as an extra parameter for the registration?

    ReplyDelete
  6. hi. Sorry but i dont understand your question. What registration?

    ReplyDelete
  7. In your tutorial's SQL code you created a database with three columns. One named username, one named password, and the last one named email.

    How would you pass data from the client to the server for the "email" value to put into the database?

    Example:
    I have three fields on my register form; username, password, and email.

    I know how I would pass the username and password values to the server by using your extension, but how would I be able to pass the email to the extension as well?

    ReplyDelete
  8. Oh I see. Unfortunately i think that the smartfox.login(username, password) doesn't let you send custom data (you can check the docs to confirm). So to achieve that, you need to send an extensionRequest, that way you can send all the custom data you want.

    ReplyDelete
  9. Really appreciate the tut, but whenever I press the login button, it does nothing, and I know the server is online since the user and passsword appear. Im using avatar chat fla., help please?

    ReplyDelete
  10. Does it show anything in the output like [Sending] and [Received]? Or does it show any error? And are u sure it connects before you press the login button?

    ReplyDelete
  11. When I press the login button, I look at the output and it says in all those random codes "Error: User already login". And it doesnt give me an error that it couldnt connect and it does say connect so...what did I do wrong?

    ReplyDelete
  12. that means that you are calling the login many times. Did you try downloading the source code and comparing to your code?

    ReplyDelete
  13. Well I downloaded the source and copied and pasted all the codes in the proper place to make sure I didnt do anything wrong, same with extension.What am I doing wrong here >_<. Im not sure if this is the error but on my blue box it says this " DB manager is not active in this zone!" twice, but the rest of the blue box looks fine, no errors. Help?

    ReplyDelete
  14. Is this mySQL? If not, can you make the same thing but in mySQL? =P

    ReplyDelete
  15. @Crazy Legs from here i cant tell you. I needed to see the fla. Did you try creating a new fla and just put there the code from the tutorial?

    @Flash Guy this tutorial is fully compatible with mysql. To work with mySQL, you only need to change the driver in the config. I think there is a note in the Basic Server Configuration tutorial

    ReplyDelete
  16. Dude, im mad at you. Not because of help or you cant solve my solution, but because the second time I attempted to download your fla. just to check my codes, it attacked my PC from the file hoster, tried to infect me so I force closed my computer. Its a good thing I had norton, and Im doing a scan right now. PLEASE change the file host to like mediafire, I dont trust downloading ANYTHING from you ANYMORE -_-. Also I think the reason is because Im using a AS2 for the extension instead of an AS1. I will check it...

    ReplyDelete
  17. hum sorry, i havent never had such an issue with my hoster. Thanks for the report, and that isnt my fault, as i didnt knew it. Im gonna change it right now to one much more reliable and safe

    ReplyDelete
  18. Done. Changed ALL the download links to Deposit Files.

    ReplyDelete
  19. Nice post. I found a major, I MEAN MAJOR SECURITY HOLE in your login. You didn't escape the quotes sent by the client, meaning that anyone can SQL inject your script. Not much people will know about this glitch, but it's something you need to know.

    ReplyDelete
  20. hi. This has already been reported. And as i said previously, i.ll update the tutorial when i have free time. Maybe this weekend

    ReplyDelete
  21. Can you do a VPS Setup? I don't have one yet but I'd like to know how to set one up.

    ReplyDelete
  22. hey Rjgstav..
    Why I can't connect to the database when I set the customLogin true?
    error.text said => "error at login: Error connecting to the database"

    BTW I'm using MySQL database

    ReplyDelete
  23. I think you got a wrong SQL syntax in extension..(in line 26).
    you write :"SELECT COUNT (NAME) ..."
    it should be : "SELECT COUNT(NAME) ..."


    thanks for the tutorial Rjgstav! :D

    ReplyDelete
  24. Hi Rjgstav.
    I was thinking about a way let the user choose to pass or the email or the nick in the userName field, and verify this on the extension. Is this possible?
    How manipulate the string inside a extension?

    ReplyDelete
  25. Hello. Everything is working in my files, except that when I enter the correct details, it still says 'Incorrect Username or Password' This is the code I used to insert the data:

    INSERT INTO orders (NAME,PASS)
    VALUES('TEST','TEST');

    I used SELECT * FROM USERS to see if the data was inserted, and it was. What am I doing wrong?

    TWITTER: @Guy71173DI

    ReplyDelete
  26. I think you forgot to set the userid to load it from the extension

    ReplyDelete
  27. better if it was a video tutorial , i need to know it
    but the info on this page is too confusing for me

    ReplyDelete
  28. No entendí nada, Mal tutorial...

    ReplyDelete