"string argument without an encoding" when using EncryptedType

[ninnzz]

Describe the bug

EncryptedType throws an error when using with mysql+pymysql connection.

The example works on sqlite type of connections but throws TypeError: string argument without an encoding when using with mysql. The cause of the error is this line of code value = bytes(value)

Expected behavior

Should be able to properly use EncryptedTypes with mysql database.

To Reproduce
We've had a complex system using sql alchemy so first, I just tried to reproduce it using the example code from the documentation

import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from sqlalchemy_utils import EncryptedType
from sqlalchemy_utils.types.encrypted.encrypted_type import AesEngine

secret_key = 'secretkey1234'
# setup
# engine = create_engine('sqlite:///:memory:')
engine = create_engine('mysql+pymysql://root:password@127.0.0.1/test_db?charset=utf8mb4')
connection = engine.connect()
Base = declarative_base()

"""
CREATE TABLE user (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  email VARCHAR(64) NOT NULL
)
"""

class User(Base):
    __tablename__ = "user"
    id = sa.Column(sa.Integer, primary_key=True)
    username = sa.Column(EncryptedType(sa.String,
                                       secret_key,
                                       AesEngine,
                                       'pkcs5'))
    email = sa.Column(EncryptedType(sa.String,
                                           secret_key,
                                           AesEngine,
                                           'pkcs5'))

sa.orm.configure_mappers()
Base.metadata.create_all(connection)

# create a configured "Session" class
Session = sessionmaker(bind=connection)

# create a Session
session = Session()

# example
user_name = 'secret_user'
test_token = 'sample@email.com'

user = User(username=user_name, email=test_token)
session.add(user)
session.commit()

user_id = user.id

session.expunge_all()

user_instance = session.query(User).get(user_id)

print('id: {}'.format(user_instance.id))
print('username: {}'.format(user_instance.username))
print('email: {}'.format(user_instance.email))


# teardown
session.close_all()
Base.metadata.drop_all(connection)
connection.close()
engine.dispose()

Error

Traceback (most recent call last):
  File "enc.py", line 53, in <module>
    user_id = user.id
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 287, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py", line 718, in get
    value = state._load_expired(state, passive)
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/state.py", line 652, in _load_expired
    self.manager.deferred_scalar_loader(self, toload)
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 1012, in load_scalar_attributes
    only_load_props=attribute_names,
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 207, in load_on_ident
    identity_token=identity_token,
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 287, in load_on_pk_identity
    return q.one()
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3436, in one
    ret = self.one_or_none()
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3405, in one_or_none
    ret = list(self)
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 101, in instances
    cursor.close()
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
    exc_value, with_traceback=exc_tb,
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 81, in instances
    rows = [proc(row) for row in fetch]
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 81, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 589, in _instance
    populators,
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 726, in _populate_full
    dict_[key] = getter(row)
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 1281, in process
    return process_value(impl_processor(value), dialect)
  File "/Users/ninz/.local/share/virtualenvs/WLbOJcsr/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 945, in process
    value = bytes(value)
TypeError: string argument without an encoding

Versions.

• OS: Mac OS Catalina - 10.15.4 (19E266)
• Python: 3.6.1
• SQLAlchemy: SQLAlchemy==1.3.17, SQLAlchemy-Utils==0.36.6
• Database: Server version: 8.0.19 MySQL Community Server - GPL
• DBAPI: PyMySQL==0.9.3

Additional context

It is also worth noting that the entries are properly inserted in the database. This observation is consistent with what we encounter in our system. The inserts seem to work. But when you try to access the object or query the object using encrypted columns as filter, it doesn't seem to work. It throws the same error as above.

+----+--------------------------+----------------------------------------------+
| id | username                 | email                                        |
+----+--------------------------+----------------------------------------------+
|  1 | VUV4WxkdtQgtr5MMkBImKA== | Xkqb6+kkuoBsUWwvM85mplgeph0H1NPr7zdY1/ff3LE= |
|  2 | VUV4WxkdtQgtr5MMkBImKA== | Xkqb6+kkuoBsUWwvM85mplgeph0H1NPr7zdY1/ff3LE= |
|  3 | VUV4WxkdtQgtr5MMkBImKA== | Xkqb6+kkuoBsUWwvM85mplgeph0H1NPr7zdY1/ff3LE= |
|  4 | VUV4WxkdtQgtr5MMkBImKA== | Xkqb6+kkuoBsUWwvM85mplgeph0H1NPr7zdY1/ff3LE= |
+----+--------------------------+----------------------------------------------+

Thank you! 🙇🏻‍♂️🙇🏻‍♂️🙇🏻‍♂️

[gordthompson]

A recent change to SQLAlchemy-Utils seems to have broken a few things with regard to EncryptedType:

kvesteri/sqlalchemy-utils#444

[ninnzz]

@gordthompson thanks for pointing it out. I'll try to get updates from them instead.

I have a quick question though. Knowing that bytes(string) only works on python 2, why is it not removed on the code? Or maybe replaced by string.encode() instead? Just a bit curious. Thanks! 🙇

[gordthompson]

SQLAlchemy 1.3 still supports Python 2.7 and (the currently pre-release) version 1.4 is expected to support Python 2.7 as well (subject to change without notice). Current plans for SQLAlchemy 2.0 is that it will be for Python 3 only.