Hey guys,
Good afternoon!

Today I'm going to make a quick but very interesting post for anyone who needs to encrypt and/or decrypt strings using the Base64 algorithm in SQL Server. Many people don't know, but SQL Server has resources to work with this encryption natively, without having to write all the logic for it.

A little about Base64
Base64 is a method for encoding data for transfer over the Internet (MIME encoding for content transfer). It is often used to transmit binary data via transmission media that only handle text, such as sending file attachments via email.

It consists of 64 characters ([A-Za-z0-9], “/” and “+”) that gave rise to its name. The “=” character is used as a special suffix and the original specification (RFC 989) defined that the “*” symbol can be used to delimit converted but not encrypted data within a stream.

Coding example:
Original text: hello world
Text converted to Base64: aGVsbG8gd29ybGQK

Base64 encoding is often used when there is a need to transfer and store binary data to a device designed to work with textual data. This encoding is widely used by applications in conjunction with the XML markup language, enabling the storage of binary data in text form.

Unlike cryptographic functions such as MD5, SHA1, SHA-256 and others, Base64 is an encryption method, since by definition, encryption is a two-way task that you use whenever you need to securely store information, but need to retrieve it later using a symmetric or private key. The hash, which is used by cryptographic functions such as those mentioned above, is commonly used when you need to compare information and it is not possible to obtain the original string from the string where the Hash was applied.

Now let's get to practice!

Encrypting a string
To encrypt a string, we will use the fncBase64_Encode function:

CREATE FUNCTION [dbo].[fncBase64_Encode] (
    @string VARCHAR(MAX)
) 
RETURNS VARCHAR(MAX)
AS BEGIN

    DECLARE 
        @source VARBINARY(MAX), 
        @encoded VARCHAR(MAX)
        
    SET @source = CONVERT(VARBINARY(MAX), @string)
    SET @encoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')

    RETURN @encoded

END

Example of use:

SELECT dbo.fncBase64_Encode('Bem vindo ao Blog') -- Irá retornar: QmVtIHZpbmRvIGFvIEJsb2c=

Decrypting a string
To decrypt a string, we will use the fncBase64_Decode function:

CREATE FUNCTION [dbo].[fncBase64_Decode] (
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS BEGIN

    DECLARE @decoded VARCHAR(MAX)
    SET @decoded = CAST('' AS XML).value('xs:base64Binary(sql:variable("@string"))', 'varbinary(max)')

    RETURN CONVERT(VARCHAR(MAX), @decoded)
	
END

Example of use:

SELECT dbo.fncBase64_Decode('Vm9sdGVtIHNlbXByZSE=') -- Irá retornar: Voltem sempre!

That's it folks!
Until later.