HASH( ) function

Returns a salted cryptographic hash value based on the input value.

Syntax

HASH(field <,salt_value>)

Parameters

field

Numeric, Character, Datetime, or Logical. The value to hash.

salt_value

Optional. Numeric constant or Character. The salt value to use. You can specify a PASSWORD identifier number from 1 to 10, or a character string enclosed in quotation marks. If you omit the parameter, the ACL default salt value is used.

The salt value is limited to 128 characters, and is automatically truncated to 128 characters if you specify a longer salt value.

Output

Character.

Remarks

You can use the HASH( ) function to protect sensitive data, such as credit card numbers, salary information, or social security numbers. HASH( ) is a one-way encoding function. Data in clear text can be used to produce a hash value, however the hash value cannot subsequently be unencoded or decrypted.

A specific clear text value always produces the same hash value, so you can search a field of hashed credit card numbers for duplicates, or join two fields of hashed credit card numbers, and the results are the same as if you had performed the operation on the equivalent clear text fields.

To avoid storing sensitive data on a server, you can create a computed field locally using the HASH( ) function and then create a new table by extracting the hashed field and any other required fields, while excluding the clear text field. You can use the new table on the server for your analysis, and once you have the results, refer back to the original table if you need to see the clear text version of any of the hashed data. If storing sensitive data locally, beyond initial use, is prohibited, you can delete the original table after you have created the new table, and refer to the original source system for the clear text values.

In order to produce identical hash values, two clear text values must be exactly identical. For example, different hash values result from the same credit card number with or without hyphens, or the same name in title case or all upper case. You may need to incorporate functions such as INCLUDE( ), EXCLUDE( ), or UPPER( ) in the HASH( ) function to standardize clear text values. Leading and trailing blanks are automatically trimmed by the HASH( ) function, so there is no need to use the TRIM( ) or ALLTRIM( ) functions.

If you have data in which leading or trailing blanks represent meaningful differences between values you need to replace the blanks with another character before hashing the values. For example:

HASH(REPLACE(field_name, " ", "_"))

You can also use the HASH( ) function to check if blocks of text in two comment fields are identical. To perform this check, create two computed fields similar to the ones shown below, and then create a filter to find any text blocks that are not identical.

If the comment fields are in separate tables, create a computed HASH( ) field in each table and then use the computed fields as a common key field to do an unmatched join of the two tables. The records in the joined output table represent text blocks that are not identical.

The HASH( ) function uses an SHA-2 cryptographic hash algorithm that produces a fixed-length hashed output of 64 bytes, regardless of the length of the input value. The clear text input value can be longer than 64 bytes.

The salt value

The protection offered by the HASH( ) function is strengthened by the addition of a salt value prior to hashing. The salt value is an alphanumeric string that is concatenated with the source data value. The entire concatenated string is then used to produce the salted, hashed value. This approach makes the hashed values more resistant to decoding techniques.

A fixed, default salt value is automatically used unless you specify a salt value. You can use either of the following methods to specify a salt value:

The password method is intended for use in scripts that prompt for the password at the beginning of the script, or prior to the HASH( ) function appearing in the script. The password method is not suitable for use in computed fields because PASSWORD assignments are deleted when you close ACL. In addition, computed fields that use a password-based salt value are automatically removed from views when you reopen ACL – to avoid the calculation of hash values, using the default salt value, that differ from the original hash values.

Examples

Example Return value

HASH("555-44-3322")

819A974BB91215D58E7753FD5A42226150100A0763087CA7DECD93F3C3090405

HASH(credit_card)

The hash value for each number in the credit_card field

HASH("555-44-3322")

HASH("555-44-3321")

HASH("555-44-3322")

HASH("999-33-7744")

819A974BB91215D58E7753FD5A42226150100A0763087CA7DECD93F3C3090405

89A0B3A366591675B57A2CF97BAE5C0FA9F79BD1B34F8BE20283EC07B8F27373

819A974BB91215D58E7753FD5A42226150100A0763087CA7DECD93F3C3090405

40B0572F9A5F8AB65644A5CCFA72A1DDBA42491063A08C548ACD49BCB4CF6CD2

HASH("555443322")

HASH("555-44-3322")

HASH(EXCLUDE("555-44-3322", "-"))

54C995C0770A53DCE8E818A9EEB2B93B7CD42A6220BAB79340B25AD22D8E8058

819A974BB91215D58E7753FD5A42226150100A0763087CA7DECD93F3C3090405

54C995C0770A53DCE8E818A9EEB2B93B7CD42A6220BAB79340B25AD22D8E8058

HASH("SMITH")

HASH("Smith")

HASH(UPPER("Smith"))

44D5BF61640879D0CF01DFDBD1F7FCDBD941DFA43050F47872BA2EFAAFA0B9E2

40812DB1654986DA961F349F5C8FD740B9F5E6947ACCCCB4B4343329FB870746

44D5BF61640879D0CF01DFDBD1F7FCDBD941DFA43050F47872BA2EFAAFA0B9E2

HASH("555443322")

HASH("555443322", "my salt value 123")

HASH("555443322", 3)

(If PASSWORD 3 = my salt value 123)

54C995C0770A53DCE8E818A9EEB2B93B7CD42A6220BAB79340B25AD22D8E8058

5ADAF66B9D8F58AC492E23B0E2B5CCE9B3D5DA60D365C3CDF314DACD28A05857

5ADAF66B9D8F58AC492E23B0E2B5CCE9B3D5DA60D365C3CDF314DACD28A05857



(C) 2015 ACL Services Ltd. All Rights Reserved.