hashbytes

Description

Produces a fixed-length, hash value expression.

Syntax

hashbytes(algorithm, expression[, expression...] [, using options])

Parameters

expression[, expression...]

– is the value to be hashed. This value can be a column name, variable, constant expression, or a combination of these that produces a single value. It cannot be image, text, unitext, or off-row Java datatypes.

algorithm

is the algorithm used to produce the hash value. A character literal (not a variable or a column name) that can take the values “md5”, “sha”, “sha1”, or “ptn”.

Algorithm

Description

Md5

Message Digest Algorithm 5 – is the cryptographic hash algorithm with a 128 bit hash value. hashbytes('md5', expression[,...]) results in a varbinary 16-byte value.

Sha-Sha1

Secure Hash Algorithm – is the cryptographic hash algorithm with a 160-bit hash value. hashbytes('shal', expression[,...]) results in a varbinary 20-byte value.

Ptn

The partition hash algorithm with 32-bit hash value. The using clause is ignored for the ‘ptn’ algorithm. hashbytes('ptn', expression[,...]) results in an unsigned int 4-byte value.

using

Orders bytes for platform independence. The optional using clause can precede the following option strings:

  • lsb – all byte-order dependent data is normalized to little-endian byte-order before being hashed.

  • msb – all byte-order dependent data is normalized to big-endian byte-order before being hashed.

  • unicode – character data is normalized to unicode (UTF–16) before being hashed.

    NoteA UTF – 16 string is similar to an array of short integers. Because it is byte-order dependent, Sybase suggest for platform independence you use lsb or msb in conjunction with UNICODE.

  • unicode_lsb – a combination of unicode and lsb.

  • unicode_msb – a combination of unicode and msb.

Examples

Example 1

Seals each row of a table against tampering. This example assumes the existence of a user table called “xtable” and col1, col2, col3 and tamper_seal.

update xtable set tamper_seal=hashbytes('sha1', col1,
col2, col4, @salt)
--
declare @nparts unsigned int
select @nparts= 5
select hashbytes('ptn', col1, col2, col3) % nparts from xtable

Example 2

Shows how col1, col2, and col3 will be used to partition rows into five partitions.

alter table xtable partition by hash(col1, col2, col3) 5

Usage

The algorithm parameter is not case-sensitive; “md5,” “Md5” and “MD5” are all equivalent. However, if the expression is specified as a character datatype, the value is case sensitive. “Time,” “TIME,” and “time” will produce different hash values.

NoteTrailing null values are trimmed by Adaptive Server when inserting into varbinary columns.

In the absence of a using clause, the bytes that form expression are fed into the hash algorithm in the order they appear in memory. For many datatypes, order is significant. For example, the binary representation of the 4-byte INT value 1 will be 0x00, 0x00, 0x00, 0x01, on MSB-first (big-endian) platforms and 0x01, 0x00, 0x00, 0x00 on LSB-first (little-endian) platforms. Because the stream of bytes is different for different platforms, the hash value is different as well.

With the using clause, the bytes that form expression can be fed into the hashing algorithm in a platform-independent manner. The using clause can also be used to transform character data into Unicode so that the hash value becomes independent of the server’s character configuration.

NoteThe hash algorithms MD5 and SHA1 are no longer considered entirely secure by the cryptographic community. Be aware of the risks of using MD5 or SHA1 in a security-critical context.

Standards

SQL92- and SQL99-compliant

Permissions

Any user can execute hashbyte.

See also

See also hash for platform dependent hash values.