• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

AAC Znote (Change "name" to "id")

Extrodus

|| Blazera.net ||
Premium User
Joined
Dec 22, 2008
Messages
2,724
Solutions
7
Reaction score
534
Location
Canada
So I've started working on the Nostalrius (RealOTs) release from @Ezzz and have begun converting the Znote AAC to be compatible with the release.
However upon my first attempts, I've already run into an error.

When I change this line of "login.php"
Code:
$account = mysql_select_single("SELECT {$fields} FROM `accounts` WHERE `name`='{$username}' AND `password`='{$password}' LIMIT 1;");

To this:
Code:
$account = mysql_select_single("SELECT {$fields} FROM `accounts` WHERE `id`='{$username}' AND `password`='{$password}' LIMIT 1;");

I get this error..

Code:
string(51) "SELECT `id` FROM `accounts` WHERE `name`='1234567';"
(query - SQL error)
Type: select_single (select single row from database)

Unknown column 'name' in 'where clause'

Any help is appreciated.

Link to original server files: Nostalrius 7.7 (https://otland.net/threads/nostalrius-7-7.262406/)
Link to Znote files: https://github.com/Znote/ZnoteAAC/archive/master.zip


Edit: I have changed this line in "www\engine\function/users.php" from "name" to "id" on line 1574
Code:
$data = mysql_select_single("SELECT `id` FROM `accounts` WHERE `id`='$username';");

Now I get this error.

Code:
string(98) "SELECT `id`, `name`, `password`, `email`, `premdays` FROM `accounts` WHERE `id` = 1234567 LIMIT 1;"
(query - SQL error)
Type: select_single (select single row from database)

Unknown column 'name' in 'field list'
 
Last edited:
Solution
I'm working through this at the moment, I had intended to document the changes but about half way through I stopped because it was taking too much extra time that I really don't have.
Anywho, you will also need to update the nostalrius schema to include the email & creation columns on the account table, and the motd column on the guilds table.

Syntax like this, unsure if other link stuff is required though - this isn't my forte.
SQL:
ALTER TABLE `guilds` ADD `motd` VARCHAR(255) NOT NULL DEFAULT '' AFTER `creationdata`;


EDIT: Done - I haven't compiled the game server yet so this is untested, but it should get you off the ground =)
Znote AAC for Nostalrius 7.7:
MEGA...
So I've started working on the Nostalrius (RealOTs) release from @Ezzz and have begun converting the Znote AAC to be compatible with the release.
However upon my first attempts, I've already run into an error.

When I change this line of "login.php"
Code:
$account = mysql_select_single("SELECT {$fields} FROM `accounts` WHERE `name`='{$username}' AND `password`='{$password}' LIMIT 1;");

To this:
Code:
$account = mysql_select_single("SELECT {$fields} FROM `accounts` WHERE `id`='{$username}' AND `password`='{$password}' LIMIT 1;");

I get this error..

Code:
string(51) "SELECT `id` FROM `accounts` WHERE `name`='1234567';"
(query - SQL error)
Type: select_single (select single row from database)

Unknown column 'name' in 'where clause'

Any help is appreciated.

Link to original server files: Nostalrius 7.7 (https://otland.net/threads/nostalrius-7-7.262406/)
Link to Znote files: https://github.com/Znote/ZnoteAAC/archive/master.zip


Edit: I have changed this line in "www\engine\function/users.php" from "name" to "id" on line 1574
Code:
$data = mysql_select_single("SELECT `id` FROM `accounts` WHERE `id`='$username';");

Now I get this error.

Code:
string(98) "SELECT `id`, `name`, `password`, `email`, `premdays` FROM `accounts` WHERE `id` = 1234567 LIMIT 1;"
(query - SQL error)
Type: select_single (select single row from database)

Unknown column 'name' in 'field list'
try this one
Code:
<?php
require_once 'engine/init.php';

// Client 11 loginWebService
if($_SERVER['HTTP_USER_AGENT'] == "Mozilla/5.0" && $config['ServerEngine'] === 'TFS_10') {

    function jsonError($message, $code = 3) {
        die(json_encode(array('errorCode' => $code, 'errorMessage' => $message)));
    }

    header("Content-Type: application/json");
    $input = file_get_contents("php://input");

    // Based on tests, input length should be at least 67+ chars.
    if (strlen($input) > 10) {
        /* {
            'accountname' => 'username',
            'password' => 'superpass',
            'stayloggedin' => true,
            'token' => '123123', (or not set)
            'type' => 'login', (What other types do we have?)
        } */
        $jsonObject = json_decode($input);

        $username = sanitize($jsonObject->accountname);
        $password = SHA1($jsonObject->password);
        $token = (isset($jsonObject->token)) ? sanitize($jsonObject->token) : false;
        
        $fields = '`id`, `premdays`, `secret`';
        if ($config['twoFactorAuthenticator']) $fields .= ', `secret`';

        $account = mysql_select_single("SELECT {$fields} FROM `accounts` WHERE `name`='{$username}' AND `password`='{$password}' LIMIT 1;");
        if ($account === false) {
            jsonError('Wrong username and/or password.');
        }

        if ($config['twoFactorAuthenticator'] === true && $account['secret'] !== null) {
            if ($token === false) {
                jsonError('Submit a valid two-factor authentication token.', 6);
            } else {
                require_once("engine/function/rfc6238.php");
                if (TokenAuth6238::verify($account['secret'], $token) !== true) {
                    jsonError('Two-factor authentication failed, token is wrong.', 6);
                }
            }
        }

        $players = mysql_select_multi("SELECT `name`, `sex` FROM `players` WHERE `account_id`='".$account['id']."';");
        if ($players !== false) {

            $gameserver = $config['gameserver'];
            // todo: Fix dynamic desition to pass along token. (and verify that it works). Hostname: otx11.lan
            $sessionKey = $username."\n".$jsonObject->password;
            if (strlen($account['secret']) > 5) $sessionKey .= "\n".$token."\n".floor(time() / 30);
            $response = array(
                'session' => array(
                    'fpstracking' => false,
                    'optiontracking' => false,
                    'isreturner' => true,
                    'returnernotification' => false,
                    'showrewardnews' => false,
                    'sessionkey' => $sessionKey,
                    'lastlogintime' => 0,
                    'ispremium' => ($account['premdays'] > 0) ? true : false,
                    'premiumuntil' => time() + ($account['premdays'] * 86400),
                    'status' => 'active'
                ),
                'playdata' => array(
                    'worlds' => array(
                        array(
                            'id' => 0,
                            'name' => $gameserver['name'],
                            'externaladdress' => $gameserver['ip'],
                            'externalport' => $gameserver['port'],
                            'previewstate' => 0,
                            'location' => 'ALL',
                            'externaladdressunprotected' => $gameserver['ip'],
                            'externaladdressprotected' => $gameserver['ip'],
                            'externalportunprotected' => $gameserver['port'],
                            'externalportprotected' => $gameserver['port'],
                            'anticheatprotection' => false
                        )
                    ),
                    'characters' => array(
                        //array( 'worldid' => ASD, 'name' => asd, 'ismale' => true, 'tutorial' => false ),
                    )
                )
            );

            foreach ($players as $player) {
                $response['playdata']['characters'][] = array(
                    'worldid' => 0,
                    'name' => $player['name'],
                    'ismale' => ($player['sex'] === 1) ? true : false,
                    'tutorial' => false
                );
            }

            //error_log("= SESSION KEY: " . $response['session']['sessionkey']);
            die(json_encode($response));
        } else {
            jsonError("Character list is empty.");
        }
    } else {
        jsonError("Unrecognized event.");
    }
} // End client 11 loginWebService

logged_in_redirect();
include 'layout/overall/header.php';

if (empty($_POST) === false) {

    if ($config['log_ip']) {
        znote_visitor_insert_detailed_data(5);
    }

    $username = $_POST['username'];
    $password = $_POST['password'];

    if (empty($username) || empty($password)) {
        $errors[] = 'You need to enter a username and password.';
    } else if (strlen($username) > 32 || strlen($password) > 64) {
            $errors[] = 'Username or password is too long.';
    } else if (user_exist($username) === false) {
        $errors[] = 'Failed to authorize your account, are the details correct, have you <a href=\'register.php\'>register</a>ed?';
    } /*else if (user_activated($username) === false) {
        $errors[] = 'You havent activated your account! Please check your email. <br>Note it may appear in your junk/spam box.';
    } */else if ($config['use_token'] && !Token::isValid($_POST['token'])) {
        Token::debug($_POST['token']);
        $errors[] = 'Token is invalid.';
    } else {

        // Starting loging
        if ($config['ServerEngine'] == 'TFS_02' || $config['ServerEngine'] == 'OTHIRE' || $config['ServerEngine'] == 'TFS_10') $login = user_login($username, $password);
        else if ($config['ServerEngine'] == 'TFS_03') $login = user_login_03($username, $password);
        else $login = false;
        if ($login === false) {
            $errors[] = 'Username and password combination is wrong.';
        } else {
            // Check if user have access to login
            $status = false;
            if ($config['mailserver']['register']) {
                $authenticate = mysql_select_single("SELECT `id` FROM `znote_accounts` WHERE `account_id`='$login' AND `active`='1' LIMIT 1;");
                if ($authenticate !== false) {
                    $status = true;
                } else {
                    $errors[] = "Your account is not activated. An email should have been sent to you when you registered. Please find it and click the activation link to activate your account.";
                }
            } else $status = true;

            if ($status) {
                // Regular login success, now lets check authentication token code
                if ($config['ServerEngine'] == 'TFS_10' && $config['twoFactorAuthenticator']) {
                    require_once("engine/function/rfc6238.php");

                    // Two factor authentication code / token
                    $authcode = (isset($_POST['authcode'])) ? getValue($_POST['authcode']) : false;

                    // Load secret values from db
                    $query = mysql_select_single("SELECT `a`.`secret` AS `secret`, `za`.`secret` AS `znote_secret` FROM `accounts` AS `a` INNER JOIN `znote_accounts` AS `za` ON `a`.`id` = `za`.`account_id` WHERE `a`.`id`='".(int)$login."' LIMIT 1;");

                    // If account table HAS a secret, we need to validate it
                    if ($query['secret'] !== NULL) {

                        // Validate the secret first to make sure all is good.
                        if (TokenAuth6238::verify($query['secret'], $authcode) !== true) {
                            $errors[] = "Submitted Two-Factor Authentication token is wrong.";
                            $errors[] = "Make sure to type the correct token from your mobile authenticator.";
                            $status = false;
                        }

                    } else {

                        // secret from accounts table is null/not set. Perhaps we can activate it:
                        if ($query['znote_secret'] !== NULL && $authcode !== false && !empty($authcode)) {

                            // Validate the secret first to make sure all is good.
                            if (TokenAuth6238::verify($query['znote_secret'], $authcode)) {
                                // Success, enable the 2FA system
                                mysql_update("UPDATE `accounts` SET `secret`= '".$query['znote_secret']."' WHERE `id`='$login';");
                            } else {
                                $errors[] = "Activating Two-Factor authentication failed.";
                                $errors[] = "Try to login without token and configure your app properly.";
                                $errors[] = "Submitted Two-Factor Authentication token is wrong.";
                                $errors[] = "Make sure to type the correct token from your mobile authenticator.";
                                $status = false;
                            }
                        }
                    }
                } // End tfs 1.0+ with 2FA auth

                if ($status) {
                    setSession('user_id', $login);

                    // if IP is not set (etc acc created before Znote AAC was in use)
                    $znote_data = user_znote_account_data($login);
                    if ($znote_data['ip'] == 0) {
                        $update_data = array(
                        'ip' => getIPLong(),
                        );
                        user_update_znote_account($update_data);
                    }

                    // Send them to myaccount.php
                    header('Location: myaccount.php');
                    exit();
                }
            }
        }
    }
} else {
    header('Location: index.php');
}

if (empty($errors) === false) {
    ?>
    <h2>We tried to log you in, but...</h2>
    <?php
    header("HTTP/1.1 401 Not Found");
    echo output_errors($errors);
}

include 'layout/overall/footer.php'; ?>
 
I'm working through this at the moment, I had intended to document the changes but about half way through I stopped because it was taking too much extra time that I really don't have.
Anywho, you will also need to update the nostalrius schema to include the email & creation columns on the account table, and the motd column on the guilds table.

Syntax like this, unsure if other link stuff is required though - this isn't my forte.
SQL:
ALTER TABLE `guilds` ADD `motd` VARCHAR(255) NOT NULL DEFAULT '' AFTER `creationdata`;


EDIT: Done - I haven't compiled the game server yet so this is untested, but it should get you off the ground =)
Znote AAC for Nostalrius 7.7:
MEGA (https://mega.nz/#!M1MXnaLZ!gcG7cKfzQzoN3ApP7aB7JOH1YPCoGWnEBBarBXyTM7Q)
Updated Schema:
MEGA (https://mega.nz/#!BlVVDQbZ!d5nlRr3dRNhwr0aHKuQ-ceStXbwPbLrBUpHrpEIKEyM)
 
Last edited:
Solution
I'm working through this at the moment, I had intended to document the changes but about half way through I stopped because it was taking too much extra time that I really don't have.
Anywho, you will also need to update the nostalrius schema to include the email & creation columns on the account table, and the motd column on the guilds table.

Syntax like this, unsure if other link stuff is required though - this isn't my forte.
SQL:
ALTER TABLE `guilds` ADD `motd` VARCHAR(255) NOT NULL DEFAULT '' AFTER `creationdata`;


EDIT: Done - I haven't compiled the game server yet so this is untested, but it should get you off the ground =)
Znote AAC for Nostalrius 7.7:
MEGA (https://mega.nz/#!M1MXnaLZ!gcG7cKfzQzoN3ApP7aB7JOH1YPCoGWnEBBarBXyTM7Q)
Updated Schema:
MEGA (https://mega.nz/#!BlVVDQbZ!d5nlRr3dRNhwr0aHKuQ-ceStXbwPbLrBUpHrpEIKEyM)


Tested everything and its working - allows account creation and character creation; logging into game works.
Only issues that I see so far, which I'll be able to quickly fix is only allowing 1-7 numbers as the account number/name.
Right now I can create "tester" as an account name, which of course doesn't work on the 7.7 client - or atleast the sources aren't set up to accept characters.

Great work @mdwilliams - was literally sitting down to do everything and went to grab the github link from here and saw your post!
You're a time saver and you definitely know what you're doing, thank you!
 
Sorry for necroing but can someone help me?

I get this error

string(90) "SELECT id, password, email, premdays FROM accounts WHERE id = 1234567 LIMIT 1;"
(query - SQL error)
Type: select_single (select single row from database)

Unknown column 'email' in 'field list'
 
Sorry for necroing but can someone help me?

I get this error

string(90) "SELECT id, password, email, premdays FROM accounts WHERE id = 1234567 LIMIT 1;"
(query - SQL error)
Type: select_single (select single row from database)

Unknown column 'email' in 'field list'
go to your phpmyadmin and do this
SQL:
alter table `accounts` ADD `email` VARCHAR(20) NOT NULL;
 
Sounds like you didn't use the schema i provided in my post above so you'll hit your next snag with the 'creation' column not existing on the accounts table, and then another snag on the 'motd' column not existing on the guilds table.
Feel free to use this schema: MEGA (https://mega.nz/#!BlVVDQbZ!d5nlRr3dRNhwr0aHKuQ-ceStXbwPbLrBUpHrpEIKEyM)

Note: I haven't looked at this since the day I posted it - I'd be surprised if there was not an alternate AAC option that just supports ID out of the box.
 
I'm working through this at the moment, I had intended to document the changes but about half way through I stopped because it was taking too much extra time that I really don't have.
Anywho, you will also need to update the nostalrius schema to include the email & creation columns on the account table, and the motd column on the guilds table.

Syntax like this, unsure if other link stuff is required though - this isn't my forte.
SQL:
ALTER TABLE `guilds` ADD `motd` VARCHAR(255) NOT NULL DEFAULT '' AFTER `creationdata`;


EDIT: Done - I haven't compiled the game server yet so this is untested, but it should get you off the ground =)
Znote AAC for Nostalrius 7.7:
MEGA (https://mega.nz/#!M1MXnaLZ!gcG7cKfzQzoN3ApP7aB7JOH1YPCoGWnEBBarBXyTM7Q)
Updated Schema:
MEGA (https://mega.nz/#!BlVVDQbZ!d5nlRr3dRNhwr0aHKuQ-ceStXbwPbLrBUpHrpEIKEyM)



----
Hi. Sorry about anything, I am totally layman on this subject. I already have a website properly installed on Linux Ubuntu, is a gesior. To install the AAC Znote compatible with Nostalrius 7.7 do I need to perform any SSH commands? Or just replacing one site with another, and editing the site is enough? Thank you.
 
I'm working through this at the moment, I had intended to document the changes but about half way through I stopped because it was taking too much extra time that I really don't have.
Anywho, you will also need to update the nostalrius schema to include the email & creation columns on the account table, and the motd column on the guilds table.

Syntax like this, unsure if other link stuff is required though - this isn't my forte.
SQL:
ALTER TABLE `guilds` ADD `motd` VARCHAR(255) NOT NULL DEFAULT '' AFTER `creationdata`;


EDIT: Done - I haven't compiled the game server yet so this is untested, but it should get you off the ground =)
Znote AAC for Nostalrius 7.7:
MEGA (https://mega.nz/#!M1MXnaLZ!gcG7cKfzQzoN3ApP7aB7JOH1YPCoGWnEBBarBXyTM7Q)
Updated Schema:
MEGA (https://mega.nz/#!BlVVDQbZ!d5nlRr3dRNhwr0aHKuQ-ceStXbwPbLrBUpHrpEIKEyM)

----

Sorry for the double post, my problems have been solved.
 
Last edited:
Hi all, the modified znote aac I posted here is for development purposes only (really just to allow you to log in locally etc.). It isn't meant for production. As much as I like to help out, I cannot provide further support on this.
As far as I know, @Znote has no intention of formally supporting nostalrius either, for the reasons he outlines in this issue: Nostalrius 7.72 compatibility · Issue #343 · Znote/ZnoteAAC (https://github.com/Znote/ZnoteAAC/issues/343)

I think @slawkens myaac has nostalrius support though, so if you're here looking for an aac that works with nostalrius I suggest you check that out: slawkens/myaac (https://github.com/slawkens/myaac)
 
Sounds like you didn't use the schema i provided in my post above so you'll hit your next snag with the 'creation' column not existing on the accounts table, and then another snag on the 'motd' column not existing on the guilds table.
Feel free to use this schema: MEGA (https://mega.nz/#!BlVVDQbZ!d5nlRr3dRNhwr0aHKuQ-ceStXbwPbLrBUpHrpEIKEyM)

Note: I haven't looked at this since the day I posted it - I'd be surprised if there was not an alternate AAC option that just supports ID out of the box.


consulta SQL:

/*!40101 SET NAMES utf8mb4 */;


MySQL ha dicho: Documentación

#1115 - Unknown character set: 'utf8mb4'
Post automatically merged:

PHP version 5.6 or higher is required.
 
Back
Top