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