Skip to main content

Extra Features

Named placeholdersโ€‹

You can use named placeholders for parameters by setting namedPlaceholders config value or query/execute time option. Named placeholders are converted to unnamed ? on the client (mysql protocol does not support named parameters). If you reference parameter multiple times under the same name it is sent to server multiple times. Unnamed placeholders can still be used by providing the values as an array instead of an object.

connection.config.namedPlaceholders = true;
connection.execute('select :x + :y as z', { x: 1, y: 2 }, (err, rows) => {
// statement prepared as "select ? + ? as z" and executed with [1,2] values
// rows returned: [ { z: 3 } ]
});

connection.execute('select :x + :x as z', { x: 1 }, (err, rows) => {
// select ? + ? as z, execute with [1, 1]
});

connection.query('select :x + :x as z', { x: 1 }, (err, rows) => {
// query select 1 + 1 as z
});

// unnamed placeholders are still valid if the values are provided in an array
connection.query('select ? + ? as z', [1, 1], (err, rows) => {
// query select 1 + 1 as z
});

Expanding an object at any placeholderโ€‹

Never expand user-controlled keys

Object expansion turns keys into SQL identifiers. Untrusted keys let an attacker choose which columns a query reads or changes, exposing or overwriting data outside the intended operation.

Never pass a request body or another object with user-controlled keys directly to expand() or a SET ? assignment. Build an object with keys fixed by your code and validate user-supplied values before binding them.

query() and format() expand a bound plain object or Map into `key` = value pairs only inside a SET or ON DUPLICATE KEY UPDATE assignment list. Everywhere else, unmarked objects are converted to quoted strings, such as '[object Object]' for a plain object or '[object Map]' for a Map.

To expand one object at any ?, mark it with mysql.expand(). Only the marked value expands, and every other object in the statement keeps the default behavior:

import { createConnection, expand, format } from 'mysql2/promise';

const connection = await createConnection({/* your config */});

await connection.query('SELECT * FROM users WHERE ? AND status = ?', [
expand({ role: 'admin' }),
'active',
]);
// SELECT * FROM users WHERE `role` = 'admin' AND status = 'active'

format('?', [expand({ id: 1 })]);
// `id` = 1
  • mysql2/promise exports the same expand().

  • A mark expands with the stringifyObjects option enabled as well.

  • With named placeholders, pass the mark under its name. A lone object is read as the named values themselves:

    connection.query(
    { sql: 'SELECT * FROM users WHERE :filter', namedPlaceholders: true },
    { filter: mysql.expand({ role: 'admin' }) }
    );
    // SELECT * FROM users WHERE `role` = 'admin'
  • A mark nested inside an array, a Set, an object, or a Map does not expand. expand() rejects non-object values, null, arrays, Set instances, dates, Uint8Array instances (including buffers), raw() values, and existing marks with a TypeError.

  • A mark created by your own copy of SQL Escaper expands in the copy MySQL2 uses.

Prepared statements reject marked values with a TypeError, whether prepared automatically or explicitly. Use query() instead.

๐Ÿšซ Unsafe counterexampleโ€‹

SQL injection through unrestricted object expansion

Validate that an ID has the expected scalar type before binding it. Under unrestricted object expansion, the condition below matches every row instead of filtering by ID.

// `id` arrives as { id: true } from a JSON request body
connection.query('SELECT * FROM users WHERE id = ?', [id]);
// Unsafe unrestricted expansion:
// SELECT * FROM users WHERE id = `id` = true
// Current MySQL2 default:
// SELECT * FROM users WHERE id = '[object Object]'
  • Please, do not copy this pattern.

See the expand section of the SQL Escaper README for the full behavior.


Receiving rows as array of columns instead of hash with column name as key:โ€‹

const options = { sql: 'select A,B,C,D from foo', rowsAsArray: true };
connection.query(options, (err, results) => {
/* results will be an array of arrays like this now:
[[
'field A value',
'field B value',
'field C value',
'field D value',
], ...]
*/
});

Sending tabular data with 'load infile' and local stream:โ€‹

In addition to sending local fs files you can send any stream using infileStreamFactory query option. If set, it has to be a function that return a readable stream. It gets file path from query as a parameter.

Note: starting from version 2.0 infileStreamFactory is required parameter for LOAD DATA LOCAL INFILE. Response from server indicates that it wants access to a local file and no infileStreamFactory option is provided the query ends with error.

// local file
connection.query(
'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test FIELDS TERMINATED BY ? (id, title)',
onInserted1
);
// local stream
const sql =
'LOAD DATA LOCAL INFILE "mystream" INTO TABLE test FIELDS TERMINATED BY ? (id, title)';
connection.query(
{
sql: sql,
infileStreamFactory: function (path) {
return getStream();
},
},
onInserted2
);

The infileStreamFactory option may also be set at a connection-level:

const fs = require('fs');
const mysql = require('mysql2');

const connection = mysql.createConnection({
user: 'test',
database: 'test',
infileStreamFactory: (path) => {
// Validate file path
const validPaths = ['/tmp/data.csv'];
if (!validPaths.includes(path)) {
throw new Error(
`invalid file path: ${path}: expected to be one of ${validPaths.join(
','
)}`
);
}
return fs.createReadStream(path);
},
});

connection.query(
'LOAD DATA LOCAL INFILE "/tmp/data.csv" INTO TABLE test',
onInserted
);

Connecting using custom stream:โ€‹

const net = require('net');
const mysql = require('mysql2');
const shape = require('shaper');
const connection = mysql.createConnection({
user: 'test',
database: 'test',
stream: net.connect('/tmp/mysql.sock').pipe(shape(10)), // emulate 10 bytes/sec link
});
connection.query('SELECT 1+1 as test1', console.log);

stream also can be a function. In that case function result has to be duplex stream, and it is used for connection transport. This is required if you connect pool using custom transport as new pooled connection needs new stream. Below is an updated SOCKS5 proxy example using the modern API, where stream returns a stream or a Promise resolving to a stream.

const mysql = require('mysql2');
const { SocksClient } = require('socks');

const pool = mysql.createPool({
database: 'test',
user: 'foo',
password: 'bar',

// modern usage: stream returns a Duplex or a Promise that resolves to one
stream: async () => {
const { socket } = await SocksClient.createConnection({
proxy: {
host: 'localhost',
port: 1080,
type: 5,
},
destination: {
host: 'remote.host',
port: 3306,
},
});
return socket; // must be a Duplex stream
},
});

Note:

Older documentation used a callback-style API:

stream: function (cb) {
cb(null, stream);
}

This callback style no longer works on current versions of mysql2 and causes:

TypeError: cb is not a function

Use the modern return-style function shown above.


In addition to password createConnection(), createPool() and changeUser() accept passwordSha1 option.โ€‹

This is useful when implementing proxies as plaintext password might be not available.


Graceful pool connection endโ€‹

const pool = mysql.createPool({
gracefulEnd: true,
});

By default, calling end on a pool connection will result in a warning being emitted and the connection being released back to the pool. This is for compatibility with mysqljs/mysql. By providing gracefulEnd: true when creating the pool, calling end on a pooled connection will actually close the connection instead of releasing it back to the pool.