September 6, 2021 at 5:13 am
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
September 6, 2021 at 5:41 pm
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
September 7, 2021 at 1:29 am
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.
September 7, 2021 at 3:51 pm
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply