Server not picking up information from database and passing it to client

Question

I am trying to get my server to get the sso from the logged in user (web) and pass that to an AS3 client.

If I set a specific SSO in the client (bellow) the server picks up the user from the database.

Currently I get the error: ERROR 1: You have an invalid SSO ticket. Please re-login and then reload.

package 
{
    import com.archicruise.external.RoomManager;
    import com.archicruise.server.Connection;
    import flash.display.Bitmap;
    import flash.display.BitmapData;
    import flash.display.LoaderInfo;
    import flash.display.Sprite;
    import flash.events.Event;
    import flash.system.Security;
    import flash.system.System;

    public class Main extends Sprite 
    {
        [Embed(source = '../assets/client_back.png')] private static const clientBackImage:Class;

        public static var SITE_URL:String = "http://localhost/archicruise/";

        public var roomLoader:RoomManager;
        private var connection:Connection;

        public function Main():void 
        {
            if (stage) init();
            else addEventListener(Event.ADDED_TO_STAGE, init);
        }

        private function init(e:Event = null):void 
        {
            removeEventListener(Event.ADDED_TO_STAGE, init);

            //Add client background
            addChild(new clientBackImage() as Bitmap);

            //Got an SSO ticket?
            var ssoTicket:String = LoaderInfo(this.root.loaderInfo).parameters["sso"];
            if (ssoTicket == "" || ssoTicket == null) ssoTicket = "2e44550b0d6e98cc9f26c39e53213e24";

            //Initialize the connection
            Security.allowDomain("*");
            connection = new Connection("localhost", 9339, this, ssoTicket);;
        }

    }

}

I am getting the ssoTicket value after a user logs into a website and launches the page with the SWF like so:

var flashvars = {
    sso: "<?php echo $self['sso_ticket']; ?>"
};

The Handler from the server:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ParticleFramework.Communication;
using ParticleFramework.Storage;
using ParticleFramework;
using MySql.Data.MySqlClient;
using ArchiCruise.Rooms;

namespace ArchiCruise.Users
{
    static class Handler
    {

        public static List<UserObject> clientObjects = new List<UserObject>();

        public static void login(string ssoTicket, TcpClient client)
        {
            if (ssoTicket == "")
            {
                client.Disconnect();
                return;
            }
            Log.Info("Client " + client.index + " logging in with SSO: " + ssoTicket);

            if (DBManager.database.getString("SELECT COUNT(*) FROM users` WHERE sso_ticket like '%" + ssoTicket.Trim() + "%'") != "0")
            {
                DBManager.database.closeClient();
                //build the user object
                UserObject userObject = newObject(ssoTicket, client);

                foreach (UserObject user in clientObjects)
                {
                    if (user.username == userObject.username)
                    {
                        user.tcpClient.Disconnect();
                    }
                }

                if (clientObjects.Count <= client.index || clientObjects[client.index] == null)
                {
                    client.userObject = userObject;
                    clientObjects.Add(userObject);
                }
                else
                {
                    client.userObject = userObject;
                    clientObjects[client.index] = userObject;
                }
                client.sendData("LO" + (char)13 + userObject.ToPrivate());
                DBManager.database.closeClient();
            }
            else
            {
                DBManager.database.closeClient();
                client.sendData("ER 1: You have an invalid SSO ticket. Please re-login and then reload.");
            }
        }

        public static void toAll(string Data)
        {
            foreach (UserObject user in clientObjects)
            {
                user.tcpClient.sendData(Data);
            }
        }

        public static void toAll(string Data, Boolean disconnect)
        {
            foreach (UserObject user in clientObjects)
            {
                user.tcpClient.sendData(Data);
                if (disconnect) user.tcpClient.Disconnect();
            }
        }

        public static void toUser(string Data, string uname)
        {
            foreach (UserObject user in clientObjects)
            {
                if (user.username.ToLower() == uname.ToLower())
                {
                    user.tcpClient.sendData(Data);
                }
            }
        }

        public static void toUser(string Data, string uname, Boolean disconnect)
        {
            foreach (UserObject user in clientObjects)
            {
                if (user.username.ToLower() == uname.ToLower())
                {
                    user.tcpClient.sendData(Data);
                    if (disconnect)
                    {
                        user.tcpClient.Disconnect();
                    }
                }
            }
        }

        public static void toRoom(int roomID, TcpClient client)
        {
            if (clientObjects.Count >= client.index && client.userObject.roomID != roomID)
            {
                Log.Info("Client " + client.index + " going to public room " + roomID);

                if (DBManager.database.getString("SELECT COUNT(*) FROM `public` WHERE `id` = '" + roomID + "';") != "0")
                {
                    DBManager.database.closeClient();

                    //kick plz
                    if (client.userObject.roomID > 0)
                    {
                        client.userObject.toRoom("KO " + client.userObject.username);
                    }

                    //update user object
                    MySqlDataReader mysqlRead = DBManager.database.getCommand("SELECT * FROM `public` WHERE `id` = '" + roomID + "' LIMIT 1").ExecuteReader();
                    mysqlRead.Read();

                    client.userObject.toRoom(roomID, Convert.ToInt32(mysqlRead["startpos"].ToString().Split(',')[0]), Convert.ToInt32(mysqlRead["startpos"].ToString().Split(',')[1]));

                    client.sendData("RO" + mysqlRead["layout"].ToString() + (char)13 + mysqlRead["name"].ToString() + (char)13 + (char)12 + mysqlRead["heightmap"].ToString() + (char)12 + mysqlRead["warps"].ToString());

                    DBManager.database.closeClient();
                }
                else
                {
                    DBManager.database.closeClient();
                    client.sendData("ER 1: You have an invalid SSO ticket. Please re-login and then reload.");
                }
            }
        }

        public static void moveUser(TcpClient client, int _x, int _y)
        {
            client.userObject.x = _x;
            client.userObject.y = _y;
            client.userObject.toRoom("MV " + client.userObject.username + " " + _x + " " + _y);
        }

        public static void sendNavigationList(TcpClient client, int pub)
        {
            string nList = "NV" + (char)13;
            MySqlDataReader mysqlRead = DBManager.database.getCommand("SELECT * FROM `public` WHERE `show` = 'yes' AND `public` = '" + pub + "'").ExecuteReader();

            while (mysqlRead.Read())
            {
                nList += mysqlRead["id"].ToString() + (char)14 + mysqlRead["name"].ToString() + (char)13;
            }

            DBManager.database.closeClient();

            client.sendData(nList);
        }

        public static void sendUserList(TcpClient client)
        {
            string userList = "UE" + (char)13;

            client.userObject.toRoom("UL" + (char)13 + client.userObject.ToString());

            foreach (UserObject user in clientObjects)
            {
                if (user.roomID == client.userObject.roomID && user.tcpClient != null)
                {
                    if (user.username != client.userObject.username && !userList.Contains(user.username + "@"))
                    {
                        userList += user.ToString();
                    }
                }
            }

            client.sendData(userList);

            //Send room object
            client.sendData("RB" + (char)13 + RoomObjects.buildObjects(client.userObject.roomID));
        }

        public static UserObject newObject(string ssoTicket, TcpClient tClient)
        {
            MySqlDataReader mysqlRead = DBManager.database.getCommand("SELECT * FROM `users` WHERE `sso_ticket` = '" + ssoTicket + "' LIMIT 1").ExecuteReader();
            mysqlRead.Read();

            return new UserObject(mysqlRead["name"].ToString(), Convert.ToInt32(mysqlRead["rank"]), Convert.ToInt32(mysqlRead["credits"]), tClient);
        }

    }
}

Requested DBManager Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;

namespace ParticleFramework.Storage
{
    static class DBManager
    {
        public static Database database;

        public static Boolean Initialize(string type, string user, string pass, string host, string dbname)
        {
            switch (type)
            {
                case "sql":
                    database = new MySQL();
                    break;

                default:
                    Log.Error("Invalid database type! (" + type + ")");
                    break;
            }

            if (database != null)
            {
                return database.connect(user, pass, dbname, host);
            }
            else
            {
                return false;
            }
        }
    }
}

MySQL Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace ParticleFramework.Storage
{
    class MySQL : Database
    {
        private MySqlConnection connection;

        public Boolean connect(string username, string password, string database, string host)
        {
            try
            {
                connection = new MySqlConnection(buildConnectionString(username, password, database, host));
                Console.WriteLine("Database connected.  Running test query...");
                getString("SHOW TABLES FROM `" + database + "`");
                Log.Info("Test query succeeded.  Database initialized.");
                closeClient();

                return true;
            }
            catch (Exception e)
            {
                Log.Error("MySQL Connect: " + e.Message);
                return false;
            }
        }

        public string getString(string query)
        {
            try
            {
                string resultStr = getCommand(query).ExecuteScalar().ToString();
                closeClient();

                return resultStr;
            }
            catch (Exception e)
            {
                Log.Error("MySQL getString: " + e.Message);
                return "";
            }
        }

        public MySqlCommand getCommand(string query)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }

                MySqlCommand command = newCommand();
                command.CommandText = query;
                connection.Open();
                return command;
            }
            catch (Exception e)
            {
                Log.Error("MySQL getCommand: " + e.Message);
                return null;
            }
        }

        public void noCommand(string query)
        {
            try
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }

                MySqlCommand command = newCommand();
                command.CommandText = query;
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception e)
            {
                Log.Error("MySQL noCommand: " + e.Message);
            }
        }

        public void closeClient()
        {
            try
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            catch (Exception e)
            {
                Log.Error("MySQL closeClient: " + e.Message);
            }
        }

        public MySqlCommand newCommand()
        {
            try
            {
                return connection.CreateCommand();
            }
            catch (Exception e)
            {
                Log.Error("MySQL newCommand: " + e.Message);
                return null;
            }
        }

        public string buildConnectionString(string username, string password, string database, string host)
        {
            return "Database=" + database + ";Data Source=" + host + ";User Id=" + username + ";Password=" + password;
        }
    }
}

Database Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace ParticleFramework.Storage
{
    interface Database
    {
        Boolean connect(string username, string password, string database, string host);
        MySqlCommand newCommand();
        MySqlCommand getCommand(string query);

        string buildConnectionString(string username, string password, string database, string host);
        string getString(string query);
        void noCommand(string query);

        void closeClient();
    }
}

LOG INFO AFTER SSO STRING CHANGE

>[1/1/0001 00:00:00] 127.0.0.1connected.  Full 127.0.0.1:56765
>[1/1/0001 00:00:00] Got LO null  from client 0
>[1/1/0001 00:00:00] Client 0 logging in with SSO: null
>[ERROR]Packet handler: MySql.Data.MySqlClient.MySqlException (0x80004005): Invalid attempt to access a field before calling Read()
>   at MySql.Data.MySqlClient.ResultSet.get_Item(Int32 index)
>   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull)
>   at MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i)
>   at MySql.Data.MySqlClient.MySqlDataReader.get_Item(Int32 i)
>   at MySql.Data.MySqlClient.MySqlDataReader.get_Item(String name)
>   at ArchiCruise.Users.Handler.newObject(String ssoTicket, TcpClient tClient) in C:\Users\Daniel\Desktop\AC\Particle Server\Particle Server\ArchiCruise\Users\Handler.cs:line 188
>   at ArchiCruise.Users.Handler.login(String ssoTicket, TcpClient client) in C:\Users\Daniel\Desktop\AC\Particle Server\Particle Server\ArchiCruise\Users\Handler.cs:line 31
>   at ArchiCruise.ArchiCruisePackets.handle(String packet, TcpClient client) in C:\Users\Daniel\Desktop\AC\Particle Server\Particle Server\ArchiCruise\ArchiCruisePackets.cs:line 23
>[1/1/0001 00:00:00] Client0 disconnected and removed.

Tcpclient Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Net.Sockets;

namespace ParticleFramework.Communication
{
    class TcpClient
    {
        #region Required Variables
        public Socket socket;
        public int index;
        private byte[] dataBuffer = new byte[0x400];
        private AsyncCallback ReceiveCallback;
        private AsyncCallback SendCallback;
        #endregion

        #region ArchiCruise Vars
        public ArchiCruise.Users.UserObject userObject;
        public string ip;
        #endregion

        public TcpClient(Socket sock, int num)
        {
            index = num;
            socket = sock;

            ip = socket.RemoteEndPoint.ToString().Split(new char[] { ':' })[0];

            ReceiveCallback = new AsyncCallback(this.ReceivedData);
            SendCallback = new AsyncCallback(this.sentData);

            this.WaitForData();
        }

        public void Disconnect()
        {
            if (socket.Connected)
            {
                socket.Close();
                if (userObject != null) userObject.remove();
                Particle.Server.removeClient(this);
                Log.Info("Client" + this.index + " disconnected and removed.");
                Console.WriteLine("Client" + this.index + " disconnected.");
            }
        }

        private void ReceivedData(IAsyncResult iAr)
        {
            try
            {
                int count = 0;

                try
                {
                    count = socket.EndReceive(iAr);
                }
                catch
                {
                    Disconnect();
                }

                StringBuilder builder = new StringBuilder();
                builder.Append(System.Text.Encoding.Default.GetString(this.dataBuffer, 0, count));
                string str = System.Text.Encoding.Default.GetString(this.dataBuffer, 0, count);

                if (str.Contains("<policy-file-requet/>"))
                {
                    Log.Info("Sending policy file to client" + this.index);
                    rawSend("<?xml version\"1.0\"?><cross-domain-policy><allow-access-from-domain=\"*\" to-ports=\"*\" /><cross-domain-policy>" + Convert.ToChar(0));
                }
                else if (!(str.ToString() == ""))
                {
                    string packet = str.Substring(0, str.Length - 1);
                    //packet = ArchiCruise.Security.Encryption.decrypt(packet);
                    Log.Info("Got " + str + " from client " + this.index);

                    Particle.packetClass.handle(packet, this);
                }
                else
                {
                    Disconnect();
                }
            }
            catch (Exception exception)
            {
                Log.Info("Data recieve error: " + exception.ToString() + " " + exception.Source);
                Disconnect();
            }
            finally
            {
                this.WaitForData();
            }
        }

        private void WaitForData()
        {
            try
            {
                socket.BeginReceive(this.dataBuffer, 0, this.dataBuffer.Length, SocketFlags.None, this.ReceiveCallback, socket);
            }
            catch
            {
                Disconnect();
            }
        }

        public void sendData(string Data)
        {
            Data += (char)1;
            rawSend(Data);
        }

        internal void rawSend(string Data)
        {
            try
            {
                Data += "\0";
                byte[] bytes = System.Text.Encoding.Default.GetBytes(Data);

                socket.BeginSend(bytes, 0, bytes.Length, SocketFlags.None, new AsyncCallback(this.sentData), null);
                Log.Info("Sent " + Data + " to client " + this.index);
            }
            catch
            {
                Disconnect();
            }
        }

        private void sentData(IAsyncResult iAr)
        {
            try
            {
                socket.EndSend(iAr);
            }
            catch
            {
                Disconnect();
            }
        }
    }
}

Answer 1

I suggest you to use a mysql ORM. your code is really error prone and highly fragile to sql injection attacks.

however, from your error log you can see that you are not checking if the sql query was executed properly and has values in it. you can do this easily with an if check as follows:

public static UserObject newObject(string ssoTicket, TcpClient tClient)
{
    string sqlQuery = "SELECT * FROM `users` WHERE `sso_ticket` = '" + ssoTicket + "' LIMIT 1";
    MySqlDataReader mysqlRead = DBManager.database.getCommand( sqlQuery ).ExecuteReader();
    if (mysqlRead.Read()) // read the query and check if we got any data
    {
        return new UserObject(mysqlRead["name"].ToString(), Convert.ToInt32(mysqlRead["rank"]), Convert.ToInt32(mysqlRead["credits"]), tClient);  
    }
    else
    {
       Log.Error("sqlQuery failed : " + sqlQuery );
       return null; //you should check the returned value if its null or not to prevent further problems.
    }                           
}

with this code you can check if your query is wrong. i would suggest you to debug step by step and see if the variables have the right values in them. check Debugging in Visual Studio from Microsoft for more information on debugging.