Postgresql "invalid end sequence"

Question

I've a function which adds users on my application. It does a few check, salts and hashes the password and then inserts the values. Now, when i run the function I get

ERROR: invalid end sequence

(to be honest, i get it in italian and had to do quite a job to find the corresponding english string). Now, the single parts work very well alone, but if I put everything together in a function I get the error so I'm pretty out of ideas. Any suggestion?

Tip: To get English error messages (in your current session only) run `SET lc_messages = 'C'`. — Erwin Brandstetter, Jul 06 '14 at 02:19
Additional tip: When trying to figure out the origin of an error, `\set VERBOSITY verbose` in `psql` will give you more info. Also, for reverse-translating, you can just grep the `.po` files in the source code. — Craig Ringer, Jul 06 '14 at 08:11

score 5 · Accepted Answer · answered Jul 05 '14 at 18:41

5

This error happens when trying to decode incorrectly encoded base64 contents. Example:

=> select decode('aa', 'base64');

ERROR: invalid end sequence

as opposed to:

=> select decode('aa==', 'base64');

 decode 
--------
 \x69
(1 row)

answered Jul 05 '14 at 18:41

Daniel Vérité

58,074
15
129
156

1

Seems like that could use a better error, like `decode(...): invalid base64 end sequence, missing padding?` I'll submit a patch. Though enabling log verbosity would provide useful context messages at least. – Craig Ringer Jul 06 '14 at 08:05
`\set VERBOSITY verbose`, `SELECT decode('fredd', 'base64');` emits `ERROR: 22023: invalid end sequence`, `LOCATION: b64_decode, encode.c:327`. – Craig Ringer Jul 06 '14 at 08:10
2

... and done. http://www.postgresql.org/message-id/53B90633.8030605@2ndquadrant.com – Craig Ringer Jul 06 '14 at 08:21
This can happen when you encode message and it comes out as lets say 7 symbols, and then you put it into a column that has size 6. It gets auto-truncated and you suspect nothing. But when you decode it, this error appears. – Imaskar Jan 14 '16 at 08:41

score 1 · Answer 2 · answered Oct 23 '14 at 12:21

try also

case 
  when char_length(sequence) in (6, 10, 14) then decode(sequence::text || '==', 'base64'::text)::character varying 
  when char_length(sequence) in (7, 11, 15) then decode(sequence::text || '=', 'base64'::text)::character varying 
  when char_length(sequence) in (8, 9, 12, 13) then decode(sequence::text, 'base64'::text)::character varying 
  else null
end AS sequence,

score 0 · Answer 3 · answered Jun 30 '22 at 11:12

if not sure input data correct,
　can catch the decode() exception, and return err value other than throw exception.

custom function

decode with exception catch

-------- try decode, ret null if err
CREATE OR REPLACE FUNCTION decode_null(str text, fmt text) RETURNS text AS $$
BEGIN
  RETURN decode(str, fmt);
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE USING
       MESSAGE = format('--decode_null: SQLSTATE %s, MSG: %s', SQLSTATE, SQLERRM);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

test

select decode_null('x', 'base64');
--decode_null: SQLSTATE 22023, MSG: invalid end sequence

select decode_null('_', 'base64');
--decode_null: SQLSTATE 22023, MSG: invalid symbol

Postgresql "invalid end sequence"

3 Answers3

custom function

test