CODEWITHSUNDEEP
python-3.xencryptionflasksqlalchemy
user6922072
user6922072

Reputation:

SQLAlchemy: call function and save returned value in table always

I have a user table, and it has a password for payment. I decided to encrypt all this information. Each API retrieves or inserts new data, it always calls a function to encrypt the password like this.

  from cryption import encrypt, decrypt
  enc_password = encrypt(password)

  data = User(id=id, password=enc_password)
  db.add(data)
  db.commit()

Since I encrypt this in API, it looks redundant. also, sometimes I forgot to do add encrypt code and it causes critical errors. So, I wonder If I can do this on models instead of doing this. If I can do encrypt before insert data and decrypt when return data on query level, it will be nice.

Upvotes: 2

Views: 2020

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1121554

First of all, an important warning:

WARNING: NEVER store passwords of users. NEVER. The moment your server is compromised the hackers will not only have your server, they will have the encryption key and so have all the passwords.

Instead, store password hashes. See Why is password hashing considered so important? and Difference between Hashing a Password and Encrypting it.

In Python, use passlib to handle password hashing for you.

With that out of the way, you can automatically store password hashes in your database, or make any other data transformation, by using properties to make the transformation. I use this in my user models.

In the following sample model, the password property actually sets the password_hash column, to a hashed value:

from passlib.context import CryptContext


PASSLIB_CONTEXT = CryptContext(
    # in a new application with no previous schemes, start with pbkdf2 SHA512
    schemes=["pbkdf2_sha512"],
    deprecated="auto",
)


class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)

    # site-specific fields, like name, email, etc.

    # password hash handling
    # pbkdf2-sha512 is 130 characters short, but it never hurts to
    # leave space for future growth of hash lengths.
    password_hash = db.Column(db.String(256), nullable=False)

    def __init__(self, password=None, password_hash=None, **kwargs):
        if password_hash is None and password is not None:
            password_hash = self.generate_hash(password)
        super().__init__(password_hash=password_hash, **kwargs)

    @property
    def password(self):
        raise AttributeError("User.password is write-only")

    @password.setter
    def password(self, password):
        self.password_hash = self.generate_hash(password)

    def verify_password(self, password):
        return PASSLIB_CONTEXT.verify(password, self.password_hash)

    @staticmethod
    def generate_hash(password):
        """Generate a secure password hash from a new password"""
        return PASSLIB_CONTEXT.hash(password.encode("utf8"))

This lets me use User(..., password=password) and it'll automatically hash the password for me. Or update the password with if new_password == new_password_again and some_user.verify_password(old_password): some_user.password = new_password without having to remember how to hash the password again.

You can also use 'hidden' columns to store any other data you need to encrypt on storing, decrypt on retrieving. Name your model attributes with a leading _ underscore to mark them API private, then pass in the real column name to the Column() object as the first argument. Then use a property object to handle encryption and decryption:

class Foo(db.Model):
    __tablename__ = "foo"

    id = db.Column(db.Integer, primary_key=True)

    # encrypted column, named "bar" in the "foo" table
    _bar = db.Column("bar", db.String(256), nullable=False)

    @property
    def bar(self):
        """The bar value, decrypted automatically"""
        return decrypt(self._bar)

    @bar.setter
    def bar(self, value):
        """Set the bar value, encrypting it before storing"""
        self._bar = encrypt(bar)

This is covered in the SQLAlchemy manual under Using Descriptors and Hybrids. Note that there is no point in using a hybrid_property in this case, as your database can't encrypt and decrypt on the server side.

If you do encrypt, keep your keys separate from your source code, and make sure you rotate your keys (keep old keys at hand to decrypt old data not yet re-encrypted), so that if a key ever is compromised you can at least replace it.

Pick a good, trusted encryption recipe. The cryptography package includes the Fernet recipe, see another answer of mine for advice on how to use it, then upgrade to the MultiFernet() class to manage key rotation.

Also, read up on key management best practices. Cryptography is very easy to get wrong.

Upvotes: 7

Related Questions