Learn about the connection phase in the client/server protocol. This section details how clients establish initial communication with the server, including handshaking and authentication processes.
Connection is done by many exchanges:
(Create socket)
If first byte from server is 0xFF
:
Packet is an ERR_Packet, socket has to be closed.
Else:
Packet is an Initial handshake packet.
If SSL/TLS connection:
Client sends SSLRequest packet and switches to SSL mode for sending and receiving the following messages:
Client sends Handshake response packet.
Server sends either:
An OK
packet in case of success OK_Packet.
An error packet in case of error ERR_Packet.
Authentication switch:
If the client or server doesn't have PLUGIN_AUTH
capability:
Server sends 0xFE
byte.
Client sends old_password
.
Else:
Server sends Authentication switch request.
Client may have many exchange with the server according to the Plugin.
Authentication switch ends with server sending either OK_Packet or ERR_Packet.
int<1> protocol version.
string server version
MariaDB Server 10.X versions are by default prefixed "5.5.5-".
MariaDB 11.0 and later versions do not have a "5.5.5-" default prefix.
int<4> connection id.
string<8> authentication plugin data (1st part).
string<1> reserved byte.
int<2> server capabilities (1st part).
int<1> server default collation.
int<2> status flags.
int<2> server capabilities (2nd part).
If (server_capabilities
& PLUGIN_AUTH
):
int<1> plugin data length.
Else:
int<1> 0x00.
string<6> filler.
If (server_capabilities
& CLIENT_MYSQL
):
string<4> filler.
Else:
int<4> server capabilities 3rd part . MariaDB specific flags /* MariaDB 10.2 or later */
.
If (server_capabilities & PLUGIN_AUTH):
string authentication plugin name.
If the client requests a TLS/SSL connection, the first response is an SSL connection request packet, followed by a handshake response packet. If no TLS is required, client directly sends a handshake response packet.
int<4> client capabilities.
int<4> max packet size.
int<1> client's default character set and collation.
string<19> reserved.
If not (server_capabilities
& CLIENT_MYSQL
):
int<4> extended client capabilities
Else:
string<4> reserved.
Automatic Encrypted Connections (MariaDB 11.4+):
Previously, failed SSL connections due to self-signed certificates prevented communication. MariaDB 11.4+ introduces a secondary validation method that works for all servers.
Even without a valid SSL certificate, the connector can still authenticate by remembering the server's fingerprint (unique identifier). However, it needs to confirm the connection is secure.
The confirmation method depends on the connection type. When using secure MitM-proof methods, like Unix sockets, connector can automatically validate the connection. Otherwise, a shared secret is used.
The shared secret is only used if the authentication plugin password is hashable (for instance, mysql_native_password
, client_ed25519
, or parsec
) and not empty.
It's calculated by hashing the user's hash password with the authentication seed and the server fingerprint.
Password hash is generated depending on authentication plugin:
ed25519
: identical to password encryption.
mysql_native_password
: identical to password encryption.
parsec
: ext-salt + raw ed25519 public key.
For servers running MariaDB 11.4 or later, the final confirmation packet contains:
If the calculated shared secret matches the received one, the SSL connection is considered valid (host validation is not needed). Otherwise, the connection must be closed for security reasons.
int<4> client capabilities.
int<4> max packet size.
int<1> client's default character set and collation.
string<19> reserved.
If not (server_capabilities
& CLIENT_MYSQL
):
int<4> extended client capabilities.
Else:
string<4> reserved.
string username.
If (password):
Else:
string<1>\0 (empty password).
If (server_capabilities
& CLIENT_CONNECT_WITH_DB
):
string default database name.
If (server_capabilities
& CLIENT_PLUGIN_AUTH
):
string authentication plugin name.
The server responds with an OK_packet, an ERR_packet, or an Authentication Switch Request packet.
(If client and server support CLIENT_AUTH
capability):
int<1> 0xFE : Authentication switch request header.
string authentication plugin name.
byte authentication plugin data.
deprecated — send a 8 byte encrypted password.
Authentication plugin data format :
byte<8> 8-byte seed.
Client response:
string old format encrypted password.
Since password is transmitted in clear, this has be used only when using SSL connection
Send clear password to server.
Client response:
string password without encryption.
SHA-1 encrypted password with server seed.
Authentication plugin data format:
string seed.
Client response:
byte SHA1
-encrypted password.
The password is encrypted with: SHA1( password ) ^ SHA1( seed + SHA1( SHA1( password ) ) )
.
Interactive exchanges to permit fill passwords — for example for 2-step authentication.
Authentication plugin data format:
The server can send one or many requests. For each of them, the client must display this prompt message to the user, to permit the user to type requested information, then send it to the server in string format. Password type indicates the answer format (2
means "read the input with the echo enabled", 4
means "password-like input, echo disabled")
First authentication format (from authentication switch packet) can be empty.
This end when the server sends an EOF_Packet, OK_Packet or ERROR_packet.
GSSAPI implementation.
Authentication plugin data format :
Client must exchange packet with server until having a mutual GSSAPI authentication. The only difference compared to standard client-server GSSAPI authentication is that exchanges contain standard protocol with packet headers.
The ed25519
plugin uses the Elliptic Curve Digital Signature Algorithm to securely store users' passwords and to authenticate users.
See plugin description.
The server sends a random nonce that the client signs.
authentication plugin data format :
byte seed.
Client response :
byte ed25519
encrypted password.
Authentication plugin data format:
string<32> server nonce.
Client has to send an empty packet to request "ext-salt".
Format of ext-salt is:
string<1> 'P' (denotes KDF algorithm = PBKDF2).
byte<1> iteration factor. number of iterations correspond to 1024 << iteration factor (0x0 means 1024, 0x1 means 2048, etc.).
byte salt.
The client must then:
Generate derived key = hash password with PBKDF2 ( sha512 digest) with iteration number and salt from ext-salt.
Generate a client 32 bytes nonce.
Generate the signature with ed25519
of an array concatenation of server nonce + client nonce with the generated derived key as private key.
Client response:
Server and Client have different capabilities, here is the possibles values. client with capabilities CLIENT_MYSQL + CONNECT_WITH_DB will have a value of 9 (1 + 8).
CLIENT_MYSQL
1
Set by older MariaDB versions. MySQL named this CLIENT_LONG_PASSWORD
.
FOUND_ROWS
2
CONNECT_WITH_DB
8
You can specify database on connect.
COMPRESS
32
Can use compression protocol
LOCAL_FILES
128
Can use LOAD DATA LOCAL.
IGNORE_SPACE
256
Ignore spaces before (
.
CLIENT_PROTOCOL_41
1 << 9
4.1 protocol.
CLIENT_INTERACTIVE
1 << 10
SSL
1 << 11
Can use SSL.
TRANSACTIONS
1 << 13
SECURE_CONNECTION
1 << 15
4.1 authentication.
MULTI_STATEMENTS
1 << 16
Enable/disable multi-statement support.
MULTI_RESULTS
1 << 17
Enable/disable multi-results.
PS_MULTI_RESULTS
1 << 18
Enable/disable multi-results for PrepareStatement.
PLUGIN_AUTH
1 << 19
Client supports plugin authentication.
CONNECT_ATTRS
1 << 20
Client send connection attributes.
PLUGIN_AUTH_LENENC_CLIENT_DATA
1 << 21
Enable authentication response packet to be larger than 255 bytes.
CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
1 << 22
Client can handle expired passwords.
CLIENT_SESSION_TRACK
1 << 23
Enable/disable session tracking in OK_Packet
.
CLIENT_DEPRECATE_EOF
1 << 24
EOF_Packet
deprecation: OK_Packet
replace EOF_Packet
at the end of the result set when in text format. EOF_Packet
between columns definition and resultsetRows
is deleted.
CLIENT_OPTIONAL_RESULTSET_METADATA
1 << 25
Not in use for MariaDB.
CLIENT_ZSTD_COMPRESSION_ALGORITHM
1 << 26
Support zstd
protocol compression.
CLIENT_CAPABILITY_EXTENSION
1 << 29
Reserved for future use.
CLIENT_SSL_VERIFY_SERVER_CERT
1 << 30
Client verify server certificate. Deprecated, client has options to indicate if server certifiate must be verified.
CLIENT_REMEMBER_OPTIONS
1 << 31
MARIADB_CLIENT_PROGRESS
1 << 32
Client support progress indicator.
MARIADB_CLIENT_COM_MULTI
1 << 33
Permit COM_MULTI
protocol.
MARIADB_CLIENT_STMT_BULK_OPERATIONS
1 << 34
Permit bulk insert.
MARIADB_CLIENT_EXTENDED_METADATA
1 << 35
Add extended metadata information.
MARIADB_CLIENT_CACHE_METADATA
1 << 36
Permit skipping metadata.
MARIADB_CLIENT_BULK_UNIT_RESULTS
1 << 37
When enabled, indicate that bulk command can use STMT_BULK_FLAG_SEND_UNIT_RESULTS
flag that permits to return a result set of all affected rows and auto-increment values.
The 20 byte string 'seed' is calculated by concatenating scramble first part (8 bytes) and scramble second part from Initial handshake packet. After that, the client calculates a password hash using the password and seed by using ^ (bitwise xor), + (string concatenation) and SHA1 as follows:
SHA1( passwd) ^ SHA1( seed + SHA1( SHA1( passwd ) ) )
This page is licensed: CC BY-SA / Gnu FDL
Caching SHA256
first sends an SHA256
-encrypted password.
MySQL server has an in-memory cache of SHA256
key for successful authentication. When a cache hit occurs, the connection is validated, if not, using some more steps to a process similar to sha256_password.
Caching SHA256 authentication possible exchanges:
Client sends an SHA-2 encrypted password.
Server result is either OK_Packet , ERR_Packet or "fast" authentication result.
If fast authentication result:
If connection uses SSL (SSLRequest Packet sent):
Client sends a clear password answer.
Else:
If client doesn't know server RSA public key:
Client sends a public key request.
Server sends a public key response.
Client sends an RSA encrypted password.
Ends with server sending either OK_Packet , ERR_Packet.
Encryption is XOR
(SHA256
(password), SHA256
(seed, SHA256
(SHA256
(password)))).
byte<32> encrypted password.
Result of fast authentication.
byte authentication result.
0x03
value means success authentication.
0x04
value means continue.
string password without encryption.
Value send is not 0x01
like sha256_password use, but 0x02
.
byte<1> fixed 0x02 value.
byte<256> RSA encrypted password.
RSA encrypted value of XOR
(password, seed) using server public key (RSA_PKCS1_OAEP_PADDING
).
This page is licensed: CC BY-SA / Gnu FDL
SHA256
authentication possible exchanges:
if connection use SSL (SSLRequest Packet sent):
Client sends a clear password answer.
Else:
If client doesn't know server RSA public key:
Client sends a public key request.
Server sends a public key response.
Client sends an RSA encrypted password.
Ends with server sending either OK_Packet , ERR_Packet.
string password without encryption.
byte<1> fixed 0x01
value.
byte<256> RSA encrypted password.
RSA encrypted value of XOR
(password, seed) using server public key (RSA_PKCS1_OAEP_PADDING
).
This page is licensed: CC BY-SA / Gnu FDL