Conversion of Numbers Between Bases - MySQL

Four functions convert numbers from one base to another:

BIN(X): Converts a base-10 number X to binary form (base 2).

CONV(X,  N,  M): Converts the number X from base N to base M. The minimum allowed value for N or M is 2; the maximum value for either one is 36 (10 digits + 26 letters of the Latin alphabet for “digits” greater than 9).

HEX(X): Converts a base-10 number X to its hexadecimal equivalent (base 16).

OCT(X): Converts a base-10 number X to octal (base 8).

All four of these functions return their result as a string. More often than not, when you use these functions within more complex expressions, MySQL will automatically convert the result to a number, as shown in the following example.

Keep in mind that numbers containing characters other than the digits 0 through 9 need to be quoted.

---

CAUTION  Do not confuse CONV() with the CONVERT() function! The latter is used to convert between datatypes, or to convert strings from one character set to another (see the previous section).

---

Encryption and Encoding Functions

Several MySQL functions encrypt or encode data. You can use these functions for purposes such as keeping passwords secure and ensuring that critical data hasn’t been corrupted.

MD5() and SHA1() are one-way hashing algorithms that are useful for safeguarding application passwords and other sensitive data, as is ENCRYPT() on Unix systems. ENCODE()/DECODE(), AES_ENCRYPT()/ AES_DECRYPT(), and DES_ENCRYPT() / DES_DECRYPT() provide keyword-based encryption and decryption. PASSWORD() and OLD_PASSWORD() duplicate the algorithms used to encrypt MySQL’s own passwords. COMPRESS() can be used to compress lengthy strings prior to storage and so save on space.

Here’s some additional information about MySQL’s encryption and encoding functions:

MD5(): Calculates a 128-bit checksum for a string and returns it as a 32-digit hexadecimal number using the RSA-MD5 Message Digest Algorithm. This function is supported in or has been ported to a number of programming languages, including C, Java, PHP, Perl, Visual Basic, and even JavaScript. For more information about MD5 and links to ports of it in various languages, visit http://userpages.umbc.edu/~mabzug1/cs/md5/md5.html.

SHA1(): Calculates a 160-bit hash for a string and returns it as a 40-bit hexadecimal number. Like the MD5 algorithm, the Secure Hashing Algorithm 1 (SHA1) has been implemented in a number of different programming languages. For more information about the SHA1 algorithm, see http://www.faqs.org/rfcs/rfc3174.html.

ENCODE() and DECODE(): ENCODE() takes as arguments a data string to be encoded and a password string, and returns a binary-encoded version of the data string that’s the same length as the original. (If you wish to store the encoded string, be sure to use a BLOB column rather than a CHAR or VARCHAR column.) DECODE() also takes two arguments—the encoded string and the same password that was used to encode it—and returns the original string.

ENCRYPT(): Uses the Unix crypt() system call to encrypt a string. This function can take an optional second parameter in addition to the string to be encrypted, a “seed” string of two or more characters. This function does not work on Windows and other operating systems that don’t support the crypt() system call; on these systems, it always returns NULL .

AES_ENCRYPT() and AES_DECRYPT(): Implement the U.S. Government’s Approved Encryption Standard, also known as FIPS-197, and are the most cryptographically secure encoding functions available in MySQL as of this writing. Like ENCODE() and DECODE() , these functions require two arguments; the second argument is a password. For more information, see the AES home page at http://csrc.nist.gov/CryptoToolkit/aes/. This pair of functions was added in MySQL 4.0.2.

DES_ENCRYPT() and DES_DECRYPT(): Implement the U.S. National Institute of Standards and Technology’s Triple-DES algorithm and are available only if MySQL has been compiled with Secure Sockets Layer (SSL) support. For more information, check the MySQL Manual or http://csrc.nist.gov/CryptoToolkit/ Encryption.html#a3DES.

PASSWORD(): Same as that used by MySQL to encrypt passwords set using GRANT commands. It is strongly recommended that you do not use it in your application code, since it is liable to change between major releases. Prior to MySQL 5.0, this function returns a 16-digit hexadecimal number; starting in MySQL 5.0, the algorithm for PASSWORD() has changed, and the function returns a 41-character string that begins with the ‘*’ (asterisk) character followed by a 40-digit hexadecimal number.

OLD_PASSWORD(): In MySQL 5.0, emulates the behavior of PASSWORD() in previous versions of MySQL.

COMPRESS() and UNCOMPRESS(): In MySQL 4.1.1 and later, COMPRESS() compresses a string using the zlib or equivalent library. The compressed string must be stored in a BLOB column. This can be useful for storing large amounts of textual data. The inverse of the COMPRESS() function is UNCOMPRESS(), which was also added in MySQL 4.1.1. You can find out how long a compressed string will be when uncompressed by using UNCOMPRESSED_LENGTH() on it before uncompressing it. If zlib functionality is not available, these functions will return NULL .

---

TIP  It’s usually better to compress data before trying to encrypt it.

---

CRC32(): Calculates a 32-bit cyclic redundancy check (CRC) for a string. This can be used as a hedge against data corruption. However, be aware that multiple errors can cancel each other out, so it is not 100% foolproof. (On the other hand, it’s fairly reliable, and the larger the block of data, the less likely this is to happen.) If you’re interested in knowing more, the MathWorld web site has some good technical information on CRCs and how they work at http://mathworld.wolfram.com/ CyclicRedundancyCheck.html .