ENCODE() and DECODE() Functions in mysql

ENCODE() and DECODE() Functions

The first encryption function that you look at is the ENCODE() function, which encrypts a specified string. To use the function, you must specify two arguments, the string to be encrypted and a key (password), as shown in the following syntax:

ENCODE(<string>, <key>)
When you use the ENCODE() function to encrypt a string, the string is stored in a column as a binary
string that is the same length as the original string value. To better understand how this function works, take a look at an example, which is based on the following table definition:

CREATE TABLE UserAccounts
(
UserID SMALLINT NOT NULL PRIMARY KEY,
Password VARCHAR(20) NOT NULL
);

Suppose that you want to insert a row in the UserAccounts table, but you want to encrypt the Password value. To encrypt the Password value, you can use an INSERT statement similar to the following:

INSERT INTO UserAccounts
VALUES (101, ENCODE(‘pw101’, ‘key101’));


SELECT UserID, DECODE(Password, ‘key101’) AS Password
FROM UserAccounts;

PASSWORD(), MD5(), SHA(), and SHA1() Functions

MySQL provides several functions that support one-way hashing encryption, as opposed to the
ENCODE() function, which facilitates two-way encryption. The advantage to one-way encryption over two-way is that a one-way is less likely to be compromised as a result of a key value being discovered. In addition, one-way encryption eliminates the need to track the decryption key.

The first of these one-way hashing encryption functions is the PASSWORD() function. The PASSWORD() function, which is shown in the following syntax, encrypts a specified string as a 41-byte hash value:

PASSWORD(<string>)

For this function, you need to provide only one argument: the string value to be encrypted. You can test how this function works by using a SELECT statement similar to the following:

SELECT PASSWORD(‘MyPassword’);

you should use the MD5() or SHA() functions, rather than the PASSWORD() function, because the MD5() and SHA() functions conform to widely accepted standards that are supported on multiple platforms. If you use the PASSWORD() function to encrypt the passwords, the platform on which your application is running might not support that particular format, preventing users from using the application.