def decrypt(self, mysql_datatype):
"""Check that when using a table provided by MySQL database engine that
contains a column encrypted in MySQL stored using specified data type
I can decrypt data in the column using the `decrypt` and `aes_decrypt_mysql`
functions in the select query.
"""
node = self.context.node
mysql_node = self.context.mysql_node
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
for func in ["decrypt", "aes_decrypt_mysql"]:
for mode, key_len, iv_len in mysql_modes:
exact_key_size = int(mode.split("-")[1])//8
if "ecb" not in mode and not iv_len:
continue
if func == "decrypt":
if iv_len and iv_len != 16:
continue
if key_len != exact_key_size:
continue
with Example(f"""{func} mode={mode.strip("'")} key={key_len} iv={iv_len}"""):
with table("user_data", node, mysql_node, mysql_datatype):
example_mode = mode
example_key = f"'{key[:key_len]}'"
example_iv = None if not iv_len else f"'{iv[:iv_len]}'"
with When("I insert encrypted data in MySQL"):
sql = f"""
SET block_encryption_mode = {example_mode};
INSERT INTO user_data VALUES (NULL, '2020-01-01', 'user0', AES_ENCRYPT('secret', {example_key}{(", " + example_iv) if example_iv else ", ''"}));
"""
mysql_node.command(f"MYSQL_PWD=password mysql -D db -u user <<'EOF'{textwrap.dedent(sql)}\nEOF", exitcode=0)
with And("I read encrypted data in MySQL to make sure it is valid"):
sql = f"""
SET block_encryption_mode = {example_mode};
SELECT id, date, name, AES_DECRYPT(secret, {example_key}{(", " + example_iv) if example_iv else ", ''"}) AS secret FROM user_data;
"""
mysql_node.command(f"MYSQL_PWD=password mysql -D db -u user <<'EOF'{textwrap.dedent(sql)}\nEOF", exitcode=0)
with And("I read raw encrypted data in MySQL"):
mysql_node.command(f"MYSQL_PWD=password mysql -D db -u user -e \"SELECT id, date, name, hex(secret) as secret FROM user_data;\"", exitcode=0)
with And("I read raw data using MySQL database engine"):
output = node.query("SELECT id, date, name, hex(secret) AS secret FROM mysql_db.user_data")
with And("I read decrypted data using MySQL database engine"):
output = node.query(f"""SELECT hex({func}({example_mode}, secret, {example_key}{(", " + example_iv) if example_iv else ""})) FROM mysql_db.user_data""").output.strip()
with Then("output should match the original plain text"):
assert output == hex("secret"), error()
def encrypt(self, mysql_datatype):
"""Check that when using a table with MySQL table engine that
we can encrypt data during insert using the `encrypt` and `aes_encrypt_mysql`
functions and decrypt it in MySQL.
"""
node = self.context.node
mysql_node = self.context.mysql_node
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
for func in ["encrypt", "aes_encrypt_mysql"]:
for mode, key_len, iv_len in mysql_modes:
exact_key_size = int(mode.split("-")[1]) // 8
if "ecb" not in mode and not iv_len:
continue
if func == "encrypt":
if iv_len and iv_len != 16:
continue
if key_len != exact_key_size:
continue
with Example(
f"""mode={mode.strip("'")} key={key_len} iv={iv_len}"""):
with table("user_data", node, mysql_node, mysql_datatype):
example_mode = mode
example_key = f"'{key[:key_len]}'"
example_iv = None if not iv_len else f"'{iv[:iv_len]}'"
example_transform = f"{func}({mode}, secret, {example_key}{(', ' + example_iv) if example_iv else ''})"
with When(
"I insert encrypted data into MySQL table engine"):
node.query(
textwrap.dedent(f"""
INSERT INTO
mysql_user_data
SELECT
id, date, name, {example_transform}
FROM
input('id Nullable(Int32), date Date, name String, secret String')
FORMAT Values (null, '2020-01-01', 'user0', 'secret')
"""))
with And("I read decrypted data via MySQL table engine"):
output = node.query(
f"""SELECT hex(aes_decrypt_mysql({example_mode}, secret, {example_key}{(", " + example_iv) if example_iv else ""})) FROM mysql_user_data"""
).output.strip()
with Then(
"decrypted data from MySQL table engine should should match the original plain text"
):
assert output == hex("secret"), error()
with And(
"I read raw data using MySQL table engine to get expected raw data"
):
expected_raw_data = node.query(
"SELECT hex(secret) AS secret FROM mysql_user_data"
).output.strip()
with And("I read raw encrypted data in MySQL"):
output = mysql_node.command(
f"MYSQL_PWD=password mysql -D db -u user -e \"SELECT hex(secret) as secret FROM user_data;\"",
exitcode=0).output.strip()
with Then(
"check that raw encryted data in MySQL matches the expected"
):
assert expected_raw_data in output, error()
with And("I decrypt data in MySQL to make sure it is valid"
):
sql = f"""
SET block_encryption_mode = {example_mode};
SELECT id, date, name, hex(AES_DECRYPT(secret, {example_key}{(", " + example_iv) if example_iv else ", ''"})) AS secret FROM user_data;
"""
output = mysql_node.command(
f"MYSQL_PWD=password mysql -D db -u user <<'EOF'{textwrap.dedent(sql)}\nEOF",
exitcode=0).output.strip()
with Then(
"decryted data in MySQL should match the original plain text"
):
assert hex("secret") in output, error()