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++]); } }