Thread: When to encrypt

A customer of mine recently asked me to try a penetration test on his website,
and I found a nice SQL Injection vulnerability. Using that vuln I was able to
wander round his DB at will, viewing customer information, user logins,
passwords, the lot. He asked me to make some recommendations, of which the
first was to close the vulnerability. But it also got me thinking about
encrypting sensitive information in the DB.

If another SQL Injection vulnerability turns up (which it might, given the
state of the website code), the data in the DB will be vulnerable to anyone
who looks, which might be someone less friendly next time. My gut reaction is
to say "encrypt anything sensitive" but a little thought makes that seem like
an over reaction. After all, aren't modern database systems supposed to be
secure in their own right?

It seems silly to tell him to encrypt everything, including customer names and
addresses, etc. - I've never heard of DB admin recommending such action - and
it'll have an impact on performance. So where do I draw the line? Encrypt
everything on the basis that it adds a layer of security? Encrypt nothing on
the basis that there shouldn't be any way of accessing the sensitive stuff so
the extra security isn't necessary? Or encrypt a few things, just in case?
What do people recommend?

Derek Fountain <dflists@iinet.net.au> writes:

> If another SQL Injection vulnerability turns up (which it might, given the
> state of the website code),

You will never see another SQL injection vulnerability if you simply switch to
always using prepared queries and placeholders. Make it a rule that you
_never_ interpolate variables into the query string. period. No manual quoting
to get right, no subtle security audit necessary: If the SQL query isn't a
constant string you reject it.

Any good driver should support prepared queries. Even on pre-8.0 it should
support them and emulate them by quoting parameters for you. The driver is
much less likely to get this wrong than you are. On 8.0 the driver can pass
the parameters to the server separately from the query in a binary protocol.

The interface you're looking for should look something like:

$sth = $dbh->prepare("select * from foo where id = ?");
$sth->execute($id);
$results = $sth->fetch();
...

Notice that there's absolutely nothing you can do to inject anything using
$id. And there's not really any way to get this code wrong and compromise
security.

Really interpolating variables in the query string should never have been
considered an acceptable coding practice. It's mixing executable code and
program data which has never been a good idea.

There are more complex queries than this where it can be useful to interpolate
some variables, things like

 if ($view_all) {
   $join_type = "OUTER";
 } else {
   $join_type = "";
 }

 $sth = $dbh->execute("SELECT * FROM foo $join_type JOIN bar USING (x) where z = ?");

but these types of queries are relatively rare and careful security analysis
should still be able to show that the query is still completely built up out
of program constants. Not parameters taken from outside data. That's still a
lot easier to prove than checking that every parameter is quoted properly.

--
greg

In the last exciting episode, dflists@iinet.net.au (Derek Fountain) wrote:
> It seems silly to tell him to encrypt everything, including customer
> names and addresses, etc. - I've never heard of DB admin
> recommending such action - and it'll have an impact on
> performance. So where do I draw the line? Encrypt everything on the
> basis that it adds a layer of security? Encrypt nothing on the basis
> that there shouldn't be any way of accessing the sensitive stuff so
> the extra security isn't necessary? Or encrypt a few things, just in
> case? What do people recommend?

There is a model for doing this sort of thing; look at Peter Wayner's
_Translucent Databases_:

  <http://www.wayner.org/books/td/>

  "Most databases provide elaborate control mechanisms for letting the
  right people in to see the right records. These tools are
  well-designed and thoroughly tested, but they can only provide so
  much support. If someone breaks into the operating system itself,
  all of the data on the hard disk is unveiled. If a clerk, a
  supervisor, or a system administrator decides to turn traitor,
  there's nothing anyone can do."

It's worth pointing out that the really serious classes of security
breaches are of the latter sorts.

I have seen several reports, of late, of _serious_ security breaches
at major banks in my country that are, in a sense, of this nature.

One of the banks, CIBC, had a central 1-800 number to which funds
transfer request information was to be faxed by branches.  A typo in
the number, regularly made by branch staff, led to tens of thousands
of records containing sensitive personal information being sent to a
junkyard in West Virginia.

Then there was the time medical billing records were used as props on
a children's show.
http://www.medbroadcast.ca/health_news_details.asp?news_channel_id=1000&news_id=2279&rating=1

In the same jurisdiction, ISM (which used to be an IBM consulting
subsidiary) lost a disk drive containing client data for multiple
insurance companies as well as multiple government departments.  It
appears an employee stole it in order to get a free disk drive; had
the intent been more ominous, the results could certainly have been
bad.

There are database vendors that promote that they are "more secure" as
a result of offering data encryption schemes; this seems a red herring
as in order to be able to use the data, the encryption keys must
correspondingly be available _in the database engine_, which makes
them likely to be readily accessible by the very people that would be
most dangerous should they "turn traitor."

That's not even the worst part; if the encryption keys are sitting in
the DBMS, that even makes them vulnerable to capture by an outsider
who finds a way to inject outside code into some DB interface.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxfinances.info/info/lisp.html
Rules of  the Evil Overlord #127.  "Prison guards will  have their own
cantina featuring  a wide  variety of tasty  treats that  will deliver
snacks to the  guards while on duty. The guards  will also be informed
that  accepting food or  drink from  any other  source will  result in
execution." <http://www.eviloverlord.com/>

On Monday 06 December 2004 12:31, you wrote:
> Derek Fountain <dflists@iinet.net.au> writes:
> > If another SQL Injection vulnerability turns up (which it might, given
> > the state of the website code),
>
> You will never see another SQL injection vulnerability if you simply switch
> to always using prepared queries and placeholders.

<much wisdom snipped>

Indeed, but I'm still interested in the general answer. The server I have been
looking at was hopelessly insecure and SQL injection is only one of its
problems. There were several other ways in! Assume, for example, an attacker
can write his own script directly into the website document tree. In this
case prepared queries don't help protect what's in the database. The attacker
can use them himself if he likes!

Given this type of mess, having logins, passwords, credit card info and the
like encrypted in the DB will add another layer of protection. The question
is, do people normally add this layer, just in case, or do they assume that
all the previous layers will do the job?

Personally I've never encrypted data in this way, but for this guy there does
seem to be a requirement.

From: "Derek Fountain" <dflists@iinet.net.au>

> [snip discussion about encrypting data]

> Indeed, but I'm still interested in the general answer. The server I have
been
> looking at was hopelessly insecure and SQL injection is only one of its
> problems. There were several other ways in! Assume, for example, an
attacker
> can write his own script directly into the website document tree. In this
> case prepared queries don't help protect what's in the database. The
attacker
> can use them himself if he likes!

For encrypted data to be usable by the website, the keys must be available
by, either in the database or in the scripts themselves. If the attacker
can write his own scripts into the document tree, these keys will be
available to him as well.

gnari

On Sun, Dec 05, 2004 at 11:31:34PM -0500, Greg Stark wrote:
> Derek Fountain <dflists@iinet.net.au> writes:
> > If another SQL Injection vulnerability turns up (which it might, given the
> > state of the website code),
>
> You will never see another SQL injection vulnerability if you simply switch to
> always using prepared queries and placeholders. Make it a rule that you
> _never_ interpolate variables into the query string. period. No manual quoting
> to get right, no subtle security audit necessary: If the SQL query isn't a
> constant string you reject it.

Another good piece of defense is mod_security (assuming that your web
server is Apache).  You can teach it about SQL injection attacks with a
little work.

    http://www.modsecurity.org

-Dom

Greg Stark wrote:
> Derek Fountain <dflists@iinet.net.au> writes:
>
>
>> If another SQL Injection vulnerability turns up (which it might,
>> given the state of the website code),
>
>
> You will never see another SQL injection vulnerability if

Never say never..

--
Until later, Geoffrey

Hi,

Citing Derek Fountain <dflists@iinet.net.au>:
> Indeed, but I'm still interested in the general answer.

There is no general answer. Depends on how deep you get into trouble, if
the data is compromised.

> The server I have been looking at was hopelessly insecure and SQL
> injection is only one of its problems. There were several othe
> ways in! Assume, for example, an attacker
> can write his own script directly into the website document tree. In
> this case prepared queries don't help protect what's in the database.
> The attacker can use them himself if he likes!

A chain of security measures is only as strong as its weakest link.
If cryptography will help you in this case really depends very much on the
level of system access an attacker can gain and on the encryption scheme
you use.
If an attacker can gain root, it is quite probable, that your cryptographic
keys will be compromised (because he will very probably be able to read
physical memory), so cryptography will not help you at all.
If an attacker can not gain root, it depends on if you use encryption
on the file system level or on record level in the db. File system
level encryption does not help much against attacks from the network
on a running system, because the file system will very probably
be mounted, and thus readable. record level encryption might help,
depending on how it is implemented (when you implement it, ask yourself:
are keys/passwords which are floating around between database server/
web server/client app unreadable by eavesdroppers on all stages of
processing?)

> Given this type of mess, having logins, passwords, credit card info and the
> like encrypted in the DB will add another layer of protection. The question
> is, do people normally add this layer, just in case.

In general, I would, if there was medical/payment/other personal data in
the db. The country I live in has quite strict regulations concerning
protection of people's private data...
(which is a good thing IMHO. Anyways just to make the point for you, that
this is more than just a technical matter ;-) Legal matters and economics
play a role here, too.)
But discussion above and conclusion below should show you, that there's a
bunch of problems elsewhere, which cannot be solved just by using
cryptography.

> or do they assume that
> all the previous layers will do the job?

Key thing is to find the weakest layer and strengthen it. Strongest
security measure does no good, if an attacker can easily bypass it
by gaining higher level system access by breaking another (weaker)
security layer.
From your description of the problem, I would conclude, that your
client's app needs fixing elsewhere first.

Regards,
Daniel

On 12/6/2004 1:33 AM, Derek Fountain wrote:
> On Monday 06 December 2004 12:31, you wrote:
>> Derek Fountain <dflists@iinet.net.au> writes:
>> > If another SQL Injection vulnerability turns up (which it might, given
>> > the state of the website code),
>>
>> You will never see another SQL injection vulnerability if you simply switch
>> to always using prepared queries and placeholders.
>
> <much wisdom snipped>
>
> Indeed, but I'm still interested in the general answer. The server I have been
> looking at was hopelessly insecure and SQL injection is only one of its
> problems. There were several other ways in! Assume, for example, an attacker
> can write his own script directly into the website document tree. In this
> case prepared queries don't help protect what's in the database. The attacker
> can use them himself if he likes!

I don't quite see how encrypted storage of data can solve your problem.
Somehow the web application must be able to unlock/decrypt the data.
Either on a per session level, or by passing in a key with every query.
Giving out the encrypt/decrypt keys to the end users, so that they have
to supply them at login time, is probably as secure as putting them in
cleartext onto the homepage. So they must be stored readable somewhere
by the middleware system.

It does obscure the data a little more. At the same time it might give
the Web application developer a completely false feeling of security.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Derek Fountain <dflists@iinet.net.au> writes:

> On Monday 06 December 2004 12:31, you wrote:
> > Derek Fountain <dflists@iinet.net.au> writes:
> > > If another SQL Injection vulnerability turns up (which it might, given
> > > the state of the website code),
> >
> > You will never see another SQL injection vulnerability if you simply switch
> > to always using prepared queries and placeholders.
>
> <much wisdom snipped>
>
> Indeed, but I'm still interested in the general answer.

I would argue that never interpolating user-provided data into your query
strings _is_ the general answer. It's going through laborious case-by-case
quoting that's non-general and can fail if any single instance isn't done
properly. If you use always use placeholders then there's nothing you can fail
to do properly that would cause an injection vulnerability.

You could use something like perl's taint tracking to keep track of whether
the data used in the query string is tainted by user-provided data. This would
even let you use manual quoting since it lets you designate functions that
untaint strings. But even that seems risky to me. taintperl is liberal about
what it considers detainting. I prefer to allow only constant program-defined
strings to be used in my queries period.

> Given this type of mess, having logins, passwords, credit card info and the
> like encrypted in the DB will add another layer of protection. The question
> is, do people normally add this layer, just in case, or do they assume that
> all the previous layers will do the job?

Layers are not useful unless they're effective. You can have 10 layers of 90%
effective security but it would be worthless. You still have an insecure
system.

The only useful way to use real-time encryption for a web server is public key
encryption for write-only data like credit card numbers. Usually the web
server really doesn't need access to existing credit card data. It only needs
to be able to add new credit card data or perhaps copy existing credit card
data.

So you could have the web server encrypt the credit card numbers using RSA and
store them in the database. Then only the credit card processing job which
might run on a highly secure dedicated box would pull the data and use the
private key to process the transactions.

The nice thing about this is that it isn't going to stop your web server or
database from being cracked, but it will limit the damage. The attacker can't
download a database of your entire customer base's credit card numbers.

Personally I think this is the only responsible way to run a system that keeps
credit card data. But sadly the rest of the world doesn't seem to agree.

By contrast, encryption is useful for non-live data such as database backups.
This lets you take them off-site and store them someplace without worrying
about someone walking off with your entire database. Or to discard the tapes
without worrying about someone reading your old data from the discarded tapes.
(Assuming of course that you don't write the key on the label...)

--
greg

On Mon, Dec 06, 2004 at 04:07:25PM -0500, Greg Stark wrote:
> By contrast, encryption is useful for non-live data such as database backups.
> This lets you take them off-site and store them someplace without worrying
> about someone walking off with your entire database. Or to discard the tapes
> without worrying about someone reading your old data from the discarded tapes.
> (Assuming of course that you don't write the key on the label...)

Actually, hard disk encryption is useful for one thing: so if somebody
kills the power and takes the hard disk/computer, the data is safe.
While it's running it's vulnerable though...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog@svana.org> writes:

> Actually, hard disk encryption is useful for one thing: so if somebody
> kills the power and takes the hard disk/computer, the data is safe.
> While it's running it's vulnerable though...

Where do you plan to keep the key?

--
greg

The world rejoiced as kleptog@svana.org (Martijn van Oosterhout) wrote:
> On Mon, Dec 06, 2004 at 04:07:25PM -0500, Greg Stark wrote:
>> By contrast, encryption is useful for non-live data such as
>> database backups.  This lets you take them off-site and store them
>> someplace without worrying about someone walking off with your
>> entire database. Or to discard the tapes without worrying about
>> someone reading your old data from the discarded tapes.  (Assuming
>> of course that you don't write the key on the label...)
>
> Actually, hard disk encryption is useful for one thing: so if
> somebody kills the power and takes the hard disk/computer, the data
> is safe.  While it's running it's vulnerable though...

Why do you think that's useful in limiting vulnerability?

In order for the system to mount the filesystem, the key has got to be
there.

If it's a "highly available" system, it's not acceptable for the
system to have to wait for a sysadmin to type in a decryption key, so
the key has to be sitting there, vulnerable to theft.

Given some sort of secure crypto hardware (nCipher, Sun Crypto
Accelerator, and such), it's possible to make the system reasonably
tamper-resistant, but the costs are pretty hefty, and tamper
resistance requires leaping back into the risk that a power outage
would require manual intervention to reinitialize the cryptographic
device.

This is a big problem: You can't just apply cryptography onto things
like you would add peanut butter to a sandwich and expect to actually
get security.  It is eminently easy for a cryptographic system to only
provide the _impression_ of security.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/crypto.html
It is usually a   good idea to  put  a capacitor of a  few microfarads
across the output, as shown.

Why do you think that's useful in limiting vulnerability?

In order for the system to mount the filesystem, the key has got to be
there.

If it's a "highly available" system, it's not acceptable for the
system to have to wait for a sysadmin to type in a decryption key, so
the key has to be sitting there, vulnerable to theft.

Given some sort of secure crypto hardware (nCipher, Sun Crypto
Accelerator, and such), it's possible to make the system reasonably
tamper-resistant, but the costs are pretty hefty, and tamper
resistance requires leaping back into the risk that a power outage
would require manual intervention to reinitialize the cryptographic
device.

This is a big problem: You can't just apply cryptography onto things
like you would add peanut butter to a sandwich and expect to actually
get security.  It is eminently easy for a cryptographic system to only
provide the _impression_ of security.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/internet.html
It is usually a   good idea to  put  a capacitor of a  few microfarads
across the output, as shown.

Hi,

Citing Greg Stark <gsstark@mit.edu>:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Actually, hard disk encryption is useful for one thing: so if somebody
> > kills the power and takes the hard disk/computer, the data is safe.
> > While it's running it's vulnerable though...
>
> Where do you plan to keep the key?

Well, where do you plan to keep the key for your encrypted backup tapes,
like you suggested in another post in this thread ;-)
That's pretty much the same problem.

Anyways, there are a bunch of solutions to this problem. All the good
ones require manual intervention (key entry, not necessarily by hand)
in case of the encrypted partition being brought from the unmounted
into the mounted state and rely on a certain person or a group of people
being trusted. Problem one (man. intervention) will not be a problem
at all, if the data is really valuable. Problem two (trust) is more
difficult. The more you distribute a single key across different people
and media, the less trust you will need in every single person, but the
more difficult will it be to conveniently access the data.

Regards,
Daniel

Daniel Martini <dmartini@uni-hohenheim.de> writes:

> Well, where do you plan to keep the key for your encrypted backup tapes,
> like you suggested in another post in this thread ;-)
> That's pretty much the same problem.

No it's not. I can keep the key for the encrypted backup tapes in my pocket. I
won't need it unless I'm restoring a backup, where I'll be present. Your hard
drives need to be decrypted by the database whenever it's running. If you can
deal with your database being unavailable after a reboot until manual
intervention then you're ok. But most people can't.


--
greg

On 12/6/2004 6:10 PM, Greg Stark wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>
>> Actually, hard disk encryption is useful for one thing: so if somebody
>> kills the power and takes the hard disk/computer, the data is safe.
>> While it's running it's vulnerable though...
>
> Where do you plan to keep the key?
>

I was wondering where he keeps his servers. Are they kept under a little
rain shelter on the parking lot, so that one who steels them at least
can't sue the company for hurting his back while carrying the racks outside?


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Christopher Browne wrote:

>Why do you think that's useful in limiting vulnerability?
>
>In order for the system to mount the filesystem, the key has got to be
>there.
>
>
>
It does not have to be locally on the system.  If I wanted to secure
such a system, I would have a private key for decrypting the files
stored in a directory server (LDAP or something) in a user or group
record.  This would allow only authorized individuals to mount the
drive.  If it has to be highly available, you could also use a machine
key to authenticate and obtain the key, which would then be stored in a
temporary file.  Granted if someone wanted to, they could impersonate
the machine and get the key, but it would be a bit more work.

In general encrypted filesystems are better at limiting the ability to
mount the drive than they are the ability to secure a highly available
system against a determined attacker.  Of course, security against the
most determined and knowledgable attacker may be a pipe dream anyway....

>
>This is a big problem: You can't just apply cryptography onto things
>like you would add peanut butter to a sandwich and expect to actually
>get security.  It is eminently easy for a cryptographic system to only
>provide the _impression_ of security.
>
>
When I started writing HERMES, I decided to use database native accounts
to enforce permissions.  As HERMES is web-based, the authentication must
occur in every http request.  This means that the login and password
must be stored somewhere.  I toyed with the idea of encrypting the
information but decided not to for exactly the reasons that you
mention.  Indeed I decided that it was better to have the passwords
cached in plain text so that the admin would decide to protect them
rather than offer a false sense of security by encrypting with a key
which an attacker could steal.

In the end, I decided that separation of data was a better strategy
toward securing the application than encryption.  In this case, the
login is stored in a cookie, and the password in a PHP session
variable.  The idea was that the cookie could be read or the session
variable, but putting them together would require reading both.

My strategies in web application security can be summed up in the
following ideas:
1)  Separate sensitive information so that it cannot be easily put
together. (this is app level and not usually db level)
2)  Push security enforcement back as far as possible.  Let the database
server maintain its security.  If it can be exploted, you are toast anyway.

Best Wishes,
Chris Travers
Metatron Technology Consulting