/src/osquery/plugins/database/sqlite.cpp

Source (jump to first uncovered line)
/**
 * Copyright (c) 2014-present, The osquery authors
 *
 * This source code is licensed as defined by the LICENSE file found in the
 * root directory of this source tree.
 *
 * SPDX-License-Identifier: (Apache-2.0 OR GPL-2.0-only)
 */

#include <sstream>

#include <sqlite3.h>
#include <sys/stat.h>

#include <osquery/core/query.h>
#include <osquery/filesystem/fileops.h>
#include <osquery/filesystem/filesystem.h>
#include <osquery/logger/logger.h>
#include <osquery/utils/conversions/tryto.h>
#include <plugins/database/sqlite.h>

namespace osquery {

const std::map<std::string, std::string> kDBSettings = {
    {"synchronous", "OFF"},
    {"count_changes", "OFF"},
    {"default_temp_store", "2"},
    {"auto_vacuum", "FULL"},
    {"journal_mode", "OFF"},
    {"cache_size", "1000"},
    {"page_count", "1000"},
};

Status SQLiteDatabasePlugin::setUp() {
  if (!allowOpen()) {
    LOG(WARNING) << RLOG(1629) << "Not allowed to set up database plugin";
  }

  // Consume the current settings.
  // A configuration update may change them, but that does not affect state.
  path_ = FLAGS_database_path;

  if (pathExists(path_).ok() && !isReadable(path_).ok()) {
    return Status(1, "Cannot read database path: " + path_);
  }

  if (!checkingDB()) {
    VLOG(1) << "Opening database handle: " << path_;
  }

  // Tests may trash calls to setUp, make sure subsequent calls do not leak.
  close();

  // Open the SQLite backing storage at path_
  auto result = sqlite3_open_v2(
      path_.c_str(),
      &db_,
      (SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE),
      nullptr);

  if (result != SQLITE_OK || db_ == nullptr) {
    close();
    // A failed open in R/W mode is a runtime error.
    return Status(1, "Cannot open database: " + std::to_string(result));
  }

  for (const auto& domain : kDomains) {
    std::string q = "create table if not exists " + domain +
                    " (key TEXT PRIMARY KEY, value TEXT);";
    result = sqlite3_exec(db_, q.c_str(), nullptr, nullptr, nullptr);
    if (result != SQLITE_OK) {
      close();
      return Status(1, "Cannot create domain: " + domain);
    }
  }

  std::string settings;
  for (const auto& setting : kDBSettings) {
    settings += "PRAGMA " + setting.first + "=" + setting.second + "; ";
  }
  sqlite3_exec(db_, settings.c_str(), nullptr, nullptr, nullptr);

  // RocksDB may not create/append a directory with acceptable permissions.
  if (platformSetSafeDbPerms(path_) == false) {
    close();
    return Status(1, "Cannot set permissions on database path: " + path_);
  }
  return Status(0);
}

void SQLiteDatabasePlugin::close() {
  WriteLock lock(close_mutex_);
  if (db_ != nullptr) {
    sqlite3_close(db_);
    db_ = nullptr;
  }
}

static int getData(void* argument, int argc, char* argv[], char* column[]) {
  if (argument == nullptr) {
    return SQLITE_MISUSE;
  }

  QueryData* qData = (QueryData*)argument;
  Row r;
  for (int i = 0; i < argc; i++) {
    if (column[i] != nullptr) {
      r[column[i]] = (argv[i] != nullptr) ? argv[i] : "";
    }
  }
  (*qData).push_back(std::move(r));
  return 0;
}

Status SQLiteDatabasePlugin::get(const std::string& domain,
                                 const std::string& key,
                                 std::string& value) const {
  QueryData results;
  char* err = nullptr;
  std::string q = "select value from " + domain + " where key = '" + key + "';";
  sqlite3_exec(db_, q.c_str(), getData, &results, &err);
  if (err != nullptr) {
    sqlite3_free(err);
  }

  // Only assign value if the query found a result.
  if (results.size() > 0) {
    value = std::move(results[0]["value"]);
    return Status(0);
  }
  return Status(1);
}

Status SQLiteDatabasePlugin::get(const std::string& domain,
                                 const std::string& key,
                                 int& value) const {
  std::string result;
  auto s = this->get(domain, key, result);
  if (s.ok()) {
    auto expectedValue = tryTo<int>(result);
    if (expectedValue.isError()) {
      return Status::failure("Could not deserialize str to int");
    } else {
      value = expectedValue.take();
    }
  }
  return s;
}

static void tryVacuum(sqlite3* db) {
  std::string q =
      "SELECT (sum(s1.pageno + 1 == s2.pageno) * 1.0 / count(*)) < 0.01 as v "
      " FROM "
      "(SELECT pageno FROM dbstat ORDER BY path) AS s1,"
      "(SELECT pageno FROM dbstat ORDER BY path) AS s2 WHERE "
      "s1.rowid + 1 = s2.rowid; ";

  QueryData results;
  sqlite3_exec(db, q.c_str(), getData, &results, nullptr);
  if (results.size() > 0 && results[0]["v"].back() == '1') {
    sqlite3_exec(db, "vacuum;", nullptr, nullptr, nullptr);
  }
}

Status SQLiteDatabasePlugin::put(const std::string& domain,
                                 const std::string& key,
                                 const std::string& value) {
  return putBatch(domain, {std::make_pair(key, value)});
}

Status SQLiteDatabasePlugin::put(const std::string& domain,
                                 const std::string& key,
                                 int value) {
  return putBatch(domain, {std::make_pair(key, std::to_string(value))});
}

Status SQLiteDatabasePlugin::putBatch(const std::string& domain,
                                      const DatabaseStringValueList& data) {
  // Prepare the query, adding placeholders for all the rows we have in `data`
  std::stringstream buffer;
  buffer << "insert or replace into " + domain + " values ";

  for (auto i = 1U; i <= data.size(); i++) {
    auto index = i * 2;
    buffer << "(?" << index - 1 << ", ?" << index << ")";

    if (i + 1 > data.size()) {
      buffer << ";";
    } else {
      buffer << ", ";
    }
  }

  const auto& q = buffer.str();

  // Bind each value from the rows we got
  sqlite3_stmt* stmt = nullptr;
  sqlite3_prepare_v2(db_, q.c_str(), -1, &stmt, nullptr);

  {
    int i = 1;

    for (const auto& p : data) {
      const auto& key = p.first;
      const auto& value = p.second;

      sqlite3_bind_text(stmt, i, key.c_str(), -1, SQLITE_STATIC);
      sqlite3_bind_text(stmt, i + 1, value.c_str(), -1, SQLITE_STATIC);

      i += 2;
    }
  }

  auto rc = sqlite3_step(stmt);
  if (rc != SQLITE_DONE) {
    return Status(1);
  }

  sqlite3_finalize(stmt);
  if (rand() % 10 == 0) {
    tryVacuum(db_);
  }

  return Status::success();
}

Status SQLiteDatabasePlugin::remove(const std::string& domain,
                                    const std::string& key) {
  sqlite3_stmt* stmt = nullptr;
  std::string q = "delete from " + domain + " where key IN (?1);";
  sqlite3_prepare_v2(db_, q.c_str(), -1, &stmt, nullptr);

  sqlite3_bind_text(stmt, 1, key.c_str(), -1, SQLITE_STATIC);
  auto rc = sqlite3_step(stmt);
  if (rc != SQLITE_DONE) {
    return Status(1);
  }

  sqlite3_finalize(stmt);
  if (rand() % 10 == 0) {
    tryVacuum(db_);
  }
  return Status(0);
}

Status SQLiteDatabasePlugin::removeRange(const std::string& domain,
                                         const std::string& low,
                                         const std::string& high) {
  if (low > high) {
    return Status::failure("Invalid range: low > high");
  }

  sqlite3_stmt* stmt = nullptr;
  std::string q = "delete from " + domain + " where key >= ?1 and key <= ?2;";
  sqlite3_prepare_v2(db_, q.c_str(), -1, &stmt, nullptr);

  sqlite3_bind_text(stmt, 1, low.c_str(), -1, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, high.c_str(), -1, SQLITE_STATIC);
  auto rc = sqlite3_step(stmt);
  if (rc != SQLITE_DONE) {
    return Status(1);
  }

  sqlite3_finalize(stmt);
  if (rand() % 10 == 0) {
    tryVacuum(db_);
  }
  return Status(0);
}

Status SQLiteDatabasePlugin::scan(const std::string& domain,
                                  std::vector<std::string>& results,
                                  const std::string& prefix,
                                  uint64_t max) const {
  QueryData _results;
  char* err = nullptr;

  std::string q =
      "select key from " + domain + " where key LIKE '" + prefix + "%'";
  if (max > 0) {
    q += " limit " + std::to_string(max);
  }
  sqlite3_exec(db_, q.c_str(), getData, &_results, &err);
  if (err != nullptr) {
    sqlite3_free(err);
  }

  // Only assign value if the query found a result.
  for (auto& r : _results) {
    results.push_back(std::move(r["key"]));
  }

  return Status::success();
}
} // namespace osquery

Coverage Report

Created: 2025-08-05 08:11

Line	Count	Source (jump to first uncovered line)
1		/**
2		* Copyright (c) 2014-present, The osquery authors
3		*
4		* This source code is licensed as defined by the LICENSE file found in the
5		* root directory of this source tree.
6		*
7		* SPDX-License-Identifier: (Apache-2.0 OR GPL-2.0-only)
8		*/
9
10		#include <sstream>
11
12		#include <sqlite3.h>
13		#include <sys/stat.h>
14
15		#include <osquery/core/query.h>
16		#include <osquery/filesystem/fileops.h>
17		#include <osquery/filesystem/filesystem.h>
18		#include <osquery/logger/logger.h>
19		#include <osquery/utils/conversions/tryto.h>
20		#include <plugins/database/sqlite.h>
21
22		namespace osquery {
23
24		const std::map<std::string, std::string> kDBSettings = {
25		{"synchronous", "OFF"},
26		{"count_changes", "OFF"},
27		{"default_temp_store", "2"},
28		{"auto_vacuum", "FULL"},
29		{"journal_mode", "OFF"},
30		{"cache_size", "1000"},
31		{"page_count", "1000"},
32		};
33
34	0	Status SQLiteDatabasePlugin::setUp() {
35	0	if (!allowOpen()) {
36	0	LOG(WARNING) << RLOG(1629) << "Not allowed to set up database plugin";
37	0	}
38
39		// Consume the current settings.
40		// A configuration update may change them, but that does not affect state.
41	0	path_ = FLAGS_database_path;
42
43	0	if (pathExists(path_).ok() && !isReadable(path_).ok()) {
44	0	return Status(1, "Cannot read database path: " + path_);
45	0	}
46
47	0	if (!checkingDB()) {
48	0	VLOG(1) << "Opening database handle: " << path_;
49	0	}
50
51		// Tests may trash calls to setUp, make sure subsequent calls do not leak.
52	0	close();
53
54		// Open the SQLite backing storage at path_
55	0	auto result = sqlite3_open_v2(
56	0	path_.c_str(),
57	0	&db_,
58	0	(SQLITE_OPEN_FULLMUTEX \| SQLITE_OPEN_READWRITE \| SQLITE_OPEN_CREATE),
59	0	nullptr);
60
61	0	if (result != SQLITE_OK \|\| db_ == nullptr) {
62	0	close();
63		// A failed open in R/W mode is a runtime error.
64	0	return Status(1, "Cannot open database: " + std::to_string(result));
65	0	}
66
67	0	for (const auto& domain : kDomains) {
68	0	std::string q = "create table if not exists " + domain +
69	0	" (key TEXT PRIMARY KEY, value TEXT);";
70	0	result = sqlite3_exec(db_, q.c_str(), nullptr, nullptr, nullptr);
71	0	if (result != SQLITE_OK) {
72	0	close();
73	0	return Status(1, "Cannot create domain: " + domain);
74	0	}
75	0	}
76
77	0	std::string settings;
78	0	for (const auto& setting : kDBSettings) {
79	0	settings += "PRAGMA " + setting.first + "=" + setting.second + "; ";
80	0	}
81	0	sqlite3_exec(db_, settings.c_str(), nullptr, nullptr, nullptr);
82
83		// RocksDB may not create/append a directory with acceptable permissions.
84	0	if (platformSetSafeDbPerms(path_) == false) {
85	0	close();
86	0	return Status(1, "Cannot set permissions on database path: " + path_);
87	0	}
88	0	return Status(0);
89	0	}
90
91	2	void SQLiteDatabasePlugin::close() {
92	2	WriteLock lock(close_mutex_);
93	2	if (db_ != nullptr) {
94	0	sqlite3_close(db_);
95	0	db_ = nullptr;
96	0	}
97	2	}
98
99	0	static int getData(void* argument, int argc, char* argv[], char* column[]) {
100	0	if (argument == nullptr) {
101	0	return SQLITE_MISUSE;
102	0	}
103
104	0	QueryData* qData = (QueryData*)argument;
105	0	Row r;
106	0	for (int i = 0; i < argc; i++) {
107	0	if (column[i] != nullptr) {
108	0	r[column[i]] = (argv[i] != nullptr) ? argv[i] : "";
109	0	}
110	0	}
111	0	(*qData).push_back(std::move(r));
112	0	return 0;
113	0	}
114
115		Status SQLiteDatabasePlugin::get(const std::string& domain,
116		const std::string& key,
117	0	std::string& value) const {
118	0	QueryData results;
119	0	char* err = nullptr;
120	0	std::string q = "select value from " + domain + " where key = '" + key + "';";
121	0	sqlite3_exec(db_, q.c_str(), getData, &results, &err);
122	0	if (err != nullptr) {
123	0	sqlite3_free(err);
124	0	}
125
126		// Only assign value if the query found a result.
127	0	if (results.size() > 0) {
128	0	value = std::move(results[0]["value"]);
129	0	return Status(0);
130	0	}
131	0	return Status(1);
132	0	}
133
134		Status SQLiteDatabasePlugin::get(const std::string& domain,
135		const std::string& key,
136	0	int& value) const {
137	0	std::string result;
138	0	auto s = this->get(domain, key, result);
139	0	if (s.ok()) {
140	0	auto expectedValue = tryTo<int>(result);
141	0	if (expectedValue.isError()) {
142	0	return Status::failure("Could not deserialize str to int");
143	0	} else {
144	0	value = expectedValue.take();
145	0	}
146	0	}
147	0	return s;
148	0	}
149
150	0	static void tryVacuum(sqlite3* db) {
151	0	std::string q =
152	0	"SELECT (sum(s1.pageno + 1 == s2.pageno) * 1.0 / count(*)) < 0.01 as v "
153	0	" FROM "
154	0	"(SELECT pageno FROM dbstat ORDER BY path) AS s1,"
155	0	"(SELECT pageno FROM dbstat ORDER BY path) AS s2 WHERE "
156	0	"s1.rowid + 1 = s2.rowid; ";
157
158	0	QueryData results;
159	0	sqlite3_exec(db, q.c_str(), getData, &results, nullptr);
160	0	if (results.size() > 0 && results[0]["v"].back() == '1') {
161	0	sqlite3_exec(db, "vacuum;", nullptr, nullptr, nullptr);
162	0	}
163	0	}
164
165		Status SQLiteDatabasePlugin::put(const std::string& domain,
166		const std::string& key,
167	0	const std::string& value) {
168	0	return putBatch(domain, {std::make_pair(key, value)});
169	0	}
170
171		Status SQLiteDatabasePlugin::put(const std::string& domain,
172		const std::string& key,
173	0	int value) {
174	0	return putBatch(domain, {std::make_pair(key, std::to_string(value))});
175	0	}
176
177		Status SQLiteDatabasePlugin::putBatch(const std::string& domain,
178	0	const DatabaseStringValueList& data) {
179		// Prepare the query, adding placeholders for all the rows we have in `data`
180	0	std::stringstream buffer;
181	0	buffer << "insert or replace into " + domain + " values ";
182
183	0	for (auto i = 1U; i <= data.size(); i++) {
184	0	auto index = i * 2;
185	0	buffer << "(?" << index - 1 << ", ?" << index << ")";
186
187	0	if (i + 1 > data.size()) {
188	0	buffer << ";";
189	0	} else {
190	0	buffer << ", ";
191	0	}
192	0	}
193
194	0	const auto& q = buffer.str();
195
196		// Bind each value from the rows we got
197	0	sqlite3_stmt* stmt = nullptr;
198	0	sqlite3_prepare_v2(db_, q.c_str(), -1, &stmt, nullptr);
199
200	0	{
201	0	int i = 1;
202
203	0	for (const auto& p : data) {
204	0	const auto& key = p.first;
205	0	const auto& value = p.second;
206
207	0	sqlite3_bind_text(stmt, i, key.c_str(), -1, SQLITE_STATIC);
208	0	sqlite3_bind_text(stmt, i + 1, value.c_str(), -1, SQLITE_STATIC);
209
210	0	i += 2;
211	0	}
212	0	}
213
214	0	auto rc = sqlite3_step(stmt);
215	0	if (rc != SQLITE_DONE) {
216	0	return Status(1);
217	0	}
218
219	0	sqlite3_finalize(stmt);
220	0	if (rand() % 10 == 0) {
221	0	tryVacuum(db_);
222	0	}
223
224	0	return Status::success();
225	0	}
226
227		Status SQLiteDatabasePlugin::remove(const std::string& domain,
228	0	const std::string& key) {
229	0	sqlite3_stmt* stmt = nullptr;
230	0	std::string q = "delete from " + domain + " where key IN (?1);";
231	0	sqlite3_prepare_v2(db_, q.c_str(), -1, &stmt, nullptr);
232
233	0	sqlite3_bind_text(stmt, 1, key.c_str(), -1, SQLITE_STATIC);
234	0	auto rc = sqlite3_step(stmt);
235	0	if (rc != SQLITE_DONE) {
236	0	return Status(1);
237	0	}
238
239	0	sqlite3_finalize(stmt);
240	0	if (rand() % 10 == 0) {
241	0	tryVacuum(db_);
242	0	}
243	0	return Status(0);
244	0	}
245
246		Status SQLiteDatabasePlugin::removeRange(const std::string& domain,
247		const std::string& low,
248	0	const std::string& high) {
249	0	if (low > high) {
250	0	return Status::failure("Invalid range: low > high");
251	0	}
252
253	0	sqlite3_stmt* stmt = nullptr;
254	0	std::string q = "delete from " + domain + " where key >= ?1 and key <= ?2;";
255	0	sqlite3_prepare_v2(db_, q.c_str(), -1, &stmt, nullptr);
256
257	0	sqlite3_bind_text(stmt, 1, low.c_str(), -1, SQLITE_STATIC);
258	0	sqlite3_bind_text(stmt, 2, high.c_str(), -1, SQLITE_STATIC);
259	0	auto rc = sqlite3_step(stmt);
260	0	if (rc != SQLITE_DONE) {
261	0	return Status(1);
262	0	}
263
264	0	sqlite3_finalize(stmt);
265	0	if (rand() % 10 == 0) {
266	0	tryVacuum(db_);
267	0	}
268	0	return Status(0);
269	0	}
270
271		Status SQLiteDatabasePlugin::scan(const std::string& domain,
272		std::vector<std::string>& results,
273		const std::string& prefix,
274	0	uint64_t max) const {
275	0	QueryData _results;
276	0	char* err = nullptr;
277
278	0	std::string q =
279	0	"select key from " + domain + " where key LIKE '" + prefix + "%'";
280	0	if (max > 0) {
281	0	q += " limit " + std::to_string(max);
282	0	}
283	0	sqlite3_exec(db_, q.c_str(), getData, &_results, &err);
284	0	if (err != nullptr) {
285	0	sqlite3_free(err);
286	0	}
287
288		// Only assign value if the query found a result.
289	0	for (auto& r : _results) {
290	0	results.push_back(std::move(r["key"]));
291	0	}
292
293	0	return Status::success();
294	0	}
295		} // namespace osquery