How the SHA() function works in Mariadb?

The SHA() function in MariaDB is used to calculate the Secure Hash Algorithm (SHA) hash value of a given string. It is a cryptographic hash function that generates a fixed-size hash value from an input string of any length. The resulting hash value is commonly used for data integrity verification, password storage, and other security-related applications.

Syntax

The syntax for the MariaDB SHA() function is as follows:

SHA(str)

• str: The string for which you want to calculate the SHA hash value. If str is NULL, the function returns NULL.

The function returns a 40-character hexadecimal string representing the SHA-1 hash value of the input string.

Examples

Example 1: Calculate the SHA hash of a simple string

This example demonstrates how to calculate the SHA hash value of a simple string.

SELECT SHA('Hello, World!');

The output for this statement is:

+------------------------------------------+
| SHA('Hello, World!')                     |
+------------------------------------------+
| 0a0a9f2a6772942557ab5355d76af442f8f65e01 |
+------------------------------------------+

The SHA() function calculates the SHA-1 hash value of the string 'Hello, World!', resulting in the hexadecimal string 0a0a9f2a6772942557ab5355d76af442f8f65e01.

Example 2: Calculate the SHA hash of a NULL value

If the SHA() function is provided with a NULL value, it returns NULL.

SELECT SHA(NULL);

The output for this statement is:

NULL

Example 3: Store and verify hashed passwords

You can use the SHA() function to store and verify hashed passwords in a database table.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password_hash CHAR(40)
);

INSERT INTO users (user_id, username, password_hash) VALUES
    (1, 'john_doe', SHA('mypassword')),
    (2, 'jane_smith', SHA('secret123'));

SELECT username, password_hash
FROM users
WHERE password_hash = SHA('mypassword');

The output for this statement is:

+----------+------------------------------------------+
| username | password_hash                            |
+----------+------------------------------------------+
| john_doe | 91dfd9ddb4198affc5c194cd8ce6d338fde470e2 |
+----------+------------------------------------------+

In this example, a users table is created with a password_hash column to store the SHA hash of the user’s password. When a user logs in, the provided password is hashed using the SHA() function, and the resulting hash is compared with the stored hash value in the database. This way, the actual password is never stored in plaintext, enhancing the security of the system.

Example 4: Calculate the SHA hash of a concatenated string

The SHA() function can be used with string concatenation to calculate the hash value of multiple strings combined.

SELECT SHA(CONCAT('Hello', ', ', 'World!'));

The output for this statement is:

+------------------------------------------+
| SHA(CONCAT('Hello', ', ', 'World!'))     |
+------------------------------------------+
| 0a0a9f2a6772942557ab5355d76af442f8f65e01 |
+------------------------------------------+

In this example, the CONCAT() function is used to concatenate the strings 'Hello', ', ', and 'World!'. The resulting concatenated string 'Hello, World!' is then passed to the SHA() function to calculate its hash value, which is the same as the hash value obtained in the first example.

Related Functions

Here are a few functions related to the MariaDB SHA() function:

• MariaDB SHA1() function is an alias for the SHA() function and calculates the SHA-1 hash value of a given string.
• MariaDB SHA2() function is used to calculate the SHA-2 hash value of a given string with various hash lengths (e.g., SHA-224, SHA-256, SHA-384, SHA-512).
• MariaDB MD5() function is used to calculate the MD5 hash value of a given string, which is another widely used cryptographic hash function.

Conclusion

The SHA() function in MariaDB is a powerful tool for calculating the SHA-1 hash value of a given string. It is widely used for data integrity verification, password storage, and other security-related applications. By understanding the syntax and usage of this function, you can effectively implement security measures in your MariaDB database. Whether you need to hash passwords, verify file integrity, or perform other cryptographic operations, the SHA() function provides a reliable and secure way to achieve your goals.