diff options
Diffstat (limited to 'src/db/sql_query.cpp')
-rw-r--r-- | src/db/sql_query.cpp | 466 |
1 files changed, 240 insertions, 226 deletions
diff --git a/src/db/sql_query.cpp b/src/db/sql_query.cpp index bafbf56..2485b0b 100644 --- a/src/db/sql_query.cpp +++ b/src/db/sql_query.cpp @@ -26,278 +26,292 @@ namespace { - #define DB_ISSUER 101 - #define DB_DATE 102 - #define DB_APP_ID 103 - #define DB_PKG_ID 104 - #define DB_UID 105 - #define DB_CHECK_ID 106 - #define DB_CERTIFICATE 107 - #define DB_VERIFIED 108 - #define DB_CHAIN_ID 109 - #define DB_CERT_ORDER 110 - - // This changes define into question mark and a number in quotes - // e.g. _(DB_ISSUER) -> "?" "101" - #define _(db) __(db) - #define __(db) "?" #db - - // setup - const char *DB_CMD_SETUP = "VACUUM; PRAGMA foregin_keys=ON;"; - - const char *DB_CMD_GET_LAST_INSERTED_ROW = "SELECT last_insert_rowid();"; - - // apps - const char *DB_CMD_ADD_APP = - "INSERT INTO to_check(app_id, pkg_id, uid, verified) VALUES(" _(DB_APP_ID) ", " _(DB_PKG_ID) ", " _(DB_UID) ", " _(DB_VERIFIED) ");"; - - const char *DB_CMD_GET_CHECK_ID = - "SELECT check_id FROM to_check WHERE app_id=" _(DB_APP_ID) " AND pkg_id=" _(DB_PKG_ID) " AND uid=" _(DB_UID) ";"; - - const char *DB_CMD_ADD_CHAIN = - "INSERT INTO chains_to_check(check_id) VALUES(" _(DB_CHECK_ID) ");"; - - const char *DB_CMD_ADD_CERT = - "INSERT INTO certs_to_check(chain_id, certificate, cert_order) VALUES(" _(DB_CHAIN_ID) ", " _(DB_CERTIFICATE) ", " _(DB_CERT_ORDER) ");"; - - 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=" - _(DB_APP_ID) " AND to_check.pkg_id=" _(DB_PKG_ID) " AND to_check.uid=" _(DB_UID) ";"; - - const char *DB_CMD_REMOVE_APP = - "DELETE FROM to_check WHERE app_id=" _(DB_APP_ID) " AND pkg_id=" _(DB_PKG_ID) " AND uid=" _(DB_UID) ";"; - - const char *DB_CMD_GET_APPS = - "SELECT app_id, pkg_id, uid, verified FROM to_check"; - - const char *DB_CMD_GET_CERTS = - "SELECT certificate FROM certs_to_check WHERE chain_id=" _(DB_CHAIN_ID) " ORDER BY cert_order ASC;"; - - const char *DB_CMD_SET_APP_AS_VERIFIED = - "UPDATE to_check SET verified=" _(DB_VERIFIED) " WHERE check_id=" _(DB_CHECK_ID) ";"; +#define DB_ISSUER 101 +#define DB_DATE 102 +#define DB_APP_ID 103 +#define DB_PKG_ID 104 +#define DB_UID 105 +#define DB_CHECK_ID 106 +#define DB_CERTIFICATE 107 +#define DB_VERIFIED 108 +#define DB_CHAIN_ID 109 +#define DB_CERT_ORDER 110 + +// This changes define into question mark and a number in quotes +// e.g. _(DB_ISSUER) -> "?" "101" +#define _(db) __(db) +#define __(db) "?" #db + +// setup +const char *DB_CMD_SETUP = "VACUUM; PRAGMA foregin_keys=ON;"; + +const char *DB_CMD_GET_LAST_INSERTED_ROW = "SELECT last_insert_rowid();"; + +// apps +const char *DB_CMD_ADD_APP = + "INSERT INTO to_check(app_id, pkg_id, uid, verified) VALUES(" _(DB_APP_ID) ", " _(DB_PKG_ID) ", " _ + (DB_UID) ", " _(DB_VERIFIED) ");"; + +const char *DB_CMD_GET_CHECK_ID = + "SELECT check_id FROM to_check WHERE app_id=" _(DB_APP_ID) " AND pkg_id=" _ + (DB_PKG_ID) " AND uid=" _(DB_UID) ";"; + +const char *DB_CMD_ADD_CHAIN = + "INSERT INTO chains_to_check(check_id) VALUES(" _(DB_CHECK_ID) ");"; + +const char *DB_CMD_ADD_CERT = + "INSERT INTO certs_to_check(chain_id, certificate, cert_order) VALUES(" _(DB_CHAIN_ID) ", " _ + (DB_CERTIFICATE) ", " _(DB_CERT_ORDER) ");"; + +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=" + _(DB_APP_ID) " AND to_check.pkg_id=" _(DB_PKG_ID) " AND to_check.uid=" _(DB_UID) ";"; + +const char *DB_CMD_REMOVE_APP = + "DELETE FROM to_check WHERE app_id=" _(DB_APP_ID) " AND pkg_id=" _(DB_PKG_ID) " AND uid=" _ + (DB_UID) ";"; + +const char *DB_CMD_GET_APPS = + "SELECT app_id, pkg_id, uid, verified FROM to_check"; + +const char *DB_CMD_GET_CERTS = + "SELECT certificate FROM certs_to_check WHERE chain_id=" _(DB_CHAIN_ID) " ORDER BY cert_order ASC;"; + +const char *DB_CMD_SET_APP_AS_VERIFIED = + "UPDATE to_check SET verified=" _(DB_VERIFIED) " WHERE check_id=" _(DB_CHECK_ID) ";"; } namespace CCHECKER { namespace DB { -SqlQuery::SqlQuery(const std::string& path) +SqlQuery::SqlQuery(const std::string &path) { - m_connection = NULL; - m_inUserTransaction = false; + m_connection = NULL; + m_inUserTransaction = false; - if (!connect(path)) - throw std::runtime_error("Database error"); + if (!connect(path)) + throw std::runtime_error("Database error"); } -bool SqlQuery::connect(const std::string& path) +bool SqlQuery::connect(const std::string &path) { - if (m_connection != NULL) { - LogError("Already connected!"); - return true; - } - - Try { - m_connection = new SqlConnection(path, SqlConnection::Flag::None, SqlConnection::Flag::Option::CRW); - m_connection->ExecCommand(DB_CMD_SETUP); - return true; - } Catch(std::bad_alloc) { - LogError("Couldn't allocate SqlConnection"); - } Catch(SqlConnection::Exception::ConnectionBroken) { - LogError("Couldn't connect to database: " << path); - } Catch(SqlConnection::Exception::InvalidColumn) { - LogError("Couldn't set the key for database"); - } Catch(SqlConnection::Exception::SyntaxError) { - LogError("Couldn't initiate the database"); - } Catch(SqlConnection::Exception::InternalError) { - LogError("Couldn't create the database"); - } - return false; + if (m_connection != NULL) { + LogError("Already connected!"); + return true; + } + + Try { + m_connection = new SqlConnection(path, SqlConnection::Flag::None, SqlConnection::Flag::Option::CRW); + m_connection->ExecCommand(DB_CMD_SETUP); + return true; + } Catch(std::bad_alloc) { + LogError("Couldn't allocate SqlConnection"); + } + Catch(SqlConnection::Exception::ConnectionBroken) { + LogError("Couldn't connect to database: " << path); + } + Catch(SqlConnection::Exception::InvalidColumn) { + LogError("Couldn't set the key for database"); + } + Catch(SqlConnection::Exception::SyntaxError) { + LogError("Couldn't initiate the database"); + } + Catch(SqlConnection::Exception::InternalError) { + LogError("Couldn't create the database"); + } + return false; } SqlQuery::~SqlQuery() { - delete m_connection; + delete m_connection; } bool SqlQuery::check_if_app_exists(const app_t &app) { - int32_t check_id; - return get_check_id(app, check_id); + int32_t check_id; + return get_check_id(app, check_id); } bool SqlQuery::get_check_id(const app_t &app, int32_t &check_id) { - SqlConnection::DataCommandAutoPtr getCheckIDCommand = - m_connection->PrepareDataCommand(DB_CMD_GET_CHECK_ID); - getCheckIDCommand->BindString(DB_APP_ID, app.app_id.c_str()); - getCheckIDCommand->BindString(DB_PKG_ID, app.pkg_id.c_str()); - getCheckIDCommand->BindInt64(DB_UID, app.uid); - if (getCheckIDCommand->Step()) { - check_id = getCheckIDCommand->GetColumnInt32(0); - LogDebug("Found check id: " << check_id << ", for app: " << app.app_id); - return true; - } - LogDebug("No check_id for app: " << app.app_id << " in database"); - return false; + SqlConnection::DataCommandAutoPtr getCheckIDCommand = + m_connection->PrepareDataCommand(DB_CMD_GET_CHECK_ID); + getCheckIDCommand->BindString(DB_APP_ID, app.app_id.c_str()); + getCheckIDCommand->BindString(DB_PKG_ID, app.pkg_id.c_str()); + getCheckIDCommand->BindInt64(DB_UID, app.uid); + + if (getCheckIDCommand->Step()) { + check_id = getCheckIDCommand->GetColumnInt32(0); + LogDebug("Found check id: " << check_id << ", for app: " << app.app_id); + return true; + } + + LogDebug("No check_id for app: " << app.app_id << " in database"); + 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(DB_CHECK_ID, 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; + // Add new chain for an app + SqlConnection::DataCommandAutoPtr addChainCommand = + m_connection->PrepareDataCommand(DB_CMD_ADD_CHAIN); + addChainCommand->BindInt32(DB_CHECK_ID, 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 - if (check_if_app_exists(app)) { - LogDebug(app.str() << " already exists in database"); - return true; - } - - m_connection->BeginTransaction(); - //Add app to to_check table - SqlConnection::DataCommandAutoPtr addAppCommand = - m_connection->PrepareDataCommand(DB_CMD_ADD_APP); - addAppCommand->BindString(DB_APP_ID, app.app_id.c_str()); - addAppCommand->BindString(DB_PKG_ID, app.pkg_id.c_str()); - addAppCommand->BindInt64(DB_UID, app.uid); - addAppCommand->BindInt32(DB_VERIFIED, static_cast<int32_t>(app_t::verified_t::UNKNOWN)); // Set app as not-verified - addAppCommand->Step(); - LogDebug("App " << app.app_id << " added to to_check table, adding certificates."); - - // Get check_id - int32_t check_id; - 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 in right order (start with 1) - end entity go first - int32_t cert_order = 1; - for (const auto &iter_cert : iter) { - SqlConnection::DataCommandAutoPtr addCertCommand = - m_connection->PrepareDataCommand(DB_CMD_ADD_CERT); - addCertCommand->BindInt32(DB_CHAIN_ID, chain_id); - addCertCommand->BindString(DB_CERTIFICATE, iter_cert.c_str()); - addCertCommand->BindInt32(DB_CERT_ORDER, cert_order); - addCertCommand->Step(); - cert_order++; - 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; + //Check if app exists in DB + if (check_if_app_exists(app)) { + LogDebug(app.str() << " already exists in database"); + return true; + } + + m_connection->BeginTransaction(); + //Add app to to_check table + SqlConnection::DataCommandAutoPtr addAppCommand = + m_connection->PrepareDataCommand(DB_CMD_ADD_APP); + addAppCommand->BindString(DB_APP_ID, app.app_id.c_str()); + addAppCommand->BindString(DB_PKG_ID, app.pkg_id.c_str()); + addAppCommand->BindInt64(DB_UID, app.uid); + addAppCommand->BindInt32(DB_VERIFIED, + static_cast<int32_t>(app_t::verified_t::UNKNOWN)); // Set app as not-verified + addAppCommand->Step(); + LogDebug("App " << app.app_id << " added to to_check table, adding certificates."); + // Get check_id + int32_t check_id; + + 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 in right order (start with 1) - end entity go first + int32_t cert_order = 1; + + for (const auto &iter_cert : iter) { + SqlConnection::DataCommandAutoPtr addCertCommand = + m_connection->PrepareDataCommand(DB_CMD_ADD_CERT); + addCertCommand->BindInt32(DB_CHAIN_ID, chain_id); + addCertCommand->BindString(DB_CERTIFICATE, iter_cert.c_str()); + addCertCommand->BindInt32(DB_CERT_ORDER, cert_order); + addCertCommand->Step(); + cert_order++; + 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) { - LogDebug("Removing app: " << app.str()); - - //Remove app from to_check table - SqlConnection::DataCommandAutoPtr removeAppCommand = - m_connection->PrepareDataCommand(DB_CMD_REMOVE_APP); - removeAppCommand->BindString(DB_APP_ID, app.app_id.c_str()); - removeAppCommand->BindString(DB_PKG_ID, app.pkg_id.c_str()); - removeAppCommand->BindInt32(DB_UID, app.uid); - removeAppCommand->Step(); - LogDebug("Removed app: " << app.str()); - - // Removing certificates should be done automatically by DB because of - // ON DELETE CASCADE for check_id + LogDebug("Removing app: " << app.str()); + //Remove app from to_check table + SqlConnection::DataCommandAutoPtr removeAppCommand = + m_connection->PrepareDataCommand(DB_CMD_REMOVE_APP); + removeAppCommand->BindString(DB_APP_ID, app.app_id.c_str()); + removeAppCommand->BindString(DB_PKG_ID, app.pkg_id.c_str()); + removeAppCommand->BindInt32(DB_UID, app.uid); + removeAppCommand->Step(); + LogDebug("Removed app: " << app.str()); + // Removing certificates should be done automatically by DB because of + // ON DELETE CASCADE for check_id } void SqlQuery::mark_as_verified(const app_t &app, const app_t::verified_t &verified) { - int32_t check_id; - - if (get_check_id(app, check_id)) { - SqlConnection::DataCommandAutoPtr setVerifiedCommand = - m_connection->PrepareDataCommand(DB_CMD_SET_APP_AS_VERIFIED); - setVerifiedCommand->BindInt32(DB_CHECK_ID, check_id); - setVerifiedCommand->BindInt32(DB_VERIFIED, static_cast<int32_t>(verified)); - setVerifiedCommand->Step(); - LogDebug("App: " << app.str() << " marked as verified: " << static_cast<int32_t>(verified)); - } + int32_t check_id; + + if (get_check_id(app, check_id)) { + SqlConnection::DataCommandAutoPtr setVerifiedCommand = + m_connection->PrepareDataCommand(DB_CMD_SET_APP_AS_VERIFIED); + setVerifiedCommand->BindInt32(DB_CHECK_ID, check_id); + setVerifiedCommand->BindInt32(DB_VERIFIED, static_cast<int32_t>(verified)); + setVerifiedCommand->Step(); + LogDebug("App: " << app.str() << " marked as verified: " << static_cast<int32_t>(verified)); + } } void SqlQuery::get_apps(std::list<app_t> &apps_buffer) { - // This function will fill buffer with check_id, app_id, pkg_id, uid and verified fields - - // it leaves certificates' list empty. - SqlConnection::DataCommandAutoPtr getAppsCommand = - m_connection->PrepareDataCommand(DB_CMD_GET_APPS); - - while (getAppsCommand->Step()) { - app_t app; - 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); - } + // This function will fill buffer with check_id, app_id, pkg_id, uid and verified fields - + // it leaves certificates' list empty. + SqlConnection::DataCommandAutoPtr getAppsCommand = + m_connection->PrepareDataCommand(DB_CMD_GET_APPS); + + while (getAppsCommand->Step()) { + app_t app; + 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); + } } void SqlQuery::get_app_list(std::list<app_t> &apps_buffer) { - m_connection->BeginTransaction(); - get_apps(apps_buffer); - - // Get chain for every application - for (auto &iter_app : apps_buffer) { - SqlConnection::DataCommandAutoPtr getChainsCommand = - m_connection->PrepareDataCommand(DB_CMD_GET_CHAINS); - getChainsCommand->BindString(DB_APP_ID, iter_app.app_id.c_str()); - getChainsCommand->BindString(DB_PKG_ID, iter_app.pkg_id.c_str()); - getChainsCommand->BindInt32(DB_UID, iter_app.uid); - - // Get all certs from chain - certs will be sorted - end entity go first - 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(DB_CHAIN_ID, 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(); + m_connection->BeginTransaction(); + get_apps(apps_buffer); + + // Get chain for every application + for (auto &iter_app : apps_buffer) { + SqlConnection::DataCommandAutoPtr getChainsCommand = + m_connection->PrepareDataCommand(DB_CMD_GET_CHAINS); + getChainsCommand->BindString(DB_APP_ID, iter_app.app_id.c_str()); + getChainsCommand->BindString(DB_PKG_ID, iter_app.pkg_id.c_str()); + getChainsCommand->BindInt32(DB_UID, iter_app.uid); + + // Get all certs from chain - certs will be sorted - end entity go first + 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(DB_CHAIN_ID, 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 |