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โ
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:
- Promise
- Callback
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
const mysql = require('mysql2');
const connection = mysql.createConnection({/* your config */});
connection.query('SELECT * FROM users WHERE ? AND status = ?', [
mysql.expand({ role: 'admin' }),
'active',
]);
// SELECT * FROM users WHERE `role` = 'admin' AND status = 'active'
mysql.format('?', [mysql.expand({ id: 1 })]);
// `id` = 1
-
mysql2/promiseexports the sameexpand(). -
A mark expands with the
stringifyObjectsoption 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 aMapdoes not expand.expand()rejects non-object values,null, arrays,Setinstances, dates,Uint8Arrayinstances (including buffers),raw()values, and existing marks with aTypeError. -
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โ
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.