def execute_query(
sql, expected=None, format="TabSeparatedWithNames", compare_func=None
):
"""Execute SQL query and compare the output to the snapshot."""
name = basename(current().name)
with When("I execute query", description=sql):
r = current().context.node.query(sql + " FORMAT " + format)
if expected is not None:
with Then("I check output against expected"):
if compare_func is None:
assert r.output.strip() == expected, error()
else:
assert compare_func(r.output.strip(), expected), error()
else:
with Then("I check output against snapshot"):
with values() as that:
assert that(
snapshot(
"\n" + r.output.strip() + "\n", "tests", name=name, encoder=str
)
), error()
def encryption(self):
"""Check that `encrypt` functions accepts `plaintext` as the second parameter
with any data type and `mode` as the first parameter.
"""
key = f"{'1' * 36}"
iv = f"{'2' * 16}"
aad = "some random aad"
for mode, key_len, iv_len, aad_len in modes:
for datatype, plaintext in plaintexts:
requirement = globals().get(
f"""RQ_SRS008_AES_Encrypt_Function_Parameters_Mode_Value_{mode.strip("'").replace("-","_").upper()}"""
)("1.0")
with Example(
f"""mode={mode.strip("'")} datatype={datatype.strip("'")} iv={iv_len} aad={aad_len}""",
requirements=[requirement]) as example:
r = encrypt(plaintext=plaintext,
key=f"'{key[:key_len]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
aad=(None if not aad_len else f"'{aad}'"))
with Then("I check output against snapshot"):
with values() as that:
example_name = basename(example.name)
assert that(
snapshot(
r.output.strip(),
"encrypt",
name=f"example_{example_name.replace(' ', '_')}"
)), error()
def encryption(self):
"""Check that `aes_encrypt_mysql` functions accepts `plaintext` as the second parameter
with any data type and `mode` as the first parameter.
"""
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
for mode, key_len, iv_len in mysql_modes:
for datatype, plaintext in plaintexts:
with Example(
f"""mode={mode.strip("'")} datatype={datatype.strip("'")} key={key_len} iv={iv_len}"""
) as example:
r = aes_encrypt_mysql(
plaintext=plaintext,
key=f"'{key[:key_len]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
)
with Then("I check output against snapshot"):
with values() as that:
example_name = basename(example.name)
assert that(
snapshot(
r.output.strip(),
"encrypt_mysql",
name=
f"example_{example_name.replace(' ', '_')}",
)), error()
def aes_decrypt_mysql_using_materialized_view(self):
"""Check that we can use `aes_decrypt_mysql` function when inserting
data into a table using a materialized view for input
data transformation.
"""
node = self.context.node
key = f"{'1' * 36}"
iv = f"{'2' * 16}"
aad = "some random aad"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(current_dir(), "snapshots",
"insert.py.insert.snapshot"),
).load_module()
for mode, key_len, iv_len, aad_len in modes:
with Example(f"""mode={mode.strip("'")} key={key_len} iv={iv_len}"""
) as example:
example_key = f"'{key[:key_len]}'"
example_mode = mode
example_iv = None if not iv_len else f"'{iv[:iv_len]}'"
example_aad = None if not aad_len else f"'{aad}'"
example_transform = (
f"aes_decrypt_mysql(mode, secret, key{', iv' if example_iv else ''})"
)
with Given("I have ciphertexts"):
example_name = basename(example.name)
ciphertexts = getattr(
snapshot_module,
varname(f"aes_encrypt_mysql_mv_example_{example_name}"),
)
example_ciphertexts = [
"'{}'".format(l.split("\t")[-1].strup("'"))
for l in ciphertexts.split("\n")
]
with table("user_data"):
with mv_transform("user_data", example_transform):
with When("I insert encrypted data"):
node.query(f"""
INSERT INTO user_data_input
(date, name, secret, mode, key)
VALUES
('2020-01-01', 'user0', 'unhex({example_ciphertexts[0]})', {example_mode}, {example_key}{(", " + example_iv) if example_iv else ""}),
('2020-01-02', 'user1', 'unhex({example_ciphertexts[1]})', {example_mode}, {example_key}{(", " + example_iv) if example_iv else ""}),
('2020-01-03', 'user2', 'unhex({example_ciphertexts[2]})', {example_mode}, {example_key}{(", " + example_iv) if example_iv else ""})
""")
with And("I read inserted data back"):
r = node.query(
"SELECT date, name, secret FROM user_data ORDER BY date"
)
with Then("output must match the expected"):
expected = r"""'2020-01-01\tuser0\tuser0_secret\n2020-01-02\tuser1\tuser1_secret\n2020-01-03\tuser2\tuser2_secret'"""
assert r.output == expected, error()
def decryption(self):
"""Check that `aes_decrypt_mysql` functions accepts `mode` as the first parameter
and `ciphertext` as the second parameter and we can convert the decrypted value into the original
value with the original data type.
"""
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(
current_dir(), "snapshots",
"encrypt_mysql.py.encrypt_mysql.snapshot")).load_module()
for mode, key_len, iv_len in mysql_modes:
for datatype, plaintext in plaintexts:
with Example(
f"""mode={mode.strip("'")} datatype={datatype.strip("'")} key={key_len} iv={iv_len}"""
) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(snapshot_module,
varname(f"example_{example_name}"))
cast = None
endcast = None
ciphertext = f"unhex({ciphertext})"
compare = plaintext
if datatype == "IPv4":
cast = "toIPv4(IPv4NumToString(reinterpretAsUInt32"
endcast = "))"
elif datatype in [
"DateTime64", "UUID", "IPv6", "LowCardinality",
"Enum8", "Enum16", "Decimal32", "Decimal64",
"Decimal128", "Array"
]:
xfail(reason="no conversion")
elif datatype == "NULL":
ciphertext = "NULL"
cast = "isNull"
compare = None
elif datatype in ["Float32", "Float64", "Date", "DateTime"
] or "Int" in datatype:
cast = f"reinterpretAs{datatype}"
aes_decrypt_mysql(
ciphertext=ciphertext,
key=f"'{key[:key_len]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
cast=cast,
endcast=endcast,
compare=compare,
message="1")
def aes_decrypt_mysql_using_input_table_function(self):
"""Check that we can use `aes_decrypt_mysql` function when inserting
data into a table using insert select and `input()` table
function.
"""
node = self.context.node
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
aad = "some random aad"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(current_dir(), "snapshots",
"insert.py.insert.snapshot"),
).load_module()
for mode, key_len, iv_len in mysql_modes:
with Example(f"""mode={mode.strip("'")} key={key_len} iv={iv_len}"""
) as example:
example_key = f"'{key[:key_len]}'"
example_mode = mode
example_iv = None if not iv_len else f"'{iv[:iv_len]}'"
example_transform = f"aes_decrypt_mysql({mode}, unhex(secret), {example_key}{(', ' + example_iv) if example_iv else ''})"
with Given("I have ciphertexts"):
example_name = basename(example.name)
ciphertexts = getattr(
snapshot_module,
varname(f"aes_encrypt_mysql_input_example_{example_name}"),
)
example_ciphertexts = [
l.split("\\t")[-1].strip("'")
for l in ciphertexts.split("\\n")
]
with table("user_data"):
with When("I insert decrypted data"):
node.query(
textwrap.dedent(f"""
INSERT INTO
user_data
SELECT
date, name, {example_transform}
FROM
input('date Date, name String, secret String')
FORMAT Values ('2020-01-01', 'user0', '{example_ciphertexts[0]}'), ('2020-01-02', 'user1', '{example_ciphertexts[1]}'), ('2020-01-03', 'user2', '{example_ciphertexts[2]}')
"""))
with And("I read inserted data back"):
r = node.query(
"SELECT date, name, secret FROM user_data ORDER BY date"
)
expected = """2020-01-01\tuser0\tuser0_secret\n2020-01-02\tuser1\tuser1_secret\n2020-01-03\tuser2\tuser2_secret"""
with Then("output must match the expected",
description=expected):
assert r.output == expected, error()
def mismatched_mode(self):
"""Check that `aes_decrypt_mysql` function returns garbage or an error when mode parameter does not match."""
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
plaintext = hex("Gãńdåłf_Thê_Gręât".encode("utf-8"))
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(
current_dir(), "snapshots", "encrypt_mysql.py.encrypt_mysql.snapshot"
),
).load_module()
for mode, key_len, iv_len in mysql_modes:
if not iv_len:
continue
with Example(
f"""mode={mode.strip("'")} datatype=utf8string key={key_len} iv={iv_len}"""
) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(
snapshot_module, varname(f"example_{example_name}")
)
for mismatched_mode, _, _ in mysql_modes:
if mismatched_mode == mode:
continue
with When(f"I decrypt using a mismatched mode {mismatched_mode}"):
r = aes_decrypt_mysql(
ciphertext=f"unhex({ciphertext})",
key=f"'{key[:key_len]}'",
mode=mismatched_mode,
iv=f"'{iv[:iv_len]}'",
cast="hex",
no_checks=True,
)
with Then("exitcode shoud be 0 or 36 or 198"):
assert r.exitcode in [0, 36, 198], error()
with And("output should be garbage or an error"):
output = r.output.strip()
assert (
"Exception: Failed to decrypt" in output
or output != plaintext
), error()
def decryption(self):
"""Check that `aes_decrypt_mysql` functions accepts `mode` as the first parameter
and `ciphertext` as the second parameter and we can convert the decrypted value into the original
value with the original data type.
"""
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(
current_dir(), "snapshots", "encrypt_mysql.py.encrypt_mysql.snapshot"
),
).load_module()
for mode, key_len, iv_len in mysql_modes:
for datatype, plaintext in plaintexts:
with Example(
f"""mode={mode.strip("'")} datatype={datatype.strip("'")} key={key_len} iv={iv_len}"""
) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(
snapshot_module, varname(f"example_{example_name}")
)
cast = None
endcast = None
ciphertext = f"unhex({ciphertext})"
compare = plaintext
if datatype == "NULL" or datatype.endswith("Null"):
ciphertext = "NULL"
cast = "isNull"
compare = None
aes_decrypt_mysql(
ciphertext=ciphertext,
key=f"'{key[:key_len]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
cast=cast,
endcast=endcast,
compare=compare,
message="1",
)
def mismatched_mode(self):
"""Check that `decrypt` function returns garbage or an error when mode parameter does not match.
"""
key = f"{'1' * 36}"
iv = f"{'2' * 16}"
aad = "some random aad"
plaintext = hex('Gãńdåłf_Thê_Gręât'.encode("utf-8"))
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(current_dir(), "snapshots",
"encrypt.py.encrypt.snapshot")).load_module()
for mode, key_len, iv_len, aad_len in modes:
with Example(
f"""mode={mode.strip("'")} datatype=utf8string iv={iv_len} aad={aad_len}"""
) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(snapshot_module,
varname(f"example_{example_name}"))
for mismatched_mode, _, _, _ in modes:
if mismatched_mode == mode:
continue
with When(
f"I decrypt using mismatched mode {mismatched_mode}"):
r = decrypt(
ciphertext=f"unhex({ciphertext})",
key=f"'{key[:key_len]}'",
mode=mismatched_mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
aad=(None if not aad_len else f"'{aad}'"),
no_checks=True,
cast="hex")
with Then("exitcode shoud be 0 or 36 or 198"):
assert r.exitcode in [0, 36, 198], error()
with And("output should be garbage or an error"):
output = r.output.strip()
condition = "Exception: Failed to decrypt" in output \
or 'Exception: Invalid key size' in output \
or output != plaintext
assert condition, error()
def mismatched_aad(self):
"""Check that `decrypt` function returns garbage or an error when aad parameter does not match."""
key = f"{'1' * 36}"
iv = f"{'2' * 16}"
aad = "some random aad"
datatype = "String"
plaintext = "'1'"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(current_dir(), "snapshots",
"encrypt.py.encrypt.snapshot"),
).load_module()
for mode, key_len, iv_len, aad_len in modes:
if not aad_len:
continue
with Example(
f"""mode={mode.strip("'")} datatype={datatype.strip("'")} iv={iv_len} aad={aad_len}"""
) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(snapshot_module,
varname(f"example_{example_name}"))
with When("I decrypt using a mismatched aad"):
r = decrypt(
ciphertext=f"unhex({ciphertext})",
key=f"'{key[:key_len]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
aad=(None if not aad_len else f"'a{aad}'"),
no_checks=True,
cast="hex",
)
with Then("exitcode shoud be 0 or 198"):
assert r.exitcode in [0, 198], error()
with And("output should be garbage or an error"):
output = r.output.strip()
assert ("Exception: Failed to decrypt" in output
or output != "31"), error()
def mismatched_key(self):
"""Check that `aes_decrypt_mysql` function returns garbage or an error when key parameter does not match.
"""
key = f"{'1' * 64}"
iv = f"{'2' * 64}"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(
current_dir(), "snapshots",
"encrypt_mysql.py.encrypt_mysql.snapshot")).load_module()
for mode, key_len, iv_len in mysql_modes:
with Example(
f"""mode={mode.strip("'")} datatype=String key={key_len} iv={iv_len}"""
) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(snapshot_module,
varname(f"example_{example_name}"))
with When("I decrypt using a mismatched key"):
r = aes_decrypt_mysql(
ciphertext=f"unhex({ciphertext})",
key=f"'a{key[:key_len-1]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
cast="hex",
no_checks=True)
with Then("exitcode shoud be 0 or 198"):
assert r.exitcode in [0, 198], error()
with And("output should be garbage or an error"):
output = r.output.strip()
assert "Exception: Failed to decrypt" in output or output != "31", error(
)
def decryption(self):
"""Check that `decrypt` functions accepts `ciphertext` as the second parameter
and `mode` as the first parameter and we can convert the decrypted value into the original
value with the original data type.
"""
key = f"{'1' * 36}"
iv = f"{'2' * 16}"
aad = "some random aad"
with Given("I load encrypt snapshots"):
snapshot_module = SourceFileLoader(
"snapshot",
os.path.join(current_dir(), "snapshots",
"encrypt.py.encrypt.snapshot")).load_module()
for mode, key_len, iv_len, aad_len in modes:
for datatype, plaintext in plaintexts:
requirement = globals().get(
f"""RQ_SRS008_AES_Decrypt_Function_Parameters_Mode_Value_{mode.strip("'").replace("-","_").upper()}"""
)("1.0")
with Example(
f"""mode={mode.strip("'")} datatype={datatype.strip("'")} iv={iv_len} aad={aad_len}""",
requirements=[requirement]) as example:
with Given("I have ciphertext"):
example_name = basename(example.name)
ciphertext = getattr(snapshot_module,
varname(f"example_{example_name}"))
cast = None
endcast = None
ciphertext = f"unhex({ciphertext})"
compare = plaintext
if datatype == "IPv4":
cast = "toIPv4(IPv4NumToString(reinterpretAsUInt32"
endcast = "))"
elif datatype in [
"DateTime64", "UUID", "IPv6", "LowCardinality",
"Enum8", "Enum16", "Decimal32", "Decimal64",
"Decimal128", "Array"
]:
xfail(reason="no conversion")
elif datatype == "NULL":
ciphertext = "NULL"
cast = "isNull"
compare = None
elif datatype in ["Float32", "Float64", "Date", "DateTime"
] or "Int" in datatype:
cast = f"reinterpretAs{datatype}"
decrypt(ciphertext=ciphertext,
key=f"'{key[:key_len]}'",
mode=mode,
iv=(None if not iv_len else f"'{iv[:iv_len]}'"),
aad=(None if not aad_len else f"'{aad}'"),
cast=cast,
endcast=endcast,
compare=compare,
message="1")