5

The scenario is that I want to encrypt finance numbers in a column with a data type of int in a sql server table. It is a big app so it is difficult to change the table column data type from int to any other data type.

I'm using sql server 2005 and asp.net C#.

Is there a two-way encryption method for a column with a data type of int?

Could I use a user-defined-function in sql server 2005 or a possibly a C# method?

Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
Mike108
  • 2,089
  • 7
  • 34
  • 45
  • What exactld do you want to achieve here? Seems non-sensible and I dont really know how to answer more than "no" or "yes" here. – TomTom Mar 23 '10 at 07:08
  • I'm concerned for you in that you may have some serious security design flaws in the application. Perhaps if you provide the context as to why you wish to implement security in this way we can provide a solution to the root cause issue. – John Sansom Mar 23 '10 at 08:51
  • @John Sansom: The scenario is simple. We want our financial data to be encrypted in the database for security reason. – Mike108 Mar 24 '10 at 01:24
  • Encrypting a single column in a database is not secure, no matter how you do it. – Hot Licks Dec 22 '11 at 03:48

5 Answers5

4

I'm sorry but I simply can't see the rationale for encrypting numbers in a database. If you want to protect the data from prying eyes, surely SQL Server has security built into it, yes?

In that case, protect the database with its standard security. If not, get a better DBMS (though I'd be surprised if this were necessary).

If you have bits of information from that table that you want to make available (like some columns but not others), use a view, or a trigger to update another table (less secured), or a periodic transfer to that table.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • You could grab the database files and examine the raw binary for valuable information. That would bypass standard security unless the entire database is encrypted which I believe requires the Enterprise edition. You could encrypt individual columns with SQL Server but then its difficult to use them with Entity Framework. – Ian Warburton Aug 20 '13 at 11:22
  • @Ian, I think it's a given that your important servers should be physically secure. Otherwise you can DOS a corporation just by walking off with them. Similarly, the network paths to a server should equally be protected so that you can't, for example, telnet to them willy nilly. – paxdiablo Aug 20 '13 at 11:30
  • Apparently Sony lost 12,700 bank card numbers which weren't encrypted. – Ian Warburton Aug 20 '13 at 11:46
1

XOR?

:)

Hmm, need more text...

 

leppie
  • 115,091
  • 17
  • 196
  • 297
  • XOR is not so good because the same value will result in the same encrypted value. And "close" integers will also result in close values. (Unless you use a different XOR for every row). – Thilo Mar 23 '10 at 07:28
  • Hence the :) after the answer. – leppie Mar 23 '10 at 07:36
0

There are a few two way encryption schemes available in .Net.

Simple insecure two-way "obfuscation" for C#

You can either convert the integer to it's byte array equivalent or convert it to a base-64 string and encrypt that.

Community
  • 1
  • 1
Spencer Ruport
  • 34,865
  • 12
  • 85
  • 147
  • None of them are FROM int TO int, though. That is the main problem. In any sensible scheme, the result is a long as the key. For an Int to Int encryoption a replacement table is the only solution I can come up with, and that would have 4 billion entries ;) Or some bitshifting (like XOR), but that hardly counts as encryption. – TomTom Mar 23 '10 at 07:12
  • Yes. The main problem is "FROM int TO int". – Mike108 Mar 23 '10 at 07:20
  • Mike why would you do that? Encrypted data is supposed to be useless to look at. In light of that why would you care what type the encrypted result is? – Spencer Ruport Mar 23 '10 at 15:47
  • Because it is a old-big app that is hard to change the column type. – Mike108 Mar 24 '10 at 01:28
  • 1
    That doesn't make any sense. If your big old app is expecting the unencrypted value then you're still going to have to make changes everywhere that make it expect the encrypted value. And if you're going to do that it's not much more of a hassle to change the field type. – Spencer Ruport Mar 24 '10 at 01:34
0

Well, every injective, surjective function from int to int can be used as a way to "encode" an integer.

You could build such a function by creating a random array with 65536 items with no duplicate entries und using f(i) = a[i]. To "decode" your int you simply create another array with b[i] = x | a[x] = i.

As the others have mentioned, this may not be what you REALLY want to do. =)

Edit: Check out Jim Dennis' comment!

Jens
  • 25,229
  • 9
  • 75
  • 117
  • Problem with this is that the same value will result in the same encrypted value. So you can still find pairs of people with the same salary. – Thilo Mar 23 '10 at 07:36
  • 1
    You might be able to do it with with an extra column containing salt. If the salt values are large enough (32 random values, for example) then you eliminate almost all collisions. (I could even have a UNIQUE constraint on the salt column, enforced by the RDBMS engine). Overall the idea here is still absurd ... but theoretically ... – Jim Dennis Mar 23 '10 at 07:46
-1

You might want to look at format preserving encryption.

Accipitridae
  • 3,136
  • 19
  • 9