Simple Encryption and Decryption

Question

Simple Encryption and Decryption

musicroxster

Old Hand

Points: 328
More actions
September 6, 2021 at 5:13 am

#3927081
Hi,
I would need help on decrypt portion as I am getting null value return.

CREATE FUNCTION [dbo].[ufn_EncryptString] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) AS
BEGIN
DECLARE @vEncryptedString NVARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT
SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vEncryptedString = ''
WHILE @vIdx <= LEN(@pClearString)
BEGIN
SET @vEncryptedString = @vEncryptedString +
NCHAR(
(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
ASCII(SUBSTRING(@pClearString , (@vIdx % len (@pClearString))+1, 1)))
)
SET @vIdx = @vIdx + 1
END
--print @vEncryptedString
RETURN @vEncryptedString
END
GO

alter FUNCTION [dbo].[ufn_DecryptString] ( @pEncryptedString NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN
DECLARE @vClearString VARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT
SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vClearString = ''
WHILE @vIdx <= LEN(@pEncryptedString)
BEGIN
SET @vClearString = @vClearString +
CHAR( ASCII(SUBSTRING(@pEncryptedString, @vIdx, 1)) -
ASCII(SUBSTRING(@pEncryptedString , (@vIdx % len (@pEncryptedString))+1, 1)))
SET @vIdx = @vIdx + 1
END
RETURN @vClearString
END
GO
- This topic was modified 4 years ago by musicroxster.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 1

This appears you've written functions to do some encryption. I am guessing somewhere your algorithm isn't implemented properly. I don't quite see what's wrong, but likely your use of ASCII and CHAR isn't quite right.

Why are you trying to write something like this? Encryption is a well studied topic and anything homegrown is problematic. Not to mention you have a looping structure here, which is inefficient.

There are encryption functions in SQL Server that are secure. EncryptByKey will handle this: https://docs.microsoft.com/en-us/sql/t-sql/functions/encryptbykey-transact-sql?view=sql-server-ver15

you can create a key with DDL. USe the AES algorithms: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-transact-sql?view=sql-server-ver15

musicroxster Old Hand Points: 328 More actions · Answer 2

Hi Steve,

the encryption function is working fine, produced the expected output.

However, when I tried to use decrypt function to "revert" the expected output to the initial input, it is not returning anything.

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 3

I think your decryption algorithm looks funny, but as I wrote, this is a poor way to implement encryption. There are algorithms out there that are tested. If this is for your own practice, then I think you need to verify the algorithm. My suspicion is that you have a problem with the way you've used ASCII and CHAR. I don't have time to check this right now.

How do you know the encryption works correctly? A lack or error doesn't mean you've implemented an algorithm correctly.