certstor_sql.cpp

Go to the documentation of this file.

/*
* Certificate Store in SQL
* (C) 2016 Kai Michaelis, Rohde & Schwarz Cybersecurity
* (C) 2018 Jack Lloyd
*
* Botan is released under the Simplified BSD License (see license.txt)
*/
 
#include <botan/certstor_sql.h>
 
#include <botan/asn1_obj.h>
#include <botan/asn1_time.h>
#include <botan/assert.h>
#include <botan/ber_dec.h>
#include <botan/data_src.h>
#include <botan/pk_keys.h>
#include <botan/pkcs8.h>
#include <botan/pkix_types.h>
#include <botan/internal/fmt.h>
 
namespace Botan {
 
Certificate_Store_In_SQL::Certificate_Store_In_SQL(std::shared_ptr<SQL_Database> db,
                                                   std::string_view passwd,
                                                   RandomNumberGenerator& rng,
                                                   std::string_view table_prefix) :
      m_rng(rng),
      m_database(std::move(db)),
      m_db_cert_table(fmt("{}certificates", table_prefix)),
      m_db_keys_table(fmt("{}keys", table_prefix)),
      m_db_crls_table(fmt("{}revoked", table_prefix)),
      m_password(passwd) {
   using DB = SQL_Database;
   const auto blob = DB::Column_Type::Blob;
   const auto integer = DB::Column_Type::Integer;
 
   BOTAN_ARG_CHECK(m_database->is_valid_table_name(m_db_cert_table), "Invalid table name");
   BOTAN_ARG_CHECK(m_database->is_valid_table_name(m_db_keys_table), "Invalid table name");
   BOTAN_ARG_CHECK(m_database->is_valid_table_name(m_db_crls_table), "Invalid table name");
 
   m_database->create_table(DB::Table_Schema(m_db_cert_table,
                                             {
                                                DB::Column("fingerprint", blob).primary_key(),
                                                DB::Column("subject_dn", blob),
                                                DB::Column("key_id", blob),
                                                DB::Column("priv_fingerprint", blob),
                                                DB::Column("certificate", blob).not_null(),
                                             })
                               .if_not_exists());
 
   m_database->create_table(DB::Table_Schema(m_db_keys_table,
                                             {
                                                DB::Column("fingerprint", blob).primary_key(),
                                                DB::Column("key", blob).not_null(),
                                             })
                               .if_not_exists());
 
   m_database->create_table(DB::Table_Schema(m_db_crls_table,
                                             {
                                                DB::Column("fingerprint", blob).primary_key(),
                                                DB::Column("reason", integer).not_null(),
                                                DB::Column("time", integer),
                                             })
                               .if_not_exists());
}
 
// Certificate handling
std::optional<X509_Certificate> Certificate_Store_In_SQL::find_cert(const X509_DN& subject_dn,
                                                                    const std::vector<uint8_t>& key_id) const {
   std::shared_ptr<SQL_Database::Statement> stmt;
 
   const std::vector<uint8_t> dn_encoding = subject_dn.BER_encode();
 
   if(key_id.empty()) {
      stmt = m_database->select("certificate", m_db_cert_table, "subject_dn = ?1", 1);
      stmt->bind(1, dn_encoding);
   } else {
      stmt =
         m_database->select("certificate", m_db_cert_table, "subject_dn = ?1 AND (key_id IS NULL OR key_id = ?2)", 1);
      stmt->bind(1, dn_encoding);
      stmt->bind(2, key_id);
   }
 
   while(stmt->step()) {
      return X509_Certificate(stmt->get_blob(0));
   }
 
   return std::optional<X509_Certificate>();
}
 
std::vector<X509_Certificate> Certificate_Store_In_SQL::find_all_certs(const X509_DN& subject_dn,
                                                                       const std::vector<uint8_t>& key_id) const {
   std::vector<X509_Certificate> certs;
 
   std::shared_ptr<SQL_Database::Statement> stmt;
 
   const std::vector<uint8_t> dn_encoding = subject_dn.BER_encode();
 
   if(key_id.empty()) {
      stmt = m_database->select("certificate", m_db_cert_table, "subject_dn = ?1");
      stmt->bind(1, dn_encoding);
   } else {
      stmt = m_database->select("certificate", m_db_cert_table, "subject_dn = ?1 AND (key_id IS NULL OR key_id = ?2)");
      stmt->bind(1, dn_encoding);
      stmt->bind(2, key_id);
   }
 
   while(stmt->step()) {
      certs.push_back(X509_Certificate(stmt->get_blob(0)));
   }
 
   return certs;
}
 
std::optional<X509_Certificate> Certificate_Store_In_SQL::find_cert_by_pubkey_sha1(
   const std::vector<uint8_t>& /*key_hash*/) const {
   throw Not_Implemented("Certificate_Store_In_SQL::find_cert_by_pubkey_sha1");
}
 
std::optional<X509_Certificate> Certificate_Store_In_SQL::find_cert_by_raw_subject_dn_sha256(
   const std::vector<uint8_t>& /*subject_hash*/) const {
   throw Not_Implemented("Certificate_Store_In_SQL::find_cert_by_raw_subject_dn_sha256");
}
 
std::optional<X509_Certificate> Certificate_Store_In_SQL::find_cert_by_issuer_dn_and_serial_number(
   const X509_DN& /*issuer_dn*/, std::span<const uint8_t> /*serial_number*/) const {
   throw Not_Implemented("Certificate_Store_In_SQL::find_cert_by_issuer_dn_and_serial_number");
}
 
std::optional<X509_CRL> Certificate_Store_In_SQL::find_crl_for(const X509_Certificate& subject) const {
   const auto all_crls = generate_crls();
 
   for(const auto& crl : all_crls) {
      if(!crl.get_revoked().empty() && crl.issuer_dn() == subject.issuer_dn()) {
         return crl;
      }
   }
 
   return std::optional<X509_CRL>();
}
 
std::vector<X509_DN> Certificate_Store_In_SQL::all_subjects() const {
   std::vector<X509_DN> ret;
   auto stmt = m_database->select("subject_dn", m_db_cert_table);
 
   while(stmt->step()) {
      BER_Decoder dec(stmt->get_blob(0), BER_Decoder::Limits::DER());
      X509_DN dn;
 
      dn.decode_from(dec);
 
      ret.push_back(dn);
   }
 
   return ret;
}
 
bool Certificate_Store_In_SQL::insert_cert(const X509_Certificate& cert) {
   const std::vector<uint8_t> dn_encoding = cert.subject_dn().BER_encode();
   const std::vector<uint8_t> cert_encoding = cert.BER_encode();
 
   auto stmt =
      m_database->upsert(m_db_cert_table, {"fingerprint", "subject_dn", "key_id", "priv_fingerprint", "certificate"});
 
   stmt->bind(1, cert.fingerprint("SHA-256"));
   stmt->bind(2, dn_encoding);
   stmt->bind(3, cert.subject_key_id());
   stmt->bind(4, std::vector<uint8_t>());
   stmt->bind(5, cert_encoding);
   stmt->spin();
 
   return true;
}
 
bool Certificate_Store_In_SQL::contains(const X509_Certificate& cert) const {
   auto stmt = m_database->select("1", m_db_cert_table, "fingerprint = ?1");
   stmt->bind(1, cert.fingerprint("SHA-256"));
   return stmt->step();
}
 
bool Certificate_Store_In_SQL::remove_cert(const X509_Certificate& cert) {
   if(!find_cert(cert.subject_dn(), cert.subject_key_id())) {
      return false;
   }
 
   auto stmt = m_database->new_statement(fmt("DELETE FROM {} WHERE fingerprint = ?1", m_db_cert_table));
 
   stmt->bind(1, cert.fingerprint("SHA-256"));
   stmt->spin();
 
   return true;
}
 
// Private key handling
std::shared_ptr<const Private_Key> Certificate_Store_In_SQL::find_key(const X509_Certificate& cert) const {
   auto stmt =
      m_database->new_statement(fmt("SELECT key FROM {} JOIN {} ON {}.fingerprint = {}.priv_fingerprint "
                                    "WHERE {}.fingerprint = ?1",
                                    m_db_keys_table,
                                    m_db_cert_table,
                                    m_db_keys_table,
                                    m_db_cert_table,
                                    m_db_cert_table));
   stmt->bind(1, cert.fingerprint("SHA-256"));
 
   std::shared_ptr<const Private_Key> key;
   while(stmt->step()) {
      DataSource_Memory src(stmt->get_blob(0));
      key = PKCS8::load_key(src, m_password);
   }
 
   return key;
}
 
std::vector<X509_Certificate> Certificate_Store_In_SQL::find_certs_for_key(const Private_Key& key) const {
   auto fprint = key.fingerprint_private("SHA-256");
   auto stmt = m_database->select("certificate", m_db_cert_table, "priv_fingerprint = ?1");
 
   stmt->bind(1, fprint);
 
   std::vector<X509_Certificate> certs;
   while(stmt->step()) {
      certs.push_back(X509_Certificate(stmt->get_blob(0)));
   }
 
   return certs;
}
 
bool Certificate_Store_In_SQL::insert_key(const X509_Certificate& cert, const Private_Key& key) {
   insert_cert(cert);
 
   if(find_key(cert)) {
      return false;
   }
 
   auto pkcs8 = PKCS8::BER_encode(key, m_rng, m_password);
   auto fprint = key.fingerprint_private("SHA-256");
 
   auto stmt1 = m_database->upsert(m_db_keys_table, {"fingerprint", "key"});
 
   stmt1->bind(1, fprint);
   stmt1->bind(2, pkcs8.data(), pkcs8.size());
   stmt1->spin();
 
   auto stmt2 =
      m_database->new_statement(fmt("UPDATE {} SET priv_fingerprint = ?1 WHERE fingerprint = ?2", m_db_cert_table));
 
   stmt2->bind(1, fprint);
   stmt2->bind(2, cert.fingerprint("SHA-256"));
   stmt2->spin();
 
   return true;
}
 
void Certificate_Store_In_SQL::remove_key(const Private_Key& key) {
   auto fprint = key.fingerprint_private("SHA-256");
   auto stmt = m_database->new_statement(fmt("DELETE FROM {} WHERE fingerprint = ?1", m_db_keys_table));
 
   stmt->bind(1, fprint);
   stmt->spin();
}
 
// Revocation
void Certificate_Store_In_SQL::revoke_cert(const X509_Certificate& cert, CRL_Code code, const X509_Time& time) {
   // TODO(Botan4) require that time be valid
   insert_cert(cert);
 
   auto stmt1 = m_database->upsert(m_db_crls_table, {"fingerprint", "reason", "time"});
 
   stmt1->bind(1, cert.fingerprint("SHA-256"));
   stmt1->bind(2, static_cast<uint32_t>(code));
 
   if(time.time_is_set()) {
      stmt1->bind(3, time.to_std_timepoint());
   } else {
      stmt1->bind_null(3);
   }
 
   stmt1->spin();
}
 
// Revocation
void Certificate_Store_In_SQL::revoke_cert(const X509_Certificate& cert, CRL_Code code) {
   insert_cert(cert);
 
   auto stmt1 = m_database->upsert(m_db_crls_table, {"fingerprint", "reason", "time"});
 
   stmt1->bind(1, cert.fingerprint("SHA-256"));
   stmt1->bind(2, static_cast<uint32_t>(code));
   stmt1->bind_null(3);
 
   stmt1->spin();
}
 
void Certificate_Store_In_SQL::affirm_cert(const X509_Certificate& cert) {
   auto stmt = m_database->new_statement(fmt("DELETE FROM {} WHERE fingerprint = ?1", m_db_crls_table));
 
   stmt->bind(1, cert.fingerprint("SHA-256"));
   stmt->spin();
}
 
std::vector<X509_CRL> Certificate_Store_In_SQL::generate_crls() const {
   auto stmt =
      m_database->new_statement(fmt("SELECT certificate,reason,time FROM {} JOIN {} ON {}.fingerprint = "
                                    "{}.fingerprint",
                                    m_db_crls_table,
                                    m_db_cert_table,
                                    m_db_cert_table,
                                    m_db_crls_table));
 
   std::map<X509_DN, std::vector<CRL_Entry>> crls;
   while(stmt->step()) {
      auto cert = X509_Certificate(stmt->get_blob(0));
      auto code = static_cast<CRL_Code>(stmt->get_size_t(1));
      auto ent = CRL_Entry(cert, code);
 
      auto i = crls.find(cert.issuer_dn());
      if(i == crls.end()) {
         crls.insert(std::make_pair(cert.issuer_dn(), std::vector<CRL_Entry>({ent})));
      } else {
         i->second.push_back(ent);
      }
   }
 
   const X509_Time t(std::chrono::system_clock::now());
 
   std::vector<X509_CRL> ret;
   ret.reserve(crls.size());
 
   for(const auto& p : crls) {
      ret.push_back(X509_CRL(p.first, t, t, p.second));
   }
 
   return ret;
}
 
}  // namespace Botan