Zach Zeid
05/14/2020, 4:55 PM"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
05/14/2020, 5:09 PMfritz
05/14/2020, 5:21 PMCROSS JOIN
sundsta
05/14/2020, 5:22 PMCROSS JOIN
?fritz
05/14/2020, 5:27 PMzwass
05/14/2020, 5:28 PMCROSS JOIN
, just like JOIN
will create a cartesian product if you don't appropriately limit the join with ON
, USING
, or the WHERE
clause.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.users
is the first table.fritz
05/14/2020, 5:41 PMJOIN
. Thanks for the SQL lesson @zwass!Zach Zeid
05/15/2020, 12:57 PMwith 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 || "%");
CROSS JOIN
in this case?fritz
05/15/2020, 2:36 PMusername
column from the users
table before shell_history is being polled.LIKE
/ IN
comboLIKE
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
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
Zach Zeid
05/15/2020, 2:40 PMRun Time:
?fritz
05/15/2020, 2:41 PM.timer ON
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
05/15/2020, 3:05 PMfritz
05/15/2020, 3:08 PMZach Zeid
05/15/2020, 3:10 PMosquery> 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
05/15/2020, 3:11 PMZach Zeid
05/20/2020, 12:31 PM