SQL Encrypt/Decrypt numbers

Question

I wonder if that is possible in TSQL

I know vigenere (cipher) encryption can encrypt values like "Hello, How are you" to become "Hfnlp, Jox crf aov" but the problem is that it keeps the numbers un-encrypted "1234567" will remain "1234567"

any idea how to encrypt everything including numbers and to have it as Function/Procedure in SQL Server?

`SQL Server` has its own Cryptographic functions https://learn.microsoft.com/en-us/sql/t-sql/functions/cryptographic-functions-transact-sql?view=sql-server-2017 — Squirrel, Mar 15 '19 at 05:23
I think you can include in the alphabet also the digits. I remember I did this during college and was enough. — Nițu Alexandru, Mar 15 '19 at 09:28

score 0 · Accepted Answer · answered Mar 15 '19 at 10:39

0

I have build the script myself

To Encrypt

ALTER FUNCTION [dbo].[fnEncrypt] 
(
    @Str varchar(max),
    @key varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
--Akram Mustafa
declare @i int = 1
declare @x int = 0
declare @Result varchar(max) = ''
declare @List varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
declare @TempKey varchar(max) = @key

WHILE LEN(@TempKey) < LEN(@Str)
BEGIN
    SET @TempKey = @TempKey + @key 
END

SET @key = @TempKey

WHILE (@i <= LEN(@Str)) 
BEGIN
    IF (CHARINDEX(SUBSTRING(@Str, @i, 1), @List) > 0)
    BEGIN
        SET @x = ((CHARINDEX(SUBSTRING(@Str, @i, 1), @List) +  CHARINDEX(SUBSTRING(@key, @i, 1), @List)) % LEN(@List)) + 1
        SET @Result = @Result + SUBSTRING(@List, @x, 1)
    END
    ELSE
    BEGIN
        SET @Result = @Result + SUBSTRING(@Str, @i, 1)
    END
    SET @i = @i + 1
END

RETURN @Result
END

To Decrypt

ALTER FUNCTION [dbo].[fnDecrypt] 
(
    @Str varchar(max),
    @key varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
--Akram Mustafa
declare @i int = 1
declare @x int = 0
declare @Result varchar(max) = ''
declare @List varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
declare @TempKey varchar(max) = @key

WHILE LEN(@TempKey) < LEN(@Str)
BEGIN
    SET @TempKey = @TempKey + @key 
END

SET @key = @TempKey

WHILE (@i <= LEN(@Str)) 
BEGIN
    IF (CHARINDEX(SUBSTRING(@Str, @i, 1), @List) > 0)
    BEGIN
        SET @x = ((CHARINDEX(SUBSTRING(@Str, @i, 1), @List) -  CHARINDEX(SUBSTRING(@key, @i, 1), @List) + LEN(@List) - 1) % LEN(@List))
        IF @x = 0
        BEGIN
            SET @x = LEN(@List)
        END
        SET @Result = @Result + SUBSTRING(@List, @x, 1)
    END
    ELSE
    BEGIN
        SET @Result = @Result + SUBSTRING(@Str, @i, 1)
    END
    SET @i = @i + 1
END

RETURN @Result
END

answered Mar 15 '19 at 10:39

asmgx

7,328
15
82
143

Whatever you do, don't actually use this to encrypt anything worth keeping secret. It's fine as a homework exercise or for fun, but not in a production database. – Jeroen Mostert Mar 15 '19 at 10:51
@JeroenMostert why? – asmgx Mar 15 '19 at 11:23
2

Because a basic rule of encryption is to never roll your own, unless you're already an expert in the field. Use algorithms and implementations that have been vetted by professionals to not be weak to attacks. Substitution ciphers like these have well-known techniques for breaking them -- try encrypting `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA` with any key you like, for example, to see how easy it is to determine the key length. [See also](https://en.wikipedia.org/wiki/Vigen%C3%A8re_cipher#Cryptanalysis). SQL Server's built-in functions are based on industry standards and do not have such weaknesses. – Jeroen Mostert Mar 15 '19 at 11:32
@JeroenMostert thanks for the advice, but it will be used for basic need for non-IT guys within the same organisation, beside they will not be able to call the function they will just see the encrypted values – asmgx Mar 15 '19 at 11:39
1

The correct measure to apply isn't "how ignorant/persistent are my users" but "how important is the data". If you're using this to protect your grandma's favorite recipe for key lime pie or the CEO's golf statistics, it's fine. If you're using this to protect credit card numbers of your customers or HR's assessment of your colleagues, not so much. Using proper encryption is a good way of covering your own behind if anything *does* happen, as opposed to having to admit that you just cobbled something together that looked good to you. – Jeroen Mostert Mar 15 '19 at 11:46
@JeroenMostert there are certain business rules we have to follow in the ecryption, one of them is to be readable easily .. so not all encryptions fit this rule – asmgx Mar 15 '19 at 12:38
That's a very nasty rule that will inevitably decrease the security of the encryption, even a non-naive one -- the whole point of encryption is that details about the original string (like its length) should not leak, as these give an attacker easy clues on how to break things. I hope that whoever is creating these rules is aware of the consequences. It may be worth considering anonymization/obfuscation in views instead for presenting values to the users (e.g. seed an RNG and produce a string of equal length, star things out, etc.) while storing the actual value with proper encryption. – Jeroen Mostert Mar 15 '19 at 12:49
See also: [dynamic data masking](https://learn.microsoft.com/sql/relational-databases/security/dynamic-data-masking). – Jeroen Mostert Mar 15 '19 at 12:52
@JeroenMostert not all businesses are the same and not becuase of you are specialist in this field means evry one has to follow, the nature of my case does not require more than what i did, if the people asked to see the who records in the database we would've give it to them ( with their managers approval) so we are not talking about highly senstive data for these users. the users are the ppl who generated the data in the first place – asmgx Mar 15 '19 at 13:35
Perhaps this is not encryption. Encryption is a way of keeping data secret. Perhaps this is Encoding or Obfuscation: it's okay if people can determine the plaintext in a few seconds, but it's important they can't see it at a glance. If that's the case, then this is fine. – David Dubois Mar 16 '19 at 14:13
... But if the data needs to be kept secret, you can't assume that it will be seen only by those you allow. You need to ask, how much would it cost your company if someone malicious got your data, decrypted it, and published it for all to see on the web. If the answer is "We'd lose millions" or "The company would be bankrupt" or "We'd all go to prison", then you need to think twice. ... If the answer is "Whatever" and you would shrug your shoulders, then this method is okay. ... Just be aware that this can be decrypted in minutes and you don't need to be an expert to do it. – David Dubois Mar 16 '19 at 14:18
@DavidDubois Thanks for the advice, I will definitely take more care if cracking this encoding will lead to very bad scenarios – asmgx Mar 17 '19 at 20:41

SQL Encrypt/Decrypt numbers

1 Answers1