laika222
4/15/2019 - 5:08 PM
Encryption
/********************************
VERY SIMPLE ASCII ENCRYPTION
*********************************/
/*
This is a very simple ASCII encryption which takes a string and transposes the characters onto the unicode set using an increment number.
This isn't a strong encryption regime, but it can be better than storing sensitive information as plain text.
Unicode has characters which are listed in a certain order. This code takes the ASCII character and adds the increment number to it, resulting
in a unicode character that is farther down the list. To slightly strengthen the encryption, the index number of the string is added so that repeated
characters do not always return the same unicode character. For example, a 'T' in the first position[0] with an increment of 120 will be moved down the
list by 120 + 0, and a 'T' in the second position[1] will be moved down by 120 + 1, resulting in a different unicode character, and a 'T'in the third
position[2] will be 120 + 2, resulting in a third unicode character, etc.
The list of unicode characters looks like this, and can be found at https://en.wikipedia.org/wiki/List_of_Unicode_characters.
Code Glyph Decimal Description #
U+0061 a 97 Latin Small Letter A 0066
U+0062 b 98 Latin Small Letter B 0067
U+0063 c 99 Latin Small Letter C 0068
U+0064 d 100 Latin Small Letter D 0069
U+0065 e 101 Latin Small Letter E 0070
...
U+00D0 Ð 0208 Latin Capital letter Eth 0144
U+00D1 Ñ 0209 Latin Capital letter N with tilde 0145
U+00D2 Ò 0210 Latin Capital letter O with grave 0146
U+00D3 Ó 0211 Latin Capital letter O with acute 0147
U+00D4 Ô 0212 Latin Capital letter O with circumflex 0148
For example, saying you're encyrpting the following string (with the index positions counted below), and say the increment is 128:
This is the string to be encrypted.
01234567890123456789012345678901234 -- count single digits
0 1 2 3 -- count tens
- For the first character 'T', this is unicode decimal 84 and it's the first position, so the calculation is 84 + 0 + 128, which is decimal 212 (Ô)
- For the next character 'h', this is unicode decimal 104 and it's the second position, so the calculation is 104 + 1 + 128, which is decimal 233 (é)
- For the next character 'i', this is unicode decimal 105 and it's the third position, so the calculation is 105 + 2 + 128, which is decimal 235 (ë)
- For the next character 's', this is unicode decimal 115 and it's the fourth position, so the calculation is 115 + 3 + 128, which is decimal 246 (ö)
- For the next character ' ', this is unicode decimal 32 and it's the fifth position, so the calculation is 32 + 4 + 128, which is decimal 164 (¤)
The resulting encrypted string is Ôéëö¤îù§üñï«ÿāĀøþø²ćăµøü¸þĈþĎĖĎēąąÐ
This string can then be put through the decryption code to decrypt it.
If you're encasing the encryption algorithm in a function, a hacker could in theory view how you're encrypting the values to be able to decrypt the values and view
the original clear strings. A second level of security is that you can use WITH ENCRYPTION when creating the fuction - this will make it so the function
definition is not visible to anyone (including admins) after creation.
*/
DECLARE @parameter_clear_string_to_be_encrypted VARCHAR(100) = 'This is the string to be encrypted.'
DECLARE @vEncryptedString NVARCHAR(100) -- variable to hold the encrypted string
DECLARE @vCounter INT -- variable to hold increment counter
DECLARE @vBaseIncrement INT -- variable to hold the increment
SET @vCounter = 1 -- set counter to 1
SET @vBaseIncrement = 128 -- set increment to 128
SET @vEncryptedString = ''
WHILE @vCounter <= LEN(@parameter_clear_string_to_be_encrypted) -- while counter is less than or equal to length of string to be encrypted
BEGIN
SET @vEncryptedString = @vEncryptedString +
NCHAR(ASCII(SUBSTRING(@parameter_clear_string_to_be_encrypted, @vCounter, 1)) +
@vBaseIncrement + @vCounter - 1)
SET @vCounter = @vCounter + 1
END
-- view the results
SELECT @vEncryptedString AS 'Encrypted String'
-- DECRYPT THE ENCRYPTED STRING
DECLARE @vClearString VARCHAR(100)
SET @vCounter = 1 -- set counter to 1
SET @vBaseIncrement = 128 -- set increment to 128
SET @vClearString = ''
WHILE @vCounter <= LEN(@vEncryptedString) -- while counter is less than or equal to length of string to be decrypted
BEGIN
SET @vClearString = @vClearString +
CHAR(UNICODE(SUBSTRING(@vEncryptedString, @vCounter, 1)) -
@vBaseIncrement - @vCounter + 1)
SET @vCounter = @vCounter + 1
END
-- view the results
select @vClearString AS 'Decrypted String'
-- EXAMPLE OF ENCRYPTION CODE AND DECRYPTION CODE AS FUNCTIONS USING 'WITH ENCRYPTION' THAT WILL HIDE THE ENCRYPTION ALGORITHM FROM ATTACKERS
-- ENCRYPTION
CREATE FUNCTION [dbo].[ufn_EncryptString] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION 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)) +
@vBaseIncrement + @vIdx - 1)
SET @vIdx = @vIdx + 1
END
RETURN @vEncryptedString
END
GO
-- DECRYPTION
CREATE 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(UNICODE(SUBSTRING(@pEncryptedString, @vIdx, 1)) -
@vBaseIncrement - @vIdx + 1)
SET @vIdx = @vIdx + 1
END
RETURN @vClearString
END
GO
/* Illustration of how WITH ENCRYPTION WORKS.
Normally when you create a function, you can view the function definition by right-clicking the function in Object Explorer and
selecting Modify or Script Function As, or you can run the following script:
-- Get the function name, definition, and relevant properties
SELECT sm.object_id,
OBJECT_NAME(sm.object_id) AS object_name,
o.type,
o.type_desc,
sm.definition,
sm.uses_ansi_nulls,
sm.uses_quoted_identifier,
sm.is_schema_bound,
sm.execute_as_principal_id
-- using the two system tables sys.sql_modules and sys.objects
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
-- from the function 'dbo.ufnGetProductDealerPrice'
WHERE sm.object_id = OBJECT_ID('dbo.ufnGetProductDealerPrice')
ORDER BY o.type;
GO
However, if you use WITH ENCRYPTION when defining the function, if you right-click and Modify/Script in Object Explorer, you
will get an error saying the text is encrypted. If you use the SQL script method, it'll show up with a NULL value in the
definition column.
*/