• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2256
  • Last Modified:

AES_DECRYPT query

I have a silly question. I'm implementing additional security to some of my code libraries by utilizing MySQL's AES_ENCRYPT and AES_DECRYPT functions in place of the MD5 and SHA1 features.

I have been able to use an INSERT query to put a sample credit card into the database. It accepts the data fine, but when trying to extract and decrypt the value, my SQL query chokes and I get an error. Here are my details:

SALT TABLE STORES A ENCRYPTION/DECRYPTION KEY
===================================

CREATE TABLE `xxx_SALT` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `salt` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

INSERT INTO `xxx_SALT` VALUES (1, 'hashkey123');

===================================

CREDIT CARD TABLE STORES CARD INFO AND FOREIGN KEY
===================================

CREATE TABLE `xxx_CC` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `clientid` smallint(5) NOT NULL default '0',
  `card_number` blob NOT NULL,
  `card_expiration` varchar(5) NOT NULL default '',
  `card_csv` blob NOT NULL,
  PRIMARY KEY  (`id`)
);

===================================

MY INITIAL QUERY TO INSERT AN ENCRYPTED CREDIT CARD:
===================================

SELECT @salt:=salt
FROM xxx_SALT
WHERE `id` = 1;

INSERT INTO xxx_CC (
id,
clientid,
card_number,
card_expiration,
card_csv
)
VALUES (
NULL,
'243',
AES_ENCRYPT(4494390400684592, @salt),
'05/09',
AES_ENCRYPT(496, @salt)
);


THE ABOVE QUERIES ARE SUCCESSFUL AND A RECORD IS INSERTED
===================================

MY QUERY TO SELECT AND DECRYPT FAILS
===================================

SELECT @salt:=salt
FROM xxx_SALT
WHERE `id` = 1;

SELECT AES_DECRYPT(card_number, @salt) AS cc
FROM xxx_CC
WHERE `clientid` = '243';

===================================

MySQL ERROR MESSAGE: "#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '( card_number , @salt ) AS cc  FROM bsolid_CC  WHERE `clientid`"


What am I doing wrong on the AES_DECRYPT query? I'm not seeing any errors.

Any help is gratefully appreciated.

Lance
0
Lance_Frisbee
Asked:
Lance_Frisbee
  • 3
  • 3
1 Solution
 
snoyes_jwCommented:
What version of MySQL is this?
0
 
Lance_FrisbeeAuthor Commented:
My apologies... I forgot to mention that.

We have an upgrade scheduled for next week, but currently running 4.0.23.

I have documentation showing that AES_ENCRYPT() and AES_DECRYPT() will work after version 4.0.

I immediately thought it would be a old version issue because of the failed query, BUT the encrypt function worked... That didn't seem right to me. Perhaps you could provide more info or tell me if it's too old of a version.

Next week we will be using MySQL v 5.1

Thanks,

Lance
0
 
snoyes_jwCommented:
The manual indicates the functions were added in 4.0.2, so I don't think that's the issue.

I notice the spacing is a little different in the error message than in the example.  Obviously you've changed the table names to protect the innocent, but are you certain nothing else was changed in the query when you created the example?  I know there have been bugs reported dealing with spaces between a function name and the opening parenthesis.

Incidentally, I ran the queries just as you posted them here on both 4.1.7 and 5.0.15, and got the results with no errors.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Lance_FrisbeeAuthor Commented:
Yes, I am sure that no changes have been made in copying. I even copied them back from here before I submitted the question, and I still got the error, but ONLY on the AES_DECRYPT.

I'll review spacing once again, but that was something that I also noticed was modified when it spit the query back out to me. I did not have any extra spaces.

AES_DECRYPT(card_number, @salt) AS cc

Above is exactly the syntax and spacing I typically use.

I'll keep testing.

Thanks,

L
0
 
snoyes_jwCommented:
Just tried it on 4.0.12, no errors.
0
 
Lance_FrisbeeAuthor Commented:
Hmmm. I appreciate your additional testing. That is quite strange... Maybe it has to do something with the ancient hardware I'm on... I'll look into it.

Thank you,
Lance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now