"invalid input syntax for integer" when input is actually a string

Question

I'm trying to extract an email from Basic Auth string like this:

SELECT substring(decode(substring('"authorization":["Basic dGVzdEB0ZXN0LmNvbTpwYXNzd29yZA=="]', '(?<="Basic ).*?(?="])'), 'base64'), '^.*(?=:)');

Expected result is [email protected], however I'm getting an error instead:

[22P02] ERROR: invalid input syntax for integer: "^.*(?=:)"

What's the problem here? Does postgres think that substring second argument must be an integer for some reason?

EDIT: Simplified this query a little just to demo that decoding is working:

SELECT decode(substring('"authorization":["Basic dGVzdEB0ZXN0LmNvbTpwYXNzd29yZA=="]', '(?<="Basic ).*?(?="])'), 'base64');

result: [email protected]:password

EDIT2: To people pointing out the absence of from - according to postgres docs you don't need it and it's interchangeable with a comma in this case - https://www.postgresql.org/docs/10/static/functions-matching.html

Example from this page:

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
Result: 123

EDIT3: Spelling.

@jarlh it should produce expected result and not throw an error. — Caballero
– Caballero, Commented Sep 4, 2018 at 13:57
@rory.ap according to postgres documentation substring() function also takes regural expression as an argument. — Caballero
– Caballero, Commented Sep 4, 2018 at 14:00

Utsav · Accepted Answer · 2018-09-04 14:07:24Z

1

You need convert_from to change the decode output to utf-8 or any other encoding format.

The output of decode is set of integer, not test.., hence the error.

http://rextester.com/NTMHG12543

SELECT substring(convert_from(decode(substring('"authorization":["Basic dGVzdEB0ZXN0LmNvbTpwYXNzd29yZA=="]', '(?<="Basic ).*?(?="])'
                                              ), 'base64'
                                    ), 'utf-8'
                             ), '^.*(?=:)'
                );

answered Sep 4, 2018 at 14:07

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grzegorz Grabek · Accepted Answer · 2018-09-04 14:02:57Z

0

The problem is that result of decode is not a text. It is a bytea and then substring expects an integer, not a regular expression. Just change type of the result of decode to text and all works perfectly (i hope so)

SELECT substring((decode(substring('"authorization":["Basic dGVzdEB0ZXN0LmNvbTpwYXNzd29yZA=="]', '(?<="Basic ).*?(?="])'), 'base64'))::text ,'^.*(?=:)');

answered Sep 4, 2018 at 14:02

Grzegorz Grabek

1,0007 silver badges16 bronze badges

3 Comments

Caballero Over a year ago

I suspected as much and tried the casting before posting this question, it didn't work.

Grzegorz Grabek Over a year ago

What version of postgres do you have? I've checked on 9.6 and it works fine after casting types. Returns "[email protected]"

Caballero Over a year ago

I'm running version 10. Tried your example and also CAST() function, didn't work.

Collectives™ on Stack Overflow

"invalid input syntax for integer" when input is actually a string

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related