How to use "%" character in sql query on linux shell?

Question

I am trying to pull all the jdk packages installed on set of hosts by sending a sql select statement to osquery on linux shell via pssh .

Here is the query:

pssh -h myhosts -i 'echo "SELECT name FROM rpm_packages where name like '%jdk%';"| osqueryi --json'

but usage of "%" is giving me below error.

Error: near line 1: near "%": syntax error

I tried to escape % ,but the error remains same. Any ideas how to overcome this error?

Answer 1

You aren't getting this error from your shell but from the query parser, and it's not actually caused by the % character, but to the ' that immediately precedes it. Look at where you have quotes:

'echo "SELECT name FROM rpm_packages where name like '%jdk%';"| osqueryi --json'
^----------------------------------------------------^     ^-------------------^

These quotes are consumed by the shell when it parses the argument. Single quotes tell the shell to ignore any otherwise-special characters inside and treat what is within the quotes as part of the argument -- but not the quotes themselves.

After shell parsing finishes, the actual, verbatim argument that gets sent to pssh looks like this:

echo "SELECT name FROM rpm_packages where name like %jdk%;"| osqueryi --json

Note that all of the single quotes have been erased. The result is that your query tool sees the % (presumably modulus) operator in a place that it doesn't expect -- right after another operator (like) which makes about as much sense to the parser as name like * jdk. The parser doesn't understand what it means to have two consecutive binary operators, so it complains about the second one: %.

In order to get a literal ' there, you need to jump through this hoop:

'\''
^^^^- start quoting again
|||
|\+-- literal '
|
\---- stop quoting

So, to fix this, replace all ' instances inside the string with '\'':

pssh -h myhosts -i 'echo "SELECT name FROM rpm_packages where name like '\''%jdk%'\'';"| osqueryi --json'

Answer 2

osqueryi accepts a single statement on the command line. Eliminating the echo can make quoting a bit simpler:

osqueryi --json "SELECT * FROM users where username like '%jdk%'"

You will, however, need the quotes to pass through your pssh command line.

While osqueryi is great for short simple things, if you're building a frequent polling service, osqueryd with scheduled queries is generally simpler.