Incorrect string value errors in AES_ENCRYPT/AES_DECRYPT

Question

I'm introducing myself to the encryption functions in MYSQL.

Just wrote a simple SQL statement to add an encrypted entry into a field

INSERT INTO test_table (field1) VALUES(aes_encrypt('fieldentry','password'))

When I execute the SQL I get the following error

Error 1366: Incorrect string value: '\xC7\xE13\xC4\xF4!...' for column 'field1' at row 1 SQL Statement - CHANGE COLUMN field1 VARCHAR(255) NOT NULL COMMENT ''

Now I've read it may have something to do with CHARACTER SET, and tried changing it from utf8-default collation to utf8mb4 - default collation as recommended, but that didnt make any difference.

I've also tried changing the column type from VARCHAR to VARBINARY. The SQL statement then ran successfully, however, when I tried the following to retreive the data:

SELECT AES_DECRYPT(field1, '12345') AS endata FROM test_table
    Do Until rst.EOF 
        Response.Write(rst("endata"))
        rst.movenext
    Loop

The loop runs but no values are returned (blank lines)

I'm just looking for a straightforward what to encrypt and then decrypt my data using a password in the function AES_ENCRYPT/AES_DECRYPT.

Answer 1

AES_ENCRYPT() encrypts a string and returns a binary string. AES_DECRYPT() decrypts the encrypted string and returns the original string.

So you should change the type of field1,firstname from VARCHAR(255)to VARBINARY(255). It will fix the issue..

EDIT : For type missmatch.. please try this..

SELECT *, 
       CAST(AES_DECRYPT(field1, 'mypassword') AS CHAR(50)) end_data
FROM   user

Hope this helps..