Data Encryption / Decryption and a Database System

[koeks525]

Good day guys,

For our second year project, we have to basically code up a Store Rental Management System for a mall. This obviously would involve code (we have to use C# for the application, and Microsoft SQL Server 2014 for the database).

Do applications like this practice some kind of data encryption / decryption? I thought it would be really sweet to encrypt sensitive information like customer, employee, shop rental agreement data, etc. I wrote out a class which deals with encrypting / decryption strings (I used a YouTube video to help me with this ).

My question is, how would you create the fields in your database? Would every field have to be of type varchar (or text)? One thing I noticed was that even decryption a single digit took up quite a number of characters in the corresponding field in the table.

This project isn't going to be made for "commercial purposes" but I would like to make it as "real" as possible.

Thanks.

 

[Necropolis]

Depends.

What sort of app is it and how are you connecting to the database?

 

[envo]

I would stay away from the "text" field type as this has been deprecated since 2005 already (even though MS still supports it). Use varchar(max)

Encryption, in any form, adds a certain overhead to the CPU and storage. It's essentially metadata that is required for the decryption to happen.

Think of Zip/RAR. You Zip or RAR a file, making it bigger than it was, because the file size is so small that the metadata to unzip/unrar it is bigger than the actual file size.

So yes, a single digit character being encrypted will end up being longer than just the single digit. And with data you don't know the length of, you will be glad to have found varchar(max). This does complicate storage and performance of the application slightly, but it depends.

Usually you're required to encrypt/store securely, credit card information. However with the POPI act coming into play, this might change dramatically as time goes on.

Encryption is fun to play with, you have to be careful of the pitfalls as well. Like being able to sniff a particular message based on it's encryption signature to figure out what it says etc.

I use a modified version of http://stephenhaunts.com/projects/block-encrypter/ for my encryption. Can read more about the in's and out's here: http://stephenhaunts.com/2013/05/18/cryptography-in-net-hybrid-encryption-protocols/

 

[shauntir]

All of this depends on what you are trying to achieve. If you are looking for a database wide encryption look into TDE.

This does not prevent you from querying your data and viewing it as is. If you are looking for some kind of specific pieces of information to encrypt, it is possible to use the master key created for TDE to encrypt specific columns.

Also note, if your data is something that you do not want to be able to decrypt (passwords, credit card info etc), then look into hashing the data.

 

[koeks525]

Depends.

What sort of app is it and how are you connecting to the database?

I am creating a desktop application which will allow system administrators to manage shops in the mall... new shops can be created, which are rented out to owners. Owners can log calls if they experience a problem with their rented shop. Calls can then be given to an outsourced service provider to handle, or the call can be handled by mall staff

I would stay away from the "text" field type as this has been deprecated since 2005 already (even though MS still supports it). Use varchar(max)

Encryption, in any form, adds a certain overhead to the CPU and storage. It's essentially metadata that is required for the decryption to happen.

Think of Zip/RAR. You Zip or RAR a file, making it bigger than it was, because the file size is so small that the metadata to unzip/unrar it is bigger than the actual file size.

So yes, a single digit character being encrypted will end up being longer than just the single digit. And with data you don't know the length of, you will be glad to have found varchar(max). This does complicate storage and performance of the application slightly, but it depends.

Usually you're required to encrypt/store securely, credit card information. However with the POPI act coming into play, this might change dramatically as time goes on.

Encryption is fun to play with, you have to be careful of the pitfalls as well. Like being able to sniff a particular message based on it's encryption signature to figure out what it says etc.

I use a modified version of http://stephenhaunts.com/projects/block-encrypter/ for my encryption. Can read more about the in's and out's here: http://stephenhaunts.com/2013/05/18/cryptography-in-net-hybrid-encryption-protocols/

Thank you so much for this information Really really useful... So I was "half-right" in thinking all fields would need to be set as varchar.... I assume varchar(max) has no limit to the amount of text it can store?

 

[Necropolis]

Store the data normally in the database - just look at an encrypted connection between the app and the database.

I know this is possible in .Net - just nothing I have done myself before.

 

[animal531]

In general you don't encrypt things unless if neccesary. You might encrypt really sensitive data, or hash passwords as shaun mentions. Don't encrypt everything.

 

[DA-LION-619]

Also why not use Entity Framework?

 

[Sonic2k]

Store the data normally in the database - just look at an encrypted connection between the app and the database.

I know this is possible in .Net - just nothing I have done myself before.

If its financial stuff, DUKPT is a good starting point... but encryption in the DB itself...nah!

 

[Spacerat]

yes only need to encrypt & salt passwords. not everything. also depends on your. if you run app in RDP then end users have to access to database so not an issue. access to certain data can be handled by app level

 

[envo]

yes only need to encrypt & salt passwords. not everything. also depends on your. if you run app in RDP then end users have to access to database so not an issue. access to certain data can be handled by app level

You mean hash and salt passwords. You do not want someone to be able to decrypt them. Salting helps with the hash because it's easy to hash passwords and brute them, but if it's salted, the hash looks different.

We had a company who hashes passwords, then never gave us the admin password. I googled the hash and it turned out to be a pretty easy straight forward decryption (against an already existing dictionary). So it showed me they never salted their hashes which meant someone like me can just google it if they have access to the database as well.

encryption implies you want it to be decrypted at some point

@OP, yes, varchar(max) has no limit, and can even go beyond the limit text offers

 

[Spacerat]

You mean hash and salt passwords. You do not want someone to be able to decrypt them. Salting helps with the hash because it's easy to hash passwords and brute them, but if it's salted, the hash looks different.

We had a company who hashes passwords, then never gave us the admin password. I googled the hash and it turned out to be a pretty easy straight forward decryption (against an already existing dictionary). So it showed me they never salted their hashes which meant someone like me can just google it if they have access to the database as well.

encryption implies you want it to be decrypted at some point

OK yes, to be semantically correct

 

[Solarion]

Encryption is something I'm also needing to pin down at some point.

If anyone has a spare moment could you please take a look at this on Codeproject:

C# AES 256 bits Encryption Library with Salt

I would love to use this example. What I find confusing is that although he is using what looks like good encryption however his AES_Decrypt function seems to be returning a plain text string. No doubt I am missing something. Passwords should be stored as encrypted bytes in SQL. He is using a custom textbox for the password and storing the contents in securestring. So plus 1 there.

One last thing. That texbox, Original Text. Is this supposed to be your Salt Password? Could I not store that in the Encryption class?

 

[Napalm2880]

This project isn't going to be made for "commercial purposes" but I would like to make it as "real" as possible.

Thanks.

I've worked on a commercial property management app a few years back. Memory is a little rusty but feel welcome to fire questions my way.

Some high level features are:
-User and Management - for Tenant contact details, company/joint venture details, etc.
-Property Management - sqm of the store, details on common areas etc.
-Forecasting - typical tenants will sign 1 to 10 year leases. you can do some graphing here based on rental cost square meterage of the store, throw in inflation, additional costs for sqm of common areas, etc. etc.
-Lease Document Management - As a nice to have you can maybe add a feature that exports the relevant info above into a PDF, for your "Tenants Lease Agreement".

On the encryption side of things. Encryption of user data isn't something that is big in the industry but it should and WILL be soon! If you've read about the recent hacking of British Airways, you'll notice they quickly came out with a statement saying "only credit card data was compromised". This might seem quite strange but the reason for this is that the fines for compromised user data (see GDPR) are much higher than the fines for credit card data (see PCI Compliance). This is a big wake up call for the IT Industry as a whole to start treating user data as importantly as credit card info.

 

[zippy]

Don’t permit direct connections to the dB tables. Access via a services layers, and very careful with api’s that allow extracting data sets.

 

[Spacerat]

For passwords you use one way encryption. I.e. you cannot decrypt. The encryption uses a secret key and also a salt. Each time encrypt the password you use a newly generated salt. You need to store the salt value as well. The idea is that 2 users with same password will have diff encrypted representations of the password because of unique salt. Also changing the salt each time makes cracking harder. Lots out there on the subject.

 

[[)roi(]]

For passwords you use one way encryption. I.e. you cannot decrypt...

A simple nuget for this is https://github.com/BcryptNet/bcrypt.net

var salt = BCrypt.GenerateSalt();
var hashedPassword = BCrypt.HashPassword(inputKey: "password", salt: salt);
Console.WriteLine("salt: {0}, hashedPassword: {1}", salt, hashedPassword);
// salt: $2a$11$pnp1Yu4kZyaaI1f.Dz41se, hashedPassword: $2a$11$pnp1Yu4kZyaaI1f.Dz41se30UuQLwKzzgmr0NjoIMFjOQKEKzjHdi

var result = BCrypt.Verify(text: "password", hash: "$2a$11$jBJXaO6j0JgG6KggmWIGnuJ4C92cGT2/ousb9KlgfLf93UjfEu05e");
Console.WriteLine("password verify: {0}", result);
// True

 

[The_Ogre]

You mean hash and salt passwords. You do not want someone to be able to decrypt them. Salting helps with the hash because it's easy to hash passwords and brute them, but if it's salted, the hash looks different.

We had a company who hashes passwords, then never gave us the admin password. I googled the hash and it turned out to be a pretty easy straight forward decryption (against an already existing dictionary). So it showed me they never salted their hashes which meant someone like me can just google it if they have access to the database as well.

encryption implies you want it to be decrypted at some point

@OP, yes, varchar(max) has no limit, and can even go beyond the limit text offers

Hey Chuck, how the heck do you decrypt a hash? It's made to be one way - and no, not talking about that joke that is MD5

 

[The_Ogre]

Rainbow tables. If it's a weak hash, rainbow tables will make it easy... salt will mitigate it.
In a nutshell, rainbow tables are precomputed hashes of a password list. Search for the hash and you have the password.

No, you're coming from the opposite point of view. As a developer you have no reason to store a users password.

If he registers you salt his password and hash it. If he logs in you compare the hashed, salted password with what you have stored. And my personal preference is to create a 6 digit random salt, hash it and adding it to the hashed password.

 

[MrGray]

There is column level encryption capability available in MS SQL, I believe. You can alternatively encrypt the entire DB but I believe you need a full enterprise license for this.