Botan  2.6.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 *
5 * Botan is released under the Simplified BSD License (see license.txt)
6 */
7 
8 #include <botan/certstor_sql.h>
9 #include <botan/pk_keys.h>
10 #include <botan/ber_dec.h>
11 #include <botan/der_enc.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  DER_Encoder enc;
50  std::shared_ptr<SQL_Database::Statement> stmt;
51 
52  subject_dn.encode_into(enc);
53 
54  if(key_id.empty())
55  {
56  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE subject_dn == ?1");
57  stmt->bind(1,enc.get_contents_unlocked());
58  }
59  else
60  {
61  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE\
62  subject_dn == ?1 AND (key_id == NULL OR key_id == ?2)");
63  stmt->bind(1,enc.get_contents_unlocked());
64  stmt->bind(2,key_id);
65  }
66 
67  std::shared_ptr<const X509_Certificate> cert;
68  while(stmt->step())
69  {
70  auto blob = stmt->get_blob(0);
71  cert = std::make_shared<X509_Certificate>(
72  std::vector<uint8_t>(blob.first,blob.first + blob.second));
73 
74  }
75 
76  return cert;
77  }
78 
79 std::vector<std::shared_ptr<const X509_Certificate>>
80 Certificate_Store_In_SQL::find_all_certs(const X509_DN& subject_dn, const std::vector<uint8_t>& key_id) const
81  {
82  std::vector<std::shared_ptr<const X509_Certificate>> certs;
83 
84  DER_Encoder enc;
85  std::shared_ptr<SQL_Database::Statement> stmt;
86 
87  subject_dn.encode_into(enc);
88 
89  if(key_id.empty())
90  {
91  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE subject_dn == ?1");
92  stmt->bind(1,enc.get_contents_unlocked());
93  }
94  else
95  {
96  stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE\
97  subject_dn == ?1 AND (key_id == NULL OR key_id == ?2)");
98  stmt->bind(1,enc.get_contents_unlocked());
99  stmt->bind(2,key_id);
100  }
101 
102  std::shared_ptr<const X509_Certificate> cert;
103  while(stmt->step())
104  {
105  auto blob = stmt->get_blob(0);
106  certs.push_back(std::make_shared<X509_Certificate>(
107  std::vector<uint8_t>(blob.first,blob.first + blob.second)));
108  }
109 
110  return certs;
111  }
112 
113 std::shared_ptr<const X509_Certificate>
114 Certificate_Store_In_SQL::find_cert_by_pubkey_sha1(const std::vector<uint8_t>& /*key_hash*/) const
115  {
116  throw Not_Implemented("TODO!");
117  }
118 
119 std::shared_ptr<const X509_Certificate>
120 Certificate_Store_In_SQL::find_cert_by_raw_subject_dn_sha256(const std::vector<uint8_t>& /*subject_hash*/) const
121  {
122  throw Not_Implemented("TODO!");
123  }
124 
125 std::shared_ptr<const X509_CRL>
127  {
128  auto all_crls = generate_crls();
129 
130  for(auto crl: all_crls)
131  {
132  if(!crl.get_revoked().empty() && crl.issuer_dn() == subject.issuer_dn())
133  return std::shared_ptr<X509_CRL>(new X509_CRL(crl));
134  }
135 
136  return std::shared_ptr<X509_CRL>();
137  }
138 
139 std::vector<X509_DN> Certificate_Store_In_SQL::all_subjects() const
140  {
141  std::vector<X509_DN> ret;
142  auto stmt = m_database->new_statement("SELECT subject_dn FROM " + m_prefix + "certificates");
143 
144  while(stmt->step())
145  {
146  auto blob = stmt->get_blob(0);
147  BER_Decoder dec(blob.first,blob.second);
148  X509_DN dn;
149 
150  dn.decode_from(dec);
151 
152  ret.push_back(dn);
153  }
154 
155  return ret;
156  }
157 
159  {
160  DER_Encoder enc;
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  cert.subject_dn().encode_into(enc);
172  stmt->bind(2,enc.get_contents_unlocked());
173  stmt->bind(3,cert.subject_key_id());
174  stmt->bind(4,std::vector<uint8_t>());
175  enc = DER_Encoder();
176  cert.encode_into(enc);
177  stmt->bind(5,enc.get_contents_unlocked());
178  stmt->spin();
179 
180  return true;
181  }
182 
183 
185  {
186  if(!find_cert(cert.subject_dn(),cert.subject_key_id()))
187  return false;
188 
189  auto stmt = m_database->new_statement("DELETE FROM " + m_prefix + "certificates WHERE fingerprint == ?1");
190 
191  stmt->bind(1,cert.fingerprint("SHA-256"));
192  stmt->spin();
193 
194  return true;
195  }
196 
197 // Private key handling
198 std::shared_ptr<const Private_Key> Certificate_Store_In_SQL::find_key(const X509_Certificate& cert) const
199  {
200  auto stmt = m_database->new_statement("SELECT key FROM " + m_prefix + "keys "
201  "JOIN " + m_prefix + "certificates ON " +
202  m_prefix + "keys.fingerprint == " + m_prefix + "certificates.priv_fingerprint "
203  "WHERE " + m_prefix + "certificates.fingerprint == ?1");
204  stmt->bind(1,cert.fingerprint("SHA-256"));
205 
206  std::shared_ptr<const Private_Key> key;
207  while(stmt->step())
208  {
209  auto blob = stmt->get_blob(0);
210  DataSource_Memory src(blob.first,blob.second);
211  key.reset(PKCS8::load_key(src, m_rng, m_password));
212  }
213 
214  return key;
215  }
216 
217 std::vector<std::shared_ptr<const X509_Certificate>>
219  {
220  auto fpr = key.fingerprint_private("SHA-256");
221  auto stmt = m_database->new_statement("SELECT certificate FROM " + m_prefix + "certificates WHERE priv_fingerprint == ?1");
222 
223  stmt->bind(1,fpr);
224 
225  std::vector<std::shared_ptr<const X509_Certificate>> certs;
226  while(stmt->step())
227  {
228  auto blob = stmt->get_blob(0);
229  certs.push_back(std::make_shared<X509_Certificate>(
230  std::vector<uint8_t>(blob.first,blob.first + blob.second)));
231  }
232 
233  return certs;
234  }
235 
237  insert_cert(cert);
238 
239  if(find_key(cert))
240  return false;
241 
242  auto pkcs8 = PKCS8::BER_encode(key, m_rng, m_password);
243  auto fpr = key.fingerprint_private("SHA-256");
244 
245  auto stmt1 = m_database->new_statement(
246  "INSERT OR REPLACE INTO " + m_prefix + "keys ( fingerprint, key ) VALUES ( ?1, ?2 )");
247 
248  stmt1->bind(1,fpr);
249  stmt1->bind(2,pkcs8.data(),pkcs8.size());
250  stmt1->spin();
251 
252  auto stmt2 = m_database->new_statement(
253  "UPDATE " + m_prefix + "certificates SET priv_fingerprint = ?1 WHERE fingerprint == ?2");
254 
255  stmt2->bind(1,fpr);
256  stmt2->bind(2,cert.fingerprint("SHA-256"));
257  stmt2->spin();
258 
259  return true;
260  }
261 
263  {
264  auto fpr = key.fingerprint_private("SHA-256");
265  auto stmt = m_database->new_statement("DELETE FROM " + m_prefix + "keys WHERE fingerprint == ?1");
266 
267  stmt->bind(1,fpr);
268  stmt->spin();
269  }
270 
271 // Revocation
273  {
274  insert_cert(cert);
275 
276  auto stmt1 = m_database->new_statement(
277  "INSERT OR REPLACE INTO " + m_prefix + "revoked ( fingerprint, reason, time ) VALUES ( ?1, ?2, ?3 )");
278 
279  stmt1->bind(1,cert.fingerprint("SHA-256"));
280  stmt1->bind(2,code);
281 
282  if(time.time_is_set())
283  {
284  DER_Encoder der;
285  time.encode_into(der);
286  stmt1->bind(3,der.get_contents_unlocked());
287  }
288  else
289  {
290  stmt1->bind(3,-1);
291  }
292 
293  stmt1->spin();
294  }
295 
297  {
298  auto stmt = m_database->new_statement("DELETE FROM " + m_prefix + "revoked WHERE fingerprint == ?1");
299 
300  stmt->bind(1,cert.fingerprint("SHA-256"));
301  stmt->spin();
302  }
303 
304 std::vector<X509_CRL> Certificate_Store_In_SQL::generate_crls() const
305  {
306  auto stmt = m_database->new_statement(
307  "SELECT certificate,reason,time FROM " + m_prefix + "revoked "
308  "JOIN " + m_prefix + "certificates ON " +
309  m_prefix + "certificates.fingerprint == " + m_prefix + "revoked.fingerprint");
310 
311  std::map<X509_DN,std::vector<CRL_Entry>> crls;
312  while(stmt->step())
313  {
314  auto blob = stmt->get_blob(0);
315  auto cert = X509_Certificate(
316  std::vector<uint8_t>(blob.first,blob.first + blob.second));
317  auto code = static_cast<CRL_Code>(stmt->get_size_t(1));
318  auto ent = CRL_Entry(cert,code);
319 
320  auto i = crls.find(cert.issuer_dn());
321  if(i == crls.end())
322  {
323  crls.insert(std::make_pair(cert.issuer_dn(),std::vector<CRL_Entry>({ent})));
324  }
325  else
326  {
327  i->second.push_back(ent);
328  }
329  }
330 
331  std::vector<X509_CRL> ret;
332  X509_Time t(std::chrono::system_clock::now());
333 
334  for(auto p: crls)
335  {
336  ret.push_back(X509_CRL(p.first,t,t,p.second));
337  }
338 
339  return ret;
340  }
341 
342 }
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
std::vector< uint8_t > get_contents_unlocked()
Definition: der_enc.h:27
bool insert_cert(const X509_Certificate &cert)
CRL_Code
Definition: crl_ent.h:22
void encode_into(class DER_Encoder &to) const override
Definition: x509_obj.cpp:93
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.
void encode_into(class DER_Encoder &) const override
Definition: x509_dn.cpp:202
const std::vector< uint8_t > & subject_key_id() const
Definition: x509cert.cpp:408
bool time_is_set() const
Return if the time has been set somehow.
Definition: asn1_time.cpp:113
void encode_into(DER_Encoder &) const override
DER encode a X509_Time.
Definition: asn1_time.cpp:38
std::string fingerprint_private(const std::string &alg) const
Definition: pk_keys.cpp:86
void decode_from(class BER_Decoder &) override
Definition: x509_dn.cpp:233
const X509_DN & subject_dn() const
Definition: x509cert.cpp:429
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:424
std::string fingerprint(const std::string &hash_name="SHA-1") const
Definition: x509cert.cpp:706
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:341
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