Encrypting/decrypting db values

[lesssugar]

I have values I would like to store encrypted in my db after user inserts them. When displayed, the data should get decrypted.

All I know about encryption in web2py is that it uses HMAC+SHA512 by default to store password in auth_user table. How do I customize it to achieve my goal?

EDIT

I found this: requires=CRYPT(digest_alg='sha512') - but I think it's only crating a hash, right? I guess I need a key to encrypt/decrypt. And how do I get the values in Row object decrypted after I perform a select? Can't find much about implementing such mechanism in web2py, unfortunately.

[Massimo Di Pierro]

You cannot use CRYPT for this purpose. That is a one way hash. The original plaintext cannot be recovered.

You need a symmetric cypher. Web2py comes with the gluon/contrib/aes.py

from gluon.contrib.aes import AES

key = "your encryption key"

IV = ' '*16

db.table.field.filter_in = lambda data,key=key,iv=iv: AES.new(key,AES.MODE_CBC,iv).encrypt(data)

db.table.field.filter_out = lambda data,key=key,iv=iv: AES.new(key,AES.MODE_CBC,iv).decrypt(data)

[lesssugar]

OK, I get it. However, using the code I get:

AttributeError: type object 'AES' has no attribute 'new'

[Massimo Di Pierro]

Sorry. try this:

import gluon.contrib.aes as AES

key = "your encryption key"

IV = ' '*16

db.table.field.filter_in = lambda data,key=key,iv=IV: AES.new(key,AES.MODE_CBC,iv).encrypt(data)

db.table.field.filter_out = lambda data,key=key,iv=IV: AES.new(key,AES.MODE_CBC,iv).decrypt(data)

[lesssugar]

It rises an error now: ValueError: Plaintext length must be multiple of 16

Not sure what's the problem. Does my input value need to be "multiple of 16"?

[Massimo Di Pierro]

One more try:

import gluon.contrib.aes as AES

key = "your encryption key"

IV = ' '*16

db.table.field.filter_in = lambda data,key=key,iv=IV: AES.new(key,AES.MODE_CBC,iv).encrypt(AES.pad(data))

db.table.field.filter_out = lambda data,key=key,iv=IV: AES.new(key,AES.MODE_CBC,iv).decrypt(data)

the output will not be identical but may have additional spaces at the end.

[lesssugar]

Hmm, as for "AES(pad(data))":

'module' object has no attribute 'pad'

In addition, can't find any reference of what "pad()" is in Python.

[Niphlod]

actually, it's not that easy..... I gave up on lambdas for this implementation, but it works out nicely

import gluon.contrib.aes as AES
import threading
import base64
import os

def w2p_encrypt(data):
    key = 'asdsaddasdasdas'
    key = pad(key[:32])
    cipher, IV = AES_new(key)
    encrypted_data = IV + cipher.encrypt(pad(data))
    return base64.urlsafe_b64encode(encrypted_data)

def w2p_decrypt(data):
    key = 'asdsaddasdasdas'
    key = pad(key[:32])
    data = base64.urlsafe_b64decode(data)
    IV, data = data[:16], data[16:]
    cipher, _ = AES_new(key, IV=IV)
    data = cipher.decrypt(data)
    data = data.rstrip(' ')
    return data

db.define_table('t_test',
                Field('f_field')
                )

db.t_test.f_field.filter_in = lambda value : w2p_encrypt(value)
db.t_test.f_field.filter_out = lambda value : w2p_decrypt(value)

PS: may I suggest the secure_dumps and secure_loads from gluon.utils ? they add signing the value and in any case the added length isn't going to take that much space in addition to what's needed....in addition they take pretty much any python object, so that can be useful too.....

In that case the implementation is a taddle bit less verbose....

from gluon.utils import secure_dumps, secure_loads

db.t_test.f_field.filter_in = lambda value : secure_dumps(value, 'your_key')
db.t_test.f_field.filter_out = lambda value : secure_loads(value, 'your_key')

[Niphlod]

sorry, cut&paste error.

import gluon.contrib.aes as AES
import threading

import os
import base64

def fast_urandom16(urandom=[], locker=threading.RLock()):
    """
    this is 4x faster than calling os.urandom(16) and prevents
    the "too many files open" issue with concurrent access to os.urandom()
    """
    try:
        return urandom.pop()
    except IndexError:
        try:
            locker.acquire()
            ur = os.urandom(16 * 1024)
            urandom += [ur[i:i + 16] for i in xrange(16, 1024 * 16, 16)]
            return ur[0:16]
        finally:
            locker.release()
           
def pad(s, n=32, padchar=' '):
    return s + (32 - len(s) % 32) * padchar

def AES_new(key, IV=None):
    """ Returns an AES cipher object and random IV if None specified """
    if IV is None:
        IV = fast_urandom16()

    return AES.new(key, AES.MODE_CBC, IV), IV

[Massimo Di Pierro]

Should we include decrypt(key,data) and encrypt(key,data) in gluon.utils?

[Derek]

Only if there is an optional algorithm argument.

[Niphlod]

because you want something else from AES ?

[Derek]

Something other than AES, yes. Perhaps ECE (elliptic curve encryption) or something other than AES. Shouldn't I have a choice?

[Massimo Di Pierro]

You have a choice already. You can import any library you like and use that for encryption. The issue is making the default choice more easily available. A built-in web2py faction cannot have a parameter to choose the encryption algorithm because web2py only comes with AES.

[lesssugar]

@Niphlod,

Thanks very much for your solution. Implemented it, it works -- no errors when inserting/updating data.

However, one thing I don't fully get: the data I store using the encryption are displayed as normal strings in the database. I'm not very familiar with encryption specifics but isn't it the point to keep the data in sort of a hard-to-crack code?

I use a normal string as key.

my test table:

db.define_table('contact',

Field('user_id', db.auth_user, default=auth.user_id, readable=False, writable=False),

Field('email', label='Contact email'),

Field('phone', label='Contact phone')

)

db.contact.email.requires = [IS_EMAIL(error_message="Wrong email address")]

db.contact.phone.requires= [IS_LENGTH(maxsize=30, error_message="Bit too long, right?")]

db.contact.email.filter_in = lambda value : w2p_encrypt(value)

db.contact.phone.filter_in = lambda value : w2p_encrypt(value)

db.contact.email.filter_out = lambda value : w2p_decrypt(value)

db.contact.phone.filter_out = lambda value : w2p_decrypt(value)

[Niphlod]

if you're inspecting your db with web2py then of course you have all in clear.... that's the whole point of the filter_in and filter_out methods, being "transparent".

Try to inspect your database with an external tool instead.

[lesssugar]

Yes, I inspected the data with psql - they're indeed encrypted. Again, thanks.

[szimszon]

+1

[Kiran Subbaraman]

I just tried this, and it works as expected. Thanks for this useful piece of code Niphlod.
Just a bunch of 'gotchas', which I thought might help someone else trying this code:

• I had existing data in the table, which was in plain text format. When I introduced this code I encountered errors like "..Incorrect padding...". Went ahead and commented out the filter* assignments, exported the plain text data. Un-commented the filter* assignments, and imported the data.
  
• My data has long / integer / numeric fields, and I noticed errors in the pad() method. Works well for non-numeric fields - primarily string fields.
  
• Also removed NULL/None values from the dataset.
  

[Kiran Subbaraman]

Just checked and noticed that the encrypt/decrypt are not in gluon.utils. Definitely would like to see them part of web2py, instead of having to include this code in my application.
Any plans?

[Niphlod]

there are plenty of libraries more focused on the encryption. As any other thing where math, ciphers and security is involved, there is no magic bullet.....

[Alex Glaros]

I assume that the key is meant to be unique for each app and that the example key above is just a sample.

key = 'asdsaddasdasdas'

(1) How to select/generate a key and (2) if key exists in plain text on my open source app, then I have to replace it with a dummy key in gitHub so whoever downloads and uses the code from my app cannot decipher my data if they steal the database, correct?

thanks to Niphlod for the example, works great.

Alex Glaros

[Dave S]

I take it you saw the recent report on how many projects have exposed keys for their backdoors, kept in github, eh?

I would move the key to the myapp/private/appconfig.ini file and read it in at runtime, say in db.py.

 /dps

[Dave S]

<URL:http://www.computerworld.com/article/3158494/security/access-tokens-and-keys-found-in-hundreds-of-android-apps.html>

<URL:http://windowsitpro.com/security/truffle-hog-finds-security-keys-hidden-github-code>

/dps

 

[Alex Glaros]

had not seen the reports Dave, thanks for confirming the concern

any ideas on how to generate a key? How long must it be?  Would not tend to trust online generator...

Alex

[Dave S]

I use KeePass for storing my collection of personal passwords.  It is happy to generate a random key for me, and as of v1.31 was defaulting to 111 bits (20 characters), but still showed "green" for 80 bits (15 characters).

If you make passwords that you can remember (I do for some), there's the problem of dictionary attacks speeding up the brute force cracking, and the common obscuring techniques merely force a larger dictionary.  And with GPUs not yet having reached their theoretical limit, 111 bits may soon be in the reach of home hackers.   Two factor schemes or hardware keys would still be indicated for anything requiring serious protection.

Also, encrypting the database is a good way to provide protection for "data at rest", but it may still be necessary to think about "data in flight" in a rigorous way.  Enforcing HTTPS and banning the broken versions of TLS is part of how that's being addressed, but servers may be vulnerable to shared memory exploits (which also exposes passwords), and clients may also be vulnerable (but if you can limit the data exposed to the client ...).

Sorry, Massimo got me started attending OWASP meetings, and I've picked up some of what they are saying.

/dps

[Alex Glaros]

is this correct:  I need roughly 44 characters generated by my password generator (no human/dictionary words) to contain about 256 bits of randomness to obtain an AES 256-bit key?

[Anthony]

On Monday, January 23, 2017 at 7:36:50 PM UTC-5, Alex Glaros wrote:

is this correct:  I need roughly 44 characters generated by my password generator (no human/dictionary words) to contain about 256 bits of randomness to obtain an AES 256-bit key?

It depends on the exact character set, but I think that is roughly the idea. Note, you would also likely use some key stretching process (i.e., a "password based key derivation function"), which would make a somewhat shorter password effectively as difficult to crack as a longer password that wasn't key stretched. So, you could get 256-bit level of protection with a slightly shorter password.

Anthony

[黄祥]

is filter_in and filter_out is part of pydal?
interested in the code example but sometime i use database that used in web2py app in terminal
so, is it possible to decrypt the encrypted database values in terminal?

p.s.
terminal is execute pure python code, sometime ruby or php just for scrapping data from website, inserted it in database that used in web2py app and generate the report in terminal

thanks and best regards,

stifan