sidorares / nodejs-mysql-native

Native mysql async client for node.js

Deprecated . If you are using it, please consider migrating to node-mysql2

About

Mysql client module for node.js, written in JavaScript. No other mysql runtime required.

Install

npm install mysql-native

Community

Check out the google group http://groups.google.com/group/node-mysql-native for questions/answers from users of the driver.

Example

var db = require("mysql-native").createTCPClient(); // localhost:3306 by default
db.auto_prepare = true;
function dump_rows(cmd)
{
   cmd.addListener('row', function(r) { console.dir(r); } );
}

db.auth("test", "testuser", "testpass");
dump_rows(db.query("select 1+1,2,3,'4',length('hello')"));
dump_rows(db.execute("select 1+1,2,3,'4',length(?)", ["hello"]));
db.close();

output is: row: [ 2, 2, 3, "4", 5] row: [ 2, 2, 3, "4", 5]

Highlights

• commands are pipelined
• types are converted mysql<->javascript according to field type
• prepared statements are cached and auto-prepared
• row packet ( query ) and binary row packet ( execute ) handled transparently equal

#API

Module Functions

• createClient(socket) - create client from duplex stream (TODO: add default path to local server socket)
• createTCPClient(host, port) - create tcp client, default host 127.0.0.1, port 3306
• createUNIXClient(path) - connect to unix domain socket, default is /var/run/mysqld/mysqld.sock

Client Functions

All commands fire 'end'() event at the end of command executing.

• auth(db, user, pass) - perform mysql connection handshake. Should be always a first command (TODO: add default user/pass if missing?). Events: 'authorized'(serverStatus) event.

• query(sql) - simple query. Events: 'field'(field) - one for each field description 'fields_eof'() - after last field 'row'(rows) - array of field values, fired for each row in result set

• client.prepre(sql) - prepare a statement and store result in client.pscache Events: 'prepared'(preparedStatement) 'parameter'(field) - input parameter description

• execute(sql, parameters) - parameters is an array of values. Known types are sent in appropriate mysql binary type (TODO: currently this is not true, type is always string and input converted using param.toString() ) Events: same as with query()

• client.close - create and enqueue corresponding command

• client.execute also adds prepare command if there is no cached statement and client.auto_prepare set to true (TODO: add better api than client.auto_prepare flag)

• client.terminate - close conection immediately

TODO

• buffers

LINKS

MySql protocol documentation:

• http://dev.mysql.com/doc/internals/en/client-server-protocol.html

Other node.js mysql clients:

• http://github.com/felixge/node-mysql
• http://github.com/Sannis/node-mysql-libmysqlclient
• http://github.com/Guille/node.dbslayer.js

nodejs-mysql-native's Issues

rows_eof

        if (r.isEOFpacket())
        {
           this.emit('rows_eof');
           return 'done';
        }

replace sys.inherits with util.inherits

max_prepared_stmt_count

events.js:45
        throw arguments[1]; // Unhandled 'error' event
                       ^
Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)
    at cmd.process_packet (/Users/francis/node_modules/mysql-native/lib/mysql-native/command.js:35:15)
    at SocketClient.dispatch_packet (/Users/francis/node_modules/mysql-native/lib/mysql-native/socketclient.js:105:32)
    at Socket.<anonymous> (/Users/francis/node_modules/mysql-native/lib/mysql-native/socketclient.js:59:18)
    at Socket.emit (events.js:64:17)
    at Socket._onReadable (net.js:673:31)
    at IOWatcher.onReadable [as callback] (net.js:177:10)

how i can remove statements? i write currently a exporter script from mysql to redis, couchdb and cassandra... the database has 1Mio+ rows...

Missing methods?

mysql.js doesn't implement createClient() and auth(), so the example doesn't work. Is this project still active?

Weird bug with prepare statement

I ran into a weird problem with prepared statements using the current tip.

The following code:

var db = require('mysql-native').createTCPClient('127.0.0.1');

db.auth('test', 'testuser', 'testpass').on('authorized', function(status) {
    console.log('connect.');
});

db.prepare('SELECT ?').on('prepared', function() {
    db.execute('SELECT ?', [1]).on('row', function(row) {
        console.log(row);
    }).on('end', function(cmd) {
        console.log('end execute');
    });
});

db.close();

Yields the error:

/usr/local/node/.npm/mysql-native/0.4.1-1-LINK-01a2e086/package/lib/mysql-native/commands/prepare.js:15
           if (this.connection.pscache[sql])
                                      ^
TypeError: Cannot read property 'SELECT ?' of undefined
    at cmd.start (/usr/local/node/.npm/mysql-native/0.4.1-1-LINK-01a2e086/package/lib/mysql-native/commands/prepare.js:15:39)
    at cmd.process_packet (/usr/local/node/.npm/mysql-native/0.4.1-1-LINK-01a2e086/package/lib/mysql-native/command.js:40:58)
    at SocketClient.dispatch_packet (/usr/local/node/.npm/mysql-native/0.4.1-1-LINK-01a2e086/package/lib/mysql-native/socketclient.js:99:32)
    at SocketClient.dispatch_packet (/usr/local/node/.npm/mysql-native/0.4.1-1-LINK-01a2e086/package/lib/mysql-native/socketclient.js:103:14)
    at Stream.<anonymous> (/usr/local/node/.npm/mysql-native/0.4.1-1-LINK-01a2e086/package/lib/mysql-native/socketclient.js:55:18)
    at Stream.emit (events:27:15)
    at IOWatcher.callback (net:484:33)
    at node.js:773:9

The error disappears if the on('prepared') is removed and the code changed to the following:

var db = require('mysql-native').createTCPClient('127.0.0.1');

db.auth('test', 'testuser', 'testpass').on('authorized', function(status) {
    console.log('connect.');
});

db.prepare('SELECT ?');
db.execute('SELECT ?', [1]).on('row', function(row) {
    console.log(row);
}).on('end', function(cmd) {
    console.log('end execute');
});


db.close();

Prepared_stmt_count exhausted by trivial test case.

Hi Sidorares,

When the code below is run on [email protected], [email protected], "mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1" I encounter the following bug:

After 8k executions of "fooFindOrCreate" I get the error: {"field_count":255,"errno":1461,"message":"Can't create more than max_prepared_stmt_count statements (current value: 16382)"}

If I watch the value of Prepared_stmt_count in MySQL, using "mysqladmin extended-status" I see the value of Prepared_stmt_count increase by two every time fooFindOrCreate is invoked.

Does any else experience a similar problem? Is this a bug, or am I using the API incorrectly?

The minimal source below is sufficient to recreate the issue, though you'll need to create a database and table with the required columns and at least one matching row.

All the best,

Chris.

P.S. If there's anything more I can do to help resolve this, please let me know.
P.P.S. You can work-around this problem by reconnecting after every few queries, but this is not a longterm solution.

#!/usr/local/bin/node

var sys = require('sys');
var net = require('net');
var my = require('mysql-native')

var db = my.createTCPClient("127.0.0.1", 3306);
db.auto_prepare = true;
db.auth("dbname", "username", "password");
db.addListener('connect', function() {
    console.log("connection connected");
}); 
db.addListener('end', function() {
    console.log("connection ended");
}); 
db.addListener('error', function() {
    console.log("connection error");
});
db.addListener('close', function() {
    console.log("connection closing");
});

setInterval(function() { fooFindOrCreate("5421"); }, 10);

function fooFindOrCreate(callsign) {
    var sql = "SELECT foo_ptr_id "
            + "FROM resource "
            + "WHERE callsign = ? "
            + ";"
            ;
    console.log("sql", sql, callsign);
    //system.exit(1);
    var query = db.execute(sql, [callsign]) ; 
    var foo_id = false;
    query.addListener('row', function(row) {
        console.log("row: " + JSON.stringify(row));
        foo_id = row[0];
    });
    query.addListener('end', function() {
        if (foo_id) {
            console.log("query completed, foo_id: " + foo_id);
        } else {
            console.log("query returned no results");
        }
    });
    query.addListener('error', function(err) {
        console.log("query returned an error", err);
    });
}

Event done on query not fired

The 'done' event is called 'end' on query function.

if connection closed during command execution nothing happens

expected behavior:

• if result callback present, call it with error
• emit command 'end' event
• emit 'error' in connection opbject

BIGINT values are truncated to 32 bits.

I have a database with BIGINT fields (millisecond timestamps). Writing these fields works fine, but reading them results in the values being truncated to 32 bits.

e.g. 1313482901000 is read as 3517875720

Thanks,

Chris.

run tests without expresso

if expresso is not installed run tests from simple expresso wrapper

SMALLINT support.

Reading SMALLINT fields is not yet implemented.

null values in results are 'null'

"select null" returns null as a string and not as a real null. makes null values pretty useless

Add compression support

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Compression

https://github.com/joyent/node/wiki/modules#compression

how do I check the empty result

I use query.on('row') to get the result.But if the result is empty,then how can I know it?
Because NodeJS is Asynchronous execution,I can't just write as this:
db.query("").on('row',function(r){
});
res.write('error');

So what I should do instead of?

make all tests working on fresh box without extra setup

when i use pool console logs and then break

..........................
queue event 0
waiters: true
queue event 0
waiters: true
queue event 0
waiters: true
queue event 0
waiters: true
queue event 0
waiters: true
queue event 0
waiters: true
queue event 0
waiters: true

buffer.js:236
if (!isFinite(offset)) {
^
undefined

Typo in documentation

it's not client.prepre(sql) but client.prepare(sql) instead

NULL use, unicode fails

On the table structure you can assign default values, if I use NULL as default and there is some row filled with 'NULL', the query function fails in fetching the fields after the NULL field.

As you can see:
19 Jun 18:29:11 - [ '2D' (varchar)
, '1,2,3,4,5,6,7' (set)
, '[{"device":"2A","action":"off","timer":5}]' (mediumtext)
, 1 (tinyint)
, 0 (tinyint)
, 0 (smallint)
, 0 (smallint)
]

19 Jun 18:29:11 - [ '2D' (varchar)
, '1,2,3,4,5,6,7' (set)
, '\u00011\u00011\u00041800\u00041830' (... fails, because mediumtext is filled with NULL)
]

Temp fix: fill it with an empty string, but I can imagine that some people use NULL frequently, so fixing it would be nice anyway :-)

MySql server-side protocol support

api:

var options = {
    // charset
    // server capabilities - ssl, gzip etc
};

var handlers = {
    query: function(query, resp)
    {
          resp.writeField({ ... });
          resp.writeField({ ... });
          resp.writeField({ ... });
          resp.writeField({ ... });
          resp.writeRow([1, 2, 3, null, 5]); // 5 is trimmed because we sent only 4 fields
          resp.writeRow([1, 2]); // 1, 2, null, null - padded with two nulls
          resp.end({}); // send mysql OK packet with result object: fields count, insertId etc
   }
   , prepere = function(query, resp)
   {} //
   , execute = function(id, resp)
   {} //
   , ping = function(resp)
   {} //
   , status = function(resp)
};

require('mysql-native').createServer(handlers, options).listen('/tmp/mysqljs.sock');

• default handlers (empty query reply, fixed id=0 prepare reply etc - the goal is to have working non-hanging, non out-oforder packets do-nothing server without user handlers)

crash when handling texts

Hey there,

I think I found a bug.
When using a table with varchar(>255) or text the select query crashes at commands.js 264:
/Users/prototype/Projects/sequelize/lib/nodejs-mysql-native/commands.js:266
var value = string2type(r.lcstring(), field.type); // todo: mo
^
TypeError: Cannot read property 'type' of undefined
at cmd.data (/Users/prototype/Projects/sequelize/lib/nodejs-mysql-native/commands.js:266:60)
at cmd.process_packet (/Users/prototype/Projects/sequelize/lib/nodejs-mysql-native/commands.js:144:43)
at [object Object].dispatch_packet (/Users/prototype/Projects/sequelize/lib/nodejs-mysql-native/client.js:115:37)
at Stream. (/Users/prototype/Projects/sequelize/lib/nodejs-mysql-native/client.js:161:28)
at Stream.emit (events:26:26)
at IOWatcher.callback (net:507:33)
at node.js:267:9

You seem to expect that every column has the same 'length', so that every iteration of while (!r.eof()) is excactly one column. This seems to be not the case if columns are 'longer' than 255 chars.

Any idea how and when you fix this?

Charset Problem

Hi~ I have a problem on displaying chinese.

The data is shown as "???".

I used db.set("charset","utf8"); and found a mistake in charset.js.

exports.Charset.by_name = function(n) {
  for(var i=0; i<charset.length; ++i) {
      if(n==charset[i][2]) // IMPORTANT Here. the array index 2 should be 1. see the charset map.
return(new exports.Charset(charset[i][0], charset[i][1], charset[i][2]));
  }
  return undefined;
}

I make sure the Charset take effort, but the displaying problem is still alive.

Can anyone help me?

'result' object no longer includes 'insert_id' (in master, regression since 0.4.1?)

In [email protected] there is an 'insert_id' property on the 'result' object which is given as the first parameter to the 'end' listener of a query.

In mysql-native master there is no such parameter on the result object.

Has the API changed, or is this a bug?

Boolean values in prepared statements break all following values

In any prepared query once a boolean value is encountered all following values in the array are ignored.

https://gist.github.com/2255412

Here is my test that demonstrates the problem. You should only need to modify the db.auth, it uses a temporary table.

accessing database without password

I'd like to see support for non-password-login. Setting no password always gives me:

{ field_count: 255
, errno: 1045
, message: 'Access denied for user 'root'@'localhost' (using password: YES)'
}

Handle socket errors

It seems like all socket errors are not being handled.

This example calling code:

mysqlClient.query(params.query)
    .on('error', function(err) {
        sendError(res, err)
    })
    .on(....)

ended up with this stack trace:

Error: Socket is not writable
    at Socket._writeOut (net.js:391:11)
    at Socket.write (net.js:377:17)
    at cmd.write (code/node_modules/mysql/lib/mysql-native/command.js:61:19)
    at cmd.start (code/node_modules/mysql/lib/mysql-native/commands/query.js:107:12)
    at cmd.process_packet (code/node_modules/mysql/lib/mysql-native/command.js:45:58)
    at SocketClient.dispatch_packet (code/node_modules/mysql/lib/mysql-native/socketclient.js:105:32)
    at SocketClient.add (code/node_modules/mysql/lib/mysql-native/socketclient.js:123:14)
    at SocketClient.query (code/node_modules/mysql/lib/mysql-native/socketclient.js:40:21)
    at Server.<anonymous> (code/graphs/scripts/run-server.js:31:15)

My best guess is that we should be checking in command.js

if (!connection.write) {
    listen for connection 'drain' event and then keep writing
}

At least the 'error' event should bubble up all the way to the client API, rather than crashing the process.

passing null parameters does not work correctly

see tests/test_null_param.js

Problem with SUM in mysql query

Hi, I dont know if it is a bug or Im doing something wrong.
Here is my code:

var db = require("mysql-native").createTCPClient();
db.auto_prepare = true;
db.auth("database", "user", "pass");

If I try to execute this:
var res = db.query(" SELECT id FROM users WHERE x = 'y' ");
everything is ok, but if I try
var res = db.query(" SELECT SUM(id) FROM users WHERE x = 'y' ");
there is an error such as:

.......\mysql-native\lib\mysql-native\commands\query.js:94
return type_parsers[t](str, t);
^
TypeError: Property '246' of object # is not a function

These queries are made up, but should give you the main idea. The sql queries are valid and execute properly, when executed manualy through mysql client.

So .. is it a bug or a noob ? :)

Documentation typo error

In your readme, you specify that the auth command has an event name authirized however in the code the event name is authorized.

Date handling

Hey guys...
The lib currently can't read dates from mysql. When do u expect to implement that feature?
Thanks a lot
sdepold

Query finished event

When executing a db.query function and reading by rows, it's not easy to tell when the query is finished. If you're loading row by row and other executions are waiting on the loading to be complete, then that is an issue. I edited query.js to emit an event when it is complete, now i can load row by row, but still emit an event when the rows are done, see: this.emit("finished"); I think it's a pretty useful event to add. Thanks.

data: function( r )
    {
        if (r.isEOFpacket())
        {
            var eof = r.readOKpacket();
            if (eof.server_status & 8) // SERVER_MORE_RESULTS_EXISTS
            {
                return 'rs_ok';
            }
            this.emit('finished');
            return 'done';
        }

When will having the current pool feature?

I like pooling feature
I hope to have this feature and hope it complete soon.

About Asynchronous

Is it possible when I code like this:
db.query("use one");
db.query("select * from ...");

and another place:
db.query("use two");
db.query("select * from ...");

there will be some mistakes about asynchronous?

problems about charset

I have some problems about using charset-set. And after study,I found two bugs:

1.execute.js isn't support utf8;

2.charset.js isn't checking whether the str isn't string(for example, number)

And I have fix these bugs and commit here: nodejs-mysql-native

Data representation issue demonstrable using LEAST() or (x > 0).

I've come across a problem when using LEAST() through mysql-native.

I have reproduced the behaviour in a testcase (below).

The query runs without any issues on the MySQL command line and produces:

+-------------+-----+
| reason_zero | num |
+-------------+-----+
|           0 |   1 |
|           1 |   6 |
+-------------+-----+

The program below gives:

mysql connected
result: { insert_id: undefined,
  affected_rows: undefined,
  rows: 
   [ { reason_zero: 4294967296, num: NaN },
     { reason_zero: 25769803777, num: NaN } ] }

If I use the commented-out reason AS reason_zero,, mysql-native works (but that is not the query I need to run).

If I use the commented-out (reason > 0) AS reason_zero,, I get the same failure mode.

I'm using the latest mysql-native (from GitHub). (git pull told me Updating 2b4f1ba..7677112.)

Thanks,

Chris.

P.S. Changing the column types in the database from bigint and smallint to int does not fix the issue.

/*
 This is a minimal test case for issue https://github.com/sidorares/nodejs-mysql-native/issues/54
*/
var my = require("mysql-native");
var sql = 'SELECT '
        //+ 'reason AS reason_zero, '
        //+ '(reason > 0) AS reason_zero, ' // FIXME: why doesn't this work?
        + 'LEAST(reason, 1) AS reason_zero, ' // FIXME: why doesn't this work?
        + 'COUNT(*) AS num '
        + 'FROM event_bearer_ip '
        + 'GROUP BY reason_zero ' 
        ;

var db = my.createTCPClient('127.0.0.1', 3306);
db.auto_prepare = true;

var db_name = "test_db";
var username = "test_user";
var password = "test_password";
db.auth(db_name, username, password);
db.addListener('connect', function() {
  console.info("mysql connected");
  var query = db.execute(sql, []);
  query.on('result', function(result) {
    console.info("result:", result);
    process.exit(0);
  });
  query.on('error', function(err) {
    console.warn("err:", err);
    process.exit(1);
  });
});

/*
-- MySQL dump 10.13  Distrib 5.1.49, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: test_db
-- ------------------------------------------------------
-- Server version   5.1.49-1ubuntu8.1-log
--
-- Table structure for table `event_bearer_ip`
--

DROP TABLE IF EXISTS `event_bearer_ip`;
CREATE TABLE `event_bearer_ip` (
  `id` bigint(20) unsigned NOT NULL,
  `reason` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Bearer used for IP';

--
-- Dumping data for table `event_bearer_ip`
--

LOCK TABLES `event_bearer_ip` WRITE;
INSERT INTO `event_bearer_ip` VALUES (2530,1006),(2834,1006),(37177,1009),(60652,1009),(647439,103),(647650,0),(762547,1008);
UNLOCK TABLES;
*/

Result as array of Objects [feature request]

My own bicycle for handy resultset, where builder is mysql.query(...), cb - callback.

function mysql_result(builder, cb)
{
var field_template = new Array;
var results = new Array;

      builder
          .on('field', function(f) { field_template[field_template.length] = f.name; }) 
          .on('row', function(r) {   

              var m = new Object;

              for(key in r)
              {
                 value = r[key];
                 m[field_template[key]] = value;
              }

              results.push(m);
          }) 
          .on('end', function() { cb(results) });
}

Pool timeout issue

Hello,

test_pool.js does not pass the test. If I change value of "this.idleTimeout=0" ( at pool.js:16) to 1, it pass.

I guess there is issue with idleTimeout property.

I like pooling feature and hope it complete soon.

MySQL DATETIME fields are 'plus one month'.

This bug is present in my recent fork, but I haven't confirmed it in master here,

MySQL DATETIME fields become Javascript Dates, which are one month in advance of the value in the database.

This looks like a trivial bug related to Javascript's zero-based months.

If someone can point me at the code which makes the Javascript Dates, I'll go and fix it.

All the best,

Chris.

Can't create more than max_prepared_stmt_count statements (current value: 16382)

I get this error when I leave my app running for 10 minutes. During this time it probably does a few thousand queries.

auto_prepare = true;.

I'm using NodeJS 2.6 and [email protected]

Is anyone else experiencing a similar issue?

Thanks,

Chris.

Documentation

This library has no documentation at all. If it weren't for the reverse engineering of code I would never have gotten this to work. The reason this may be better than nodejs-mysql but has less adoption is because you can't figure out how to use it without reading the source, which is not commented. The names are not always very verbose either. If you want people to adopt and contribute then make simple guides. There are places in the issues in which you yourself didn't remember that escape is in the library. Just spend a few hours and document or heavily comment your code. Make it more user friendly and it would receive worlds more use and praise. As it is its a nightmare to get going and try to interpret what you did in the code.

'end' emitted twice for execute

The 'end' callback is triggered twice for execute statements. A simple demo:

var db = require('mysql-native').createTCPClient('127.0.0.1');

db.auth('test', 'testuser', 'testpass').on('authorized', function(status) {
    console.log('connect.');
});

db.prepare('SELECT ?').on('end', function() {
    console.log('end prepare');
});

db.execute('SELECT ?', [1]).on('row', function(row) {
    console.log(row);
}).on('end', function(cmd) {
    console.log('end execute');
});

db.close();

Expected output:
connect.
end prepare
{ '0': '1' }
end execute

Actual output:
connect.
end prepare
{ '0': '1' }
end execute
end execute

This did not happen with the version that's current on npm but happens with the trunk version.

getting id of inserted entries?

Is there a way I can execute an insert query and get the id of the new entry in return? or do I have to make a select query after that?

UTF-8 characters not stored

Persevere uses the execute command to retrieve and store data, but it does not store UTF-8 data. I can follow UTF-8 data up to executeSql being called, but by the time the record reaches 'binrow', the UTF-8 data is truncated up to the first non-ascii char. I don't know enough about nodejs to fix this myself, but I cannot quite understand why a mysql connector in this day and age is not capable of storing UTF-8 data. Can this be fixed please?

sql query escaping function

First of all, great job!

However, I think one feature is missing: a function to escape special characters of mysql in order to safely place user input / other unprocessed information into the query. Almost in all the other implementations (not JS ones) such a function is provided, for example mysql_real_escape_string in php (or currently mysql_escape_string).

Sorry to bother you guys. :)

switch to new createConnection/addConnection api

• keep old createTCPClient (with deprecated message?)
• documentation!!!

implement COM_CLOSE_STMT command

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#COM_CLOSE_STMT

see also #38

Multibyte characters can't be saved correctly.

Hello there,

There is a multibyte chacactors problem in 'nodejs-mysql-native'.

See this test fails ngs-archives/nodejs-mysql-native@24a3a89

When tried to insert records with multibyte values like the below, multibyte charactors cannot be saved correctly.
https://gist.github.com/839181#file_test_mysql_native.js

better error handling for unsupported field types

currently unsupported field type result in an error similar to "TypeError: Property '246' of object # is not a function"
where 246 is type ID (here - MYSQL_TYPE_NEWDECIMAL). Need to have better handling for unsupported types (or make default converter or even better add support for all current types)

create connection using url-encoded hostname,user/passwd etc

see code in the Postgres lib https://github.com/creationix/postgres-js/blob/master/lib/postgres-pure.js#L222

need to decide how to distinguish clearly based on arguments set
createConnection(port, host, {args}); // host default to localhost, args default to empty user/pwd/db
createConnection(socketpath, {args}); // /var/run/mysql.sock, { user: 'testuser:, password: '123', database: 'testdb' }
createConnection(url); // root:[email protected]:3307/mydatabase

about execute

How can I use (?) in execute?

I just write like that:

db.execute('UPDATE '+config.userTableName+' SET (?) = (?) WHERE name= (?)',[escape(key),escape(value),escape(user)]);

But it's failed. There are two types: one is normal and the other needs to add "" to indicate it's a string in mysql. How can I write it using (?)?

implement secure connection

if server support secure connection, client should be able to negotiate and switch to secure channel.

Upgrade a regular net.Stream connection to a secure tls connection: https://gist.github.com/848444
http://stackoverflow.com/questions/5146666/starttls-on-node-js-0-4-0

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Encryption

If the server advertises SSL (aka TLS) capability by setting the CLIENT_SSL flag in the initial greeting packet, then the client can request that the connection be encrypted.
For an encrypted connection, the client first sends an abbreviated client authentication packet containing only the flags word, with the CLIENT_SSL bit set. Without waiting for a server response, the client then begins the TLS handshake (starting with the ClientHello message). Once the SSL/TLS session has been established, the client sends the full client authentication packet over the newly established channel (including the flags field again, as well as the remaining authentication fields). The server responds to this client authentication packet as usual.