SQL Functions for Encrypting and Decrypting Large text fields

After my post about Encrypting Rich Text fields in Power Apps that store their data in SQL server now the promised decryption code in this post as well.

Changed approach

Where last week I wrote the script as a stored procedure, I’ve now implemented both the encryption and decryption as functions in my SQL database. Then these Functions are then called from my Stored Procedures that are called form Power Apps.

In SQL I’m creating the following Functions

• EncryptData
• DecryptData

Encrypt Data

First I’m having a look at the Encrypt function. The explanation of the detailed logic can be found in earlier mentioned post.

Since my earlier post I made a few changes. I’m using NVARCHAR data instead of VARCHAR. NVARCHAR support larger character sets. This means that every character uses 2 bytes of data. This also means that i’m using th eLENM function rather than the DATALENGTH function.

In my example below I’ve also created an additional ChunkSize parameter in my function. So that I can control the Chunk sizes from outside my function.

ALTER FUNCTION dbo.ENCRYPTDATA (@Data AS NVARCHAR(MAX), @ChunkSize AS BIGINT)

RETURNS VARBINARY(max)

BEGIN

	DECLARE @eString NVARCHAR(MAX), 	
		@TextLength BIGINT,
		@HandledLength BIGINT,
		@NextPart INT,
		@eChunk VARBINARY(8000),
		@OldChunk VARBINARY(8000),
		@OldChunkText NVARCHAR(MAX)
		
	SELECT @HandledLength=0, @TextLength=LEN(@Data)

	WHILE @HandledLength < @TextLength
	BEGIN

	     SET @NextPart = CASE WHEN @HandledLength + @ChunkSize < @TextLength 
				  THEN @ChunkSize
				  ELSE @TextLength - @HandledLength
			     END

	    SELECT @OldChunkText = SUBSTRING(@Data,
                                             @HandledLength + 1,
				             @NextPart 
				   )					

	   SELECT @OldChunk = CONVERT(VARBINARY(8000),@OldChunkText)

	   SELECT @eChunk = ENCRYPTBYKEY(KEY_GUID('ISSUE_KEY'), @OldChunk)
					
	   SET @eString = CONCAT(@eString, CONVERT(NVARCHAR(max),@eChunk))
	   SET @HandledLength = @HandledLength + @NextPart
       END
		
       RETURN  CONVERT(VARBINARY(MAX), @eString)
END

GO

Then finally, I choose to use Chunk sizes of 3000 (for decrypt 3026), as my output generate by the Encryption shouldn’t exceed 8000 bytes.

Decrypt Data

ALTER FUNCTION dbo.DECRYPTDATA (@Data AS VARBINARY(MAX), @ChunkSize AS BIGINT)

RETURNS NVARCHAR(MAX)

BEGIN

   DECLARE @DeCryptedString NVARCHAR(MAX),
	   @OldChunkToDecrypt VARBINARY(8000),
	   @DeCryptedChunk VARBINARY(8000),
	   @HandledLength BIGINT,
	   @TextLength BIGINT,
	   @NextPart BIGINT,
	   @OldChunkText NVARCHAR(MAX),
	   @eString NVARCHAR(MAX)

   SET @eString = CONVERT(NVARCHAR(MAX), @Data)

   SELECT @HandledLength=0, @TextLength=LEN(@eString)

   WHILE @HandledLength < @TextLength
   BEGIN
	SET @NextPart = CASE WHEN @HandledLength + @ChunkSize < @TextLength 
			     THEN @ChunkSize
			     ELSE @TextLength - @HandledLength
			END
					
       SELECT @OldChunkToDecrypt = 
           Convert(varbinary(MAX), SUBSTRING(@eString,													           
                                             @HandledLength + 1,
														  @NextPart
												))						 
			
	   SELECT @DeCryptedChunk = DECRYPTBYKEY(@OldChunkToDecrypt)	
	   	 
	   SET  @DeCryptedString = CONCAT( @DeCryptedString, Convert(NVARCHAR(MAX), @DeCryptedChunk))
		
	   SET @HandledLength = @HandledLength + @NextPart 

	END

	RETURN  @DeCryptedString
END

GO

How to use the above functions?

All you need to do is run the above code and your functions will get created. Then within Stored Procedures you can use these to encrypt your data.