Mysql aes_encrypted data comparison

user_14287524 0
Viewed 33

I want to compare data encrypted with mysql aes_encrypt() against the encrypted data sent from my app

MySQL version: 10.2.43-MariaDB
Test data: 'abc'
password: '123'
encrypted data sent from my app: 0x911ff5b9a15aae9b52e7e9fde75315b1

The tests table contains one single record and the column varbinary50 contains data encrypted with mysql's aes_encrypt():

CREATE TABLE `tests` (
  `test_id` int(11) NOT NULL,
  `varbinary50` varbinary(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tests`
--

INSERT INTO `tests` (`test_id`, `varbinary50`) VALUES
(6, 0x911ff5b9a15aae9b52e7e9fde75315b1);

Since varbinary50 contains exactly the value '0x911ff5b9a15aae9b52e7e9fde75315b1', I expect following query to return 1; instead it's 0.

select varbinary50='0x911ff5b9a15aae9b52e7e9fde75315b1' from tests

Could someone tell me what I did wrong? TIA

compare encryption mysql
1 Answers

Solution: apply hex to varbinary50 and remove "0x" prefix from the data

  select lower(hex(varbinary50))='911ff5b9a15aae9b52e7e9fde75315b1' from tests
user_142875240
Related