Solved

securing data from code vs db- pros and cons

Posted on 2013-06-26
13
345 Views
Last Modified: 2013-07-15
when you want to secure the data, is there a standard which is better approach.. from code or db side?
0
Comment
Question by:25112
  • 7
  • 5
13 Comments
 
LVL 5

Author Comment

by:25112
ID: 39277842
from db side, you have encryption, hash etc

i assume you can do the same from front end code (.net/java etc).. and hence the question. when do you prefer the db route and when the code route?
0
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 100 total points
ID: 39277865
If you encrypt on the database, that means the data is sent to and from the database in unencrypted form. This potentially allows for snooping/tampering between the application and the encryption routines on the database. Even if you store the key on the application side, it is still required on the database side to perform encryption. If the database is compromised, your data is at serious risk (just imagine someone running profiler while your application runs).

If you encrypt/decrypt in the application, sensitive data (including the key) is never revealed outside of the application server. Someone would have to compromise both the Web server and database server to access all of your data.

the consideration may change depends on the environment used but as a whole i'd use server-side/application level encryption for the ease of usa and the variety of implementations.
0
 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 400 total points
ID: 39278101
Both approaches have their strengths and weaknesses.

If you are authenticated to the DB, then often the serverside crypto can be fully integrated at the DB side and hence "transparent" to the code - you just read and write SQL as usual, and if you have access to the encrypted columns, they are sent as plaintext (otherwise they are denied in some fashion, DB, dependent). That relieves the author of the responsibility to get the crypto right (it becomes a DBA responsibility) and it remains keyed on the user's account data - hence, there is nothing to reverse-engineer in the code.

By implication though, it requires you to have some secure method of accessing the DB - unless the actual sql link is encrypted, you are sending plain data over the network, and hence interception is possible. it is also possible that an attacker could force the installation of a odbc driver (say) that allows access to the data without the program's knowledge.

Doing the crypto clientside is significantly harder (in that most programmers couldn't code good crypto if their lives depended on it, although there are known-good libraries) and increases the workload on the client, while preventing server-side optimizations from taking place during the queries.

on the other hand, if you do the crypto clientside, you are less dependent on the *database* getting it right - many databases have poor or no support for data encryption, and rumours of "work factor reduction" for agencies being built into major american systems have been common for years (there are plenty of proven examples for things like email of course). Provided you use a good, peer reviewed algo and preferably a library, then you can be sure you are getting the full weight of the crypto, right at the point of use, and that nothing has to be relied upon outside of your security demarc to not fail open.  If you are doing your own security, the security of the link is often no longer an issue (the data is inherently secure) and your key management can be exclusive to your app, not shared with a commodity OS and dbms.
0
 
LVL 5

Author Comment

by:25112
ID: 39278671
Dave,
if the actual app link is https:// and the data is encrypted in db, is that what you are referring in the first point?

sedgwick, does the above resolve the concern you mentioned? (potential for snooping/tampering)
0
 
LVL 5

Author Comment

by:25112
ID: 39278675
when you say 'crypto clientside', are you referring to using encryption through .net code, say, for example?
0
 
LVL 5

Author Comment

by:25112
ID: 39278681
>>If you are doing your own security

what will this app or term be?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 400 total points
ID: 39278710
on your first question:
no. https is how you talk to a webapp; how the webapp talks to the db must be encrypted, unless the webserver and the db server are the same host (and its usually advisable that they aren't) - usually, you can enable TLS on the link from app to database, but that adds extra overheads, and its amazing how many implimentations don't bother to check the certificate is valid.

for the second:
when I say "crypto clientside" I mean either native encryption in the application itself (so dotnet maybe, but I would usually use a known good third party library rather than the ms dotnet core, as there is no guarantee there are no backdoors in something updated via windows update) or some intermediate service that takes the sql query and encrypts it for transmission, or encrypts the data when sent/received.

and finally:
"doing your own security" is more specific. it implies that the crypto keys are handled by your own code, and are dependent on user credentials stored and validated by your own code and not a third party library.
0
 
LVL 5

Author Comment

by:25112
ID: 39278781
thanks

1)so what is the alternative to TLS (SSL certificates?)
2)can you give an example for good third party library or intermediate service?
3)"doing your own security" is then, just using native authentication between SQL and App?
(the app  login has specific permission on tables/views etc)
0
 
LVL 33

Assisted Solution

by:Dave Howe
Dave Howe earned 400 total points
ID: 39278802
1) TLS *is* ssl certificates - for reasons I never entirely followed, the standards body decided that SSL was insufficiently descriptive, so release 3.1 of SSL became "Transport Layer Security" v1.0

2) depends on the language. personally, I favour the LBC libraries

3) more or less. usually, you would want DB level security applied *in addition to* any cryptography. Even if an attacker can't read the data, they can still delete or alter it if the permissions are inappropriate.  You would also (assuming a webapp again) wish to do sql injection mitigation, using techniques such as parameterization or data sanatization, on any data stored unencrypted.
0
 
LVL 5

Author Comment

by:25112
ID: 39279170
Dave,

when doing from code level(.net for example), i understand there is machine key authentication that encrypts while storing the data in database and descrypts while reading the data..
but this way, you can't just analyze the data on the database for any purpose, because the code side has the control.

is this scenario the 2nd option you shared? what are the downside to the above, other than that it is not easy to analyze data at will?
0
 
LVL 33

Accepted Solution

by:
Dave Howe earned 400 total points
ID: 39280578
the main downside is that you have to do anything that involves the encrypted fields yourself, in code - you can't (for example) use sql to sort by that field and get a top 10, you need to pull *all* records, decrypt them, and manually sort and select.
0
 
LVL 5

Author Comment

by:25112
ID: 39286103
Dave, thanks for mentioning the main downside to the above approach.. have you seen any written article what might  other downsides to this approach, if any?

is this the approach (more pros than cons) you would personally choose if you were developing a strategy to secure credit card numbers on the database?
0
 
LVL 33

Expert Comment

by:Dave Howe
ID: 39326437
Depends on what you want to do with the CC numbers.

if you want to use them for a oneshot payment, then usually I wouldn't store them at all - I would pass them immediately to my merchant vendor, get the fulfilment code, and store that unencrypted (its harmless!)

if I wanted to store them for one-click ordering (assuming my MV agreed - I have known MVs cut clients off at the knees for storing CC data) I would concat the user's username and password, hash it, concat the security code from the back of the card, then hash it again as the (client-side) encryption key. I would then store the user+password hash in the db to verify the password on login, and use the hash plus the security code to decode the card data (making it trivial to verify you really have the right security code, as anything else would decrypt to garbage)
0

Featured Post

Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

Join & Write a Comment

If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now