All Apps and Add-ons

UUIDs as variables passed to postgres dbquery / Removing quotes / ?

arichman
Explorer

Hello,
I've tried a wide variety of queries but Postgres is returning errors when trying to pass a UUID in a variable to my query:
base search | table l_id, l_name | eval l_id_quotes="'".l_id."'" | map maxsearches=100 search="dbquery mydb \"select * from my_table where id LIKE '$l_id_quotes$' \""

The errors look like:
[map]: command="dbquery", A database error occurred: ERROR: syntax error at or near "027e435" Position: 47

Without the eval statement, I was unable to get a single-quoted value for postgres to not parse as a column. And if I exclude single quotes from around '$l_id_quotes$' then the query is always wrapped in double quotes, which causes the same problem with postgres.

In addition to WHERE id LIKE..., I've tried:
WHERE id::varchar='$l_id_quotes$'
WHERE CHARINDEX (id, '$_id_quotes$') > 0
WHERE id IN ('$_id_quotes$')
... all to return the same errors.

The query WHERE id::varchar='fake-literal-valid-uuid' actually works.

Anybody done this successfully with postgres?
Thanks!

1 Solution

arichman
Explorer

I'll answer my own question...
I resolved this issue with some egregious string manipulation...

base search | table l_id, l_name | eval sql_str="select name, id::varchar from my_table where id = '".$l_id$."';" | map maxsearches=100 search="| dbquery mydb $sql_str$"

View solution in original post

arichman
Explorer

I'll answer my own question...
I resolved this issue with some egregious string manipulation...

base search | table l_id, l_name | eval sql_str="select name, id::varchar from my_table where id = '".$l_id$."';" | map maxsearches=100 search="| dbquery mydb $sql_str$"

mishin
Explorer

thanks, very useful!!

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...