Custom Database MySQL ssl

gobasu · April 20, 2017, 5:35pm

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?

Santiago_Rodriguez · May 9, 2017, 9:46pm

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:

github.com

mysqljs/mysql/blob/v2.0.0-alpha8/lib/ConnectionConfig.js

var urlParse        = require('url').parse;
var ClientConstants = require('./protocol/constants/client');
var Charsets        = require('./protocol/constants/charsets');

module.exports = ConnectionConfig;
function ConnectionConfig(options) {
  if (typeof options === 'string') {
    options = ConnectionConfig.parseUrl(options);
  }

  this.host               = options.host || 'localhost';
  this.port               = options.port || 3306;
  this.socketPath         = options.socketPath;
  this.user               = options.user || undefined;
  this.password           = options.password || undefined;
  this.database           = options.database;
  this.insecureAuth       = options.insecureAuth || false;
  this.supportBigNumbers  = options.supportBigNumbers || false;
  this.bigNumberStrings   = options.bigNumberStrings || false;
  this.debug              = options.debug;

This file has been truncated. show original

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:

github.com

mysqljs/mysql/blob/v2.6.2/lib/ConnectionConfig.js#L33


      
          this.supportBigNumbers  = options.supportBigNumbers || false;
          this.bigNumberStrings   = options.bigNumberStrings || false;
          this.dateStrings        = options.dateStrings || false;
          this.debug              = options.debug;
          this.trace              = options.trace !== false;
          this.stringifyObjects   = options.stringifyObjects || false;
          this.timezone           = options.timezone || 'local';
          this.flags              = options.flags || '';
          this.queryFormat        = options.queryFormat;
          this.pool               = options.pool || undefined;
          this.ssl                = (typeof options.ssl === 'string')
            ? ConnectionConfig.getSSLProfile(options.ssl)
            : (options.ssl || false);
          this.multipleStatements = options.multipleStatements || false; 
          this.typeCast           = (options.typeCast === undefined)
            ? true
            : options.typeCast;
          
          
if (this.timezone[0] == " ") {
            // "+" is a url encoded char for space so it
            // gets translated to space when giving a

github.com

mysqljs/mysql/blob/v2.7.0/lib/ConnectionConfig.js#L33


      
          this.supportBigNumbers  = options.supportBigNumbers || false;
          this.bigNumberStrings   = options.bigNumberStrings || false;
          this.dateStrings        = options.dateStrings || false;
          this.debug              = options.debug;
          this.trace              = options.trace !== false;
          this.stringifyObjects   = options.stringifyObjects || false;
          this.timezone           = options.timezone || 'local';
          this.flags              = options.flags || '';
          this.queryFormat        = options.queryFormat;
          this.pool               = options.pool || undefined;
          this.ssl                = (typeof options.ssl === 'string')
            ? ConnectionConfig.getSSLProfile(options.ssl)
            : (options.ssl || false);
          this.multipleStatements = options.multipleStatements || false; 
          this.typeCast           = (options.typeCast === undefined)
            ? true
            : options.typeCast;
          
          
if (this.timezone[0] == " ") {
            // "+" is a url encoded char for space so it
            // gets translated to space when giving a

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.

gobasu · May 9, 2017, 10:08pm

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.

Santiago_Rodriguez · May 9, 2017, 10:49pm

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.

gobasu · May 10, 2017, 4:22pm

@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.

Santiago_Rodriguez · May 10, 2017, 4:49pm

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.

Santiago_Rodriguez · May 10, 2017, 4:54pm

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?

gobasu · May 10, 2017, 5:27pm

@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.

Santiago_Rodriguez · May 10, 2017, 9:13pm

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

gobasu · May 11, 2017, 12:02am

@santiago Sure, feel free to change them.

Santiago_Rodriguez · May 11, 2017, 9:26pm

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:

In the Auth0 dashboard to Connections => Database => Username-Password-Authenticaton
Click on the tab Custom Database
In the Login tab, click on DEBUG SCRIPT and follow the instructions
Close the popup once you have a console executing the command wt logs -p “gobasu-eu-logs”
Click on TRY
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?

gobasu · May 11, 2017, 9:38pm

@santiago

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

Santiago_Rodriguez · May 11, 2017, 9:57pm

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

Santiago_Rodriguez · May 11, 2017, 10:12pm

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!

gobasu · May 11, 2017, 10:27pm

@santiago Lovely!! Thank you very much.

Topic		Replies	Views
401 Unauthorized with SSL custom database Help mysql , custom-database , ssl	2	3489	September 7, 2020
Problems making SSL MySQL connection with custom database Help custom-database , ssl	4	5243	March 2, 2018
Custom DB script receives empty configuration object Help custom-database , login , configuration	3	5234	October 9, 2019
401 Unauthorized error when connecting to custom MySQL database Help auth0 , mysql , custom-database	3	5800	April 17, 2020
Connect mysql in azure from custom database with node.js Help auth0	4	4687	April 10, 2020

Custom Database MySQL ssl

Related topics