2 * Copyright (c) 2014-present, Facebook, Inc.
5 * This source code is licensed in accordance with the terms specified in
6 * the LICENSE file found in the root directory of this source tree.
11 #include <osquery/core.h>
12 #include <osquery/logger.h>
13 #include <osquery/registry.h>
14 #include <osquery/sql.h>
15 #include <osquery/tables.h>
17 #include <osquery/plugins/sql.h>
19 #include <osquery/utils/conversions/split.h>
20 #include <osquery/utils/info/tool_type.h>
24 CREATE_LAZY_REGISTRY(SQLPlugin, "sql");
26 SQL::SQL(const std::string& query, bool use_cache) {
27 TableColumns table_columns;
28 status_ = getQueryColumns(query, table_columns);
30 for (auto c : table_columns) {
31 columns_.push_back(std::get<0>(c));
33 status_ = osquery::query(query, results_, use_cache);
37 const QueryData& SQL::rows() const {
41 QueryData& SQL::rows() {
45 const ColumnNames& SQL::columns() const {
49 bool SQL::ok() const {
53 const Status& SQL::getStatus() const {
57 std::string SQL::getMessageString() const {
58 return status_.toString();
61 static inline void escapeNonPrintableBytes(std::string& data) {
64 char const hex_chars[16] = {
65 '0', '1', '2', '3', '4', '5', '6', '7',
66 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F',
70 bool needs_replacement = false;
71 for (size_t i = 0; i < data.length(); i++) {
72 if (((unsigned char)data[i]) < 0x20 || ((unsigned char)data[i]) >= 0x80) {
73 needs_replacement = true;
75 escaped += hex_chars[(((unsigned char)data[i])) >> 4];
76 escaped += hex_chars[((unsigned char)data[i] & 0x0F) >> 0];
82 // Only replace if any escapes were made.
83 if (needs_replacement) {
84 data = std::move(escaped);
88 void escapeNonPrintableBytesEx(std::string& data) {
89 return escapeNonPrintableBytes(data);
92 QueryData SQL::selectAllFrom(const std::string& table) {
93 PluginResponse response;
94 Registry::call("table", table, {{"action", "generate"}}, response);
98 QueryData SQL::selectAllFrom(const std::string& table,
99 const std::string& column,
100 ConstraintOperator op,
101 const std::string& expr) {
102 return selectFrom({}, table, column, op, expr);
105 QueryData SQL::selectFrom(const std::initializer_list<std::string>& columns,
106 const std::string& table,
107 const std::string& column,
108 ConstraintOperator op,
109 const std::string& expr) {
110 PluginRequest request = {{"action", "generate"}};
111 // Create a fake content, there will be no caching.
113 ctx.constraints[column].add(Constraint(op, expr));
114 if (columns.size() > 0) {
115 auto colsUsed = UsedColumns(columns);
116 colsUsed.insert(column);
117 ctx.colsUsed = colsUsed;
119 // We can't set colsUsedBitset here (because we don't know the column
120 // indexes). The plugin that handles the request will figure it out from the
122 TablePlugin::setRequestFromContext(ctx, request);
124 PluginResponse response;
125 Registry::call("table", table, request, response);
127 std::remove_if(response.begin(),
129 [&ctx, &column](const PluginRequest& row) -> bool {
130 return !ctx.constraints[column].matches(row.at(column));
136 Status SQLPlugin::call(const PluginRequest& request, PluginResponse& response) {
138 if (request.count("action") == 0) {
139 return Status(1, "SQL plugin must include a request action");
142 if (request.at("action") == "query") {
143 bool use_cache = (request.count("cache") && request.at("cache") == "1");
144 return this->query(request.at("query"), response, use_cache);
145 } else if (request.at("action") == "columns") {
146 TableColumns columns;
147 auto status = this->getQueryColumns(request.at("query"), columns);
148 // Convert columns to response
149 for (const auto& column : columns) {
151 {{"n", std::get<0>(column)},
152 {"t", columnTypeName(std::get<1>(column))},
153 {"o", INTEGER(static_cast<size_t>(std::get<2>(column)))}});
156 } else if (request.at("action") == "attach") {
157 // Attach a virtual table name using an optional included definition.
158 return this->attach(request.at("table"));
159 } else if (request.at("action") == "detach") {
160 this->detach(request.at("table"));
161 return Status::success();
162 } else if (request.at("action") == "tables") {
163 std::vector<std::string> tables;
164 auto status = this->getQueryTables(request.at("query"), tables);
166 for (const auto& table : tables) {
167 response.push_back({{"t", table}});
172 return Status(1, "Unknown action");
175 Status query(const std::string& q, QueryData& results, bool use_cache) {
176 return Registry::call(
179 {{"action", "query"}, {"cache", (use_cache) ? "1" : "0"}, {"query", q}},
183 Status getQueryColumns(const std::string& q, TableColumns& columns) {
184 PluginResponse response;
185 auto status = Registry::call(
186 "sql", "sql", {{"action", "columns"}, {"query", q}}, response);
188 // Convert response to columns
189 for (const auto& item : response) {
190 columns.push_back(make_tuple(
191 item.at("n"), columnTypeName(item.at("t")), ColumnOptions::DEFAULT));
196 Status mockGetQueryTables(std::string copy_q,
197 std::vector<std::string>& tables) {
198 std::transform(copy_q.begin(), copy_q.end(), copy_q.begin(), ::tolower);
199 auto offset_from = copy_q.find("from ");
200 if (offset_from == std::string::npos) {
204 auto simple_tables = osquery::split(copy_q.substr(offset_from + 5), ",");
205 for (const auto& table : simple_tables) {
206 tables.push_back(table);
211 Status getQueryTables(const std::string& q, std::vector<std::string>& tables) {
212 if (kToolType == ToolType::TEST) {
213 // We 'mock' this functionality for internal tests.
214 return mockGetQueryTables(q, tables);
217 PluginResponse response;
218 auto status = Registry::call(
219 "sql", "sql", {{"action", "tables"}, {"query", q}}, response);
221 for (const auto& table : response) {
222 tables.push_back(table.at("t"));