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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...