[RESOLVED] MySQL SELECT query failing...

[examancer]

I have a MySQL table that looks like the following

posts
-id
-user_id
-data

data is an AES encrypted field.

The following query properly selects all of the user's encrypted posts and orders them alphabetically (I know this is slow, but its a trade off for security... it works well):

SELECT id, CONVERT(AES_DECRYPT(data, 'mykey') USING latin1) as content FROM posts WHERE user_id = 1 ORDER BY content LIMIT 0, 100

AES_DECRYPT() returns a binary value, so I have to pass the result through CONVERT() to get a string, otherwise the order clause doesn't work.

What is bothering me is that although the above query works great, the following query does not. It fails to select the records with the specified keyword.

SELECT id, CONVERT(AES_DECRYPT(data, 'mykey') USING latin1) as content FROM posts WHERE user_id = 1 AND content LIKE '%keyword%' ORDER BY content LIMIT 0, 100

any ideas on why this isn't working and how I can get it to work?

[djjjozsi]

try this:

SELECT user_id, CONVERT(AES_DECRYPT(data, 'mykey') USING latin1) as content FROM posts

HAVING (user_id = 2 AND content LIKE '%content%') ORDER BY content LIMIT 0, 100

[examancer]

yes, thank you. I had never used HAVING before and had no idea what it was used for. I ended up using both a WHERE and HAVING clause:

SELECT id, CONVERT(AES_DECRYPT(data, 'mykey') USING latin1) as content FROM posts WHERE user_id = 1 HAVING content LIKE '%keyword%'

Seems to work well. Thanks.

[djjjozsi]

read this:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html