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"]);
}
Exemple #8
0
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);
}
Exemple #12
0
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;
}
Exemple #17
0
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++]);
  }
}