Title
#sql
z

Zach Zeid

05/14/2020, 4:55 PM
I'm running this sql from a conversation earlier in this channel
"WITH forbidden_commands(cmd) AS (SELECT * FROM (values (\"rsync\"), (\"ngrok\"), (\"curl\"), (\"scp\"), (\"nc\")) ) SELECT username, uid, command FROM shell_history JOIN forbidden_commands on shell_history.command LIKE (\"%\" || forbidden_commands.cmd || \"%\") JOIN users USING(uid);
but I'm getting this error
W0514 12:24:23.284471 17017 virtual_table.cpp:959] The shell_history table returns data based on the current user by default, consider JOINing against the users table
It looks like it is joining against the users table,?
sundsta

sundsta

05/14/2020, 5:09 PM
Order of the joins matters. Select from users first and join on shell_history
f

fritz

05/14/2020, 5:21 PM
You can force this behavior by utilizing a
CROSS JOIN
sundsta

sundsta

05/14/2020, 5:22 PM
@fritz Good to know. Is there any performance penalty for
CROSS JOIN
?
f

fritz

05/14/2020, 5:27 PM
I believe in general CROSS JOIN's are treated with trepidation because they can be used to produce cartesian products in other SQL contexts. I am unsure of the cartesian pitfall potential within osquery. Maybe @zwass would be able to shed some light.
zwass

zwass

05/14/2020, 5:28 PM
This is a misconception.
CROSS JOIN
, just like
JOIN
will create a cartesian product if you don't appropriately limit the join with
ON
,
USING
, or the
WHERE
clause.
5:29 PM
The only difference with
CROSS JOIN
is that it prevents the optimizer from reordering the query. This is a desired effect in cases in which the order of the joins matter (like these tables that require a join to users). In these cases
CROSS JOIN
should always be used.
5:30 PM
Now with the above query the joins will have to be reordered so that
users
is the first table.
f

fritz

05/14/2020, 5:41 PM
TIL regarding limiting the
JOIN
. Thanks for the SQL lesson @zwass!
z

Zach Zeid

05/15/2020, 12:57 PM
Thanks for the input, I redid the query (minus a few values for readability, and I think this works?
with forbidden_commands(cmd) as (select * from (values ("curl"))) select username, uid, shell_history.command from users join shell_history using (uid) join forbidden_commands on shell_history.command like ("%" || forbidden_commands.cmd || "%");
1:52 PM
or should I be using
CROSS JOIN
in this case?
f

fritz

05/15/2020, 2:36 PM
@Zach Zeid That appears to work for me as well. The reason is likely because you are calling the
username
column from the
users
table before shell_history is being polled.
2:37 PM
I love the usage of cte's to do the
LIKE
/
IN
combo
2:39 PM
The other good news is that it appears to be no slower than doing a straight
LIKE
when there is only one variable at play:
osquery> with forbidden_commands(cmd) AS (
    ...>   select * from (values ("curl")))
    ...> select username, 
    ...>        uid, 
    ...>        shell_history.command 
    ...> FROM users 
    ...> JOIN shell_history USING (uid) 
    ...> JOIN forbidden_commands ON shell_history.command LIKE ("%" || forbidden_commands.cmd || "%");

>>>

Run Time: real 0.570 user 0.418604 sys 0.049183
2:39 PM
osquery> SELECT u.username, u.uid, sh.command FROM users u CROSS JOIN shell_history sh USING (uid)
    ...> WHERE command LIKE '%curl%';

>>>

Run Time: real 0.557 user 0.411912 sys 0.046748
z

Zach Zeid

05/15/2020, 2:40 PM
how are you getting that
Run Time:
?
f

fritz

05/15/2020, 2:41 PM
When you are in osqueryi prompt type:
.timer ON
2:42 PM
osquery> .help
Welcome to the osquery shell. Please explore your OS!
You are connected to a transient 'in-memory' virtual database.

.all [TABLE]     Select all from a table
.bail ON|OFF     Stop after hitting an error
.echo ON|OFF     Turn command echo on or off
.exit            Exit this program
.features        List osquery's features and their statuses
.headers ON|OFF  Turn display of headers on or off
.help            Show this message
.mode MODE       Set output mode where MODE is one of:
                   csv      Comma-separated values
                   column   Left-aligned columns see .width
                   line     One value per line
                   list     Values delimited by .separator string
                   pretty   Pretty printed SQL results (default)
.nullvalue STR   Use STRING in place of NULL values
.print STR...    Print literal STRING
.quit            Exit this program
.schema [TABLE]  Show the CREATE statements
.separator STR   Change separator used by output mode
.socket          Show the osquery extensions socket path
.show            Show the current values for various settings
.summary         Alias for the show meta command
.tables [TABLE]  List names of tables
.types [SQL]     Show result of getQueryColumns for the given query
.width [NUM1]+   Set column widths for "column" mode
.timer ON|OFF      Turn the CPU timer measurement on or off
zwass

zwass

05/15/2020, 3:05 PM
I would advise using CROSS JOIN otherwise you are at the risk of the optimizer reordering it and no longer having the users table called first.
f

fritz

05/15/2020, 3:08 PM
@Zach Zeid
z

Zach Zeid

05/15/2020, 3:10 PM
I see, that makes sense, so like this?
osquery> WITH forbidden_commands(cmd) AS (SELECT * FROM (values ("rsync"), ("ngrok"), ("curl"), ("scp"), ("nc")) ) SELECT username, uid, command FROM shell_history CROSS JOIN forbidden_commands on shell_history.command LIKE ("%" || forbidden_commands.cmd || "%") JOIN users USING(uid);
zwass

zwass

05/15/2020, 3:11 PM
Yes!
5:28 PM
I wrote up a little blog post explaining this: https://dactiv.llc/blog/osquery-join-users-table/
z

Zach Zeid

05/20/2020, 12:31 PM
This was incredibly easy to digest, thank you for writing it up!