Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

False positive on JSON operator '#>' and '#>>' #29

Open
wennergr opened this issue Jun 5, 2019 · 3 comments
Open

False positive on JSON operator '#>' and '#>>' #29

wennergr opened this issue Jun 5, 2019 · 3 comments

Comments

@wennergr
Copy link

wennergr commented Jun 5, 2019

First thanks for a great tool! Very useful ;)

The issue:
Valid postgres sql statements that contains "'#>" and "#>>" fails validation.

Postgres documentation for these operations: https://www.postgresql.org/docs/11/functions-json.html

Example:

bash-4.4# echo "select '{\"a\":[1,2,3],\"b\":[4,5,6]}'::json#>>'{a,2}'" | pgsanity
line 2: ERROR: syntax error at or near "#>>'{a,2}'
"
@markdrago
Copy link
Owner

Tobias - thanks so much for reporting this issue. It's a fun one. ;-)

So, the way pgsanity works is that it tweaks the raw SQL to look like a C program with embedded SQL and then feeds that in to ecpg, a tool provided by postgresql, to perform the actual syntax checking. So, the trick to get this to work is to figure out what ecpg needs to see for it to understand that #>> is a valid operator. I've been looking through documentation and haven't yet figured out what it is about the # sign that ecpg doesn't like and I was hoping you could help.

Basically, we need to figure out what we need to do with the # sign in this command to get ecpg to accept it:

echo "EXEC SQL select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';" | ecpg -o - -

That command cuts pgsanity out of the picture and just passes a very small embedded C file to ecpg. I've tried a bunch of things (escaping the # with , doubling it up with ##, doing some trickery with C macros on a whim, etc.) and I haven't been able to figure it out. It's possible that this is just a bug in ecpg. I'm willing to bet that there are not a lot of people using both embedded SQL in C and putting JSON in their database. It'd be great if you could try and figure it out and maybe we can come to a conclusion together. Thanks again.

@mstovicek
Copy link

hi, any news about this issue?

@prussyuval
Copy link

Any news regarding this?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants