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