Thread: decode, base64 problem

Hi,

I don't know what this is about, so I could get from someone who knows more PostgreSQL than I do,

some rational explanation of what the problem is.

I have a table that has a "lice" field that is of type byte and the following 5 selects:

1. OK

2. error !

3. error ?

4. error ?
5. error !!!

1 -------------------------------------------------------

select

substring(encode(lice,'base64'), 110, 5) as Lokal0,
length(substring(encode(lice,'base64'), 110, 5)) as Len05

from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

 lokal0 | len05
--------+-------
 ICAgI  |     5
(1 row)

2 -------------------------------------------------------

select

substring(encode(lice,'base64'), 110, 5) as Lokal0,
length(substring(encode(lice,'base64'), 110, 5)) as Len05,

decode(substring(encode(lice,'base64'), 110, 5),'base64') as Lokal0decode

from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

ERROR:  22023: invalid end sequence
LOCATION:  b64_decode, encode.c:327

3. -------------------------------------------------------

select

substring(encode(lice,'base64'), 110, 5) as Lokal0,
length(substring(encode(lice,'base64'), 110, 5)) as Len05,

decode(substring(encode(lice,'base64'), 110, 5) || '=','base64') as Lokal0decode

from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

ERROR:  22023: unexpected "="
LOCATION:  b64_decode, encode.c:295

4. -------------------------------------------------------

select

substring(encode(lice,'base64'), 110, 5) as Lokal0,
length(substring(encode(lice,'base64'), 110, 5)) as Len05,

decode(substring(encode(lice,'base64'), 110, 5) || '==','base64') as Lokal0decode

from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

ERROR:  22023: unexpected "="
LOCATION:  b64_decode, encode.c:295

5. -------------------------------------------------------

select

substring(encode(lice,'base64'), 110, 5) as Lokal0,
length(substring(encode(lice,'base64'), 110, 5)) as Len05,

decode(/*substring(encode(lice,'base64'), 110, 5) || '=='*/'ICAgI','base64') as Lokal0decode

from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

ERROR:  22023: invalid end sequence
LOCATION:  b64_decode, encode.c:327
---------------------------------------------------------

My question is how to make a decode for a field for which encode text is known and the length is 5?

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, May 22, 2020

=?UTF-8?Q?gmail_Vladimir_Kokovi=c4=87?= <vladimir.kokovic@gmail.com> writes:
> I don't know what this is about, so I could get from someone who knows 
> more PostgreSQL than I do,

base64 translates groups of three bytes into groups of 4 characters, so
you can't just randomly chop the result at spots that aren't multiples of
4.  Even if the result happens to look like valid base64 data, the decode
output would be wrong, with bits incorrectly reassembled into bytes.

I'd recommend decoding the whole base64 string and then substring'ing
the result of that.

            regards, tom lane

What makes you believe that you should be able to get a decoded result after you've corrupted the encoded data?  You no longer have an encoded value, of any length, after doing substring - you now just have a sequence of five characters chosen from a subset of ASCII.

David J.

OK, I realized my mistake, but now I have a problem again:

select

encode(substring(lice, 110, 5),'base64') as Lokal0,

length(encode(substring(lice, 110, 5),'base64')) as Len05,

decode(encode(substring(lice, 110, 5),'base64')) as Lokal0decode
from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

ERROR:  42883: function decode(text) does not exist
LINE 19: decode(encode(substring(lice, 110, 5),'base64')) as Lokal0de...
         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
LOCATION:  ParseFuncOrColumn, parse_func.c:247

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, May 22, 2020

On Fri, May 22, 2020 at 9:24 AM gmail Vladimir Koković <vladimir.kokovic@gmail.com> wrote:

ERROR:  42883: function decode(text) does not exist
LINE 19: decode(encode(substring(lice, 110, 5),'base64')) as Lokal0de...
         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
LOCATION:  ParseFuncOrColumn, parse_func.c:247

You forgot the second argument to decode...

David J.

You are right, so definitely select works properly, thank you very much.

select

encode(substring(lice, 110, 5),'base64') as Lokal0,
length(encode(substring(lice, 110, 5),'base64')) as Len05,

decode(encode(substring(lice, 110, 5),'base64'),'base64') as Lokal0decode
from grupa002.ispp_partner
where obrisan=0 and maticnibroj=30643;

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, May 22, 2020