insecureAuth support (was: bad handshake)

[mscdex]

Output: 'Query error: Bad handshake'

Server: MySQL server 5.0.95 (Linux)

Code:

var mysql = require('mysql2');

var config = {
  host: '127.0.0.1',
  user: 'user',
  password: 'password',
  database: 'database',
  typeCast: false,
  supportBigNumbers: true,
  bigNumberStrings: true,
  insecureAuth: true
}, c;

function doConnect() {
  c = mysql.createConnection(config);
  c.connect(function(err) {
    console.log('Client connected');
  });
  c.on('error', function(err) {
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      console.log('Disconnected -- reconnecting ...');
      doConnect();
    } else
      throw err;
  });
}

function doQuery() {
  var count = 0;

  console.log('starting query ...');

  c.query('select * from foo')
   .on('error', function(err) {
     console.log('Query error: ' + err);
   })
   .on('result', function(row) {
     ++count;
   })
   .once('end', function(info) {
     console.log('Query finished successfully');
     console.log(count + ' rows');
   });
}

doConnect();
doQuery();

[mscdex]

I should note that the same code works with the 'mysql' module.

[sidorares]

Thanks, Brian.

can't confirm it on osx/mysql 5.6.10 I'll try linux/mysql 5.0.95. Can you post foo schema? Should not affect connection error though...

sh-3.2$ node qqq.js
starting query ...
Client connected
Query finished successfully
50001 rows

tmp ❯ mysql test                                                                                                                                                                                                                                                              ⏎
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 367
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table insert_test;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                     |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| insert_test | CREATE TABLE `insert_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12313334 DEFAULT CHARSET=utf8 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[mscdex]

It happens with any schema. It may have something to do with 'insecureAuth' ?

[sidorares]

probably. I think this makes client connect with no PROTOCOL_41 flag. Which version of mysql2 are you using? Is that from npm or git master?

[mscdex]

npm

[sidorares]

Still can't reproduce, and I'm sure I don't support insecure auth at the moment (flag is ignored)

[sidorares]

Do you have this error if you comment out insecureAuth: true?

[sidorares]

also this is what I have:

mysql> SHOW VARIABLES LIKE 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

[mscdex]

It looks like insecureAuth doesn't do anything currently. Grep returns:

mysql2/lib/connection_config.js:  this.insecureAuth       = options.insecureAuth || false;

[sidorares]

you are right, it is ignored. Not sure why your connection fails though

[mscdex]

I created a new user using the new password format and that connected fine.

[sidorares]

I changed the caption. Will close this issue once pre insecure auth is supported.

Current problems: secure auth is always used - server insecure auth flag is ignored and client still tries to connect using secure auth method; client insecure auth flag is ignored and there is no way to force insecure connection.

[midnightcodr]

I am trying to switch from using mysqljs/mysql to mysql2@1.5.1 but unfortunately I can't because insecureAuth is not supported. Everything including insecureAuth is working fine with mysqljs/mysql. So far this is the only road blocker for my project. I understand that changing the password format for the user will fix the issue but that option is out of the question now as we are afraid that change might break other parts of our system. Any plan to add full support for this option @sidorares? Thanks.

[sidorares]

@midnightcodr does your server support plugin authentication / authentication switch ? ( if you connect with debug: true you'll see that in 'server capabilities flags' line

If yes, insecureAuth can be added as authentication switch plugin via config ( and I'd like to make this to happen automatically when insecureAuth is on )

[midnightcodr]

@sidorares plugin auth is in the capability flags list. How does insecureAuth can be added as authentication switch plugin via config work exactly? I am getting Unhandled promise rejection (rejection id: 1): Error: Server requires auth switch, but no auth switch handler provided with the basic { host, user, password, database, debug: true} config.

[sidorares]

@midnightcodr at the moment it's not very trivial. You'll need to port this code https://github.com/mysqljs/mysql/blob/3f371ca18a46150fc1fe1b8bb31a099b9f62f2fb/lib/protocol/Auth.js#L37-L152 and put it into handler similar to implementation for mysql_clear_plugin in https://github.com/sidorares/node-mysql2/issues/438#issuecomment-255343793

[sidorares]

this is also relevant - https://github.com/sidorares/node-mysql2/issues/504

one problem could be that plugin name is reported as empty string - can you verify this? ( add console log in auth switch handler )

[midnightcodr]

I got

{ pluginName: 'mysql_old_password',
  pluginData: ...
}

with authSwitchHandler: function(data, cb) { console.log(data) }. Will give the implementations you mentioned above a try. Thank you.

[sidorares]

ok cool. So what you need to add is

function(data, cb) {
  if (data.pluginName === 'mysql_old_password') {
     cb(null, authenticateMysqlOldPassword(yourPassword, data.pluginData))
  }
}

where authenticateMysqlOldPassword is code similar to https://github.com/mysqljs/mysql/blob/3f371ca18a46150fc1fe1b8bb31a099b9f62f2fb/lib/protocol/Auth.js#L37-L152

[sidorares]

this is how it's used in mysqljs/mysql: https://github.com/mysqljs/mysql/blob/e8fea7068476ac65d78532bbf786d6393502f54b/lib/protocol/sequences/Handshake.js#L124

So basically to test you can copy/paste whole Auth.js to your code, require it and have handler like this:

function(data, cb) {
  if (data.pluginName === 'mysql_old_password') {
    cb(null, Auth.scramble323(data.pluginData, password))
  }
}

[midnightcodr]

You meant Auth.hashPassword? But it only takes one argument. Sorry for so many newbie questions.

[midnightcodr]

Never mind, just saw your new comment after I posted mine.

[sidorares]

that's ok, they don't look newbie at all!

[midnightcodr]

Got it working. Can't thank you enough @sidorares for your help.

[sidorares]

great! you you think you can find time to port this to mysql2 core that would be great!

Pseudo code:

• add lib/auth_323.js code with scramble323 and dependent functions
• at https://github.com/sidorares/node-mysql2/blob/5f0fb8f1f5035e2c0207490aa2f0b838dc82fdc2/lib/commands/client_handshake.js#L160 similar to native password add second if: if name is old_password, check if insecureAuth is on and respond with scramble323. If it's not on, respond with meaningful error

Don't be stressed if you don't have time or experience to do this, in that case these are notes to future me

[midnightcodr]

Would definitely give it a try.

[sidorares]

maybe would be good to allow and bundle switch handler plugin for mysql_clear_password if insecureAuth is on - see code in https://github.com/sidorares/node-mysql2/issues/438#issuecomment-255343793

[midnightcodr]

@sidorares I am getting an empty authSwitchHandlerParams.pluginName when trying to implement what you suggested. marker has a value of 0xfe, asr is

AuthSwitchRequest { pluginName: '', pluginData: <Buffer > }

[sidorares]

you mentioned earlier that you getting pluginName: 'mysql_old_password'? Can you give a bit more context what's different now when you have pluginName: ''?

[midnightcodr]

@sidorares sorry my bad. I am getting empty pluginName in the if clause after line 160 in node-mysql2/lib/commands/client_handshake.js. The non-empty pluginName was from the authSwitchHandler: function(data, cb) { console.log(data) } option while initializing the connection.

The mysql server version I am testing against is 5.6.20.

I am using the following code to do a quick test:

const mysql = require('./promise')
// previously defined host, user, password & database
const opts = { host, user, password, database, insecureAuth: true}

const run = async() => {
    const conn = await mysql.createConnection(opts)
    const [rows, fields] = await conn.query('select 100 as result')
    console.log(rows)
    await conn.end()
}

run()

I have forked the repo on another computer but I was not able to commit due to a linting issue. Will show you the changes to the files you mentioned once I am able to commit and push my code.

[midnightcodr]

Pulling my hair out but I can't figure out even the simplest update I made can't pass pre-commit.

To replicate:

1. fork the repo

2. clone the forked repo to your local computer

3. make a simple change to, for example, lib/commands/client_handshake.js

4. git add and git commit, I got the problem in the first screenshot

[sidorares]

@midnightcodr which version of prettier gets installed after you do npm install? Need to double check if we use exact version numbers and/or package-lock

[midnightcodr]

@sidorares

package-lock.json: "version": "https://registry.npmjs.org/prettier/-/prettier-1.3.1.tgz", package.json: "prettier": "^1.3.1", node_modules/prettier/package.json: "version": "1.9.2"

Somehow the version installed (1.9.2) is different to what states in package (and -lock).json. More strangely I can't uninstall prettier by

npm uninstall prettier

Which returns

npm ERR! Cannot read property '0' of undefined

npm ERR! A complete log of this run can be found in:
npm ERR!     /Users/username/.npm/_logs/2018-01-05T18_09_13_219Z-debug.log

[nwohaibi]

@sidorares facing the same issue with empty pluginName string. MySQL 5.0.95 same code works with the 'mysql' module.

[sidorares]

@nwohaibi this is currently relatively low priority for me ( adding insecure 323 password ). Though if we can find reliable way to handle this via authPlugins that would be good. I'll need instructions to replicate this from scratch ( ideally image on docker hub? ) to progress on this