Custom Database MySQL ssl

Hi

I’ve prepared scripts to connect to custom database (it’s google cloud sql running mysql). It require certificates to authenticate. My login script looks like following:

function login(email, password, callback) {
    var connectionConfiguration = {
        host: configuration.DB_HOST,
        user: configuration.DB_USER,
        password: configuration.DB_PASSWORD,
        database: configuration.DB_NAME,
        ssl: {
            rejectUnauthorized: false,
            key: new Buffer(configuration.DB_CLIENT_KEY, 'base64'),
            cert: new Buffer(configuration.DB_CLIENT_CERT, 'base64')
        }
    };
    var connection = mysql(connectionConfiguration);


    connection.connect();

    var query = "SELECT id, email, password " +
        "FROM users WHERE email = ?";

    connection.query(query, [email], function (err, results) {
        if (err) return callback(err);
        if (results.length === 0) return callback(new WrongUsernameOrPasswordError(email));
        var user = results[0];

        bcrypt.compare(password, user.password, function (err, isValid) {
            if (err) {
                callback(err);
            } else if (!isValid) {
                callback(new WrongUsernameOrPasswordError(email));
            } else {
                callback(null, {
                    id: user.id.toString(),
                    email: user.email
                });
            }
        });

    });
}

Configuration variables are exported. When I try to run it I got: ER_ACCESS_DENIED_ERROR: Access denied for user ‘root’@‘52.28.56.226’ (using password: YES). Ip addresses are whitelisted.

When connected to debug, and modified script a bit to:

function login(email, password, callback) {
    var connectionConfiguration = {
        host: configuration.DB_HOST,
        user: configuration.DB_USER,
        password: configuration.DB_PASSWORD,
        database: configuration.DB_NAME,
        ssl: {
            rejectUnauthorized: false,
            key: new Buffer(configuration.DB_CLIENT_KEY, 'base64'),
            cert: new Buffer(configuration.DB_CLIENT_CERT, 'base64')
        }
    };
    var connection = mysql(connectionConfiguration);


    connection.connect(function(err) {
    if (err) {
        console.log(connectionConfiguration);
        console.error('error connecting: ' + err.stack);
        return;
    }

    console.log('connected as id ' + connection.threadId);
});

    var query = "SELECT id, email, password " +
        "FROM users WHERE email = ?";

    connection.query(query, [email], function (err, results) {
        if (err) return callback(err);
        if (results.length === 0) return callback(new WrongUsernameOrPasswordError(email));
        var user = results[0];

        bcrypt.compare(password, user.password, function (err, isValid) {
            if (err) {
                callback(err);
            } else if (!isValid) {
                callback(new WrongUsernameOrPasswordError(email));
            } else {
                callback(null, {
                    id: user.id.toString(),
                    email: user.email
                });
            }
        });

    });
}

and going to debug I can see this:

    error connecting: Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'52.28.56.226' (using password: YES)
        at Handshake.Sequence._packetToError (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/sequences/Sequence.js:30:14)
        at Handshake.ErrorPacket (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/sequences/Handshake.js:67:18)
        at Protocol._parsePacket (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/Protocol.js:197:24)
        at Parser.write (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/Parser.js:62:12)
        at Protocol.write (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/Protocol.js:37:16)
        at Socket.ondata (_stream_readable.js:536:20)
        at emitOne (events.js:82:20)
        at Socket.emit (events.js:169:7)
        at readableAddChunk (_stream_readable.js:153:18)
        at Socket.Readable.push (_stream_readable.js:111:10)
        --------------------
        at Protocol._enqueue (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/Protocol.js:110:26)
        at Protocol.handshake (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/protocol/Protocol.js:42:41)
        at Connection.connect (/data/sandbox/node_modules/auth0-authz-rules-api/node_modules/mysql/lib/Connection.js:81:18)
        at login (/data/webtask.js:21:16)
        at /data/webtask.js:54:2
        at Async.series.createError.code (/data/sandbox/lib/sandbox.js:743:33)
        at /data/sandbox/node_modules/async/dist/async.js:3816:26
        at replenish (/data/sandbox/node_modules/async/dist/async.js:982:17)
        at iterateeCallback (/data/sandbox/node_modules/async/dist/async.js:967:17)
        at /data/sandbox/node_modules/async/dist/async.js:944:16

Object is configured correctly and as a test I’ve wrote the script on my local machine. It’s working. Can I ask for help in that?

Hi,

The mysql version included by default is ~2.0.0-alpha8. I am not sure that ssl is an option supported in that version:

You can check available libraries for your scripts here:

https://tehsis.github.io/webtaskio-canirequire/#mysql

And it seems that version 2.6.2 and 2.6.0 have support for ssl:

So please try requiring one of these:

require('mysql@2.7.0')

and try again.

On the other hand it seems that the documentation states that the ssl property can take a string or an object: mysql - npm

I am not sure if the buffer will work.

Hi @santiago

Requiring MySQL helps in all scripts apart of create script. When I try create script:

function create(user, callback) {
    var connectionConfiguration = {
        host: configuration.DB_HOST,
        user: configuration.DB_USER,
        password: configuration.DB_PASSWORD,
        database: configuration.DB_NAME,
        ssl: {
            key: new Buffer(configuration.DB_CLIENT_KEY, 'base64'),
            cert: new Buffer(configuration.DB_CLIENT_CERT, 'base64')
        }
    };

    var mysql_b = require('mysql@2.7.0');
    var connection = mysql_b.createConnection(connectionConfiguration);

    connection.connect();
    var query = "INSERT INTO user SET ?";

    bcrypt.hash(user.password, 10, function (err, hash) {
        if (err) {
            return callback(err);
        }
        var insert = {
            password: hash,
            email: user.email
        };
        connection.query(query, insert, function (err, results) {
            if (err) return callback(err);
            if (results.length === 0) return callback();
            callback(null);
        });
    });

}

I am getting error: [WrongUsernameOrPasswordError] wrongusernameorpassword - undefined

Even if I remove this script totaly and save I see the same error all the time.
I think something got cached or container handling this script went into limbo?
How to fix it or test it properly?

PS. Buffer works.

Could you please add some log statements and debug the script? The error you are referring to is the standard error thrown in case of not finding the user in the database.

Example of default mysql login script:

if (results.length === 0) return callback(new WrongUsernameOrPasswordError(email));

So add some console.log() statements and debug in order to find the issue.

Please paste here what you find: we need to find out if the hash is calculated, if the insert is executed, etc.

@santiago I dont think it’s the problem with the script. I’ve created another account, copied this script to new account, copied same global variables and script is working on the second account. I belive first account container went into limbo, scripts are not getting saved correctly - I would make safe assumption it might have be a bug on your side.

Thanks for noticing this! I’ll check our internal logs to see what is going on and report to the engineering team. I’ll let you know what I find.

Could you please tell me which account? I just tried to save a comment in your custom login script without issues. No error messages either. Could you please try again?

@santiago my account user is gobasu (just to confirm: gobasu@hotmail.com). You can save the script - in the browser it looks like it’s saved but while you click Try and actually try to run it you will find out that old script is being executed. You can even remove whole create function, save it and stil old script is getting executed.

Can I make changes to your script in order to debug the issue?

@santiago Sure, feel free to change them.

I tried adding some log messages and I see them in the logs, so the script is being udpated and executed without issues.

Please try the following to reproduce:

  1. In the Auth0 dashboard to Connections => Database => Username-Password-Authenticaton
  2. Click on the tab Custom Database
  3. In the Login tab, click on DEBUG SCRIPT and follow the instructions
  4. Close the popup once you have a console executing the command wt logs -p “gobasu-eu-logs”
  5. Click on TRY
  6. Enter email/password of one of your users

You will see in the output the messages I added to your script via console.log():

[14:25:11.411Z]  INFO wt: Preparing connection...
[14:25:11.470Z]  INFO wt: 
    Connected to DB: OK!
    Query for user...
[14:25:11.662Z]  INFO wt: Query executed: OK!

Could you please verify this?

@santiago

Login user script worked before as mentioned. I am asking about Create User script - it doesn’t work and throws weird error.

Thanks! I could reproduce and I am reaching the engineering team. I will be back to you as soon as I get an answer.

I think I found the issue:

The GET USER script is always executed before the CREATE USER script (we check that the user does not exist first).

So the bug is in the GET USER Script. This is what you had (plus some log code I added):

function getByEmail(email, callback) {
    console.log("Executing Get User script...");
    var connectionConfiguration = {
        host: configuration.DB_HOST,
        user: configuration.DB_USER,
        password: configuration.DB_PASSWORD,
        database: configuration.DB_NAME,
        ssl: {
            rejectUnauthorized: false,
            key: new Buffer(configuration.DB_CLIENT_KEY, 'base64'),
            cert: new Buffer(configuration.DB_CLIENT_CERT, 'base64')
        }
    };
    console.log("Preparing connection to DB...");
    var mysql_b = require('mysql');
    var connection = mysql_b.createConnection(connectionConfiguration);
  
    connection.connect();
    console.log("Connected to DB: OK!");
    var query = "SELECT id, email, password " +
        "FROM user WHERE email = ?";

    console.log("Query DB for email: " + email);
    connection.query(query, [email], function (err, results) {
        console.log("Error???: " + err);
        if (err) return callback(err);
        console.log("Query executed OK!");
        console.log("Users found: " + results.length);
        if (results.length === 0) return callback(new WrongUsernameOrPasswordError(email));
        var user = results[0];

        callback(null, {
            id: user.id.toString(),
            email: user.email
        });

    });
}

This is the code which you need:

function getByEmail(email, callback) {
    console.log("Executing Get User script...");
    var connectionConfiguration = {
        host: configuration.DB_HOST,
        user: configuration.DB_USER,
        password: configuration.DB_PASSWORD,
        database: configuration.DB_NAME,
        ssl: {
            rejectUnauthorized: false,
            key: new Buffer(configuration.DB_CLIENT_KEY, 'base64'),
            cert: new Buffer(configuration.DB_CLIENT_CERT, 'base64')
        }
    };
    console.log("Preparing connection to DB...");
    var mysql_b = require('mysql');
    var connection = mysql_b.createConnection(connectionConfiguration);
  
    connection.connect();
    console.log("Connected to DB: OK!");
    var query = "SELECT id, email, password " +
        "FROM user WHERE email = ?";

    console.log("Query DB for email: " + email);
    connection.query(query, [email], function (err, results) {
        console.log("Error???: " + err);
        if (err) return callback(err);
        console.log("Query executed OK!");
        console.log("Users found: " + results.length);
        if (results.length === 0) return callback();
        var user = results[0];
        
        callback(null, {
            user_id: user.id.toString(),
            email: user.email
        });
    });
}

Please notice that I changed this line

if (results.length === 0) return callback(new WrongUsernameOrPasswordError(email));

(which is the one that was throwing the error you indicated before) for this

if (results.length === 0) return callback();

I also modified the mysql require to specify the proper version and the returned profile from the GET USER script to include the ‘user_id’ field.

Then, after clicking TRY on the CREATE USER script I got this output:

Executing CREATE SCRIPT
Preparing DB connection...
Connected to DB: OK!
Password hashed!
Inserting user in DB...
Error??: Error: unable to verify the first certificate
[Error] HANDSHAKE_SSL_ERROR - unable to verify the first certificate

I think you can continue from here as it now seems to be related to SSL.

Please do not hesitate to reach us again if you find other issues!

@santiago Lovely!! Thank you very much.