Node.js Connection Options

Connection Options

Essential Options

OptionDescriptionTypeDefault
userUser to access database.string
passwordUser password.string
hostIP address or DNS of database server. Not used when using the socketPath option.string"localhost"
portDatabase server port number.integer3306
databaseDefault database to use when establishing the connection.string
socketPathPermit connecting to the database via Unix domain socket or named pipe, if the server allows it.string
compressCompress exchanges with database using gzip. This can give you better performance when accessing a database in a different location.booleanfalse
connectTimeoutConnection timeout in milliseconds.integer10 000
socketTimeoutSocket timeout in milliseconds after the connection is established.integer0
rowsAsArrayReturn resultsets as array, rather than a JSON object. This is a faster way to get results. For more information, see the Promise and Callback query implementations.booleanfalse

Big Integer Support

Integers in JavaScript use IEEE-754 representation. This means that Node.js cannot exactly represent integers in the ±9,007,199,254,740,991 range. However, MariaDB does support larger integers.

This means that when the value set on a column is not in the safe range, the default implementation receives an inexact representation of the number.

The Connector provides two options to address this issue.

OptionDescriptionTypeDefault
bigNumberStringsWhen an integer is not in the safe range, the Connector interprets the value as a stringboolean.false
supportBigNumbersWhen an integer is not in the safe range, the Connector interprets the value as a Long object.booleanfalse

SSL

The Connector can encrypt data during transfer using the Transport Layer Security (TLS) protocol. TLS/SSL allows for transfer encryption, and can optionally use identity validation for the server and client.

The term SSL (Secure Sockets Layer) is often used interchangeably with TLS, although strictly-speaking the SSL protocol is the predecessor of TLS, and is not implemented as it is now considered insecure.

There are two different kinds of SSL authentication:

  • One-Way SSL Authentication: The client verifies the certificate of the server. This allows you to encrypt all exchanges and make sure that you are connecting to the expected server (to avoid a man-in-the-middle attack).
  • Two-Way SSL Authentication The client verifies the certificate of the server, the server verifies the certificate of the client. This is also called mutual authentication or client authentication. When using this system, the client also requires a dedicated certificate.

Server Configuration

In order to use SSL, you need to ensure that the MariaDB Server is correctly configured. You can determine this using the have_ssl system variable.

SHOW VARIABLES LIKE 'have_ssl';

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+

A value of NO indicates that MariaDB was compiled without support for TLS. DISABLED means that it was compiled with TLS support, but it's currently turned off. In order to use SSL with the Connector, the server must return YES, indicating that TLS support is available and turned on. For more information, see the MariaDB Server documentation.

User Configuration

Enabling the ssl option on the server, the Connector uses one-way SSL authentication to connect to the server. Additionally, it's recommended that you also configure your users to connect through SSL. This ensures that their accounts can only be used with an SSL connection.

For GRANT statements, use the REQUIRE SSL option for one-way SSL authentication and the REQUIRE X509 option for two-way SSL authentication. For more information, see the CREATE USER documentation.

CREATE USER 'johnSmith'@'%' IDENTIFIED BY PASSWORD('passwd');
GRANT ALL ON company.* TO 'johnSmith'@'%' REQUIRE SSL;

Now when this user attempts to connect to MariaDB without SSL, the server rejects the connection.

Configuration

  • ssl: boolean/JSON object.

JSON object:

OptionDescriptionTypeDefault
checkServerIdentityfunction(servername, cert) to replace SNI default functionFunction
minDHSizeMinimum size of the DH parameter in bits to accept a TLS connectionnumber1024
pfxOptional PFX or PKCS12 encoded private key and certificate chain. Encrypted PFX will be decrypted with passphrase if provided*string / string[] / Buffer / Buffer[] / Object[]
keyOptional private keys in PEM format. Encrypted keys are decrypted with passphrase if provided*string / string[] / Buffer / Buffer[] / Object[]
passphraseOptional shared passphrase used for a single private key and/or a PFXstring
certOptional cert chains in PEM format. One cert chain should be provided per private keystring / string[] / Buffer / Buffer[]
caOptionally override the trusted CA certificates. Default is to trust the well-known CAs curated by Mozilla. For self-signed certificates, the certificate is its own CA, and must be providedstring / string[] / Buffer / Buffer[]
ciphersOptional cipher suite specification, replacing the defaultstring
honorCipherOrderAttempt to use the server's cipher suite preferences instead of the client'sboolean
ecdhCurveA string describing a named curve or a colon separated list of curve NIDs or names, for example P-521:P-384:P-256, to use for ECDH key agreement, or false to disable ECDH. Set to auto to select the curve automaticallystringtls.DEFAULT_ECDH_CURVE
clientCertEngineOptional name of an OpenSSL engine which can provide the client certificatestring
crlOptional PEM formatted CRLs (Certificate Revocation Lists)string / string[] / Buffer / Buffer[]
dhparamDiffie Hellman parameters, required for Perfect Forward Secrecystring / Buffer
secureProtocolOptional SSL method to use, default is "SSLv23_method"string

The Connector uses the Node.js implementation of TLS. For more information, see the Node.js TLS API documentation.

Certificate Validation

Trusted CA

By default, Node.js trusts the well-known root Certificate Authorities (CA), based on Mozilla. For a complete list, (including the popular and free Let's Encrypt), see the CA Certificate List.

When using a certificate signed with a certificate chain from a root CA known to Node.js, the only configuration you need to do is enable the ssl option.

Certificate Chain Validation

A certificate chain is a list of certificates that were issued from the same Certification Authority hierarchy. In order for any certificate to be validated, all certificates in the chain have to be validated.

In cases where intermediate or root certificates are not trusted by the Connector, the Connector rejects the connection and issues an error.

Hostname Verification (SNI)

Certificates can provide hostname verification to the driver. By default this is done against the certificate's subjectAlternativeName DNS name field.

One-way SSL Authentication

When the server certificate is signed using the certificate chain that uses a root CA known in the JavaScript trust store, setting the ssl option enables one-way SSL authentication.

For instance,

const mariadb = require('mariadb');
mariadb
 .createConnection({
   host: 'myHost.com', 
   ssl: true, 
   user: 'myUser', 
   password:'MyPwd', 
   database:'db_name'
 }).then(conn => {})

When the server uses a self-signed certificate or uses an intermediate certificate, there are two different possibilities:

In non-production environments, you can tell the Connector to trust all certificates by setting rejectUnauthorized to false. Do NOT use this in production.

//connecting
mariadb
 .createConnection({
   host: 'myHost.com', 
   ssl: {
	 rejectUnauthorized: false
   }, 
   user: 'myUser', 
   password:'MyPwd', 
 }).then(conn => {})

A more secure alternative is to provide the certificate chain to the Connector.

const fs = require("fs");
const mariadb = require('mariadb');

//reading certificates from file
const serverCert = [fs.readFileSync("server.pem", "utf8")];

//connecting
mariadb
 .createConnection({
   user: "myUser",
   host: "myHost.com",
   ssl: {
	 ca: serverCert
   }
 }).then(conn => {})

Using Specific TLS Protocols or Ciphers

In situations where you don't like the default TLS protocol or cipher or where you would like to use a specific version, you force the Connector to use the one you want using the secureProtocol and cipher options.

For instance, say you want to connect using TLS version 1.2:

//connecting
mariadb
 .createConnection({ 
   user:"myUser", 
   host: "myHost.com",
   ssl: {
	 ca: serverCert,
	 secureProtocol: "TLSv1_2_method",
	 ciphers:
	   "ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256"        
   }
 }).then(conn => {})

For more information on what's available, see possible protocol values.

Two-way SSL Authentication

Mutual SSL authentication or certificate-based mutual authentication refers to two parties authenticating each other by verifying the provided digital certificates. This allows both parties to be assured of the other's identity. In order to use mutual authentication, you must set the REQUIRE X509 option in the GRANT statement. For example,

GRANT ALL ON company.* TO 'johnSmith'@'%' REQUIRE X509;

This option causes the server to ask the Connector for a client certificate. If the user is not set with REQUIRE X509, the server defaults to one-way authentication

When using mutual authentication, you need a certificate, (and its related private key), for the Connector as well as the server. If the Connector doesn't provide a certificate and the user is set to REQUIRE X509, the server returns a basic Access denied for user message.

In the event that you would like to see how users are defined, you can find this information by querying the mysql.user table on the server. For instance, say you wanted information on the johnSmith user.

SELECT ssl_type, ssl_cipher, x509_subject 
FROM mysql.user
WHERE User = 'johnSmith';

You can test it by creating a user with REQUIRE X509 for testing:

CREATE USER 'X509testUser'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'X509testUser'@'%' REQUIRE X509;

Then use its credentials in your application:

const fs = require("fs");
const mariadb = require('mariadb');

//reading certificates from file
const serverCert = [fs.readFileSync("server.pem", "utf8")];
const clientKey = [fs.readFileSync("client.key", "utf8")];
const clientCert = [fs.readFileSync("client.pem", "utf8")];

//connecting
mariadb
 .createConnection({ 
   user:"X509testUser", 
   host: "mariadb.example.com",
   ssl: {
	 ca: serverCert,
	 cert: clientCert,
	 key: clientKey
   }
 }).then(conn => {})

Using Keystores

Keystores allow you to store private keys and certificate chains encrypted with a password to file. For instance, using OpenSSL you can generate a keystore using PKCS12 format:

$ openssl pkcs12 \
	-export \
	-in "${clientCertFile}" \
	-inkey "${clientKeyFile}" \
	-out "${keystoreFile}" \
	-name "mariadbAlias" \
	-passout pass:kspass

You can then use the keystore in your application:

const fs = require("fs");
const mariadb = require('mariadb');

//reading certificates from file (keystore must be read as binary)
const serverCert = fs.readFileSync("server.pem", "utf8");
const clientKeystore = fs.readFileSync("keystore.p12");

//connecting
mariadb.createConnection({ 
 user:"X509testUser", 
 host: "mariadb.example.com",
 ssl: {
   ca: serverCert,
   pfx: clientKeystore,
   passphrase: "kspass"
 }
}).then(conn => {});

Other Options

OptionDescriptionTypeDefault
charsetProtocol character set used with the server. It's mainly used for micro-optimizations. The default is often sufficient.stringUTF8MB4_UNICODE_CI
dateStringsWhether to retrieve dates as strings or as Date objects.booleanfalse
debugLogs all exchanges with the server. Displays in hexa.booleanfalse
foundRowsWhen enabled, the update number corresponds to update rows. When disabled, it indicates the real rows changed.booleantrue
multipleStatementsAllows you to issue several SQL statements in a single quer() call. (That is, INSERT INTO a VALUES('b'); INSERT INTO c VALUES('d');).

This may be a security risk as it allows for SQL Injection attacks.
booleanfalse
namedPlaceholdersAllows the use of named placeholders.booleanfalse
permitLocalInfileAllows the use of LOAD DATA INFILE statements.

Loading data from a file from the client may be a security issue, as a man-in-the-middle proxy server can change the actual file the server loads. Being able to execute a query on the client gives you access to files on the client.
booleanfalse
timezoneForces use of the indicated timezone, rather than the current Node.js timezone. Possible values are Z for UTC, local or ±HH:MM formatstring
nestTablesPresents resultsets by table to avoid results with colliding fields. See the query() description for more information.booleanfalse
pipeliningSends queries one by one without waiting for the results of the previous entry. For more information, see Pipeliningbooleantrue
traceAdds the stack trace at the time of query creation to the error stack trace, making it easier to identify the part of the code that issued the query. Note: This feature is disabled by default due to the performance cost of stack creation. Only turn it on when you need to debug issues.booleanfalse
typeCastAllows you to cast result types.function
connectAttributesSends information (client name, version, operating system, Node.js version, and so on) to the Performance Schema. When enabled, the Connector sends JSON attributes in addition to the defaults.boolean/jsonfalse
metaAsArrayCompatibility option, causes Promise to return an array object, [rows, metadata] rather than the rows as JSON objects with a meta property.booleanfalse

F.A.Q.

error Hostname/IP doesn't match certificate's altnames

Clients verify certificate SAN (subject alternative names) and CN to ensure that the certificate corresponds to the hostname. If the certificate's SAN/CN does not correspond to the host option, it returns an error such as:

Hostname/IP doesn't match certificate's altnames: "Host: other.example.com. is not cert's CN: mariadb.example.com"

To fix this, correct the host value to correspond to the host identified in the certificate.

Comments

Comments loading...