?
Solved

AES_DECRYPT query

Posted on 2006-06-06
9
Medium Priority
?
2,251 Views
Last Modified: 2008-01-09
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
Comment
Question by:Lance_Frisbee
  • 3
  • 3
6 Comments
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 16845577
What version of MySQL is this?
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 16845631
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
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 16845832
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 16845872
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 16852204
Just tried it on 4.0.12, no errors.
0
 
LVL 2

Author Comment

by:Lance_Frisbee
ID: 16853102
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question