base 64 decode in mysql

[trileletri]

Hi,
I have a table column which is base 64 encoded.

Fields are like:

SQB6AHIAYQBkAGEAIABuAGEAZABzAHQAcgBlAGEBbgBpAGMAZQA=

when i do something like this:

SELECT base64_decode(opis)
FROM `gsopis` 
WHERE `id` =1
LIMIT 0 , 30

Inested of:

Demontaza armirackog stola.

I get:

D\0e\0m\0o\0n\0t\0a\0~
a\0 \0a\0r\0m\0i\0r\0a\0\r
k\0o\0g\0 \0s\0t\0o\0l\0a\0

How can i get rid of unwanted characters? \0 and my language has these šđčćž, they also don't appear in string...

[Fou-Lu]

You'll need to wait for the SQL guys to comment on a fix for this, but it appears to me that its simply a charset issue. The results you have are showing terminators for multibyte, so that would indicate to me that the field it has come from is a multibyte charset, while it should be stored base64 encoded within a 8bit charset like latin.
Of course a better alternative is to simply not use base64 encoding at all and provide the field with the necessary charset to store multibyte characters.

[Old Pedant]

Yeah, FouLu is right. Somehow, you managed to encode it as UTF-16 but now you are decoding it as UTF-8 (or equivalent...in any case, encoded as 16 bit, decoded as 8 bit). Maybe you changed the CHARSET in the meantime? Or maybe you encoded it with something other than MySQL?