Characters are the basic building blocks of writing. Whether you are composing a writing contest entry, a Google headline or a tweet, the number of characters you use in your writing is important. So what counts as a character? Do spaces count? What about commas and periods? Case operator CAST Cast a value as a certain type CEIL Return the smallest integer value not less than the argument CEILING Return the smallest integer value not less than the argument CHAR Return the character for each integer passed CHARLENGTH Return number of characters in argument CHARACTERLENGTH.
I'm using MySql 5.5.32 and trying to reproduce some code in a stored procedure that we have in the business layer so I can set passwords for people using
sql
. It appears that there is something wrong with the SHA2 function though, but maybe I'm missing something:Returns 128. Shouldn't it be 64?
Which returns 64, so it appears that either I missing something, or there is a bug in SHA2. Any ideas?
bpeikesbpeikes
3 Answers
I coded the patch for the SHA2() function in 2005 and contributed it to MySQL (the developers then edited my code a bit to match their coding standards).
The function always returned a string of hex digits, just like all the other hash functions in MySQL.
You are probably reading this statement in the documentation:
As of MySQL 5.5.6, the return value is a nonbinary string in the connection character set. Before 5.5.6, the return value is a binary string.
I can see how a reader would think this means it returns binary bytes, but that's a misinterpretation.
What is actually meant by that is that the string had a binary character set. It's still a plaintext string of hex digits. In fact, none of the hash functions in MySQL return a string of bytes as if you ran UNHEX() on it, they all return strings of hex digits. The length of a string of hex digits is twice the length of the equivalent binary bytes.
If you don't know I mean by a binary character set, see What is binary character set?
SHA2() was changed to use the connection character set in 5.5.6, whereas other hash functions were changed in the same way in 5.5.3.
![Mysql Mysql](/uploads/1/2/3/9/123900935/185612974.png)
Community♦
Bill KarwinBill Karwin
The SHA2(str, hash_length) function returns back a nonbinary string. Previously the
SHA2
function in MySQL returned back a value of a binary string.From the docs in the link I provided:
As of MySQL 5.5.6, the return value is a nonbinary string in the connection character set.
We can dissect all of this via the following
SELECT
statement:Which via MySQL Workbench the output will result in this:
UPDATE Per Comment:
The UNHEX(str) function states in the documentation that it interprets each pair of characters in the inputted string as hexadecimal numbers. As for whether the input string is binary or nonbinary, the documentation answers that question for us here:
A NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval.
Derek WDerek W
Comment was correct, even though the docs say that it returns binary string, it returns hex encoded string. To get the correct length use:
bpeikesbpeikes
Not the answer you're looking for? Browse other questions tagged mysqlsha2 or ask your own question.
MySQL provides two authentication plugins that implement SHA-256 hashing for user account passwords:
sha256_password
: Implements basic SHA-256 authentication.caching_sha2_password
: Implements SHA-256 authentication (likesha256_password
), but uses caching on the server side for better performance and has additional features for wider applicability.
This section describes the original noncaching SHA-2 authentication plugin. For information about the caching plugin, see Section 6.4.1.5, “Caching SHA-2 Pluggable Authentication”.
To connect to the server using an account that authenticates with the
sha256_password
plugin, you must use either a TLS connection or an unencrypted connection that supports password exchange using an RSA key pair, as described later in this section. Either way, the sha256_password
plugin uses MySQL's encryption capabilities. See Section 6.3, “Using Encrypted Connections”. In the name
sha256_password
, “sha256” refers to the 256-bit digest length the plugin uses for encryption. In the name caching_sha2_password
, “sha2” refers more generally to the SHA-2 class of encryption algorithms, of which 256-bit encryption is one instance. The latter name choice leaves room for future expansion of possible digest lengths without changing the plugin name. The following table shows the plugin names on the server and client sides.
Table 6.10 Plugin and Library Names for SHA-256 Authentication
Plugin or File | Plugin or File Name |
---|---|
Server-side plugin | sha256_password |
Client-side plugin | sha256_password |
Library file | None (plugins are built in) |
The following sections provide installation and usage information specific to SHA-256 pluggable authentication:
For general information about pluggable authentication in MySQL, see Section 6.2.13, “Pluggable Authentication”.
The
sha256_password
plugin exists in server and client forms:- The server-side plugin is built into the server, need not be loaded explicitly, and cannot be disabled by unloading it.
- The client-side plugin is built into the
libmysqlclient
client library and is available to any program linked againstlibmysqlclient
.
To set up an account that uses the
sha256_password
plugin for SHA-256 password hashing, use the following statement, where password
is the desired account password: The server assigns the
sha256_password
plugin to the account and uses it to encrypt the password using SHA-256, storing those values in the plugin
and authentication_string
columns of the mysql.user
system table. ![Sha2 Sha2](/uploads/1/2/3/9/123900935/403714945.png)
The preceding instructions do not assume that
sha256_password
is the default authentication plugin. If sha256_password
is the default authentication plugin, a simpler CREATE USER
syntax can be used. To start the server with the default authentication plugin set to
sha256_password
, put these lines in the server option file: That causes the
sha256_password
plugin to be used by default for new accounts. As a result, it is possible to create the account and set its password without naming the plugin explicitly: Another consequence of setting
default_authentication_plugin
to sha256_password
is that, to use some other plugin for account creation, you must specify that plugin explicitly. For example, to use the mysql_native_password
plugin, use this statement: sha256_password
supports connections over secure transport. sha256_password
also supports encrypted password exchange using RSA over unencrypted connections if these conditions are satisfied:- MySQL is compiled using OpenSSL. MySQL can be compiled using either OpenSSL or yaSSL (see Section 6.3.3, “SSL Library-Dependent Capabilities”), and
sha256_password
works with distributions compiled using either package, but RSA support requires OpenSSL. - The MySQL server to which you wish to connect is configured to support RSA (using the RSA configuration procedure given later in this section).
RSA support has these characteristics:
- On the server side, two system variables name the RSA private and public key-pair files:
sha256_password_private_key_path
andsha256_password_public_key_path
. The database administrator must set these variables at server startup if the key files to use have names that differ from the system variable default values. - The server uses the
sha256_password_auto_generate_rsa_keys
system variable to determine whether to automatically generate the RSA key-pair files. See Section 6.3.2, “Creating SSL and RSA Certificates and Keys”. - The
Rsa_public_key
status variable displays the RSA public key value used by thesha256_password
authentication plugin. - Clients that are in possession of the RSA public key can perform RSA key pair-based password exchange with the server during the connection process, as described later.
- For connections by accounts that authenticate using
sha256_password
and RSA public key pair-based password exchange, the server sends the RSA public key to the client as needed. However, if a copy of the public key is available on the client host, the client can use it to save a round trip in the client/server protocol:- For these command-line clients, use the
--server-public-key-path
option to specify the RSA public key file: mysql, mysqltest, and (as of MySQL 5.7.23) mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlpump, mysqlshow, mysqlslap, mysqltest. - For programs that use the C API, call
mysql_options()
to specify the RSA public key file by passing theMYSQL_SERVER_PUBLIC_KEY
option and the name of the file. - For replication slaves, RSA key pair-based password exchange cannot be used to connect to master servers for accounts that authenticate with the
sha256_password
plugin. For such accounts, only secure connections can be used.
For clients that use the
sha256_password
plugin, passwords are never exposed as cleartext when connecting to the server. How password transmission occurs depends on whether a secure connection or RSA encryption is used:- If the connection is secure, an RSA key pair is unnecessary and is not used. This applies to encrypted connections that use TLS. The password is sent as cleartext but cannot be snooped because the connection is secure.
- If the connection is not secure, and an RSA key pair is available, the connection remains unencrypted. This applies to unencrypted connections without TLS. RSA is used only for password exchange between client and server, to prevent password snooping. When the server receives the encrypted password, it decrypts it. A scramble is used in the encryption to prevent repeat attacks.
- If a secure connection is not used and RSA encryption is not available, the connection attempt fails because the password cannot be sent without being exposed as cleartext.
As mentioned previously, RSA password encryption is available only if MySQL was compiled using OpenSSL. The implication for MySQL distributions compiled using yaSSL is that, to use SHA-256 passwords, clients must use an encrypted connection to access the server. See Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.
To use RSA password encryption with
sha256_password
, the client and server both must be compiled using OpenSSL, not just one of them. Assuming that MySQL has been compiled using OpenSSL, use the following procedure to enable use of an RSA key pair for password exchange during the client connection process:
- Create the RSA private and public key-pair files using the instructions in Section 6.3.2, “Creating SSL and RSA Certificates and Keys”.
- If the private and public key files are located in the data directory and are named
private_key.pem
andpublic_key.pem
(the default values of thesha256_password_private_key_path
andsha256_password_public_key_path
system variables), the server uses them automatically at startup.Otherwise, to name the key files explicitly, set the system variables to the key file names in the server option file. If the files are located in the server data directory, you need not specify their full path names:If the key files are not located in the data directory, or to make their locations explicit in the system variable values, use full path names: - Restart the server, then connect to it and check the
Rsa_public_key
status variable value. The value will differ from that shown here, but should be nonempty:If the value is empty, the server found some problem with the key files. Check the error log for diagnostic information.
After the server has been configured with the RSA key files, accounts that authenticate with the
sha256_password
plugin have the option of using those key files to connect to the server. As mentioned previously, such accounts can use either a secure connection (in which case RSA is not used) or an unencrypted connection that performs password exchange using RSA. Suppose that an unencrypted connection is used. For example: For this connection attempt by
sha256user
, the server determines that sha256_password
is the appropriate authentication plugin and invokes it (because that was the plugin specified at CREATE USER
time). The plugin finds that the connection is not encrypted and thus requires the password to be transmitted using RSA encryption. In this case, the plugin sends the RSA public key to the client, which uses it to encrypt the password and returns the result to the server. The plugin uses the RSA private key on the server side to decrypt the password and accepts or rejects the connection based on whether the password is correct. The server sends the RSA public key to the client as needed. However, if the client has a file containing a local copy of the RSA public key required by the server, it can specify the file using the
--server-public-key-path
option: The public key value in the file named by the
--server-public-key-path
option should be the same as the key value in the server-side file named by the sha256_password_public_key_path
system variable. If the key file contains a valid public key value but the value is incorrect, an access-denied error occurs. If the key file does not contain a valid public key, the client program cannot use it. In this case, the sha256_password
plugin sends the public key to the client as if no --server-public-key-path
option had been specified. Client users can obtain the RSA public key two ways:
- The database administrator can provide a copy of the public key file.
- A client user who can connect to the server some other way can use a
SHOW STATUS LIKE 'Rsa_public_key'
statement and save the returned key value in a file.