Sql string translation

I'm looking for a simple (ideally inline) tsql statement to implement the following translations. We have reference number field in our data that we wish to obscure. It's in the format of AAA1234, always 7 digits, with the first three being alpha characters and the rest being numbers. We could use some kind of strong encryption, but we would also like the users of the data to be able to decipher开发者_Go百科 the customer numbers if required so they can cross reference. It's likely that cross referencing will be done from a paper report.

For basic obscuring there are numerous approachs, for instance we could convert

• ABC1234 to ZYX4321 (ie A becomes Z, B - Y etc)
• ABC1234 to 0102034321 (ie A becomes 01, Z becomes 26)
• ABC1234 to CBA3412 (reverse the letters and swap digits 1 and 2 with 3 and 4)

Edit - Assume the requirements are as stated, I know my way around encryption, security, user authentication/authorisation and there are genuine reasons for wanting to approach the problem in this way. No snake oil, no big bad programmer out guessing requirements. Does anyone have some neat sql to do this?

Others already mentioned that the whole concept is, well, questionable.
You should ask yourself what kind of attacks this kind of encryption (I prefer the word 'scrambling' here to differentiate from 'real' encryption) could prevent, what kind of attacks it could not prevent, and how do they correspond to attacks you actually expect and are trying to prevent.

Generally, the idea as you describe it is 'security through obscurity' at best, so it adds some 'security feeling' but almost no 'real security'.

Nevertheless, here are some simple solutions.

1. Store the string as its hex representation
'ABC1234' becomes '0x41424331323334'.

DECLARE @x varchar(30)

-- scrambling: convert string to its hex representation
SET @x = sys.fn_varbintohexstr(CAST('ABC1234' AS varbinary))
SELECT @x -- returns '0x41424331323334'

-- unscrambling
EXEC('SELECT CAST(' + @x + ' AS varchar(30))')

Here I used undocumented fn_varbintohexstr function to scramble and EXEC to unscramble. You cannot easily unscramble all the data this way, and have to do it by one record - you can see it as bad (inconvinient) or good (an adversary will have to think a little bit to obtain all the unscrambled data at once).

There are also other techniques for binary-to-string conversion if you don't like these ones.

2. Store the string as a number (XORed with a random salt)
'ABC1234' becomes -5389962212370045368.

Please note that this won't work for strings longer than 8 characters, because MSSQL builtin XOR works only with integers (and the largest of them is 8 bytes long). Of course, one could create a UDF to perform XOR on arbitrary long binary type, but here I tried to keep things simple.

-- use a random salt (a kind of 'shared secret')
DECLARE @salt bigint
SET @salt = 0xf47142dde0d49248

DECLARE @x bigint

-- scramble: cast to bigint and XOR with the seed
SET @x = CAST(CAST('ABC1234' AS binary(8)) AS BIGINT) ^ @salt
SELECT @x -- returns -5389962212370045368

-- unscramble
SELECT CAST(CAST(@x ^ @salt as varbinary) as varchar)

Here a random salt is used for both scrambling and unscrambling. You could try building some extra security on top of it - say, store the salt value in your application and pass it to the database on each call, so that database access alone is not enough to easily get the data (of course, this is still quite easy to break - for instance, with a known plaintext attack).

3. Use builtin symmetric encryption
'ABC1234' becomes 0x01000000AECBC6E2A5B51F09B6953DFD7A648675E4DD3CE46E93BC0D.

This relies on MSSQL2005+ builtin symmetric encryption functions (I believe, internally it uses AES256 or 3DES depending on the platform).

-- use random passphrase
DECLARE @pwd varchar(30)
SET @pwd = 0xA0880D9980AE0C4EA28A8A247763AB5B
SELECT @pwd

-- encrypt with the passphrase
DECLARE @x varbinary(8000)
SET @x = EncryptByPassPhrase(@pwd, 'ABC1234')
SELECT @x -- 0x01000000AECBC6E2A5B51F09B6953DFD7A648675E4DD3CE46E93BC0D

-- decrypt
SELECT CAST(DecryptByPassPhrase(@pwd, @x) AS varchar)

See also EncryptByKey and other crpytographic functions and How to: Encrypt a Column of Data. Here some real encryption is used, and key management is natively supported by MSSQL, so this could be a basis for building some 'real' security (surely, there's still a lot of ways to screw it up :-) ).

A better thing to do is to use a hash in the column, then create another table that stores hashes and the reference number, then restrict access to that table.

The users of the data need to access it, but the field should be obscured so no one can read it? And then, what do you want to do, inform the people who need access to the numbers about your snakeoil algorithm and then hope that they will not tell it to others? This way will make it more complicated for your users and insecure for your employer. That's not acceptable in my eyes. You need some kind of user restriction or strong encryption, but encryption is not the first choice here, because everyone would need the same key and so it wouldn't need a long time until someone who's not authorized will get the key, and then you have to change the whole DB. The best way, really, would be a good user authentication and restriction system.