Has anyone written a stored procedure in the database management tool that can query the decrypted data?

[Brawns]

[planeteleven0]

done with clr

[Brawns]

Want to directly query the decrypted data in the database management tool

[planeteleven0]

yes , create a .net framework library project , with same algorithm , use that dll as clr by creating the functions

[cargt3]

my sql script

`SET @keyBase = 'xxx'; SET @encrypted_data = 'xxx';

SET @@SESSION.block_encryption_mode = 'aes-256-cbc';

-- Convert the keyBase to binary SET @keyBaseBinary = CONVERT(@keyBase USING utf8);

-- Convert the binary keyBase to Base64 SET @key = TO_BASE64(@keyBaseBinary);

-- Decode the keyBase from Base64 encoding SET @binary_encrypted_data = FROM_BASE64(@encrypted_data);

-- Extract the first 16 bytes from the decoded data as the IV SET @iv = SUBSTRING(@binary_encrypted_data, 1, 16);

-- Remove the IV from the binary data SET @data_without_iv = SUBSTRING(@binary_encrypted_data, 17);

-- Decrypt the data using AES_DECRYPT in MySQL SELECT CAST(AES_DECRYPT(@data_without_iv, @key, @iv) AS CHAR) AS decrypted_data;`