mask_canada_sin(str [, mask_char])

Masks a Canada Social Insurance Number (SIN) and returns the number with all meaningful digits replaced by 'X' characters. An optional masking character can be specified.

Arguments:

Return value:

The masked Canada SIN as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_canada_sin('046-454-286'), mask_canada_sin('abcdefijk');
+--------------------------------+------------------------------+
| mask_canada_sin('046-454-286') | mask_canada_sin('abcdefijk') |
+--------------------------------+------------------------------+
| XXX-XXX-XXX                    | XXXXXXXXX                    |
+--------------------------------+------------------------------+
mysql> SELECT mask_canada_sin('909');
ERROR 1123 (HY000): Can't initialize function 'mask_canada_sin'; Argument 0 is too short.
mysql> SELECT mask_canada_sin('046-454-286-909');
ERROR 1123 (HY000): Can't initialize function 'mask_canada_sin'; Argument 0 is too long.

mask_iban(str [, mask_char])

Masks an International Bank Account Number (IBAN) and returns the number with all but the first two letters (denoting the country) replaced by '*' characters. An optional masking character can be specified.

Arguments:

Return value:

The masked International Bank Account Number as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_iban('IE12 BOFI 9000 0112 3456 78'), mask_iban('abcdefghijk');
+------------------------------------------+--------------------------+
| mask_iban('IE12 BOFI 9000 0112 3456 78') | mask_iban('abcdefghijk') |
+------------------------------------------+--------------------------+
| IE** **** **** **** **** **              | ab*********              |
+------------------------------------------+--------------------------+
mysql> SELECT mask_iban('909');
ERROR 1123 (HY000): Can't initialize function 'mask_iban'; Argument 0 is too short.
mysql> SELECT mask_iban('IE12 BOFI 9000 0112 3456 78 IE12 BOFI 9000 0112 3456 78');
ERROR 1123 (HY000): Can't initialize function 'mask_iban'; Argument 0 is too long.

mask_inner(str, margin1, margin2 [, mask_char])

Masks the interior part of a string, leaving the ends untouched, and returns the result. An optional masking character can be specified.

mask_inner supports all character sets.

Arguments:

Return value:

The masked string encoded in the same character set used for str, or an error if either margin is negative.

If the sum of the margin values is larger than the argument length, no masking occurs and the argument is returned unchanged.

Example:

mysql> SELECT mask_inner('abcdef', 1, 2), mask_inner('abcdef',0, 5);
+----------------------------+---------------------------+
| mask_inner('abcdef', 1, 2) | mask_inner('abcdef',0, 5) |
+----------------------------+---------------------------+
| aXXXef                     | Xbcdef                    |
+----------------------------+---------------------------+
mysql> SELECT mask_inner('abcdef', 1, 2, '*'), mask_inner('abcdef',0, 5, '#');
+---------------------------------+--------------------------------+
| mask_inner('abcdef', 1, 2, '*') | mask_inner('abcdef',0, 5, '#') |
+---------------------------------+--------------------------------+
| a***ef                          | #bcdef                         |
+---------------------------------+--------------------------------+

mask_outer(str, margin1, margin2 [, mask_char])

Masks the left and right ends of a string, leaving the interior unmasked, and returns the result. An optional masking character can be specified.

mask_outer supports all character sets.

Arguments:

Return value:

The masked string encoded in the same character set used for str, or an error if either margin is negative.

If the sum of the margin values is larger than the argument length, the entire argument is masked.

Example:

mysql> SELECT mask_outer('abcdef', 1, 2), mask_outer('abcdef',0, 5);
+----------------------------+---------------------------+
| mask_outer('abcdef', 1, 2) | mask_outer('abcdef',0, 5) |
+----------------------------+---------------------------+
| XbcdXX                     | aXXXXX                    |
+----------------------------+---------------------------+
mysql> SELECT mask_outer('abcdef', 1, 2, '*'), mask_outer('abcdef',0, 5, '#');
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 1, 2, '*') | mask_outer('abcdef',0, 5, '#') |
+---------------------------------+--------------------------------+
| *bcd**                          | a#####                         |
+---------------------------------+--------------------------------+

mask_pan(str [, mask_char])

Masks a payment card Primary Account Number (PAN) and returns the number with all but the last four digits replaced by 'X' characters. An optional masking character can be specified.

Arguments:

Return value:

The masked payment number as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_pan(gen_rnd_pan());
+-------------------------+
| mask_pan(gen_rnd_pan()) |
+-------------------------+
| XXXXXXXXXXXX9102        |
+-------------------------+
mysql> SELECT mask_pan(gen_rnd_pan(19));
+---------------------------+
| mask_pan(gen_rnd_pan(19)) |
+---------------------------+
| XXXXXXXXXXXXXXX8268       |
+---------------------------+
mysql> SELECT mask_pan('a*Z');
ERROR 1123 (HY000): Can't initialize function 'mask_pan'; Argument 0 is too short.

mask_pan_relaxed(str)

Masks a payment card Primary Account Number and returns the number with all but the first six and last four digits replaced by 'X' characters. The first six digits indicate the payment card issuer. An optional masking character can be specified.

Arguments:

Return value:

The masked payment number as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_pan_relaxed(gen_rnd_pan());
+---------------------------------+
| mask_pan_relaxed(gen_rnd_pan()) |
+---------------------------------+
| 551279XXXXXX3108                |
+---------------------------------+
mysql> SELECT mask_pan_relaxed(gen_rnd_pan(19));
+-----------------------------------+
| mask_pan_relaxed(gen_rnd_pan(19)) |
+-----------------------------------+
| 462634XXXXXXXXX6739               |
+-----------------------------------+
mysql> SELECT mask_pan_relaxed('a*Z');
ERROR 1123 (HY000): Can't initialize function 'mask_pan_relaxed'; Argument 0 is too short.

mask_ssn(str [, mask_char])

Masks a US Social Security Number (SSN) and returns the number with all but the last four digits replaced by '*' characters. An optional masking character can be specified.

Arguments:

Return value:

The masked Social Security Number as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_ssn('909-63-6922'), mask_ssn('cdefghijk');
+-------------------------+-------------------------+
| mask_ssn('909-63-6922') | mask_ssn('cdefghijk')   |
+-------------------------+-------------------------+
| ***-**-6922             | *******hijk             |
+-------------------------+-------------------------+
mysql> SELECT mask_ssn('909');
ERROR 1123 (HY000): Can't initialize function 'mask_ssn'; Argument 0 is too short.
mysql> SELECT mask_ssn('123456789123456789');
ERROR 1123 (HY000): Can't initialize function 'mask_ssn'; Argument 0 is too long.

mask_uk_nin(str [, mask_char])

Masks a United Kingdom National Insurance Number (UK NIN) and returns the number with all but the first two digits replaced by '*' characters. An optional masking character can be specified.

Arguments:

Return value:

The masked UK NIN as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_uk_nin('QQ 12 34 56 C'), mask_uk_nin('abcdefghi');
+------------------------------+--------------------------+
| mask_uk_nin('QQ 12 34 56 C') | mask_uk_nin('abcdefghi') |
+------------------------------+--------------------------+
| QQ ** ** ** *                | ab*******                |
+------------------------------+--------------------------+
mysql> SELECT mask_uk_nin('909');
ERROR 1123 (HY000): Can't initialize function 'mask_uk_nin'; Argument 0 is too short.
mysql> SELECT mask_uk_nin('abcdefghijk');
ERROR 1123 (HY000): Can't initialize function 'mask_uk_nin'; Argument 0 is too long.

mask_uuid(str [, mask_char])

Masks a Universally Unique Identifier (UUID) and returns the number with all meaningful characters replaced by '*' characters. An optional masking character can be specified.

Arguments:

Return value:

The masked UUID as a string encoded in the utf8mb4 character set, an error if the argument is not the correct length, or NULL if str is in incorrect format or contains a multibyte character.

Example:

mysql> SELECT mask_uuid(gen_rnd_uuid());
+--------------------------------------+
| mask_uuid(gen_rnd_uuid())            |
+--------------------------------------+
| ********-****-****-****-************ |
+--------------------------------------+
mysql> SELECT mask_uuid('909');
ERROR 1123 (HY000): Can't initialize function 'mask_uuid'; Argument 0 is too short.
mysql> SELECT mask_uuid('123e4567-e89b-12d3-a456-426614174000-123e4567-e89b-12d3');
ERROR 1123 (HY000): Can't initialize function 'mask_uuid'; Argument 0 is too long.

gen_range(lower, upper)

Generates a random number chosen from a specified range.

Arguments:

Return value:

A random integer (encoded in the utf8mb4 character set) in the range from lower to upper, inclusive, or NULL if the upper argument is less than lower.

Example:

mysql> SELECT gen_range(100, 200), gen_range(-1000, -800);
+---------------------+------------------------+
| gen_range(100, 200) | gen_range(-1000, -800) |
+---------------------+------------------------+
|                 177 |                   -917 |
+---------------------+------------------------+
mysql> SELECT gen_range(1, 0);
+-----------------+
| gen_range(1, 0) |
+-----------------+
|            NULL |
+-----------------+

gen_rnd_canada_sin()

Generates a random Canada Social Insurance Number (SIN) in AAA-BBB-CCC format. The generated number passes the Luhn check algorithm, which ensures the consistency of this number.

Arguments:

None.

Return value:

A random Canada SIN as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_canada_sin();
+----------------------+
| gen_rnd_canada_sin() |
+----------------------+
| 046-454-286          |
+----------------------+

gen_rnd_email(name_size, surname_size, domain)

Generates a random email address in the form of random_name.random_surname@domain.

Arguments:

Return value:

A random email address as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_email(name_size = 4, surname_size = 5, domain = 'mynet.com');
+----------------------------------------------------------------------+
| gen_rnd_email(name_size = 4, surname_size = 5, domain = 'mynet.com') |
+----------------------------------------------------------------------+
| lsoy.qwupp@mynet.com                                                 |
+----------------------------------------------------------------------+
mysql> SELECT gen_rnd_email();
+---------------------------+
| gen_rnd_email()           |
+---------------------------+
| ijocv.mwvhhuf@example.com |
+---------------------------+

gen_rnd_iban([country, size])

Generates a random International Bank Account Number (IBAN) in AAAA BBBB CCCC DDDD format. The generated string starts with a two-character country code, two check digits computed according to the IBAN specification and random alphanumeric characters up to the required size.

Arguments:

Return value:

A random IBAN as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_iban();
+-----------------------------+
| gen_rnd_iban()              |
+-----------------------------+
| ZZ79 3K2J WNH9 1V0DI        |
+-----------------------------+

gen_rnd_pan([size])

Generates a random payment card Primary Account Number. The number passes the Luhn check (an algorithm that performs a checksum verification against a check digit).

Arguments:

Return value:

A random payment number as a string, or an error if a size argument outside the permitted range is given.

Example:

mysql> SELECT mask_pan(gen_rnd_pan());
+-------------------------+
| mask_pan(gen_rnd_pan()) |
+-------------------------+
| XXXXXXXXXXXX5805        |
+-------------------------+
mysql> SELECT mask_pan(gen_rnd_pan(19));
+---------------------------+
| mask_pan(gen_rnd_pan(19)) |
+---------------------------+
| XXXXXXXXXXXXXXX5067       |
+---------------------------+
mysql> SELECT mask_pan_relaxed(gen_rnd_pan());
+---------------------------------+
| mask_pan_relaxed(gen_rnd_pan()) |
+---------------------------------+
| 398403XXXXXX9547                |
+---------------------------------+
mysql> SELECT mask_pan_relaxed(gen_rnd_pan(19));
+-----------------------------------+
| mask_pan_relaxed(gen_rnd_pan(19)) |
+-----------------------------------+
| 578416XXXXXXXXX6509               |
+-----------------------------------+
mysql> SELECT gen_rnd_pan(20);
ERROR 1123 (HY000): Can't initialize function 'gen_rnd_pan'; Minimal value of argument 0 is 14.

gen_rnd_ssn()

Generates a random US Social Security Number in AAA-BB-CCCC format. The AAA part is greater than 900, which are characteristics not used for legitimate social security numbers.

Arguments:

None.

Return value:

A random Social Security Number as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_ssn();
+---------------+
| gen_rnd_ssn() |
+---------------+
| 951-26-0058   |
+---------------+

gen_rnd_uk_nin()

Generates a random United Kingdom National Insurance Number (UK NIN) in nine-character format. NIN starts with two character prefix randomly selected from the set of valid prefixes, six random numbers, and one character suffix randomly selected from the set of valid suffixes.

Arguments:

None.

Return value:

A random UK NIN as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_uk_nin();
+----------------------+
| gen_rnd_uk_nin()     |
+----------------------+
| QQ123456C            |
+----------------------+

gen_rnd_us_phone()

Generates a random US phone number in 1-555-AAA-BBBB format. The 555 area code is not used for legitimate phone numbers.

Arguments:

None.

Return value:

A random US phone number as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_us_phone();
+--------------------+
| gen_rnd_us_phone() |
+--------------------+
| 1-555-682-5423     |
+--------------------+

gen_rnd_uuid()

Generates a random Universally Unique Identifier (UUID) segmented with dashes.

Arguments:

None.

Return value:

A random UUID as a string encoded in the utf8mb4 character set.

Example:

mysql> SELECT gen_rnd_uuid();
+--------------------------------------+
| gen_rnd_uuid()                       |
+--------------------------------------+
| 123e4567-e89b-12d3-a456-426614174000 |
+--------------------------------------+

masking_dictionaries_flush()

Flush the data from the masking dictionaries table to the memory cache. Requires the MASKING_DICTIONARIES_ADMIN privilege.

masking_dictionary_remove(dictionary_name)

Removes a dictionary and all of its terms from the dictionary registry. This function requires the MASKING_DICTIONARIES_ADMIN privilege.

Arguments:

Return value:

A string that indicates whether the remove operation succeeded. 1 indicates success. NULL indicates the dictionary name is not found.

Example:

mysql> SELECT masking_dictionary_remove('mydict');
+-------------------------------------+
| masking_dictionary_remove('mydict') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
mysql> SELECT masking_dictionary_remove('no-such-dict');
+-------------------------------------------+
| masking_dictionary_remove('no-such-dict') |
+-------------------------------------------+
|                                      NULL |
+-------------------------------------------+

masking_dictionary_term_add(dictionary_name, term_name)

Adds one term to the named dictionary. This function requires the MASKING_DICTIONARIES_ADMIN privilege.

Each term is defined by a named dictionary. masking_dictionary_term_add() permits you to add one dictionary term at a time.

Arguments:

Return value:

A string that indicates whether the add term operation succeeded. 1 indicates success. NULL indicates failure. Term add failure can occur for several reasons, including:

Example:

mysql> SELECT masking_dictionary_term_add('mydict','newterm');
+-------------------------------------------------+
| masking_dictionary_term_add('mydict','newterm') |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
mysql> SELECT masking_dictionary_term_add('mydict','');
+------------------------------------------+
| masking_dictionary_term_add('mydict','') |
+------------------------------------------+
|                                     NULL |
+------------------------------------------+

masking_dictionary_term_remove(dictionary_name, term_name)

Removes one term from the named dictionary. This function requires the MASKING_DICTIONARIES_ADMIN privilege.

Arguments:

Return value:

A string that indicates whether the remove term operation succeeded. 1 indicates success. NULL indicates failure. Term remove failure can occur for several reasons, including:

Example:

mysql> SELECT masking_dictionary_term_add('mydict','newterm');
+-------------------------------------------------+
| masking_dictionary_term_add('mydict','newterm') |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+
mysql> SELECT masking_dictionary_term_remove('mydict','');
+---------------------------------------------+
| masking_dictionary_term_remove('mydict','') |
+---------------------------------------------+
|                                        NULL |
+---------------------------------------------+

gen_blocklist(str, from_dictionary_name, to_dictionary_name)

Replaces a term present in one dictionary with a term from a second dictionary and returns the replacement term. This masks the original term by substitution.

Arguments:

Return value:

A string encoded in the utf8mb4 character set randomly chosen from to_dictionary_name as a replacement for term, or term if it does not appear in from_dictionary_name, or an error if either dictionary name is not in the dictionary registry.

Example:

mysql> SELECT gen_blocklist('Berlin', 'DE_Cities', 'US_Cities');
+---------------------------------------------------+
| gen_blocklist('Berlin', 'DE_Cities', 'US_Cities') |
+---------------------------------------------------+
| Phoenix                                           |
+---------------------------------------------------+

gen_dictionary(dictionary_name)

Returns a random term from a dictionary.

Arguments:

Return value:

A random term from the dictionary as a string encoded in the utf8mb4 character set, or NULL if the dictionary name is not in the dictionary registry.

Example:

mysql> SELECT gen_dictionary('mydict');
+--------------------------+
| gen_dictionary('mydict') |
+--------------------------+
| My term                  |
+--------------------------+
mysql> SELECT gen_dictionary('no-such-dict');
ERROR 1123 (HY000): Can't initialize function 'gen_dictionary'; Cannot access 
dictionary, check if dictionary name is valid.