/// <summary>
/// Find the specified certificate.
/// </summary>
/// <remarks>
/// Searches the database for the specified certificate, returning the matching
/// record with the desired fields populated.
/// </remarks>
/// <returns>The matching record if found; otherwise <c>null</c>.</returns>
/// <param name="certificate">The certificate.</param>
/// <param name="fields">The desired fields.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="certificate"/> is <c>null</c>.
/// </exception>
public X509CertificateRecord Find(X509Certificate certificate, X509CertificateRecordFields fields)
{
if (certificate == null)
{
throw new ArgumentNullException(nameof(certificate));
}
using (var command = GetSelectCommand(certificate, fields)) {
var reader = command.ExecuteReader();
try {
if (reader.Read())
{
var parser = new X509CertificateParser();
var buffer = new byte[4096];
return(LoadCertificateRecord(reader, parser, ref buffer));
}
} finally {
#if NETSTANDARD
reader.Dispose();
#else
reader.Close();
#endif
}
}
return(null);
}
/// <summary>
/// Gets the database command to update the specified record.
/// </summary>
/// <remarks>
/// Gets the database command to update the specified record.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="record">The certificate record.</param>
/// <param name="fields">The fields to update.</param>
protected override DbCommand GetUpdateCommand(X509CertificateRecord record, X509CertificateRecordFields fields)
{
var statement = new StringBuilder("UPDATE CERTIFICATES SET ");
var columns = GetColumnNames(fields & ~X509CertificateRecordFields.Id);
var command = connection.CreateCommand();
for (int i = 0; i < columns.Length; i++)
{
var value = GetValue(record, columns[i]);
var variable = "@" + columns[i];
if (i > 0)
{
statement.Append(", ");
}
statement.Append(columns[i]);
statement.Append(" = ");
statement.Append(variable);
command.AddParameterWithValue(variable, value);
}
statement.Append(" WHERE ID = @ID");
command.AddParameterWithValue("@ID", record.Id);
command.CommandText = statement.ToString();
command.CommandType = CommandType.Text;
return(command);
}
/// <summary>
/// Gets the column names for the specified fields.
/// </summary>
/// <remarks>
/// Gets the column names for the specified fields.
/// </remarks>
/// <returns>The column names.</returns>
/// <param name="fields">The fields.</param>
protected static string[] GetColumnNames(X509CertificateRecordFields fields)
{
var columns = new List <string> ();
if ((fields & X509CertificateRecordFields.Id) != 0)
{
columns.Add("ID");
}
if ((fields & X509CertificateRecordFields.Trusted) != 0)
{
columns.Add("TRUSTED");
}
if ((fields & X509CertificateRecordFields.Algorithms) != 0)
{
columns.Add("ALGORITHMS");
}
if ((fields & X509CertificateRecordFields.AlgorithmsUpdated) != 0)
{
columns.Add("ALGORITHMSUPDATED");
}
if ((fields & X509CertificateRecordFields.Certificate) != 0)
{
columns.Add("CERTIFICATE");
}
if ((fields & X509CertificateRecordFields.PrivateKey) != 0)
{
columns.Add("PRIVATEKEY");
}
return(columns.ToArray());
}
/// <summary>
/// Update the specified certificate record.
/// </summary>
/// <remarks>
/// Updates the specified fields of the record in the database.
/// </remarks>
/// <param name="record">The certificate record.</param>
/// <param name="fields">The fields to update.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="record"/> is <c>null</c>.
/// </exception>
public void Update(X509CertificateRecord record, X509CertificateRecordFields fields)
{
if (record == null)
{
throw new ArgumentNullException(nameof(record));
}
using (var command = GetUpdateCommand(record, fields))
command.ExecuteNonQuery();
}
public X509CertificateRecord Find(X509Certificate certificate, X509CertificateRecordFields fields)
{
var certs = this.GetCerts();
var cert = certs.IndexOf(certificate);
if (cert >= 0)
{
var record = new X509CertificateRecord(certificate);
return(record);
}
return(null);
}
/// <summary>
/// Gets the database command to select the record matching the specified certificate.
/// </summary>
/// <remarks>
/// Gets the database command to select the record matching the specified certificate.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="certificate">The certificate.</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(X509Certificate certificate, X509CertificateRecordFields fields)
{
var query = "SELECT " + string.Join(", ", GetColumnNames(fields)) + " FROM CERTIFICATES ";
var fingerprint = certificate.GetFingerprint().ToLowerInvariant();
var serialNumber = certificate.SerialNumber.ToString();
var issuerName = certificate.IssuerDN.ToString();
var command = connection.CreateCommand();
command.CommandText = query + "WHERE ISSUERNAME = @ISSUERNAME AND SERIALNUMBER = @SERIALNUMBER AND FINGERPRINT = @FINGERPRINT LIMIT 1";
command.AddParameterWithValue("@ISSUERNAME", issuerName);
command.AddParameterWithValue("@SERIALNUMBER", serialNumber);
command.AddParameterWithValue("@FINGERPRINT", fingerprint);
command.CommandType = CommandType.Text;
return(command);
}
static StringBuilder CreateSelectQuery(X509CertificateRecordFields fields)
{
var query = new StringBuilder("SELECT ");
var columns = GetColumnNames(fields);
for (int i = 0; i < columns.Length; i++)
{
if (i > 0)
{
query = query.Append(", ");
}
query = query.Append(columns[i]);
}
return(query.Append(" FROM CERTIFICATES"));
}
/// <summary>
/// Gets the database command to select the record matching the specified certificate.
/// </summary>
/// <remarks>
/// Gets the database command to select the record matching the specified certificate.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="certificate">The certificate.</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(X509Certificate certificate, X509CertificateRecordFields fields)
{
var fingerprint = certificate.GetFingerprint().ToLowerInvariant();
var serialNumber = certificate.SerialNumber.ToString();
var issuerName = certificate.IssuerDN.ToString();
var command = connection.CreateCommand();
var query = CreateSelectQuery(fields);
// FIXME: Is this really the best way to query for an exact match of a certificate?
query = query.Append(" WHERE ISSUERNAME = @ISSUERNAME AND SERIALNUMBER = @SERIALNUMBER AND FINGERPRINT = @FINGERPRINT LIMIT 1");
command.AddParameterWithValue("@ISSUERNAME", issuerName);
command.AddParameterWithValue("@SERIALNUMBER", serialNumber);
command.AddParameterWithValue("@FINGERPRINT", fingerprint);
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
return(command);
}
public IEnumerable <X509CertificateRecord> Find(
MailboxAddress mailbox,
DateTime now,
bool requirePrivateKey,
X509CertificateRecordFields fields)
{
var certs = this.GetCerts();
foreach (var certificate in certs)
{
if (certificate.GetCommonName() == mailbox.Address)
{
var record = new X509CertificateRecord(certificate);
return(new SingletonList <X509CertificateRecord>(record));
}
}
return(new List <X509CertificateRecord>());
}
/// <summary>
/// Find the specified certificate.
/// </summary>
/// <remarks>
/// Searches the database for the specified certificate, returning the matching
/// record with the desired fields populated.
/// </remarks>
/// <returns>The matching record if found; otherwise <c>null</c>.</returns>
/// <param name="certificate">The certificate.</param>
/// <param name="fields">The desired fields.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="certificate"/> is <c>null</c>.
/// </exception>
public X509CertificateRecord Find(X509Certificate certificate, X509CertificateRecordFields fields)
{
if (certificate == null)
{
throw new ArgumentNullException(nameof(certificate));
}
using (var command = GetSelectCommand(certificate, fields)) {
using (var reader = command.ExecuteReader()) {
if (reader.Read())
{
var parser = new X509CertificateParser();
var buffer = new byte[4096];
return(LoadCertificateRecord(reader, parser, ref buffer));
}
}
}
return(null);
}
/// <summary>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="selector">Selector.</param>
/// <param name="trustedOnly">If set to <c>true</c> trusted only.</param>
/// <param name="requirePrivateKey">true</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(IX509Selector selector, bool trustedOnly, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var query = "SELECT " + string.Join(", ", GetColumnNames(fields)) + " FROM CERTIFICATES";
var match = selector as X509CertStoreSelector;
var command = connection.CreateCommand();
var constraints = " WHERE ";
if (trustedOnly)
{
command.AddParameterWithValue("@TRUSTED", true);
constraints += "TRUSTED = @TRUSTED";
}
if (match != null)
{
if (match.BasicConstraints != -1)
{
if (command.Parameters.Count > 0)
{
constraints += " AND ";
}
command.AddParameterWithValue("@BASICCONSTRAINTS", match.BasicConstraints);
constraints += "BASICCONSTRAINTS = @BASICCONSTRAINTS";
}
if (match.Issuer != null)
{
if (command.Parameters.Count > 0)
{
constraints += " AND ";
}
command.AddParameterWithValue("@ISSUERNAME", match.Issuer.ToString());
constraints += "ISSUERNAME = @ISSUERNAME";
}
if (match.SerialNumber != null)
{
if (command.Parameters.Count > 0)
{
constraints += " AND ";
}
command.AddParameterWithValue("@SERIALNUMBER", match.SerialNumber.ToString());
constraints += "SERIALNUMBER = @SERIALNUMBER";
}
if (match.KeyUsage != null)
{
var flags = BouncyCastleCertificateExtensions.GetKeyUsageFlags(match.KeyUsage);
if (flags != X509KeyUsageFlags.None)
{
if (command.Parameters.Count > 0)
{
constraints += " AND ";
}
command.AddParameterWithValue("@FLAGS", (int)flags);
constraints += "(KEYUSAGE == 0 OR (KEYUSAGE & @FLAGS) == @FLAGS)";
}
}
}
if (requirePrivateKey)
{
if (command.Parameters.Count > 0)
{
constraints += " AND ";
}
constraints += "PRIVATEKEY IS NOT NULL";
}
else if (command.Parameters.Count == 0)
{
constraints = string.Empty;
}
command.CommandText = query + constraints;
command.CommandType = CommandType.Text;
return(command);
}
/// <summary>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="mailbox">The mailbox.</param>
/// <param name="now">The date and time for which the certificate should be valid.</param>
/// <param name="requirePrivateKey">true</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand (MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var query = "SELECT " + string.Join (", ", GetColumnNames (fields)) + " FROM CERTIFICATES";
var secure = mailbox as SecureMailboxAddress;
var command = connection.CreateCommand ();
var constraints = " WHERE ";
constraints += "BASICCONSTRAINTS = @BASICCONSTRAINTS ";
command.AddParameterWithValue ("@BASICCONSTRAINTS", -1);
if (secure != null && !string.IsNullOrEmpty (secure.Fingerprint)) {
if (secure.Fingerprint.Length < 40) {
constraints += "AND FINGERPRINT LIKE @FINGERPRINT ";
command.AddParameterWithValue ("@FINGERPRINT", secure.Fingerprint.ToLowerInvariant () + "%");
} else {
constraints += "AND FINGERPRINT = @FINGERPRINT ";
command.AddParameterWithValue ("@FINGERPRINT", secure.Fingerprint.ToLowerInvariant ());
}
} else {
constraints += "AND SUBJECTEMAIL = @SUBJECTEMAIL ";
command.AddParameterWithValue ("@SUBJECTEMAIL", mailbox.Address.ToLowerInvariant ());
}
constraints += "AND NOTBEFORE < @NOW AND NOTAFTER > @NOW ";
command.AddParameterWithValue ("@NOW", now);
if (requirePrivateKey)
constraints += "AND PRIVATEKEY IS NOT NULL";
command.CommandText = query + constraints;
command.CommandType = CommandType.Text;
return command;
}
/// <summary>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="mailbox">The mailbox.</param>
/// <param name="now">The date and time for which the certificate should be valid.</param>
/// <param name="requirePrivateKey">true</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var query = "SELECT " + string.Join(", ", GetColumnNames(fields)) + " FROM CERTIFICATES";
var secure = mailbox as SecureMailboxAddress;
var command = connection.CreateCommand();
var constraints = " WHERE ";
constraints += "BASICCONSTRAINTS = @BASICCONSTRAINTS ";
command.AddParameterWithValue("@BASICCONSTRAINTS", -1);
if (secure != null && !string.IsNullOrEmpty(secure.Fingerprint))
{
if (secure.Fingerprint.Length < 40)
{
constraints += "AND FINGERPRINT LIKE @FINGERPRINT ";
command.AddParameterWithValue("@FINGERPRINT", secure.Fingerprint.ToLowerInvariant() + "%");
}
else
{
constraints += "AND FINGERPRINT = @FINGERPRINT ";
command.AddParameterWithValue("@FINGERPRINT", secure.Fingerprint.ToLowerInvariant());
}
}
else
{
constraints += "AND SUBJECTEMAIL = @SUBJECTEMAIL ";
command.AddParameterWithValue("@SUBJECTEMAIL", mailbox.Address.ToLowerInvariant());
}
constraints += "AND NOTBEFORE < @NOW AND NOTAFTER > @NOW ";
command.AddParameterWithValue("@NOW", now.ToUniversalTime());
if (requirePrivateKey)
{
constraints += "AND PRIVATEKEY IS NOT NULL";
}
command.CommandText = query + constraints;
command.CommandType = CommandType.Text;
return(command);
}
/// <summary>
/// Finds the certificate records matching the specified selector.
/// </summary>
/// <remarks>
/// Searches the database for certificate records matching the selector, returning all
/// of the matching records populated with the desired fields.
/// </remarks>
/// <returns>The matching certificate records populated with the desired fields.</returns>
/// <param name="selector">The match selector or <c>null</c> to match all certificates.</param>
/// <param name="trustedOnly"><c>true</c> if only trusted certificates should be returned.</param>
/// <param name="fields">The desired fields.</param>
public IEnumerable<X509CertificateRecord> Find (IX509Selector selector, bool trustedOnly, X509CertificateRecordFields fields)
{
using (var command = GetSelectCommand (selector, trustedOnly, false, fields | X509CertificateRecordFields.Certificate)) {
var reader = command.ExecuteReader ();
try {
var parser = new X509CertificateParser ();
var buffer = new byte[4096];
while (reader.Read ()) {
var record = LoadCertificateRecord (reader, parser, ref buffer);
if (selector == null || selector.Match (record.Certificate))
yield return record;
}
} finally {
reader.Close ();
}
}
yield break;
}
/// <summary>
/// Update the specified certificate record.
/// </summary>
/// <remarks>
/// Updates the specified fields of the record in the database.
/// </remarks>
/// <param name="record">The certificate record.</param>
/// <param name="fields">The fields to update.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="record"/> is <c>null</c>.
/// </exception>
public void Update (X509CertificateRecord record, X509CertificateRecordFields fields)
{
if (record == null)
throw new ArgumentNullException ("record");
using (var command = GetUpdateCommand (record, fields)) {
command.ExecuteNonQuery ();
}
}
/// <summary>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="selector">Selector.</param>
/// <param name="trustedOnly">If set to <c>true</c> trusted only.</param>
/// <param name="requirePrivateKey">true</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand (IX509Selector selector, bool trustedOnly, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var query = "SELECT " + string.Join (", ", GetColumnNames (fields)) + " FROM CERTIFICATES";
var match = selector as X509CertStoreSelector;
var command = connection.CreateCommand ();
var constraints = " WHERE ";
if (trustedOnly) {
command.AddParameterWithValue ("@TRUSTED", true);
constraints += "TRUSTED = @TRUSTED";
}
if (match != null) {
if (match.BasicConstraints != -1) {
if (command.Parameters.Count > 0)
constraints += " AND ";
command.AddParameterWithValue ("@BASICCONSTRAINTS", match.BasicConstraints);
constraints += "BASICCONSTRAINTS = @BASICCONSTRAINTS";
}
if (match.Issuer != null) {
if (command.Parameters.Count > 0)
constraints += " AND ";
command.AddParameterWithValue ("@ISSUERNAME", match.Issuer.ToString ());
constraints += "ISSUERNAME = @ISSUERNAME";
}
if (match.SerialNumber != null) {
if (command.Parameters.Count > 0)
constraints += " AND ";
command.AddParameterWithValue ("@SERIALNUMBER", match.SerialNumber.ToString ());
constraints += "SERIALNUMBER = @SERIALNUMBER";
}
if (match.KeyUsage != null) {
var flags = X509CertificateExtensions.GetKeyUsageFlags (match.KeyUsage);
if (flags != X509KeyUsageFlags.None) {
if (command.Parameters.Count > 0)
constraints += " AND ";
command.AddParameterWithValue ("@FLAGS", (int) flags);
constraints += "(KEYUSAGE & @FLAGS) != 0";
}
}
}
if (requirePrivateKey) {
if (command.Parameters.Count > 0)
constraints += " AND ";
constraints += "PRIVATEKEY IS NOT NULL";
} else if (command.Parameters.Count == 0) {
constraints = string.Empty;
}
command.CommandText = query + constraints;
command.CommandType = CommandType.Text;
return command;
}
/// <summary>
/// Finds the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Searches the database for certificates matching the specified mailbox that are valid
/// for the date and time specified, returning all matching records populated with the
/// desired fields.
/// </remarks>
/// <returns>The matching certificate records populated with the desired fields.</returns>
/// <param name="mailbox">The mailbox.</param>
/// <param name="now">The date and time.</param>
/// <param name="requirePrivateKey"><c>true</c> if a private key is required.</param>
/// <param name="fields">The desired fields.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="mailbox"/> is <c>null</c>.
/// </exception>
public IEnumerable<X509CertificateRecord> Find (MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields)
{
if (mailbox == null)
throw new ArgumentNullException ("mailbox");
using (var command = GetSelectCommand (mailbox, now, requirePrivateKey, fields)) {
var reader = command.ExecuteReader ();
try {
var parser = new X509CertificateParser ();
var buffer = new byte[4096];
while (reader.Read ()) {
yield return LoadCertificateRecord (reader, parser, ref buffer);
}
} finally {
reader.Close ();
}
}
yield break;
}
/// <summary>
/// Finds the certificate records matching the specified selector.
/// </summary>
/// <remarks>
/// Searches the database for certificate records matching the selector, returning all
/// of the matching records populated with the desired fields.
/// </remarks>
/// <returns>The matching certificate records populated with the desired fields.</returns>
/// <param name="selector">The match selector or <c>null</c> to match all certificates.</param>
/// <param name="trustedOnly"><c>true</c> if only trusted certificates should be returned.</param>
/// <param name="fields">The desired fields.</param>
public IEnumerable <X509CertificateRecord> Find(IX509Selector selector, bool trustedOnly, X509CertificateRecordFields fields)
{
using (var command = GetSelectCommand(selector, trustedOnly, false, fields | X509CertificateRecordFields.Certificate)) {
var reader = command.ExecuteReader();
try {
var parser = new X509CertificateParser();
var buffer = new byte[4096];
while (reader.Read())
{
var record = LoadCertificateRecord(reader, parser, ref buffer);
if (selector == null || selector.Match(record.Certificate))
{
yield return(record);
}
}
} finally {
#if NETSTANDARD
reader.Dispose();
#else
reader.Close();
#endif
}
}
yield break;
}
public void Update(X509CertificateRecord record, X509CertificateRecordFields fields)
{
// throw new NotImplementedException();
}
/// <summary> /// Gets the database command to select certificate records matching the specified selector. /// </summary> /// <remarks> /// Gets the database command to select certificate records matching the specified selector. /// </remarks> /// <returns>The database command.</returns> /// <param name="selector">Selector.</param> /// <param name="trustedOnly"><c>true</c> if only trusted certificates should be matched.</param> /// <param name="requirePrivateKey"><c>true</c> if the certificate must have a private key.</param> /// <param name="fields">The fields to return.</param> protected abstract DbCommand GetSelectCommand(IX509Selector selector, bool trustedOnly, bool requirePrivateKey, X509CertificateRecordFields fields);
/// <summary>
/// Get the database command to select the requested certificate records.
/// </summary>
/// <remarks>
/// Gets the database command to select the requested certificate records.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="selector">The certificate selector.</param>
/// <param name="trustedOnly"><c>true</c> if only trusted certificates should be matched; otherwise, <c>false</c>.</param>
/// <param name="requirePrivateKey"><c>true</c> if the certificate must have a private key; otherwise, <c>false</c>.</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(IX509Selector selector, bool trustedOnly, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var match = selector as X509CertStoreSelector;
var command = connection.CreateCommand();
var query = CreateSelectQuery(fields);
int baseQueryLength = query.Length;
query = query.Append(" WHERE ");
// FIXME: We could create an X509CertificateDatabaseSelector subclass of X509CertStoreSelector that
// adds properties like bool Trusted, bool Anchor, and bool HasPrivateKey ? Then we could drop the
// bool method arguments...
if (trustedOnly)
{
command.AddParameterWithValue("@TRUSTED", true);
query = query.Append("TRUSTED = @TRUSTED");
}
// FIXME: This query is used to get the TrustedAnchors in DefaultSecureMimeContext. If the database
// had an ANCHOR (or ROOT?) column, that would likely improve performance a bit because we would
// protentially reduce the number of certificates we load.
if (match != null)
{
if (match.BasicConstraints >= 0 || match.BasicConstraints == -2)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
if (match.BasicConstraints == -2)
{
command.AddParameterWithValue("@BASICCONSTRAINTS", -1);
query = query.Append("BASICCONSTRAINTS = @BASICCONSTRAINTS");
}
else
{
command.AddParameterWithValue("@BASICCONSTRAINTS", match.BasicConstraints);
query = query.Append("BASICCONSTRAINTS >= @BASICCONSTRAINTS");
}
}
if (match.CertificateValid != null)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@DATETIME", match.CertificateValid.Value.ToUniversalTime());
query = query.Append("NOTBEFORE < @DATETIME AND NOTAFTER > @DATETIME");
}
if (match.Issuer != null || match.Certificate != null)
{
// Note: GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
// queries for ISSUERNAME, SERIALNUMBER, and FINGERPRINT so we'll do the same.
var issuer = match.Issuer ?? match.Certificate.IssuerDN;
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@ISSUERNAME", issuer.ToString());
query = query.Append("ISSUERNAME = @ISSUERNAME");
}
if (match.SerialNumber != null || match.Certificate != null)
{
// Note: GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
// queries for ISSUERNAME, SERIALNUMBER, and FINGERPRINT so we'll do the same.
var serialNumber = match.SerialNumber ?? match.Certificate.SerialNumber;
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@SERIALNUMBER", serialNumber.ToString());
query = query.Append("SERIALNUMBER = @SERIALNUMBER");
}
if (match.Certificate != null)
{
// Note: GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
// queries for ISSUERNAME, SERIALNUMBER, and FINGERPRINT so we'll do the same.
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@FINGERPRINT", match.Certificate.GetFingerprint());
query = query.Append("FINGERPRINT = @FINGERPRINT");
}
// FIXME: maybe the database should have a SUBJECTNAME column as well? Then we could match against
// selector.SubjectDN. Plus it might be nice to have when querying the database manually to see
// what's there.
if (match.KeyUsage != null)
{
var flags = BouncyCastleCertificateExtensions.GetKeyUsageFlags(match.KeyUsage);
if (flags != X509KeyUsageFlags.None)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@FLAGS", (int)flags);
query = query.Append("(KEYUSAGE = 0 OR (KEYUSAGE & @FLAGS) = @FLAGS)");
}
}
}
if (requirePrivateKey)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
query = query.Append("PRIVATEKEY IS NOT NULL");
}
else if (command.Parameters.Count == 0)
{
query.Length = baseQueryLength;
}
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
return(command);
}
/// <summary> /// Gets the database command to select the record matching the specified certificate. /// </summary> /// <remarks> /// Gets the database command to select the record matching the specified certificate. /// </remarks> /// <returns>The database command.</returns> /// <param name="certificate">The certificate.</param> /// <param name="fields">The fields to return.</param> protected abstract DbCommand GetSelectCommand(X509Certificate certificate, X509CertificateRecordFields fields);
/// <summary>
/// Get the database command to select the requested certificate records.
/// </summary>
/// <remarks>
/// Gets the database command to select the requested certificate records.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="selector">The certificate selector.</param>
/// <param name="trustedAnchorsOnly"><c>true</c> if only trusted anchor certificates should be matched; otherwise, <c>false</c>.</param>
/// <param name="requirePrivateKey"><c>true</c> if the certificate must have a private key; otherwise, <c>false</c>.</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(IX509Selector selector, bool trustedAnchorsOnly, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var match = selector as X509CertStoreSelector;
var command = connection.CreateCommand();
var query = CreateSelectQuery(fields);
int baseQueryLength = query.Length;
query = query.Append(" WHERE ");
// FIXME: We could create an X509CertificateDatabaseSelector subclass of X509CertStoreSelector that
// adds properties like bool Trusted, bool Anchor, and bool HasPrivateKey ? Then we could drop the
// bool method arguments...
if (trustedAnchorsOnly)
{
query = query.Append("TRUSTED = @TRUSTED AND ANCHOR = @ANCHOR");
command.AddParameterWithValue("@TRUSTED", true);
command.AddParameterWithValue("@ANCHOR", true);
}
if (match != null)
{
if (match.BasicConstraints >= 0 || match.BasicConstraints == -2)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
if (match.BasicConstraints == -2)
{
command.AddParameterWithValue("@BASICCONSTRAINTS", -1);
query = query.Append("BASICCONSTRAINTS = @BASICCONSTRAINTS");
}
else
{
command.AddParameterWithValue("@BASICCONSTRAINTS", match.BasicConstraints);
query = query.Append("BASICCONSTRAINTS >= @BASICCONSTRAINTS");
}
}
if (match.CertificateValid != null)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@DATETIME", match.CertificateValid.Value.ToUniversalTime());
query = query.Append("NOTBEFORE < @DATETIME AND NOTAFTER > @DATETIME");
}
if (match.Issuer != null || match.Certificate != null)
{
// Note: GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
// queries for ISSUERNAME, SERIALNUMBER, and FINGERPRINT so we'll do the same.
var issuer = match.Issuer ?? match.Certificate.IssuerDN;
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@ISSUERNAME", issuer.ToString());
query = query.Append("ISSUERNAME = @ISSUERNAME");
}
if (match.SerialNumber != null || match.Certificate != null)
{
// Note: GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
// queries for ISSUERNAME, SERIALNUMBER, and FINGERPRINT so we'll do the same.
var serialNumber = match.SerialNumber ?? match.Certificate.SerialNumber;
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@SERIALNUMBER", serialNumber.ToString());
query = query.Append("SERIALNUMBER = @SERIALNUMBER");
}
if (match.Certificate != null)
{
// Note: GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
// queries for ISSUERNAME, SERIALNUMBER, and FINGERPRINT so we'll do the same.
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@FINGERPRINT", match.Certificate.GetFingerprint());
query = query.Append("FINGERPRINT = @FINGERPRINT");
}
if (match.Subject != null)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@SUBJECTNAME", match.Subject.ToString());
query = query.Append("SUBJECTNAME = @SUBJECTNAME");
}
if (match.SubjectKeyIdentifier != null)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
var id = (Asn1OctetString)Asn1Object.FromByteArray(match.SubjectKeyIdentifier);
var subjectKeyIdentifier = id.GetOctets().AsHex();
command.AddParameterWithValue("@SUBJECTKEYIDENTIFIER", subjectKeyIdentifier);
query = query.Append("SUBJECTKEYIDENTIFIER = @SUBJECTKEYIDENTIFIER");
}
if (match.KeyUsage != null)
{
var flags = BouncyCastleCertificateExtensions.GetKeyUsageFlags(match.KeyUsage);
if (flags != X509KeyUsageFlags.None)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
command.AddParameterWithValue("@FLAGS", (int)flags);
query = query.Append("(KEYUSAGE = 0 OR (KEYUSAGE & @FLAGS) = @FLAGS)");
}
}
}
if (requirePrivateKey)
{
if (command.Parameters.Count > 0)
{
query = query.Append(" AND ");
}
query = query.Append("PRIVATEKEY IS NOT NULL");
}
else if (command.Parameters.Count == 0)
{
query.Length = baseQueryLength;
}
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
return(command);
}
/// <summary>
/// Get the database command to select the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Gets the database command to select the certificate records for the specified mailbox.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="mailbox">The mailbox.</param>
/// <param name="now">The date and time for which the certificate should be valid.</param>
/// <param name="requirePrivateKey">true</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand(MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields)
{
var secure = mailbox as SecureMailboxAddress;
var command = connection.CreateCommand();
var query = CreateSelectQuery(fields);
query = query.Append(" WHERE BASICCONSTRAINTS = @BASICCONSTRAINTS ");
command.AddParameterWithValue("@BASICCONSTRAINTS", -1);
if (secure != null && !string.IsNullOrEmpty(secure.Fingerprint))
{
if (secure.Fingerprint.Length < 40)
{
command.AddParameterWithValue("@FINGERPRINT", secure.Fingerprint.ToLowerInvariant() + "%");
query = query.Append("AND FINGERPRINT LIKE @FINGERPRINT ");
}
else
{
command.AddParameterWithValue("@FINGERPRINT", secure.Fingerprint.ToLowerInvariant());
query = query.Append("AND FINGERPRINT = @FINGERPRINT ");
}
}
else
{
command.AddParameterWithValue("@SUBJECTEMAIL", mailbox.Address.ToLowerInvariant());
query = query.Append("AND SUBJECTEMAIL = @SUBJECTEMAIL ");
}
query = query.Append("AND NOTBEFORE < @NOW AND NOTAFTER > @NOW");
command.AddParameterWithValue("@NOW", now.ToUniversalTime());
if (requirePrivateKey)
{
query = query.Append(" AND PRIVATEKEY IS NOT NULL");
}
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
return(command);
}
/// <summary>
/// Gets the database command to select the record matching the specified certificate.
/// </summary>
/// <remarks>
/// Gets the database command to select the record matching the specified certificate.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="certificate">The certificate.</param>
/// <param name="fields">The fields to return.</param>
protected override DbCommand GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields)
{
var query = "SELECT " + string.Join (", ", GetColumnNames (fields)) + " FROM CERTIFICATES ";
var fingerprint = certificate.GetFingerprint ().ToLowerInvariant ();
var serialNumber = certificate.SerialNumber.ToString ();
var issuerName = certificate.IssuerDN.ToString ();
var command = connection.CreateCommand ();
command.CommandText = query + "WHERE ISSUERNAME = @ISSUERNAME AND SERIALNUMBER = @SERIALNUMBER AND FINGERPRINT = @FINGERPRINT LIMIT 1";
command.AddParameterWithValue ("@ISSUERNAME", issuerName);
command.AddParameterWithValue ("@SERIALNUMBER", serialNumber);
command.AddParameterWithValue ("@FINGERPRINT", fingerprint);
command.CommandType = CommandType.Text;
return command;
}
/// <summary>
/// Find the specified certificate.
/// </summary>
/// <remarks>
/// Searches the database for the specified certificate, returning the matching
/// record with the desired fields populated.
/// </remarks>
/// <returns>The matching record if found; otherwise <c>null</c>.</returns>
/// <param name="certificate">The certificate.</param>
/// <param name="fields">The desired fields.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="certificate"/> is <c>null</c>.
/// </exception>
public X509CertificateRecord Find (X509Certificate certificate, X509CertificateRecordFields fields)
{
if (certificate == null)
throw new ArgumentNullException ("certificate");
using (var command = GetSelectCommand (certificate, fields)) {
var reader = command.ExecuteReader ();
try {
if (reader.Read ()) {
var parser = new X509CertificateParser ();
var buffer = new byte[4096];
return LoadCertificateRecord (reader, parser, ref buffer);
}
} finally {
reader.Close ();
}
}
return null;
}
/// <summary>
/// Gets the column names for the specified fields.
/// </summary>
/// <remarks>
/// Gets the column names for the specified fields.
/// </remarks>
/// <returns>The column names.</returns>
/// <param name="fields">The fields.</param>
protected static string[] GetColumnNames (X509CertificateRecordFields fields)
{
var columns = new List<string> ();
if ((fields & X509CertificateRecordFields.Id) != 0)
columns.Add ("ID");
if ((fields & X509CertificateRecordFields.Trusted) != 0)
columns.Add ("TRUSTED");
if ((fields & X509CertificateRecordFields.Algorithms) != 0)
columns.Add ("ALGORITHMS");
if ((fields & X509CertificateRecordFields.AlgorithmsUpdated) != 0)
columns.Add ("ALGORITHMSUPDATED");
if ((fields & X509CertificateRecordFields.Certificate) != 0)
columns.Add ("CERTIFICATE");
if ((fields & X509CertificateRecordFields.PrivateKey) != 0)
columns.Add ("PRIVATEKEY");
return columns.ToArray ();
}
/// <summary>
/// Gets the database command to update the specified record.
/// </summary>
/// <remarks>
/// Gets the database command to update the specified record.
/// </remarks>
/// <returns>The database command.</returns>
/// <param name="record">The certificate record.</param>
/// <param name="fields">The fields to update.</param>
protected override DbCommand GetUpdateCommand (X509CertificateRecord record, X509CertificateRecordFields fields)
{
var statement = new StringBuilder ("UPDATE CERTIFICATES SET ");
var columns = GetColumnNames (fields & ~X509CertificateRecordFields.Id);
var command = connection.CreateCommand ();
for (int i = 0; i < columns.Length; i++) {
var value = GetValue (record, columns[i]);
var variable = "@" + columns[i];
if (i > 0)
statement.Append (", ");
statement.Append (columns[i]);
statement.Append (" = ");
statement.Append (variable);
command.AddParameterWithValue (variable, value);
}
statement.Append (" WHERE ID = @ID");
command.AddParameterWithValue ("@ID", record.Id);
command.CommandText = statement.ToString ();
command.CommandType = CommandType.Text;
return command;
}
/// <summary> /// Gets the database command to select the certificate records for the specified mailbox. /// </summary> /// <remarks> /// Gets the database command to select the certificate records for the specified mailbox. /// </remarks> /// <returns>The database command.</returns> /// <param name="mailbox">The mailbox.</param> /// <param name="now">The date and time for which the certificate should be valid.</param> /// <param name="requirePrivateKey"><c>true</c> if the certificate must have a private key.</param> /// <param name="fields">The fields to return.</param> protected abstract DbCommand GetSelectCommand(MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields);
/// <summary> /// Gets the database command to select the record matching the specified certificate. /// </summary> /// <remarks> /// Gets the database command to select the record matching the specified certificate. /// </remarks> /// <returns>The database command.</returns> /// <param name="certificate">The certificate.</param> /// <param name="fields">The fields to return.</param> protected abstract IDbCommand GetSelectCommand (X509Certificate certificate, X509CertificateRecordFields fields);
/// <summary> /// Gets the database command to update the specified record. /// </summary> /// <remarks> /// Gets the database command to update the specified record. /// </remarks> /// <returns>The database command.</returns> /// <param name="record">The certificate record.</param> /// <param name="fields">The fields to update.</param> protected abstract DbCommand GetUpdateCommand(X509CertificateRecord record, X509CertificateRecordFields fields);
/// <summary> /// Gets the database command to select the certificate records for the specified mailbox. /// </summary> /// <remarks> /// Gets the database command to select the certificate records for the specified mailbox. /// </remarks> /// <returns>The database command.</returns> /// <param name="mailbox">The mailbox.</param> /// <param name="now">The date and time for which the certificate should be valid.</param> /// <param name="requirePrivateKey"><c>true</c> if the certificate must have a private key.</param> /// <param name="fields">The fields to return.</param> protected abstract IDbCommand GetSelectCommand (MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields);
/// <summary>
/// Finds the certificate records for the specified mailbox.
/// </summary>
/// <remarks>
/// Searches the database for certificates matching the specified mailbox that are valid
/// for the date and time specified, returning all matching records populated with the
/// desired fields.
/// </remarks>
/// <returns>The matching certificate records populated with the desired fields.</returns>
/// <param name="mailbox">The mailbox.</param>
/// <param name="now">The date and time.</param>
/// <param name="requirePrivateKey"><c>true</c> if a private key is required.</param>
/// <param name="fields">The desired fields.</param>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="mailbox"/> is <c>null</c>.
/// </exception>
public IEnumerable <X509CertificateRecord> Find(MailboxAddress mailbox, DateTime now, bool requirePrivateKey, X509CertificateRecordFields fields)
{
if (mailbox == null)
{
throw new ArgumentNullException(nameof(mailbox));
}
using (var command = GetSelectCommand(mailbox, now, requirePrivateKey, fields)) {
var reader = command.ExecuteReader();
try {
var parser = new X509CertificateParser();
var buffer = new byte[4096];
while (reader.Read())
{
yield return(LoadCertificateRecord(reader, parser, ref buffer));
}
} finally {
#if NETSTANDARD
reader.Dispose();
#else
reader.Close();
#endif
}
}
yield break;
}
/// <summary> /// Gets the database command to select certificate records matching the specified selector. /// </summary> /// <remarks> /// Gets the database command to select certificate records matching the specified selector. /// </remarks> /// <returns>The database command.</returns> /// <param name="selector">Selector.</param> /// <param name="trustedOnly"><c>true</c> if only trusted certificates should be matched.</param> /// <param name="requirePrivateKey"><c>true</c> if the certificate must have a private key.</param> /// <param name="fields">The fields to return.</param> protected abstract IDbCommand GetSelectCommand (IX509Selector selector, bool trustedOnly, bool requirePrivateKey, X509CertificateRecordFields fields);
/// <summary> /// Gets the database command to update the specified record. /// </summary> /// <remarks> /// Gets the database command to update the specified record. /// </remarks> /// <returns>The database command.</returns> /// <param name="record">The certificate record.</param> /// <param name="fields">The fields to update.</param> protected abstract IDbCommand GetUpdateCommand (X509CertificateRecord record, X509CertificateRecordFields fields);
public IEnumerable <X509CertificateRecord> Find(IX509Selector selector, bool trustedOnly, X509CertificateRecordFields fields)
{
var certs = this.GetCerts();
foreach (var certificate in certs)
{
if (selector.Match(certificate))
{
var record = new X509CertificateRecord(certificate);
return(new SingletonList <X509CertificateRecord>(record));
}
}
return(new List <X509CertificateRecord>());
}
