Hello guys,
Good afternoon!
Today I will make a quick post, but very interesting for those who need to encrypt and / or decrypt strings using the Base64 algorithm in SQL Server. A lot of people don't know, but SQL Server has the capabilities 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 Internet transfer (MIME encoding for content transfer). It is often used to transmit binary data via text-only transmission means, such as sending attached files by 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 data converted, but not encrypted, within a stream.
Encoding Example:
Original text: hello world
Text converted to Base64: aGVsbG8gd29ybGQK
Base64 encoding is often used when there is a need for binary data transfer and storage 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 a method of encryption, as by definition encryption is a two-way task that you use whenever you need to securely store information, but you need to retrieve it later using a symmetric or private key. Hashing, which is used by cryptographic functions like the ones mentioned above, is commonly used when you need to compare information and you cannot get the original string from the string where the hash was applied.
Now let's get to it!
Encrypting a string
To encrypt a string, let's use the fncBase64_Encode function:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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:
| 1 | SELECT dbo.fncBase64_Encode('Bem vindo ao Blog') -- Irá retornar: QmVtIHZpbmRvIGFvIEJsb2c= |
Decrypting a string
To decrypt a string, let's use the fncBase64_Decode function:
| 1 2 3 4 5 6 7 8 9 10 11 12 | 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:
| 1 | SELECT dbo.fncBase64_Decode('Vm9sdGVtIHNlbXByZSE=') -- Irá retornar: Voltem sempre! |
That's it folks!
See you.
