EnryptedType in python3 and MySQL fails with 'string argument without an encoding'

[mklassen]

When querying a record with a column of EncryptedType in python3.7 with a MySQL backend and packages

• SQLAlchemy==1.3.1
• SQLAlchemy-Utils==0.33.11
• mysql-connector-python==8.0.15

The query fails with TypeError: 'string argument without an encoding'.

The issue appears to be that EncryptedType has an impl of LargeBinary which in python3 processes the returned value with the command

value = bytes(value)

However the value is an Unicode string and therefore the above call is not valid. The following code reproduces the error

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Unicode, create_engine, Integer
from sqlalchemy_utils import EncryptedType
from sqlalchemy.orm import sessionmaker
from sqlalchemy_utils.types.encrypted.encrypted_type import AesGcmEngine

Base = declarative_base()
class MyTable(Base):
    __tablename__ = 'test'
    id = Column('table_id', Integer, primary_key=True)
    field = Column('field', EncryptedType(Unicode(250), 'secret', AesGcmEngine, 'pkcs5'))
engine = create_engine('mysql+mysqlconnector://user:password@localhost/scratch?charset=utf8')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
session.add(MyTable(field='to encrypt'))
session.commit()
session.query(MyTable).all()

Traceback (most recent call last):
File "/tmp/tester.py", line 28, in
session.query(MyTable).all()
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3161, in all
return list(self)
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 105, in instances
util.raise_from_cause(err)
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 129, in reraise
raise value
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 85, in instances
rows = [proc(row) for row in fetch]
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 85, in
rows = [proc(row) for row in fetch]
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 572, in _instance
populators,
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 693, in populate_full
dict[key] = getter(row)
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py", line 1247, in process
return process_value(impl_processor(value), dialect)
File "/tmp/python/lib/python3.7/site-packages/sqlalchemy/sql/sqltypes.py", line 943, in process
value = bytes(value)
TypeError: string argument without an encoding

[mklassen]

More digging shows that the LargeBinary type is broken in sqlalchemy==1.3.1 (at least for python3 and mysql backend), as I am able to reproduce the error using just a LargeBinary type column in place of the EncryptedType.

[mklassen]

The issue is due to the use of mysqlconnector and using pymysql instead fixes the issue.

[aicioara]

I am having the exact same issue using psycopg2. It looks like an encrypted key such as

u4l3U4adiXfWV0vbD5C6kQ==

Gets transformed into

<psycopg2.extensions.Binary object at 0x10c9d7a80>

# where
# .__str__() == "'u4l3U4adiXfWV0vbD5C6kQ=='::bytea"

And gets saved into the database as

\x75346c33553461646958665756307662443543366b513d3d

Which looks like it's some base16 encoding of the ASCII value of u4l3U4adiXfWV0vbD5C6kQ==