Database operation failed on mysql 8.0

[manyuanrong]

import { Client } from 'https://deno.land/x/mysql@1.2.3/mod.ts';
const config = {
  timeout: 10000,
  pool: 3,
  debug: true,
  hostname: '127.0.0.1',
  username: 'admin001',
  password: 'admin001'
};

let client = await new Client().connect(config);
await client.execute(`CREATE DATABASE mydemo;`);
await client.execute(`USE mydemo;`);
await client.execute(`
    CREATE TABLE user_info (
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(100) NOT NULL,
        is_top tinyint(1) default 0,
        created_at timestamp not null default current_timestamp,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`);
await client.close();
console.log('success!')

[manyuanrong]

Need to implement caching_sha2_password auth plugin

[magichim]

Need to implement caching_sha2_password auth plugin

https://github.com/manyuanrong/deno_mysql/blob/master/src/auth.ts#L40

It looks like a good working above code. Why do you remain inactive this code?

+------+-----------+-----------------------+
| User | Host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+

Just In plugin mysql_native_password case,
In parse function of packet.ts, this.body.buffer[0] has a 0xff(Error) on switch and then get message "error: Uncaught Error: Got packets out of order throw new Error(error.message)".

I tested it in mysql8.0.

@manyuanrong

[manyuanrong]

To achieve it requires multiple communications and server establishment negotiation, refer to #52

[wenjoy]

@magichim It's not a working code. I alos try it by uncommenting the code. SQL excution result will return in mess order.

[magichim]

@wenjoy Can I know your mysql version? It might be under v8.0. It's right?

[wenjoy]

@magichim It's 8.0.20. I'm also want to make it works on mysql 8.0, still in progress of learning on the new authentication of mysql.

[wenjoy]

@manyuanrong

This issue has been stale. Post code with version 1.2.3 encountered error:

error: Import 'https://deno.land/std@v0.17.0/strings/mod.ts' failed: 404 Not Found

And I tried to uncomment the cachingSha2Password, and there will be some mismatched issue that can be easily fixed I think. What I dont understand is u guys mentioned back and forth in #52.

I went through the official doc didn't see back and forth process specially for cachingSha2Password plugin. Or I missed something?

I'm note sure I resolve the problem u guys concerned. Let's talk a little more when u get some time.

[manyuanrong]

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

[manyuanrong]

@wenjoy If we don't use ssl connection, it seems that we need to get the public key from the server first.

[manyuanrong]

I did not explore in depth, but I can refer to https://github.com/sidorares/node-mysql2/blob/master/lib/auth_plugins/caching_sha2_password.js

[wenjoy]

@manyuanrong Thanks for refs.

You're right. I got the public key in unsecure context.

I digged slightly deeper, but I had to stop when try to encrypt the password with public key for lacks of such module in deno. At least I cant find a appropriate one.
I'm not sure whether going further to figure out it with rust module or waiting deno to implement it.

Any thoughts or suggestions?

ref: denoland/deno#1891

[manyuanrong]

@wenjoy I found the jsencrypt library on JSPM, but because it uses window.navigator, it cannot be used directly, I made some modifications to make it available to Deno. Maybe you can try to use it to temporarily replace crypto

https://gist.githubusercontent.com/manyuanrong/39f151181a193b454c3b11dac1b60e15/raw/1d5692e4556a1e59420fbfc80dee041e22e2ab34/jsencrypt.js

[wenjoy]

@manyuanrong Thanks. That helps a lot. I will continue with that.

[invisal]

Since we only need to encrypt password, I quickly wrote one here (https://github.com/invisal/god-crypto). You can use as the following

import { RSA } from "https://github.com/invisal/god-crypto/raw/master/mod.ts";

const publicKey = RSA.parseKey("public_key_that_mysql_send_to_you");
const xorPassword = xor(`${password}\0`, handshakePacket.seed);
await new SendPacket(RSA.encrypt(xorPassword, publicKey));

I tested it and it works.

Useful Information

• https://mariadb.com/kb/en/caching_sha2_password-authentication-plugin/
• https://github.com/sidorares/node-mysql2/blob/master/lib/auth_plugins/caching_sha2_password.js

[wenjoy]

@manyuanrong Thanks. I tried with jsencrypt, however it cant support oaep padding. I have to find other ways.

I do get two methods, one is bridge to rust's rsa crate, the other is a bundle of forge.

I choose the later as it has none business of platform difference at all.

And now seems we have extra option thanks @invisal . I would try to integrate that lib when I get time.

[invisal]

@wenjoy Nice job. If you want to integrate, you can simply change a few line of code and it will work.

In your src/auth_plugin/crypt.ts, You just change to the following code.

import { RSA } from "https://deno.land/x/god_crypto@v0.2.0/mod.ts";

function encryptWithPublicKey(key: string, data: Uint8Array): Uint8Array {
  const publicKey = RSA.parseKey(key);
  return RSA.encrypt(data, publicKey);
}

[wenjoy]

@invisal switched to god_crypto, works well under my test. Great work! 👍