AES DECRYPT and ENCRYPT in edit view not working

[cperrot]

Steps to reproduce

1. take any table and go to into a record edit view and choose function AES_DECRYPT, this will result in an error. right of the function.

Expected behaviour

no error

Actual behaviour

having an error

Server configuration

Ubuntu 16.04 and Windows too.

Database server

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.21-0ubuntu0.16.04.1 - (Ubuntu)
Protocol version: 10
User: root@localhost
Server charset: UTF-8 Unicode (utf8)

Web server

Apache
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation
PHP version: 7.0.25-0ubuntu0.16.04.1

phpMyAdmin

Version information: 4.7.4

Client configuration

Firefox 58.0.2

Operating system:
Ubuntu 16.04

[aroralakshya]

According to the documentation (https://www.w3resource.com/mysql/encryption-and-compression-functions/aes_decrypt().php)
AES_DECRYPT returns a binary string, so you should use it for a field defined as binary or varbinary, ot it will show the error.
I can confirm that it doesn't show any error for binary/varbinary fields.

[ibennetch]

Also, what is the error message shown?

[aroralakshya]

[williamdes]

@cperrot What should be done ?
@ibennetch We can enable AES_ENCRYPT for varchar fields ?

[cperrot]

After 1.5 years it's hard to remember what the issue was. Apparently AES_Encrypt only works for BINARY and VARBINARY fields.

[pmarquesmota]

Also, aes_decrypt returns binary in phpmyadmin and must be CASTed to CHAR, unlike the mysql client and the mysql documentation which says it returns the original plaintext string. See Stackoverflow

[williamdes]

Can you please send a step by step guide to help me know what you expect?

[williamdes]

@pmarquesmota @cperrot 🏓

[williamdes]

One year later, I am back on this issue and you out this block of code is blocking the execution.
It makes no sense for me, but I am investigating.

To make the functions works:

diff --git a/js/src/table/change.js b/js/src/table/change.js
index 31a09ca6c1..9b64f4c498 100644
--- a/js/src/table/change.js
+++ b/js/src/table/change.js
@@ -270,6 +270,7 @@ function verificationsAfterFieldChange (urlField, multiEdit, theType) {
         $('#salt_' + target.id).remove();
     }
 
+    /*
     if (target.value === 'AES_DECRYPT'
             || target.value === 'AES_ENCRYPT'
             || target.value === 'MD5') {
@@ -281,7 +282,7 @@ function verificationsAfterFieldChange (urlField, multiEdit, theType) {
                 }
             }
         });
-    }
+    }*/
 
     if (target.value === 'HEX' && theType.substring(0,3) === 'int') {
         // Add note when HEX function is selected on a int

[williamdes]

I made a fix for it: #16632