All Apps and Add-ons

Token substitues value with double quotes | unable to use panel token values in DB connect query to compare string values as they need single quotes

sh254087
Communicator

I have come across many questions related to quotations but did not find the one that addresses my scenario.

I have a dashboard with a drop down and 4 panels. Drop down populates values by using DB Connect's dbxquery. The panels are intended to populate appropriate result by running a dbxquery command, using the value from the drop down token as the value for WHERE condition in its query argument.

The problem I'm facing is with the format in which the dropdown token value is being substituted.

For example,
let the selected drop down value be - sampleText
when I use
|dbxquery query=".... WHERE value1=$token_value$" connection="xyz"
the query will be run as ".... WHERE value1="sampleText"
This doesn't fetch me any result as SQL doesn't accept " " for string comparison and will expect ' ' instead (i.e., 'sampleText' instead of "sampleText")

It seems I should do some string conversion in the query, just before comparing.

Can someone please let me know how to get over this scenario so that either the "$token_value$" pass single quotes or doesn't pass any quotes at all, or any string conversions that I can perform before comparing. Any help is much appreciated.

0 Karma
1 Solution

sh254087
Communicator

I got the answer - The problem was with the way I was using/accessing the token. I was accessing the token $token_value$ as $token_value|s$. I had just followed how it was used in another scenario, but not with dbxquery/SQL(may be '|s' does not ignore the spaces). I tried using the token without '|s' i.e., like $token_value$ itself and it worked fine.
So when I was using '|s' the value was being considered as a String and Splunk was hence placing it between double quotes.

View solution in original post

sh254087
Communicator

I got the answer - The problem was with the way I was using/accessing the token. I was accessing the token $token_value$ as $token_value|s$. I had just followed how it was used in another scenario, but not with dbxquery/SQL(may be '|s' does not ignore the spaces). I tried using the token without '|s' i.e., like $token_value$ itself and it worked fine.
So when I was using '|s' the value was being considered as a String and Splunk was hence placing it between double quotes.

cmerriman
Super Champion

adding |s to a token adds double quotes around a tokens' value.
http://docs.splunk.com/Documentation/Splunk/7.0.3/Viz/EventHandlerReference#set

sh254087
Communicator

Thank you, @cmerriman !

0 Karma

niketn
Legend

@cmerriman a better link to read and understand is Splunk Dashboard Token Filters Documentation.

@sh254087 Adding as much details in your question as possible helps as in this case we would have no clue that you had tried to apply token filters. In any case Accept your own answer to mark this question as answered. Also don't forget to up vote @cmerriman 's comment with the link to |s token filter example. Refer to one of my older answer https://answers.splunk.com/answers/568209/how-to-prevent-injection-from-field-in-a-dashboard.html where |s is suggested approach to pass query as string to prevent SQL Injection. Do try to grasp the concept and use token as per your need (with/without filter).

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

sh254087
Communicator

Thanks, @niketnilay ! Will check all these.

0 Karma

cmerriman
Super Champion

thank you @niketnilay ! i could not find that documentation, but I knew it was out there!

0 Karma

cmerriman
Super Champion

do you have " set as a prefix/suffix in your input? does it need to be or can it be set to '? Or if you're using this token in regular SPL as well, can you hardcode the "/' into the SQL and SPL around the token itself?

OTHERWISE, the option i see is to create a second token based off of this input. just create a hidden search like this one and use this token in your SQL

<search>
    <query>|makeresults|eval token_value2=replace($token_value$,"\"","'")</query>
    <done>
      <set token="token_value2">$result.token_value2$</set>
    </done>
  </search>

sh254087
Communicator

@cmerriman I had not set any prefix/suffix. I added single quotes after reading your comment(Thank you, I did not know about it's exact usage). But again I got the value in double quotes, but between single quotes like - ' "sampleText" '. Tried the hidden search, but that did not work as expected.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...