/// <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>()); }