How to compare base64 data in database with binary data?

Question

Due to some project requirement I have to convert my DB VARCHAR data into base64(VARBINARY). Now the problem is that while I am trying to query DB from my java application using:

criteria.createCriteria("parties").add(Restrictions.ilike("name", "%"+ "james".getBytes() +"%"));

My query returns me null while I already have base64 representation of james in my DB name column. Why?

shouldn't you convert james to base64 encoding instead of getting the raw bytes? — WeMakeSoftware, Aug 21 '14 at 12:50
don't have that option , because can't break the existing code — Minion, Aug 21 '14 at 12:52

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

Inspired by this question

Try something like this:

criteria.createCriteria("parties").add(
    Restrictions.ilike(
      "name",
      "%"+ new String(Base64.encodeBase64("james".getBytes())) +"%"));

edited May 23 '17 at 12:29

Community

1
1

answered Aug 21 '14 at 12:58

WeMakeSoftware

9,039
5
34
52

This won't work unless there are a multiple of 3 characters before "james" in the decoded text. – Olivier Grégoire Aug 21 '14 at 15:15
@OlivierGrégoire, could you comment on that? I'm not into base64 encoding myself. Why multiple of 3 characters are required? – WeMakeSoftware Aug 21 '14 at 16:50
`base64_encode("james")` returns `amFtZXM=` ; `base64_encode("ajames")` returns `YWphbWVz` ; `base64_encode("abjames")` returns `YWJqYW1lcw==` ; `base64_encode("abcjames")` returns `YWJjamFtZXM=`. Look at the first and the last one. You'll see the first result in the 4th. Do you see the first result in the 2nd or 3rd result ? – Olivier Grégoire Aug 22 '14 at 08:38
@OlivierGrégoire good point. So that makes like search practically impossible unless the whole dataset is padded? – WeMakeSoftware Aug 22 '14 at 09:02

How to compare base64 data in database with binary data?

1 Answers1