summaryrefslogtreecommitdiff
path: root/src/db/sql_query.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'src/db/sql_query.cpp')
-rw-r--r--src/db/sql_query.cpp124
1 files changed, 93 insertions, 31 deletions
diff --git a/src/db/sql_query.cpp b/src/db/sql_query.cpp
index 0968717..27eb52e 100644
--- a/src/db/sql_query.cpp
+++ b/src/db/sql_query.cpp
@@ -36,6 +36,12 @@ namespace {
// 107 - check_id
// 108 - certificate
// 109 - verified
+ // 110 - chain_id
+
+ // setup
+ const char *DB_CMD_SETUP = "VACUUM; PRAGMA foregin_keys=ON;";
+
+ const char *DB_CMD_GET_LAST_INSERTED_ROW = "SELECT last_insert_rowid();";
// urls
const char *DB_CMD_GET_URL =
@@ -54,17 +60,24 @@ namespace {
const char *DB_CMD_GET_CHECK_ID =
"SELECT check_id FROM to_check WHERE app_id=?104 AND pkg_id=?105 AND uid=?106;";
+ const char *DB_CMD_ADD_CHAIN =
+ "INSERT INTO chains_to_check(check_id) VALUES(?107);";
+
const char *DB_CMD_ADD_CERT =
- "INSERT INTO certs_to_check(check_id, certificate) VALUES(?107, ?108);";
+ "INSERT INTO certs_to_check(chain_id, certificate) VALUES(?110, ?108);";
+
+ const char *DB_CMD_GET_CHAINS =
+ "SELECT chain_id FROM chains_to_check INNER JOIN to_check ON chains_to_check.check_id=to_check.check_id \
+WHERE to_check.app_id=?104 AND to_check.pkg_id=?105 AND to_check.uid=?106;";
const char *DB_CMD_REMOVE_APP =
"DELETE FROM to_check WHERE app_id=?104 AND pkg_id=?105 AND uid=?106;";
const char *DB_CMD_GET_APPS =
- "SELECT * FROM to_check";
+ "SELECT app_id, pkg_id, uid, verified FROM to_check";
const char *DB_CMD_GET_CERTS =
- "SELECT certificate FROM certs_to_check WHERE check_id=?107;";
+ "SELECT certificate FROM certs_to_check WHERE chain_id=?110;";
const char *DB_CMD_SET_APP_AS_VERIFIED =
"UPDATE to_check SET verified=?109 WHERE check_id=?107";
@@ -91,7 +104,7 @@ bool SqlQuery::connect(const std::string& path)
Try {
m_connection = new SqlConnection(path, SqlConnection::Flag::None, SqlConnection::Flag::Option::CRW);
- m_connection->ExecCommand("VACUUM;");
+ m_connection->ExecCommand(DB_CMD_SETUP);
return true;
} Catch(std::bad_alloc) {
LogError("Couldn't allocate SqlConnection");
@@ -185,6 +198,25 @@ bool SqlQuery::get_check_id(const app_t &app, int32_t &check_id)
return false;
}
+bool SqlQuery::add_chain_id(const int32_t check_id, int32_t &chain_id)
+{
+ // Add new chain for an app
+ SqlConnection::DataCommandAutoPtr addChainCommand =
+ m_connection->PrepareDataCommand(DB_CMD_ADD_CHAIN);
+ addChainCommand->BindInt32(107, check_id);
+ addChainCommand->Step();
+
+ // get chain_id
+ SqlConnection::DataCommandAutoPtr getLastInserted =
+ m_connection->PrepareDataCommand(DB_CMD_GET_LAST_INSERTED_ROW);
+ if(getLastInserted->Step()) {
+ chain_id = getLastInserted->GetColumnInt32(0);
+ LogDebug("Found chain_id: " << chain_id << ", for check_id " << check_id);
+ return true;
+ }
+ return false;
+}
+
bool SqlQuery::add_app_to_check_list(const app_t &app)
{
//Check if app exists in DB
@@ -206,23 +238,35 @@ bool SqlQuery::add_app_to_check_list(const app_t &app)
// Get check_id
int32_t check_id;
- if (get_check_id(app, check_id)) {
- // If get check_id succeed we can add certificates to database
- for (const auto &iter : app.certificates) {
- SqlConnection::DataCommandAutoPtr addCertCommand =
- m_connection->PrepareDataCommand(DB_CMD_ADD_CERT);
- addCertCommand->BindInt32(107, check_id);
- addCertCommand->BindString(108, iter.c_str());
- addCertCommand->Step();
- LogDebug("Certificate for app " << app.app_id << "added");
- }
- m_connection->CommitTransaction();
- return true;
- } else { // If get check_id failed return false;
+ if (!get_check_id(app, check_id)) { // If get check_id failed return false;
LogDebug("Failed while addind app "<< app.app_id << " to to_check table.");
m_connection->RollbackTransaction();
return false;
}
+
+ // If get check_id succeed we can add chain to database
+ int32_t chain_id;
+ for (const auto &iter : app.signatures) {
+ // Add chain
+ if (add_chain_id(check_id, chain_id)) {
+ // add certificates from chain
+ for (const auto &iter_cert : iter) {
+ SqlConnection::DataCommandAutoPtr addCertCommand =
+ m_connection->PrepareDataCommand(DB_CMD_ADD_CERT);
+ addCertCommand->BindInt32(110, chain_id);
+ addCertCommand->BindString(108, iter_cert.c_str());
+ addCertCommand->Step();
+ LogDebug("Certificate for app " << app.app_id << "added");
+ }
+ } else {
+ LogDebug("Failed to add certificates chain");
+ m_connection->RollbackTransaction();
+ return false;
+ }
+
+ }
+ m_connection->CommitTransaction();
+ return true;
}
void SqlQuery::remove_app_from_check_list(const app_t &app)
@@ -265,12 +309,11 @@ void SqlQuery::get_apps(std::list<app_t> &apps_buffer)
while (getAppsCommand->Step()) {
app_t app;
- app.check_id = getAppsCommand->GetColumnInt32(0);
- app.app_id = getAppsCommand->GetColumnString(1);
- app.pkg_id = getAppsCommand->GetColumnString(2);
- app.uid = getAppsCommand->GetColumnInt64(3);
- app.verified = static_cast<app_t::verified_t>(getAppsCommand->GetColumnInt32(4));
- app.certificates = {};
+ app.app_id = getAppsCommand->GetColumnString(0);
+ app.pkg_id = getAppsCommand->GetColumnString(1);
+ app.uid = getAppsCommand->GetColumnInt64(2);
+ app.verified = static_cast<app_t::verified_t>(getAppsCommand->GetColumnInt32(3));
+ app.signatures = {};
LogDebug("App read from DB: app_id: " << app.str() << ", verified: " << static_cast<int32_t>(app.verified));
apps_buffer.push_back(app);
}
@@ -278,18 +321,37 @@ void SqlQuery::get_apps(std::list<app_t> &apps_buffer)
void SqlQuery::get_app_list(std::list<app_t> &apps_buffer)
{
+ m_connection->BeginTransaction();
get_apps(apps_buffer);
- // Get certificates for apps
- for (auto &iter : apps_buffer) {
- SqlConnection::DataCommandAutoPtr getCertsCommand =
- m_connection->PrepareDataCommand(DB_CMD_GET_CERTS);
- getCertsCommand->BindInt32(107, iter.check_id);
-
- while (getCertsCommand->Step()) {
- iter.certificates.push_back(getCertsCommand->GetColumnString(0));
+ // Get chain for every application
+ for (auto &iter_app : apps_buffer) {
+ SqlConnection::DataCommandAutoPtr getChainsCommand =
+ m_connection->PrepareDataCommand(DB_CMD_GET_CHAINS);
+ getChainsCommand->BindString(104, iter_app.app_id.c_str());
+ getChainsCommand->BindString(105, iter_app.pkg_id.c_str());
+ getChainsCommand->BindInt32(106, iter_app.uid);
+
+ // Get all certs from chain
+ while (getChainsCommand->Step()) {
+ chain_t chain;
+ int32_t chain_id;
+ chain_id = getChainsCommand->GetColumnInt32(0);
+ LogDebug("Found chain (" << chain_id << ") for " << iter_app.str());
+
+ SqlConnection::DataCommandAutoPtr getCertsCommand =
+ m_connection->PrepareDataCommand(DB_CMD_GET_CERTS);
+ getCertsCommand->BindInt32(110, chain_id);
+
+ // Add found certs to chain
+ while (getCertsCommand->Step()) {
+ chain.push_back(getCertsCommand->GetColumnString(0));
+ LogDebug("Found certificate (" << chain.size() << ") in chain no. " << chain_id);
+ }
+ iter_app.signatures.push_back(chain);
}
}
+ m_connection->CommitTransaction();
}
} // DB
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-13 08:04:56 +0000