473,322 Members | 1,408 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Encrypting a database

mshmyob
904 Expert 512MB
This is NOT a question regarding coding, it is a question concerning methodology.

I need to encrypt a database. The database being used does not contain any encryption abilities like TDE for SQL Server. I have therefore worked out a method to do symetric encryption and key storage etc.

My question is which method should I use for the encryption/decryption and executing SQL statements.

I am thinking to encrypt the database and then decrypt it when I need to run an SQL statement against the data. After I execute the SQL statement and get my return set I can encrypt it again.

I see a problem with this method as when the database is in the decrypted state all the data is in clear text. If the system crashed when the SQL statement is being executed the database would be left in a clear text state until I could encrypt it again. This is not acceptable.

I am not sure what the proper method would be to encrypt/decrypt and execute SQL statements against the database.

Any ideas or suggestions.. especially from anyone who has attempted this before.

*****
New thought

I have thought about encrypting and decrypting just the data rather than the database file but am unsure what would happen if for instance I had a column with a data type of DATE. After I encrypt the date it would I assume no longer look like a date and how would the system accept it in a date column?
Thanks....
Jan 24 '19 #1
12 1584
NeoPa
32,556 Expert Mod 16PB
Are you aware that ACCDB databases are encrypted if you give them a database password?
Jan 24 '19 #2
mshmyob
904 Expert 512MB
Hey NEO,

Long time... I am not using a MS Access database. This is a pet project that I am working on. I am using an SQLite database and Python. There are addons to buy for encryption for SQLite but I do not want to pay for them. I have the code for Python to do all the encryption and decryption and I think I have a proper method to hide the keys but I am stuck on the concept of saving and retrieving the encrypted/decrypted data in the database.
Jan 24 '19 #3
NeoPa
32,556 Expert Mod 16PB
I was trying to keep you happy, just minding my own business ;-)

If I'm honest what you plan sounds like it could be a bit of an overhead for every transaction, but it's not really an area I can help much with.

Best of luck anyway of course :-)
Jan 24 '19 #4
Rabbit
12,516 Expert Mod 8TB
If you're encrypting field by field, you would have to set every field to a binary or text data type. You would need to use type conversion functions to interact with the data. You would also want to use a unique salt for each row and/or data element. NeoPa is right, it would probably introduce a lot of overhead.

If you really need the security, it's better and more secure to pay for it than trying to roll your own.
Jan 24 '19 #5
NeoPa
32,556 Expert Mod 16PB
Rabbit:
If you really need the security, it's better and more secure to pay for it than trying to roll your own.
I have to say I agree with that. The ramifications of rolling your own are already (becoming) obvious to you. Far from trivial.

NB. I would say Rabbit is our security guru here as far as Access is concerned, and probably beyond that too.
Jan 24 '19 #6
mshmyob
904 Expert 512MB
Thanks. I am going to use pre built functions for encryption (the paid functions for Sqlite) do it field by field so I can just do that myself. I did find an article talking about binary fields and such. Just because this is my own pet project I would like to try just to get more knowledge.

Thanks for the feedback.
Jan 24 '19 #7
NeoPa
32,556 Expert Mod 16PB
A very interesting project as far as exploring potentials is concerned. Just be very careful of promising results until you've ironed out all the complications ;-)
Jan 25 '19 #8
mshmyob
904 Expert 512MB
So assume I have a table with 5 columns

col1 - primary key - auto generated - Not encrypted
col2 - text - encrypted
col3 - text - encrypted
col4 - text encrypted
col5 - IV column - unique for each row - obviously not encrypted

Assume col4 has just contains single words. Now I execute the following simple query

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tablename WHERE col4='Apple';
I can only think that I would decrypt each row and do a comparison for the word Apple. I have basically eliminated using indexes since I would have to go through each row and I also defeated the purpose of a SELECT statement since I am not returning a return set with the SELECT statement really but with my code that I need to create.

I assume I am thinking about this incorrectly since I don't see this working properly. What am I missing???

Confused....
Jan 25 '19 #9
mshmyob
904 Expert 512MB
Hey Neo, not promising anything to anybody. I am doing this all for myself to get a better understanding of encrypting a database without built in tools. I really want to know the details.
Jan 25 '19 #10
mshmyob
904 Expert 512MB
Actually with some more research I think I came up with the solution of how to query encrypted data without first decrypting and then iterating through the data with application code.

I can directly query the encrypted data with the concept of Blind Indexing. I think I will try this route.

Thanks
Jan 25 '19 #11
Rabbit
12,516 Expert Mod 8TB
It will come down to how secure is secure enough.

Creating an index on a HMAC means loosening the security slightly to accommodate the increase in performance. The security weakness with the this approach is that the HMAC can't use a salt/nonce/initialization vector as part of its calculation. Therefore, the same inputs run through the HMAC result in the same output. Otherwise the index wouldn't work.

In practice, what this means for an attacker is that if they are able to figure out the value of one field on one account, then at a minimum, they now know that value maps to that HMAC throughout the rest of the records in that table for that field.

How much that small leak of information matters comes down to how sensitive it is to maintain the confidentiality of that piece of data.

For example, leaking data on their preference of deodorant and everyone else who likes that same deodorant probably isn't much of an issue. But leaking a zip code and everyone else that lives at that zip code might be more of an issue.
Jan 25 '19 #12
mshmyob
904 Expert 512MB
You are correct Rabbit but I want to see if I hash it with a hidden salt (I will encrypt The salt) and have it on a different server. That way if the database gets compromised all they will know is that one of the encrypted columns (they won't know which one) contains like data in some rows. But without the salt they will not be able to determine the prefix which is the actual data.
Jan 26 '19 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

12
by: kimi | last post by:
Hello, I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have been working with SQL Server, Building ASp WebSites for many years now. I am by no means an expert - nor have I had...
6
by: Dayne | last post by:
Guys, I am writing a database application(vb.net , sql server) and is presently storing the connection settings in a xml file...not very secure though. What is a safer method in a dynamic...
1
by: jimfortune | last post by:
This idea is still in the process of formulation. I'm considering the idea of storing encrypted data in memo fields. Since the data is for internal use only I don't think the legal limits on...
11
by: frizzle | last post by:
Hi there, I need an encrypting function, but haven't got a clue where to start. First a string has to be encrypted with two different encryption keys. Both output should be anything a-z / A-Z /...
3
by: Thirsty Traveler | last post by:
I hear that MD5 is not recommended for encrypting database passwords in that it can be compromised. Does anyone have a recomendation (SHA-1, etc.) on an algorithm that would be more appropriate.
5
by: Chris Dunaway | last post by:
I have an application which is installed on a network share to be run from one or more workstations. I have granted trust to the applications on the workstations and the appropriate permissions on...
1
by: vikram | last post by:
How can we encrypt app.comfig keys in .net 2003 ? Thanks
2
by: Amar | last post by:
Hi All, I want to insert my password into the mysql database by encrypting it so that I can also retrieve the password. Before I was using sha1() for encrypting password,but it is an one way...
2
by: SeeSharp Bint | last post by:
Visual Studio 2005, dotnet, c#. Microsoft SQL Server. Windows XP forms application. Temporarily, for my database application, I have been storing the various elements of database connection...
1
Rabbit
by: Rabbit | last post by:
INTRODUCTION There has been considerable interest in the forum regarding Access and security. This is a primer on that topic. First off, if you have a need for security, don't do it in Access....
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.