Offshore Software Outsourcing Company, offshore IT outsourcing services, offshore software development Russia

About ALTOROS: ALTOROS Systems is headquartered in Tampa, Florida and maintains an office near Boston, Massachusetts and technology development center in Belarus and Russia. ALTOROS specializes on providing value-added e-commerce and web-based software development and offshore software outsourcing services to emerging enterprises helping them successfully plan and implement business initiatives.

DATABASE()

Returns the current database name:

mysql> SELECT DATABASE();
-> 'test'

If there is no current database, DATABASE() returns the empty string.

USER()

SYSTEM_USER()

SESSION_USER()

Returns the current MySQL user name:

mysql> SELECT USER();
-> 'davida@localhost'

In MySQL Version 3.22.11 or later, this includes the client hostname as well as the user name. You can extract just the user name part like this (which works whether the value includes a hostname part):

mysql> SELECT SUBSTRING_INDEX(USER(),"@",1);
-> 'davida'

PASSWORD(str)

Calculates a password string from the plaintext password str. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table:

mysql> SELECT PASSWORD('badpwd');
-> '7f84554057dd964b'

PASSWORD() encryption is non-reversible.

PASSWORD() does not perform password encryption in the same way that Unix passwords are encrypted. You should not assume that if your Unix password and your MySQL password are the same, PASSWORD() will result in the same encrypted value as is stored in the Unix password file. See ENCRYPT().

ENCRYPT(str[,salt])

Encrypt str using the Unix crypt() system call. The salt argument should be a string with two characters. (As of MySQL Version 3.22.16, salt may be longer than two characters.):

mysql> SELECT ENCRYPT("hello");
-> 'VxuFAJXVARROc'

If crypt() is not available on your system, ENCRYPT() always returns NULL.

ENCRYPT() ignores all but the first 8 characters of str, at least on some systems. This will be determined by the behaviour of the underlying crypt() system call.

ENCODE(str,pass_str)

Encrypt str using pass_str as the password. To decrypt the result, use DECODE().

The results is a binary string of the same length as string. If you want to save it in a column, use a BLOB column type.

DECODE(crypt_str,pass_str)

Descrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE().

MD5(string)

Calculates an MD5 128 bit checksum for the string. The value is returned as a 32 digit hex number that may, for example, be used as a hash key:

mysql> SELECT MD5("testing");
-> 'ae2b1fca515949e5d54fb22b8ed95575'

This is the "RSA Data Security, Inc. MD5 Message-Digest Algorithm".

SHA1(string)

SHA(string)

Calculates an SHA1 160 bit checksum for the string, as described in RFC 3174 (Secure Hash Algorithm). The value is returned as a 40 digit hex number, or NULL in case the input argument was NULL. One of the possible uses for this function is as a hash key. You can also use it as cryptographically safe function for storing passwords.

mysql> SELECT SHA1("abc");
-> 'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1() was added in version 4.0.2, and can be considered a cryptographically more secure equivalent of MD5(). SHA() is synonym for SHA1().

AES_ENCRYPT(string,key_string)

AES_DECRYPT(string,key_string)

These functions allow encryption/decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as Rijndael. Encoding with 128 bit key length is used, but you can extend it up to 256 bit by patching the source. We chose 128 bits because it is much faster and it is usually secure enough.

The input arguments may be any length. If either argument is NULL, the result of this function is also NULL.

As AES is a block level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated as 16*(trunc(string_length/16)+1).

If AES_DECRYPT() detects invalid data or incorrect padding, it will return NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key was invalid.

You can use the AES functions to store data in an encrypted form by modifying your queries:

INSERT INTO t VALUES (1,AES_ENCRYPT("text","password"));

You can get even more security by avoiding transferring the key over the connection for each query, which can be accomplished by storing it in a server side variable at connection time:

SELECT @password:="my password";
INSERT INTO t VALUES (1,AES_ENCRYPT("text",@password));

AES_ENCRYPT() and AES_DECRYPT() were added in version 4.0.2, and can be considered the most cryptographically secure encryption functions currently available in MySQL.

DES_ENCRYPT(string_to_encrypt [, (key_number | key_string) ] )

Encrypts the string with the given key using the DES algorithm.

Note that this function only works if you have configured MySQL with SSL support. See Secure connections.

The encryption key to use is chosen the following way:

Argument	Description
Only one argument	The first key from des-key-file is used.
key number	The given key (0-9) from the des-key-file is used.
string	The given key_string will be used to crypt string_to_encrypt.

The return string will be a binary string where the first character will be CHAR(128 | key_number).

The 128 is added to make it easier to recognize an encrypted key. If you use a string key, key_number will be 127.

On error, this function returns NULL.

The string length for the result will be new_length= org_length + (8-(org_length % 8))+1.

The des-key-file has the following format:

key_number des_key_string
key_number des_key_string

Each key_number must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_string is the string that will be used to encrypt the message. Between the number and the key there should be at least one space. The first key is the default key that will be used if you don't specify any key argument to DES_ENCRYPT()

You can tell MySQL to read new key values from the key file with the FLUSH DES_KEY_FILE command. This requires the Reload_priv privilege.

One benefit of having a set of default keys is that it gives applications a way to check for existence of encrypted column values, without giving the end user the right to decrypt those values.

mysql> SELECT customer_address FROM customer_table WHERE
crypted_credit_card = DES_ENCRYPT("credit_card_number");

DES_DECRYPT(string_to_decrypt [, key_string])

Decrypts a string encrypted with DES_ENCRYPT().

Note that this function only works if you have configured MySQL with SSL support. See Secure connections.

If no key_string argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, then reads the key from the des-key-file to decrypt the message. For this to work the user must have the SUPER privilege.

If you pass this function a key_string argument, that string is used as the key for decrypting the message.

If the string_to_decrypt doesn't look like an encrypted string, MySQL will return the given string_to_decrypt.

On error, this function returns NULL.

LAST_INSERT_ID([expr])

Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. See mysql_insert_id().

mysql> SELECT LAST_INSERT_ID();
-> 195

The last ID that was generated is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).

If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row. The reason for this is so that you it makes it possible to easily reproduce the same INSERT statement against some other server.

If expr is given as an argument to LAST_INSERT_ID(), then the value of the argument is returned by the function, and is set as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

First create the table:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

Then the table can be used to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value (multi-user safe). You can retrieve the new ID as you would read any normal AUTO_INCREMENT value in MySQL. For example, LAST_INSERT_ID() (without an argument) will return the new ID. The C API function mysql_insert_id() can also be used to get the value.

Note that as mysql_insert_id() is only updated after INSERT and UPDATE statements, so you can't use the C API function to retrieve the value for LAST_INSERT_ID(expr) after executing other SQL statements like SELECT or SET.

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimals. If D is 0, the result will have no decimal point or fractional part:

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'

VERSION()

Returns a string indicating the MySQL server version:

mysql> SELECT VERSION();
-> '3.23.13-log'

Note that if your version ends with -log this means that logging is enabled.

CONNECTION_ID()

Returns the connection id (thread_id) for the connection. Every connection has its own unique id:

mysql> SELECT CONNECTION_ID();
-> 1

GET_LOCK();timeout

GET_LOCK(str,timeout)

Tries to obtain a lock with a name given by the string str, with a timeout of timeout seconds. Returns 1 if the lock was obtained successfully, 0 if the attempt timed out, or NULL if an error occurred (such as running out of memory or the thread was killed with mysqladmin kill). A lock is released when you execute RELEASE_LOCK(), execute a new GET_LOCK(), or the thread terminates. This function can be used to implement application locks or to simulate record locks. It blocks requests by other clients for locks with the same name; clients that agree on a given lock string name can use the string to perform cooperative advisory locking:

mysql> SELECT GET_LOCK("lock1",10);
        -> 1
mysql> SELECT IS_FREE_LOCK("lock2");
        -> 1
mysql> SELECT GET_LOCK("lock2",10);
        -> 1
mysql> SELECT RELEASE_LOCK("lock2");
        -> 1
mysql> SELECT RELEASE_LOCK("lock1");
        -> NULL

Note that the second RELEASE_LOCK() call returns NULL because the lock "lock1" was automatically released by the second GET_LOCK() call.

RELEASE_LOCK()

RELEASE_LOCK(str)

Releases the lock named by the string str that was obtained with GET_LOCK(). Returns 1 if the lock was released, 0 if the lock wasn't locked by this thread (in which case the lock is not released), and NULL if the named lock didn't exist. The lock will not exist if it was never obtained by a call to GET_LOCK() or if it already has been released.

The DO statement is convinient to use with RELEASE_LOCK(). See DO.

IS_FREE_LOCK()

IS_FREE_LOCK(str)

Checks if the the lock named str is free to use (i.e., not locked). Returns 1 if the lock is free (no one is using the lock), 0 if the lock is in use, and NULL on errors (like incorrect arguments).

BENCHMARK()

BENCHMARK(count,expr)

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how fast MySQL processes the expression. The result value is always 0. The intended use is in the mysql client, which reports query execution times:

mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE("hello","goodbye")) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (4.74 sec)

The time reported is elapsed time on the client end, not CPU time on the server end. It may be advisable to execute BENCHMARK() several times, and interpret the result with regard to how heavily loaded the server machine is.

INET_NTOA()

INET_NTOA(expr)

Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string:

mysql> SELECT INET_NTOA(3520061480);
-> "209.207.224.40"

INET_ATON()

INET_ATON(expr)

Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4 or 8 byte addresses:

mysql> SELECT INET_ATON("209.207.224.40");
-> 3520061480

The generated number is always in network byte order; for example the above number is calculated as 209*256^3 + 207*256^2 + 224*256 +40.

MASTER_POS_WAIT()

MASTER_POS_WAIT(log_name, log_pos)

Blocks until the slave reaches the specified position in the master log during replication. If master information is not initialised, returns NULL. If the slave is not running, will block and wait until it is started and goes to or past the specified position. If the slave is already past the specified position, returns immediately. The return value is the number of log events it had to wait to get to the specified position, or NULL in case of error. Useful for control of master-slave synchronisation, but was originally written to facilitate replication testing.

LIMIT;FOUND_ROWS()

FOUND_ROWS()

Returns the number of rows that the last SELECT SQL_CALC_FOUND_ROWS ... command would have returned, if wasn't restricted with LIMIT.

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

Note that if you are using SELECT SQL_CALC_FOUND_ROWS ... MySQL has to calculate all rows in the result set. However, this is faster than if you would not use LIMIT, as the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS is available starting at MySQL version 4.0.0.