[Release] Simple MySQL Lib Connector for C#

[12tails]

Hey guys,

I've done this some time ago (about 2 years i guess), i use it alot until now, works pretty fine (this is an old version and you can implement it as you wish, do not wait for me to support this).

Basically imports the native libmysql.dll functions and use them as done in the old C++ leaked source of TQ Digital.

Some people likes to use NHibernate, i hate it got many failures with it, so i use this instead.

This code is used as an extension to get the field names as they are done in your tables:

 Spoiler

namespace GeniusDataCore
{
    using System;
    using System.ComponentModel;
    using System.Reflection;

    public static class Extend
    {
        public static String GetEnumDesc(Enum pEnum)
        {
            FieldInfo pField = pEnum.GetType().GetField(pEnum.ToString());
            DescriptionAttribute[] setAttr = pField.GetCustomAttributes(typeof(DescriptionAttribute), false) as DescriptionAttribute[];

            if (setAttr.Length > 0)
                return setAttr[0].Description;
            else
                return String.Empty;
        }

        public static String GetEnumDesc(Enum pEnum, string szEnumName)
        {
            FieldInfo pField = pEnum.GetType().GetField(szEnumName);
            DescriptionAttribute[] setAttr = pField.GetCustomAttributes(typeof(DescriptionAttribute), false) as DescriptionAttribute[];

            if (setAttr.Length > 0)
                return setAttr[0].Description;
            else
                return String.Empty;
        }

        public static int GetEnumTypeId(Enum pEnum)
        {
            return (int)pEnum.GetType().GetField(pEnum.ToString()).GetValue(pEnum);
        }
    }
}

You will find a folder named Tables, with samples of the structure to be done.

Here is a sample of a way to use it:

 Spoiler

namespace GeniusAccServer.Structures
{
    using GeniusMySQLCore.Structures;
    using GeniusMySQLCore.Tables;
    using GeniusNetwork.Interfaces;
    using System;
    using System.Text;

    /// <summary>
    /// Create a new instance to handle the account information sent by the client and registered at the server database.
    /// </summary>
    public sealed class Account
    {
        private string _name;
        private string _password;

        // The mysql record loaded from database.
        private MySQLRecord _data;

        /// <summary>
        /// Create a new instance to handle the account information sent by the client and registered at the server database.
        /// </summary>
        /// <param name="szUsername">Username informed by the client when ask for connection</param>
        /// <param name="szPassword">Password informed by the client when ask for connection, the value is encrypted by the client.</param>
        public Account(string szUsername, string szPassword)
        {
            this._data = MySQLRecord.CreateNew("SELECT * FROM `zf_account` WHERE `name`='" + szUsername + "' LIMIT 1", "zf_account");
            if(this._data)
            {
                // User does not exist, return it!
                if (!this._data.MoveNext())
                {
                    Console.WriteLine("Account {0} does not exists!", szUsername);
                    return;
                }

                // Assert the username and password used on login
                this._name = szUsername;
                this._password = szPassword;
            }
        }

        /// <summary>
        /// Account identity, server control only!
        /// </summary>
        public uint Identity { get { return _data.RowsCount() > 0 ? _data.LoadUInt32(ZfAccounts.Id) : 0; } }

        /// <summary>
        /// Account authority on server (is just a client, or is an super admin, or gm, or pm) this field may be validated on server side.
        /// </summary>
        public byte Authority { get { return (byte)(_data.RowsCount() > 0 ? _data.LoadByte(ZfAccounts.Authority) : 0); } }

        /// <summary>
        /// Account authentication type (banned, suspended or whatever).
        /// </summary>
        public byte AuthType { get { return (byte)(_data.RowsCount() > 0 ? _data.LoadByte(ZfAccounts.AuthType) : 0); } }

        /// <summary>
        /// check if the account password is encrypted on our database, if is not, encrypt it!
        /// </summary>
        public bool Encrypted { get { return Convert.ToBoolean(_data.RowsCount() > 0 ? _data.LoadByte(ZfAccounts.Seal) : 0); } }

        /// <summary>
        /// Account username used to authenticate the connection.
        /// </summary>
        public string Name { get { return _name; } }

        /// <summary>
        /// Account password used to authenticate the connection, it's encrypted by the client and encrypted server side.
        /// </summary>
        public string Password { get { return this._password; } }

        /// <summary>
        /// Decrypt the server password and client password, if the values match return true.
        /// </summary>
        public bool CheckPassAuth(ICipher cipher)
        {
            // Check if the password has been already encrypted
            if (Encrypted)
            {
                // Gets the stored encrypted password
                string szPassword = String.Empty;
                this._data.LoadString(out szPassword, ZfAccounts.Name, 16);

                // Decrypt it and compare
                Byte[] encrypted = Encoding.ASCII.GetBytes(szPassword);
                szPassword = Encoding.ASCII.GetString(cipher.Decrypt(encrypted, encrypted.Length));
                return this.Password == szPassword;
            }
            else// if not, encrypt it!
            {
                // Gets the stored password
                string szPassword = String.Empty;
                this._data.LoadString(out szPassword, ZfAccounts.Password, 16);

                // Encrypt and Save the data, but does not change on our main string check
                Byte[] encrypted = Encoding.ASCII.GetBytes(szPassword);
                this._data.SetString(ZfAccounts.Password, cipher.Encrypt(encrypted, encrypted.Length).ToString());
                this._data.SetInt(ZfAccounts.Seal, 1);
                this._data.Update();

                // Compare booth passwords
                return this.Password == szPassword;
            }
        }

        /// <summary>
        /// Update the account information on database, will mostly be used after login.
        /// </summary>
        public bool Update()
        {
            if (this.Identity == 0)
                return false;// No client to update!

            this._data.SetDword(ZfAccounts.LastLogin, DateTime.Now.Ticks);
            return this._data.Update();
        }
    }
}

To set the configurations just set the global variables:

 Spoiler

            // Initialise the MySQL Manager informations
            MySQLManager.Hostname = Kernel.Configuration.Hostname;
            MySQLManager.Username = Kernel.Configuration.Username;
            MySQLManager.Password = Kernel.Configuration.Password;
            MySQLManager.Database = Kernel.Configuration.Database;
            MySQLManager.Port = Kernel.Configuration.Port;

Be aware: If you make any, i mean ANYTHING wrong with your SQL command syntax, the source will just close, this is a problem for you to solve . So make sure that your SQL commands are allways right.

I guess that's all!

P.S: The LibMySQL.DLL must be at the folder with the .exe, otherwise you'll get errors.

[Super Aids]

Nice sql injection examples (I really hope you're not using this in production because if anyone with just a tiny bit of sql injection knowledge knows what to do then they could possibly compromise your whole database.)

Also another side note is that you do not encrypt passwords in a database, in fact that has little to no effect. You hash passwords because it's not possible to reverse (in general speaking, there are of course md5 databases etc. - but you can usually get around those too with seeds etc.)

[KraHen]

[12tails]

Originally Posted by Super Aids

Nice sql injection examples (I really hope you're not using this in production because if anyone with just a tiny bit of sql injection knowledge knows what to do then they could possibly compromise your whole database.)

Also another side note is that you do not encrypt passwords in a database, in fact that has little to no effect. You hash passwords because it's not possible to reverse (in general speaking, there are of course md5 databases etc. - but you can usually get around those too with seeds etc.)

Ah yes, this is an old code, just took it as sample, ignore the password thing...

Also, i've never had a problem with the MySQL injection, works fine and pretty "secure", with many changes made in this old code, of course...

Thanks for feedback tho <3 u guys!

[Super Aids]

Originally Posted by 12tails

Ah yes, this is an old code, just took it as sample, ignore the password thing...

Also, i've never had a problem with the MySQL injection, works fine and pretty "secure", with many changes made in this old code, of course...

Thanks for feedback tho <3 u guys!

It's not "pretty secure" just because you never had issues with mysql injection.

Plenty of big companies aren't secure but never had that issue either.

This code allows anyone to literally just drop your whole database in an instant, you're not even sanitizing input which means anyone can do anything.

Don't ever write queries directly, use prepared statements.

They're actually easier to use and maintain AND they're secure.

[Spirited]

Honestly, there are plenty of other libraries that are secure using parameterization that gets sanitized. I applaud your effort, but this library shouldn't be used in any production scenario. I agree with the rest of the group here.