TEST_F(VirtualTableTests, test_table_cache) {
// Get a database connection.
Registry::add<cacheTablePlugin>("table", "cache");
auto dbc = SQLiteDBManager::getUnique();
{
auto cache = std::make_shared<cacheTablePlugin>();
attachTableInternal("cache", cache->columnDefinition(), dbc);
}
QueryData results;
// Run a query with a join within.
std::string statement = "SELECT c2.data as data FROM cache c1, cache c2;";
auto status = queryInternal(statement, results, dbc->db());
dbc->clearAffectedTables();
EXPECT_TRUE(status.ok());
ASSERT_EQ(results.size(), 1U);
EXPECT_EQ(results[0]["data"], "more_awesome_data");
// Run the query again, the virtual table cache should have been expired.
results.clear();
statement = "SELECT data from cache c1";
queryInternal(statement, results, dbc->db());
ASSERT_EQ(results.size(), 1U);
ASSERT_EQ(results[0]["data"], "awesome_data");
}
TEST_F(VirtualTableTests, test_null_values) {
auto dbc = SQLiteDBManager::getUnique();
std::string statement = "SELECT NULL as null_value;";
{
QueryData results;
auto status = queryInternal(statement, results, dbc->db());
EXPECT_TRUE(status.ok());
EXPECT_EQ(results[0]["null_value"], "");
}
// Try INTEGER.
{
QueryData results;
statement = "SELECT CAST(NULL as INTEGER) as null_value;";
queryInternal(statement, results, dbc->db());
EXPECT_EQ(results[0]["null_value"], "");
}
// BIGINT.
{
QueryData results;
statement = "SELECT CAST(NULL as BIGINT) as null_value;";
queryInternal(statement, results, dbc->db());
EXPECT_EQ(results[0]["null_value"], "");
}
// Try DOUBLE.
{
QueryData results;
statement = "SELECT CAST(NULL as DOUBLE) as null_value;";
queryInternal(statement, results, dbc->db());
EXPECT_EQ(results[0]["null_value"], "");
}
}
QueryPlanner::QueryPlanner(const std::string& query, sqlite3* db) {
QueryData plan;
queryInternal("EXPLAIN QUERY PLAN " + query, plan, db);
queryInternal("EXPLAIN " + query, program_, db);
for (const auto& row : plan) {
auto details = osquery::split(row.at("detail"));
tables_.push_back(details[2]);
}
}
TEST_F(SQLiteUtilTests, test_aggregate_query) {
auto dbc = getTestDBC();
QueryData results;
auto status = queryInternal(kTestQuery, results, dbc.db());
EXPECT_TRUE(status.ok());
EXPECT_EQ(results, getTestDBExpectedResults());
}
TEST_F(VirtualTableTests, test_sqlite3_attach_vtable) {
auto table = std::make_shared<sampleTablePlugin>();
table->setName("sample");
// Request a managed "connection".
// This will be a single (potentially locked) instance or a transient
// SQLite database if there is contention and a lock was not requested.
auto dbc = SQLiteDBManager::get();
// Virtual tables require the registry/plugin API to query tables.
auto status = attachTableInternal("failed_sample", "(foo INTEGER)", dbc.db());
EXPECT_EQ(status.getCode(), SQLITE_ERROR);
// The table attach will complete only when the table name is registered.
Registry::add<sampleTablePlugin>("table", "sample");
PluginResponse response;
status = Registry::call("table", "sample", {{"action", "columns"}}, response);
EXPECT_TRUE(status.ok());
// Use the table name, plugin-generated schema to attach.
status = attachTableInternal("sample", columnDefinition(response), dbc.db());
EXPECT_EQ(status.getCode(), SQLITE_OK);
std::string q = "SELECT sql FROM sqlite_temp_master WHERE tbl_name='sample';";
QueryData results;
status = queryInternal(q, results, dbc.db());
EXPECT_EQ(
"CREATE VIRTUAL TABLE sample USING sample(`foo` INTEGER, `bar` TEXT)",
results[0]["sql"]);
}
TEST_F(SQLiteUtilTests, test_direct_query_execution) {
auto dbc = getTestDBC();
QueryData results;
auto status = queryInternal(kTestQuery, results, dbc->db());
EXPECT_TRUE(status.ok());
EXPECT_EQ(results, getTestDBExpectedResults());
}
TEST_F(VirtualTableTests, test_sqlite3_attach_vtable) {
auto table = std::make_shared<sampleTablePlugin>();
table->setName("sample");
//sqlite3* db = nullptr;
//sqlite3_open(":memory:", &db);
auto dbc = SQLiteDBManager::get();
// Virtual tables require the registry/plugin API to query tables.
auto status =
tables::attachTableInternal("failed_sample", "(foo INTEGER)", dbc.db());
EXPECT_EQ(status.getCode(), SQLITE_ERROR);
// The table attach will complete only when the table name is registered.
Registry::add<sampleTablePlugin>("table", "sample");
PluginResponse response;
status = Registry::call("table", "sample", {{"action", "columns"}}, response);
EXPECT_TRUE(status.ok());
// Use the table name, plugin-generated schema to attach.
status = tables::attachTableInternal(
"sample", tables::columnDefinition(response), dbc.db());
EXPECT_EQ(status.getCode(), SQLITE_OK);
std::string q = "SELECT sql FROM sqlite_temp_master WHERE tbl_name='sample';";
QueryData results;
status = queryInternal(q, results, dbc.db());
EXPECT_EQ("CREATE VIRTUAL TABLE sample USING sample(foo INTEGER, bar TEXT)",
results[0]["sql"]);
}
static void SQL_select_metadata(benchmark::State& state) {
auto dbc = SQLiteDBManager::get();
while (state.KeepRunning()) {
QueryData results;
queryInternal("select count(*) from sqlite_temp_master;", results,
dbc->db());
}
}
SQLInternal::SQLInternal(const std::string& q) {
auto dbc = SQLiteDBManager::get();
status_ = queryInternal(q, results_, dbc->db());
// One of the advantages of using SQLInternal (aside from the Registry-bypass)
// is the ability to "deep-inspect" the table attributes and actions.
event_based_ = (dbc->getAttributes() & TableAttributes::EVENT_BASED) != 0;
dbc->clearAffectedTables();
}
TEST_F(SQLiteUtilTests, test_affected_tables) {
auto dbc = getTestDBC();
QueryData results;
auto status = queryInternal("SELECT * FROM time", results, dbc->db());
// Since the table scanned from "time", it should be recorded as affected.
EXPECT_EQ(dbc->affected_tables_.count("time"), 1U);
dbc->clearAffectedTables();
EXPECT_EQ(dbc->affected_tables_.size(), 0U);
}
TEST_F(VirtualTableTests, test_sqlite3_table_joins) {
// Get a database connection.
auto dbc = SQLiteDBManager::get();
QueryData results;
// Run a query with a join within.
std::string statement =
"SELECT p.pid FROM osquery_info oi, processes p WHERE oi.pid=p.pid";
auto status = queryInternal(statement, results, dbc.db());
EXPECT_TRUE(status.ok());
EXPECT_EQ(results.size(), 1U);
}
static void SQL_virtual_table_internal_long(benchmark::State& state) {
Registry::add<BenchmarkLongTablePlugin>("table", "long_benchmark");
PluginResponse res;
Registry::call("table", "long_benchmark", {{"action", "columns"}}, res);
// Attach a sample virtual table.
auto dbc = SQLiteDBManager::get();
attachTableInternal("long_benchmark", columnDefinition(res), dbc->db());
while (state.KeepRunning()) {
QueryData results;
queryInternal("select * from long_benchmark", results, dbc->db());
}
}
TEST_F(SQLiteUtilTests, test_get_test_db_result_stream) {
auto dbc = getTestDBC();
auto results = getTestDBResultStream();
for (auto r : results) {
char* err_char = nullptr;
sqlite3_exec(dbc.db(), (r.first).c_str(), nullptr, nullptr, &err_char);
EXPECT_TRUE(err_char == nullptr);
if (err_char != nullptr) {
sqlite3_free(err_char);
ASSERT_TRUE(false);
}
QueryData expected;
auto status = queryInternal(kTestQuery, expected, dbc.db());
EXPECT_EQ(expected, r.second);
}
}
TEST_F(SQLiteUtilTests, test_reset) {
auto internal_db = SQLiteDBManager::get()->db();
ASSERT_NE(nullptr, internal_db);
sqlite3_exec(internal_db,
"create view test_view as select 'test';",
nullptr,
nullptr,
nullptr);
SQLiteDBManager::resetPrimary();
auto instance = SQLiteDBManager::get();
QueryData results;
queryInternal("select * from test_view", results, instance);
// Assume the internal (primary) database we reset and recreated.
EXPECT_EQ(results.size(), 0U);
}
TEST_F(VirtualTableTests, test_json_extract) {
// Get a database connection.
Registry::add<jsonTablePlugin>("table", "json");
auto dbc = SQLiteDBManager::get();
{
auto json = std::make_shared<jsonTablePlugin>();
attachTableInternal("json", json->columnDefinition(), dbc->db());
}
QueryData results;
// Run a query with a join within.
std::string statement =
"SELECT JSON_EXTRACT(data, '$.test') AS test FROM json;";
auto status = queryInternal(statement, results, dbc->db());
EXPECT_TRUE(status.ok());
ASSERT_EQ(results.size(), 1U);
EXPECT_EQ(results[0]["test"], "1");
}
TimeInformation::List TimeQuery::query(const QString &stationId, const QStringList &lineNumbers, const QDateTime &dateTime)
{
const TimeInformation::List information = queryInternal(stationId, dateTime);
TimeInformation::List filteredInformation;
if (!lineNumbers.isEmpty()) {
for (int i = 0; i < information.count(); ++i) {
const TimeInformation info = information.at(i);
for (int j = 0; j < lineNumbers.count(); ++j) {
if (info.direction().startsWith(QString("%1 ").arg(lineNumbers.at(j))))
filteredInformation.append(info);
}
}
} else {
filteredInformation = information;
}
return filteredInformation;
}
SQLInternal::SQLInternal(const std::string& q) {
auto dbc = SQLiteDBManager::get();
status_ = queryInternal(q, results_, dbc->db());
dbc->clearAffectedTables();
}
Status SQLiteSQLPlugin::query(const std::string& q, QueryData& results) const {
auto dbc = SQLiteDBManager::get();
auto result = queryInternal(q, results, dbc->db());
dbc->clearAffectedTables();
return result;
}
TEST_F(VirtualTableTests, test_constraints_stacking) {
// Add two testing tables to the registry.
Registry::add<pTablePlugin>("table", "p");
Registry::add<kTablePlugin>("table", "k");
auto dbc = SQLiteDBManager::get();
{
// To simplify the attach, just access the column definition directly.
auto p = std::make_shared<pTablePlugin>();
attachTableInternal("p", p->columnDefinition(), dbc->db());
auto k = std::make_shared<kTablePlugin>();
attachTableInternal("k", k->columnDefinition(), dbc->db());
}
QueryData results;
std::string statement;
std::map<std::string, std::string> expected;
std::vector<std::pair<std::string, QueryData> > constraint_tests = {
MP("select k.x from p, k", makeResult("x", {"1", "2", "1", "2"})),
MP("select k.x from (select * from k) k2, p, k where k.x = p.x",
makeResult("k.x", {"1", "1", "2", "2"})),
MP("select k.x from (select * from k where z = 1) k2, p, k where k.x = "
"p.x",
makeResult("k.x", {"1", "2"})),
MP("select k.x from k k1, (select * from p) p1, k where k.x = p1.x",
makeResult("k.x", {"1", "1", "2", "2"})),
MP("select k.x from (select * from p) p1, k, (select * from k) k2 where "
"k.x = p1.x",
makeResult("k.x", {"1", "1", "2", "2"})),
MP("select k.x from (select * from p) p1, k, (select * from k where z = "
"2) k2 where k.x = p1.x",
makeResult("k.x", {"1", "2"})),
MP("select k.x from k, (select * from p) p1, k k2, (select * from k "
"where z = 1) k3 where k.x = p1.x",
makeResult("k.x", {"1", "1", "2", "2"})),
MP("select p.x from (select * from k where z = 1) k1, (select * from k "
"where z != 1) k2, p where p.x = k2.x",
makeResult("p.x", {"1"})),
MP("select p.x from (select * from k, (select x as xx from k where x = "
"1) k2 where z = 1) k1, (select * from k where z != 1) k2, p, k as k3 "
"where p.x = k2.x",
makeResult("p.x", {"1", "1"})),
};
for (const auto& test : constraint_tests) {
QueryData results;
queryInternal(test.first, results, dbc->db());
EXPECT_EQ(results, test.second);
}
std::vector<QueryData> union_results = {
makeResult("x", {"1", "2"}), makeResult("k.x", {"1", "2"}),
makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}),
makeResult("k.x", {"1", "2"}), makeResult("k.x", {"1", "2"}),
makeResult("k.x", {"1", "2"}), makeResult("p.x", {"1"}),
makeResult("p.x", {"1"}),
};
size_t index = 0;
for (const auto& test : constraint_tests) {
QueryData results;
queryInternal(test.first + " union " + test.first, results, dbc->db());
EXPECT_EQ(results, union_results[index++]);
}
}