Botan  2.7.0
Crypto and TLS for C++11
certstor_sql.cpp
Go to the documentation of this file.
1 /*
2 * Certificate Store in SQL
3 * (C) 2016 Kai Michaelis, Rohde & Schwarz Cybersecurity
4 * (C) 2018 Jack Lloyd
5 *
6 * Botan is released under the Simplified BSD License (see license.txt)
7 */
8 
9 #include <botan/certstor_sql.h>
10 #include <botan/pk_keys.h>
11 #include <botan/ber_dec.h>
12 #include <botan/pkcs8.h>
13 #include <botan/data_src.h>
14 
15 namespace Botan {
16 
17 Certificate_Store_In_SQL::Certificate_Store_In_SQL(std::shared_ptr<SQL_Database> db,
18  const std::string& passwd,
20  const std::string& table_prefix) :
21  m_rng(rng),
22  m_database(db),
23  m_prefix(table_prefix),
24  m_password(passwd)
25  {
26  m_database->create_table("CREATE TABLE IF NOT EXISTS " +
27  m_prefix + "certificates ( \
28  fingerprint BLOB PRIMARY KEY, \
29  subject_dn BLOB, \
30  key_id BLOB, \
31  priv_fingerprint BLOB, \
32  certificate BLOB UNIQUE NOT NULL\
33  )");
34  m_database->create_table("CREATE TABLE IF NOT EXISTS " + m_prefix + "keys (\
35  fingerprint BLOB PRIMARY KEY, \
36  key BLOB UNIQUE NOT NULL \
37  )");
38  m_database->create_table("CREATE TABLE IF NOT EXISTS " + m_prefix + "revoked (\
39  fingerprint BLOB PRIMARY KEY, \
40  reason BLOB NOT NULL, \
41  time BLOB NOT NULL \
42  )");
43  }
44 
45 // Certificate handling
46 std::shared_ptr<const X509_Certificate>
47 Certificate_Store_In_SQL::find_cert(const X509_DN& subject_dn, const std::vector<uint8_t>& key_id) const
48  {
49  std::shared_ptr<SQL_Database::Statement> stmt;
50 
51  const std::vector<uint8_t> dn_encoding = subject_dn.BER_encode();
52 
53  if(key_id.empty())
54  {
55  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE subject_dn == ?1");
56  stmt->bind(1, dn_encoding);
57  }
58  else
59  {
60  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE\
61  subject_dn == ?1 AND (key_id == NULL OR key_id == ?2)");
62  stmt->bind(1, dn_encoding);
63  stmt->bind(2,key_id);
64  }
65 
66  std::shared_ptr<const X509_Certificate> cert;
67  while(stmt->step())
68  {
69  auto blob = stmt->get_blob(0);
70  cert = std::make_shared<X509_Certificate>(
71  std::vector<uint8_t>(blob.first,blob.first + blob.second));
72 
73  }
74 
75  return cert;
76  }
77 
78 std::vector<std::shared_ptr<const X509_Certificate>>
79 Certificate_Store_In_SQL::find_all_certs(const X509_DN& subject_dn, const std::vector<uint8_t>& key_id) const
80  {
81  std::vector<std::shared_ptr<const X509_Certificate>> certs;
82 
83  std::shared_ptr<SQL_Database::Statement> stmt;
84 
85  const std::vector<uint8_t> dn_encoding = subject_dn.BER_encode();
86 
87  if(key_id.empty())
88  {
89  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE subject_dn == ?1");
90  stmt->bind(1, dn_encoding);
91  }
92  else
93  {
94  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE\
95  subject_dn == ?1 AND (key_id == NULL OR key_id == ?2)");
96  stmt->bind(1, dn_encoding);
97  stmt->bind(2, key_id);
98  }
99 
100  std::shared_ptr<const X509_Certificate> cert;
101  while(stmt->step())
102  {
103  auto blob = stmt->get_blob(0);
104  certs.push_back(std::make_shared<X509_Certificate>(
105  std::vector<uint8_t>(blob.first,blob.first + blob.second)));
106  }
107 
108  return certs;
109  }
110 
111 std::shared_ptr<const X509_Certificate>
112 Certificate_Store_In_SQL::find_cert_by_pubkey_sha1(const std::vector<uint8_t>& /*key_hash*/) const
113  {
114  throw Not_Implemented("Certificate_Store_In_SQL::find_cert_by_pubkey_sha1");
115  }
116 
117 std::shared_ptr<const X509_Certificate>
118 Certificate_Store_In_SQL::find_cert_by_raw_subject_dn_sha256(const std::vector<uint8_t>& /*subject_hash*/) const
119  {
120  throw Not_Implemented("Certificate_Store_In_SQL::find_cert_by_raw_subject_dn_sha256");
121  }
122 
123 std::shared_ptr<const X509_CRL>
125  {
126  auto all_crls = generate_crls();
127 
128  for(auto crl: all_crls)
129  {
130  if(!crl.get_revoked().empty() && crl.issuer_dn() == subject.issuer_dn())
131  return std::shared_ptr<X509_CRL>(new X509_CRL(crl));
132  }
133 
134  return std::shared_ptr<X509_CRL>();
135  }
136 
137 std::vector<X509_DN> Certificate_Store_In_SQL::all_subjects() const
138  {
139  std::vector<X509_DN> ret;
140  auto stmt = m_database->new_statement("SELECT subject_dn FROM " + m_prefix + "certificates");
141 
142  while(stmt->step())
143  {
144  auto blob = stmt->get_blob(0);
145  BER_Decoder dec(blob.first,blob.second);
146  X509_DN dn;
147 
148  dn.decode_from(dec);
149 
150  ret.push_back(dn);
151  }
152 
153  return ret;
154  }
155 
157  {
158  const std::vector<uint8_t> dn_encoding = cert.subject_dn().BER_encode();
159  const std::vector<uint8_t> cert_encoding = cert.BER_encode();
160 
161  auto stmt = m_database->new_statement("INSERT OR REPLACE INTO " +
162  m_prefix + "certificates (\
163  fingerprint, \
164  subject_dn, \
165  key_id, \
166  priv_fingerprint, \
167  certificate \
168  ) VALUES ( ?1, ?2, ?3, ?4, ?5 )");
169 
170  stmt->bind(1,cert.fingerprint("SHA-256"));
171  stmt->bind(2,dn_encoding);
172  stmt->bind(3,cert.subject_key_id());
173  stmt->bind(4,std::vector<uint8_t>());
174  stmt->bind(5,cert_encoding);
175  stmt->spin();
176 
177  return true;
178  }
179 
180 
182  {
183  if(!find_cert(cert.subject_dn(),cert.subject_key_id()))
184  return false;
185 
186  auto stmt = m_database->new_statement("DELETE FROM " + m_prefix + "certificates WHERE fingerprint == ?1");
187 
188  stmt->bind(1,cert.fingerprint("SHA-256"));
189  stmt->spin();
190 
191  return true;
192  }
193 
194 // Private key handling
195 std::shared_ptr<const Private_Key> Certificate_Store_In_SQL::find_key(const X509_Certificate& cert) const
196  {
197  auto stmt = m_database->new_statement("SELECT key FROM " + m_prefix + "keys "
198  "JOIN " + m_prefix + "certificates ON " +
199  m_prefix + "keys.fingerprint == " + m_prefix + "certificates.priv_fingerprint "
200  "WHERE " + m_prefix + "certificates.fingerprint == ?1");
201  stmt->bind(1,cert.fingerprint("SHA-256"));
202 
203  std::shared_ptr<const Private_Key> key;
204  while(stmt->step())
205  {
206  auto blob = stmt->get_blob(0);
207  DataSource_Memory src(blob.first,blob.second);
208  key.reset(PKCS8::load_key(src, m_rng, m_password));
209  }
210 
211  return key;
212  }
213 
214 std::vector<std::shared_ptr<const X509_Certificate>>
216  {
217  auto fpr = key.fingerprint_private("SHA-256");
218  auto stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE priv_fingerprint == ?1");
219 
220  stmt->bind(1,fpr);
221 
222  std::vector<std::shared_ptr<const X509_Certificate>> certs;
223  while(stmt->step())
224  {
225  auto blob = stmt->get_blob(0);
226  certs.push_back(std::make_shared<X509_Certificate>(
227  std::vector<uint8_t>(blob.first,blob.first + blob.second)));
228  }
229 
230  return certs;
231  }
232 
234  insert_cert(cert);
235 
236  if(find_key(cert))
237  return false;
238 
239  auto pkcs8 = PKCS8::BER_encode(key, m_rng, m_password);
240  auto fpr = key.fingerprint_private("SHA-256");
241 
242  auto stmt1 = m_database->new_statement(
243  "INSERT OR REPLACE INTO " + m_prefix + "keys ( fingerprint, key ) VALUES ( ?1, ?2 )");
244 
245  stmt1->bind(1,fpr);
246  stmt1->bind(2,pkcs8.data(),pkcs8.size());
247  stmt1->spin();
248 
249  auto stmt2 = m_database->new_statement(
250  "UPDATE " + m_prefix + "certificates SET priv_fingerprint = ?1 WHERE fingerprint == ?2");
251 
252  stmt2->bind(1,fpr);
253  stmt2->bind(2,cert.fingerprint("SHA-256"));
254  stmt2->spin();
255 
256  return true;
257  }
258 
260  {
261  auto fpr = key.fingerprint_private("SHA-256");
262  auto stmt = m_database->new_statement("DELETE FROM " + m_prefix + "keys WHERE fingerprint == ?1");
263 
264  stmt->bind(1,fpr);
265  stmt->spin();
266  }
267 
268 // Revocation
270  {
271  insert_cert(cert);
272 
273  auto stmt1 = m_database->new_statement(
274  "INSERT OR REPLACE INTO " + m_prefix + "revoked ( fingerprint, reason, time ) VALUES ( ?1, ?2, ?3 )");
275 
276  stmt1->bind(1,cert.fingerprint("SHA-256"));
277  stmt1->bind(2,code);
278 
279  if(time.time_is_set())
280  {
281  stmt1->bind(3, time.BER_encode());
282  }
283  else
284  {
285  stmt1->bind(3,-1);
286  }
287 
288  stmt1->spin();
289  }
290 
292  {
293  auto stmt = m_database->new_statement("DELETE FROM " + m_prefix + "revoked WHERE fingerprint == ?1");
294 
295  stmt->bind(1,cert.fingerprint("SHA-256"));
296  stmt->spin();
297  }
298 
299 std::vector<X509_CRL> Certificate_Store_In_SQL::generate_crls() const
300  {
301  auto stmt = m_database->new_statement(
302  "SELECT certificate,reason,time FROM " + m_prefix + "revoked "
303  "JOIN " + m_prefix + "certificates ON " +
304  m_prefix + "certificates.fingerprint == " + m_prefix + "revoked.fingerprint");
305 
306  std::map<X509_DN,std::vector<CRL_Entry>> crls;
307  while(stmt->step())
308  {
309  auto blob = stmt->get_blob(0);
310  auto cert = X509_Certificate(
311  std::vector<uint8_t>(blob.first,blob.first + blob.second));
312  auto code = static_cast<CRL_Code>(stmt->get_size_t(1));
313  auto ent = CRL_Entry(cert,code);
314 
315  auto i = crls.find(cert.issuer_dn());
316  if(i == crls.end())
317  {
318  crls.insert(std::make_pair(cert.issuer_dn(),std::vector<CRL_Entry>({ent})));
319  }
320  else
321  {
322  i->second.push_back(ent);
323  }
324  }
325 
326  std::vector<X509_CRL> ret;
327  X509_Time t(std::chrono::system_clock::now());
328 
329  for(auto p: crls)
330  {
331  ret.push_back(X509_CRL(p.first,t,t,p.second));
332  }
333 
334  return ret;
335  }
336 
337 }
std::shared_ptr< const X509_Certificate > find_cert_by_pubkey_sha1(const std::vector< uint8_t > &key_hash) const override
std::vector< std::shared_ptr< const X509_Certificate > > find_all_certs(const X509_DN &subject_dn, const std::vector< uint8_t > &key_id) const override
bool insert_cert(const X509_Certificate &cert)
CRL_Code
Definition: crl_ent.h:22
std::vector< uint8_t > BER_encode() const
Definition: asn1_obj.cpp:16
std::shared_ptr< const Private_Key > find_key(const X509_Certificate &) const
Returns the private key for "cert" or an empty shared_ptr if none was found.
const std::vector< uint8_t > & subject_key_id() const
Definition: x509cert.cpp:407
bool time_is_set() const
Return if the time has been set somehow.
Definition: asn1_time.cpp:113
std::string fingerprint_private(const std::string &alg) const
Definition: pk_keys.cpp:88
void decode_from(class BER_Decoder &) override
Definition: x509_dn.cpp:233
const X509_DN & subject_dn() const
Definition: x509cert.cpp:428
Certificate_Store_In_SQL(const std::shared_ptr< SQL_Database > db, const std::string &passwd, RandomNumberGenerator &rng, const std::string &table_prefix="")
const X509_DN & issuer_dn() const
Definition: x509cert.cpp:423
std::string fingerprint(const std::string &hash_name="SHA-1") const
Definition: x509cert.cpp:705
void remove_key(const Private_Key &key)
Removes "key" from the store.
Definition: alg_id.cpp:13
Definition: crl_ent.h:42
secure_vector< uint8_t > BER_encode(const Private_Key &key)
Definition: pkcs8.cpp:139
std::vector< X509_CRL > generate_crls() const
std::shared_ptr< const X509_Certificate > find_cert(const X509_DN &subject_dn, const std::vector< uint8_t > &key_id) const override
bool insert_key(const X509_Certificate &cert, const Private_Key &key)
void revoke_cert(const X509_Certificate &, CRL_Code, const X509_Time &time=X509_Time())
Marks "cert" as revoked starting from "time".
bool remove_cert(const X509_Certificate &cert)
std::shared_ptr< const X509_CRL > find_crl_for(const X509_Certificate &issuer) const override
std::vector< std::shared_ptr< const X509_Certificate > > find_certs_for_key(const Private_Key &key) const
Returns all certificates for private key "key".
std::unique_ptr< Private_Key > load_key(DataSource &source, std::function< std::string()> get_pass)
Definition: pkcs8.cpp:348
void affirm_cert(const X509_Certificate &)
Reverses the revokation for "cert".
std::vector< X509_DN > all_subjects() const override
std::shared_ptr< const X509_Certificate > find_cert_by_raw_subject_dn_sha256(const std::vector< uint8_t > &subject_hash) const override