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