Cryptography functions

chm.GetHashFromBytes

This function receives a byte array and generates a hash for that based on the requested algorithm.

CREATE FUNCTION chm.GetHashFromBytes
(
    @algorithm NVARCHAR(20)   NULL,
    @data      VARBINARY(MAX) NULL,
    @key       NVARCHAR(128)  NULL
)
RETURNS VARBINARY(MAX)

Possible values for @algorithm parameter (case-insensitive):

md5
sha1
sha256
sha384
sha512
hmac
hmacmd5
hmacsha1
hmacsha256
hmacsha384
hmacsha512
hmacripemd160

Example:

declare @a varbinary(max)
declare @key varchar(128)

set @a = cast('Hello World' as varbinary(max))
set @key = '12345678'

select chm.GetHashFromBytes('md5', @a, @key)    -- 0xB10A8DB164E0754105B7A99BE72E3FE5
select chm.GetHashFromBytes('sha1', @a, @key)   -- 0x0A4D55A8D778E5022FAB701977C5D840BBC486D0
select chm.GetHashFromBytes('sha256', @a, @key) -- 0xA591A6D40BF420404A011733CFB7B190D62C65BF0BCDA32B57B277D9AD9F146E
select chm.GetHashFromBytes('hmac', @a, @key)   -- 0xB1CA8DD4FAD2C49781D32DD3FDEBC1AE7FC553AE
select chm.GetHashFromBytes('hmacsha512', @a, @key) -- 0xB1CA8DD4FAD2C49781D32DD3FDEBC1AE7FC553AE

chm.GetHashFromString

This function receives a string and generates a hash for that based on the requested algorithm.

CREATE FUNCTION chm.GetHashFromString
(
    @algorithm NVARCHAR(20)  NULL,
    @data      NVARCHAR(MAX) NULL,
    @encoding  NVARCHAR(20)  NULL,
    @key       NVARCHAR(128) NULL
)
RETURNS VARBINARY(MAX)

Possible values for @algorithm parameter (case-insensitive):

md5
sha1
sha256
sha384
sha512
hmac
hmacmd5
hmacsha1
hmacsha256
hmacsha384
hmacsha512
hmacripemd160

Possible values for @encoding parameter (case-insensitive):

ascii
unicode
utf8
utft
utf32
bigendianunicode
code page name of the preferred encoding

Example:

declare @a nvarchar(max)
declare @encoding varchar(20) = 'unicode'
declare @key varchar(128)

set @a = N'Hello World'
set @key = '12345678'

select chm.GetHashFromString('md5', @a, @encoding, @key)    -- 0xE9F73A54305808EC350C3BBE3DB371B9
select chm.GetHashFromString('sha1', @a, @encoding, @key)   -- 0xCFB47CC81B59CF4F9EA9E69DED4118CCFBD5163C
select chm.GetHashFromString('sha256', @a, @encoding, @key) -- 0x979AB6536128EE79BCBB8D0386F29429D09B706B5CA77BFA5497968187E304C3
select chm.GetHashFromString('hmac', @a, @encoding, @key)   -- 0xC4316333F6EDD7E9410CB4869A508694C93616A1
select chm.GetHashFromString('hmacsha512', @a, @encoding, @key) -- 0xC4316333F6EDD7E9410CB4869A508694C93616A1

chm.GetBase64HashFromBytes

This function receives a byte array and generates a hash in base64 format for that based on the requested algorithm.

CREATE OR ALTER FUNCTION chm.GetBase64HashFromBytes
(
    @algorithm NVARCHAR(20)   NULL,
    @data      VARBINARY(MAX) NULL,
    @key       NVARCHAR(128)  NULL
)
RETURNS NVARCHAR(MAX)

Possible values for @algorithm parameter (case-insensitive):

md5
sha1
sha256
sha384
sha512
hmac
hmacmd5
hmacsha1
hmacsha256
hmacsha384
hmacsha512
hmacripemd160

Example:

declare @a varbinary(max)
declare @key varchar(128)

set @a = cast('Hello World' as varbinary(max))
set @key = '12345678'

select chm.GetBase64HashFromBytes('md5', @a, @key)    -- sQqNsWTgdUEFt6mb5y4/5Q==
select chm.GetBase64HashFromBytes('sha1', @a, @key)   -- Ck1VqNd45QIvq3AZd8XYQLvEhtA=
select chm.GetBase64HashFromBytes('sha256', @a, @key) -- pZGm1Av0IEBKARczz7exkNYsZb8LzaMrV7J32a2fFG4=
select chm.GetBase64HashFromBytes('hmac', @a, @key)   -- scqN1PrSxJeB0y3T/evBrn/FU64=
select chm.GetBase64HashFromBytes('hmacsha512', @a, @key) -- scqN1PrSxJeB0y3T/evBrn/FU64=

chm.GetBase64HashFromString

This function receives a string and generates a hash in base64 format for that based on the requested algorithm.

FUNCTION chm.GetHashFromString
(
    @algorithm NVARCHAR(20)  NULL,
    @data      NVARCHAR(MAX) NULL,
    @encoding  NVARCHAR(20)  NULL,
    @key       NVARCHAR(128) NULL
)
RETURNS NVARCHAR(MAX)

Possible values for @algorithm parameter (case-insensitive):

md5
sha1
sha256
sha384
sha512
hmac
hmacmd5
hmacsha1
hmacsha256
hmacsha384
hmacsha512
hmacripemd160

Possible values for @encoding parameter (case-insensitive):

ascii
unicode
utf8
utft
utf32
bigendianunicode
code page name of the preferred encoding

Example:

declare @a nvarchar(max)
declare @encoding varchar(20) = 'unicode'
declare @key varchar(128)

set @a = N'Hello World'
set @key = '12345678'

select chm.GetBase64HashFromString('md5', @a, @encoding, @key)    -- 6fc6VDBYCOw1DDu+PbNxuQ==
select chm.GetBase64HashFromString('sha1', @a, @encoding, @key)   -- z7R8yBtZz0+eqead7UEYzPvVFjw=
select chm.GetBase64HashFromString('sha256', @a, @encoding, @key) -- l5q2U2Eo7nm8u40DhvKUKdCbcGtcp3v6VJeWgYfjBMM=
select chm.GetBase64HashFromString('hmac', @a, @encoding, @key)   -- xDFjM/bt1+lBDLSGmlCGlMk2FqE=
select chm.GetBase64HashFromString('hmacsha512', @a, @encoding, @key) -- xDFjM/bt1+lBDLSGmlCGlMk2FqE=

chm.[Base64.EncodeString]

This function returns base64 of a given string.

CREATE FUNCTION chm.[Base64.EncodeString]
(
    @data     NVARCHAR(MAX) NULL,
    @encoding NVARCHAR(20)  NULL
)
RETURNS NVARCHAR(MAX)

Possible values for @encoding parameter (case-insensitive):

ascii
unicode
utf8
utft
utf32
bigendianunicode
code page name of the preferred encoding

Example:

select chm.[Base64.EncodeString]('Hello World', 'ascii')    -- SGVsbG8gV29ybGQ=

chm.[Base64.EncodeBytes]

This function returns base64 of a given string.

CREATE FUNCTION chm.[Base64.EncodeBytes]
(
    @data     VARBINARY(MAX)  NULL
)
RETURNS NVARCHAR(MAX)

Example:

declare @data varbinary(max)

set @data = cast('Hello World' as varbinary(max))
select chm.[Base64.EncodeBytes](@data)    -- SGVsbG8gV29ybGQ=

chm.[Base64.DecodeToString]

This function receives a base64 string, decodes it and returns the result as a string.

CREATE FUNCTION chm.[Base64.DecodeToString]
(
    @data     NVARCHAR(MAX) NULL,
    @encoding NVARCHAR(20)  NULL
)
RETURNS NVARCHAR (MAX)

Example:

select chm.[Base64.DecodeToString]('SGVsbG8gV29ybGQ=', 'ascii')    -- Hello World

chm.[Base64.DecodeToBytes]

This function receives a base64 string, decodes it and returns the result as byte array.

Example:

select chm.[Base64.DecodeToBytes]('SGVsbG8gV29ybGQ=')    -- 0x48656C6C6F20576F726C64

chm.XorString

This function returns XOR encode of a given string using a given string key.

CREATE FUNCTION chm.XorString
(
    @data NVARCHAR(MAX) NULL,
    @key  NVARCHAR(MAX) NULL
)
RETURNS NVARCHAR(MAX)

Example:

select chm.XorString('Hello World', '1234')    -- yW_X^d[C^W

chm.XorInt

This function returns XOR encode of a given string using a given integer key.

CREATE FUNCTION chm.XorString
(
    @data NVARCHAR(MAX) NULL,
    @key  INT           NULL
)
RETURNS NVARCHAR(MAX)

Example:

select chm.XorInt('Hello World', 1234)    -- ҚҷҾҾҽӲ҅ҽҠҾҶ

AES encryption/decryption

The CLR functions here are dedicated to AES ecnryption/decryption. There a number of parameters that they have in common and provide settings for AES algorithm. These parameters are as follows:

@key: Given encryption key in base64 format
@IV: vector array in base64 format
@cipherMode: possible values (case-insensitive) are CBC (default), ECB, OFB, CFB, CTS
@paddingMode: possible values (case-insensitive) are None, PKCS7 (default), Zeros, ANSIX923, ISO10126

chm.[AES.EncryptToBytes]

This function encrypts a given string based on AES algorithm and returns ciphered data as byte array ()varbinary(max)).

CREATE FUNCTION chm.[AES.EncryptToBytes]
(
    @plainText    NVARCHAR(MAX) NULL,
    @Key          NVARCHAR(MAX) NULL,
    @IV           NVARCHAR(MAX) NULL,
    @cipherMode   NVARCHAR(3)   NULL,
    @paddingMode  NVARCHAR(8)   NULL
)
RETURNS VARBINARY(MAX)

Example:

select chm.[AES.EncryptToBytes]
    (
        N'Hello from Chameleon',
        'KJbz7kO3+BzurcP22jdXanmWRJbs6Tbo+2dWnoOE9o0=',
        'Oo4aLWoevXG6R6y5/uYE/Q==',
        '',
        ''
    )   -- 0xAA975D005A6B410936C796779F96800E55D6A8A6172D3377CB20BDB60C033990

chm.[AES.DecryptFromBytes]

This function decrypts a given AES-encrypted byte array and returned the result as string (nvarchar(max)).

CREATE FUNCTION chm.[AES.DecryptFromBytes]
(
    @cipheredData VARBINARY(MAX) NULL,
    @Key          NVARCHAR(MAX) NULL,
    @IV           NVARCHAR(MAX) NULL,
    @cipherMode   NVARCHAR(3)   NULL,
    @paddingMode  NVARCHAR(8)   NULL
)
RETURNS NVARCHAR(MAX)

Example:

declare @a varbinary(max)

set @a = 0xAA975D005A6B410936C796779F96800E55D6A8A6172D3377CB20BDB60C033990

select chm.[AES.DecryptFromBytes]
		(
			@a,
			'KJbz7kO3+BzurcP22jdXanmWRJbs6Tbo+2dWnoOE9o0=',
			'Oo4aLWoevXG6R6y5/uYE/Q==',
			'',
			''
		)   -- Hello from Chameleon

chm.[AES.EncryptToBase64]

This function encrypts a given string based on AES algorithm and returns ciphered data in base64 format (nvarchar(max)).

CREATE FUNCTION chm.[AES.EncryptToBase64]
(
    @plainText    NVARCHAR(MAX) NULL,
    @Key          NVARCHAR(MAX) NULL,
    @IV           NVARCHAR(MAX) NULL,
    @cipherMode   NVARCHAR(3)   NULL,
    @paddingMode  NVARCHAR(8)   NULL
)
RETURNS NVARCHAR(MAX)

Example:

select chm.[AES.EncryptToBase64]
    (
        N'Hello from Chameleon',
        'KJbz7kO3+BzurcP22jdXanmWRJbs6Tbo+2dWnoOE9o0=',
        'Oo4aLWoevXG6R6y5/uYE/Q==',
        '',
        ''
    )   -- qpddAFprQQk2x5Z3n5aADlXWqKYXLTN3yyC9tgwDOZA=

chm.[AES.DecryptFromBase64]

This function decrypts a given AES-encrypted ciphered data in base64 format and returns the result as string (nvarchar(max)).

CREATE FUNCTION chm.[AES.DecryptFromBytes]
(
    @base64Text   NVARCHAR(MAX) NULL,
    @Key          NVARCHAR(MAX) NULL,
    @IV           NVARCHAR(MAX) NULL,
    @cipherMode   NVARCHAR(3)   NULL,
    @paddingMode  NVARCHAR(8)   NULL
)
RETURNS NVARCHAR(MAX)

Example:

select chm.[AES.DecryptFromBase64]
    (
        'qpddAFprQQk2x5Z3n5aADlXWqKYXLTN3yyC9tgwDOZA=',
        'KJbz7kO3+BzurcP22jdXanmWRJbs6Tbo+2dWnoOE9o0=',
        'Oo4aLWoevXG6R6y5/uYE/Q==',
        '',
        ''
    )   -- Hello from Chameleon