Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Function to Encrypt Integer Values


By:   |   Last Updated: 2017-02-03   |   Comments (3)   |   Related Tips: More > Encryption

Problem

You need a simple and fast tool to encrypt and decrypt integer values without having to deal with complex encryption keys, certificates and other complex encryption methods. In this tip we cover a simple approach to encrypt integers.

Solution

I coded two very short and simple T-SQL functions that do integer encryption and decryption. The algorithms used are very simple and implement a simple arithmetic encryption algorithm that encrypts an integer value to a string using the one of these values (ABCDEFGHIJKLMNOPQRSTUVWXYZ).

The encryption algorithm:

  1. Takes the modulo of the given integer number by 26 (the length of alphabet)
  2. Finds the letter in the alphabet that in the position of the result and concatenates it to the result string
  3. Divide the given number by 26 and continue with only the integer result
  4. Continue steps 1,2,3 until the division result is 0
  5. The resulting string is encrypted value

The decryption algorithm:

  1. Take the given string and reverse it
  2. Loop through all the characters of the string, from 1 to the length of the encrypted string
  3. Find the position of that letter in the alphabet and multiply it by the power of 26 by the current index letter in the given string
  4. Accumulate the results for each value into the decrypted number result
  5. After the loop is finished the decrypted value is returned

Here is the T-SQL code:

Encryption Function

CREATE FUNCTION dbo.Encrypt2String (@Number2Encrypt INT)
RETURNS VARCHAR(30)
AS
BEGIN
 DECLARE @AlphabetCode VARCHAR(26)
 DECLARE @AlphabetLength INT = 26
 DECLARE @EncryptedString VARCHAR(30) = ''
 DECLARE @CurrDigitInNumber CHAR(1)

 SET @AlphabetCode = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 SET @EncryptedString = SUBSTRING(@AlphabetCode, (@Number2Encrypt % @AlphabetLength) + 1, 1)

 WHILE @Number2Encrypt > 0
 BEGIN
  SET @CurrDigitInNumber = SUBSTRING(@AlphabetCode, ((@Number2Encrypt / @AlphabetLength) % @AlphabetLength) + 1, 1)
  SET @Number2Encrypt = @Number2Encrypt / @AlphabetLength

  IF @Number2Encrypt != 0
   SET @EncryptedString = @CurrDigitInNumber + @EncryptedString
 END

 RETURN @EncryptedString
END
GO

Decryption Function

CREATE FUNCTION dbo.Decrypt2Number (@encryptString VARCHAR(7))
RETURNS INT
AS
BEGIN
 DECLARE @AlphabetCode VARCHAR(26)
 DECLARE @AlphabetLength INT = 26
 DECLARE @Idx INT = 0
 DECLARE @Letter CHAR(1)
 DECLARE @LetterValue INT
 DECLARE @ReversedEncryptString VARCHAR(7)
 DECLARE @DecryptedNumber INT = 0

 SET @ReversedEncryptString = REVERSE(@encryptString)
 SET @AlphabetCode = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

 WHILE @Idx < LEN(@encryptString)
 BEGIN
  SET @Letter = SUBSTRING(@ReversedEncryptString, @Idx + 1, 1)
  SET @LetterValue = (CHARINDEX(@Letter, @AlphabetCode) - 1) * POWER(@AlphabetLength, @Idx)
  SET @DecryptedNumber = @DecryptedNumber + @LetterValue
  SET @Idx = @Idx + 1
 END

 RETURN @DecryptedNumber
END
GO

Here is an example of how to use these functions.

SELECT dbo.Encrypt2String(65536) 

The result is: DSYQ

SELECT dbo.Decrypt2Number('DSYQ')

The result is: 65536

Notes

These functions were tested using SQL Server 2012 and 2014 Developer edition, but should work on all versions and editions.

Next Steps
  • You can modify the order of the letters in the @AlphabetCode string so the values are not in order like ABCDEFG...
  • You can add other characters into the @AlphabetCode string such as numbers or other characters
  • Use this to scramble IDs, SSNs, etc., but note that leading zeroes are removed when the value is decrypted.
  • Use these functions like any other SQL Server function.


Last Updated: 2017-02-03


next webcast button


next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 19, 2018 - 12:28:13 AM - Marwan Back To Top

.Is it possible to encrypt an integer to integer, meaning the function of encrypt RETURN integer not String, because I need to save the result into the same integer field in the table?

Thanks


Sunday, February 18, 2018 - 3:52:21 AM - Eli Leiba Back To Top

Plese note that this code is a custom encoding scheme, but it is not different from using Base64 (if you change the alphabet & add padding logic) to try to obfuscate data (which is not a recommended way to protect data). 

It is important to warn users that this approach amounts to simply obfuscation through obscurity

The given code assumes the adversary cannot guess/find the encoding “alphabet”)

 


Monday, February 06, 2017 - 8:09:43 AM - Brent Newbury Back To Top

Please be careful with this method as it is not encryption. This is a convoluted form of encoding.


Learn more about SQL Server tools