| crt.sh ID |
';
IF t_certificateID IS NOT NULL THEN
t_output := t_output || '' || t_certificateID::text || '';
ELSE
t_output := t_output || 'Not found';
END IF;
t_output := t_output || ' |
';
t_showMetadata := lower(',' || t_opt) NOT LIKE '%,nometadata,%';
IF t_showMetadata THEN
t_output := t_output ||
'
| Summary |
' || t_certSummary || ' |
Certificate
Transparency |
';
t_temp := '';
FOR l_record IN (
SELECT ctl.NAME, ctl.URL, ctl.OPERATOR, ctle.ENTRY_ID, ctle.ENTRY_TIMESTAMP
FROM ct_log_entry ctle, ct_log ctl
WHERE ctle.CERTIFICATE_ID = t_certificateID
AND ctle.CT_LOG_ID = ctl.ID
ORDER BY ctle.ENTRY_TIMESTAMP
) LOOP
t_temp := t_temp ||
' |
| ' || to_char(l_record.ENTRY_TIMESTAMP, 'YYYY-MM-DD')
|| ' '
|| to_char(l_record.ENTRY_TIMESTAMP, 'HH24:MI:SS UTC')
|| ' |
' || l_record.ENTRY_ID::text || ' |
' || html_escape(l_record.OPERATOR) || ' |
' || html_escape(l_record.URL) || ' |
';
END LOOP;
IF t_temp != '' THEN
t_output := t_output ||
'
| Timestamp |
Entry # |
Log Operator |
Log URL |
' || t_temp ||
'
';
ELSE
t_output := t_output ||
' No log entries found
';
END IF;
t_output := t_output ||
'
';
IF t_caID IS NOT NULL THEN
t_output := t_output ||
'
Audit details
|
';
t_temp := NULL;
t_temp2 := NULL;
FOR l_record IN (
SELECT *
FROM ccadb_certificate cc
WHERE cc.CCADB_RECORD_ID IS NOT NULL
AND cc.CERTIFICATE_ID = t_certificateID
) LOOP
IF t_temp IS NULL THEN
t_temp :=
'
| Auditor |
Standard Audit |
BR Audit |
Documents |
CCADB |
Root Owner / Certificate |
';
END IF;
t_temp := t_temp ||
'
| ' || coalesce(l_record.AUDITOR, '') || ' |
';
IF coalesce(l_record.STANDARD_AUDIT_URL, '') NOT LIKE '%://%' THEN
t_temp := t_temp || coalesce(l_record.STANDARD_AUDIT_URL, 'Not disclosed');
ELSE
t_temp := t_temp || '
' || coalesce(l_record.STANDARD_AUDIT_DATE::text, 'Yes') || '
';
END IF;
t_temp := t_temp || ' |
';
IF coalesce(l_record.BRSSL_AUDIT_URL, '') NOT LIKE '%://%' THEN
t_temp := t_temp || coalesce(l_record.BRSSL_AUDIT_URL, 'No');
ELSE
t_temp := t_temp || '
Yes
';
END IF;
t_temp := t_temp || ' |
';
IF coalesce(l_record.CP_URL, '') != '' THEN
t_temp := t_temp ||
' CP
';
END IF;
IF coalesce(l_record.CPS_URL, '') != '' THEN
t_temp := t_temp ||
' CPS
';
END IF;
t_temp := t_temp ||
' |
';
IF l_record.CCADB_RECORD_ID IS NOT NULL THEN
t_temp := t_temp || '' || l_record.CCADB_RECORD_ID || '';
ELSE
t_temp := t_temp || ' ';
END IF;
t_temp := t_temp || ' |
';
IF l_record.INCLUDED_CERTIFICATE_ID IS NULL THEN
t_temp := t_temp || coalesce(html_escape(l_record.INCLUDED_CERTIFICATE_OWNER), ' ');
ELSE
t_temp := t_temp || '' || coalesce(html_escape(l_record.INCLUDED_CERTIFICATE_OWNER), ' ') || '';
END IF;
IF l_record.CERT_RECORD_TYPE = 'Root Certificate' THEN
t_temp2 :=
' |
Telemetry
|
CERT_VALIDATION_SUCCESS_BY_CA |
';
END IF;
END LOOP;
IF t_temp IS NOT NULL THEN
t_temp := t_temp || '
';
ELSE
t_temp := 'Not Disclosed';
END IF;
t_output := t_output || t_temp || '
|
' || coalesce(t_temp2, '');
END IF;
SELECT 'Revoked'
|| CASE coalesce(cr.REASON_CODE, 0)
WHEN 1 THEN ' (keyCompromise)'
WHEN 2 THEN ' (cACompromise)'
WHEN 3 THEN ' (affiliationChanged)'
WHEN 4 THEN ' (superseded)'
WHEN 5 THEN ' (cessationOfOperation)'
WHEN 6 THEN ' (certificateHold)'
WHEN 7 THEN ' (privilegeWithdrawn)'
WHEN 8 THEN ' (aACompromise)'
ELSE ''
END
|| ''
|| to_char(cr.REVOCATION_DATE, 'YYYY-MM-DD') || ' '
|| to_char(cr.REVOCATION_DATE, 'HH24:MI:SS UTC') || ' | '
|| to_char(cr.LAST_SEEN_CHECK_DATE, 'YYYY-MM-DD') || ' '
|| to_char(cr.LAST_SEEN_CHECK_DATE, 'HH24:MI:SS UTC') || ''
INTO t_temp0
FROM crl_revoked cr
WHERE cr.CA_ID = t_issuerCAID
AND cr.SERIAL_NUMBER = t_serialNumber;
t_count := 1;
IF NOT FOUND THEN
SELECT count(*)
INTO t_count
FROM crl
WHERE crl.CA_ID = t_issuerCAID
AND crl.ERROR_MESSAGE IS NULL
AND crl.NEXT_UPDATE > statement_timestamp();
IF t_count > 0 THEN
t_temp0 := 'Not Revoked | n/a | n/a';
ELSE
t_temp0 := 'Unknown | n/a | n/a';
END IF;
END IF;
SELECT to_char(max(crl.LAST_CHECKED), 'YYYY-MM-DD') || ' '
|| to_char(max(crl.LAST_CHECKED), 'HH24:MI:SS UTC') || ''
INTO t_temp
FROM crl
WHERE crl.CA_ID = t_issuerCAID;
t_temp0 := t_temp0 || ' | ' || coalesce(t_temp, '');
IF t_count = 0 THEN
SELECT array_to_string(array_agg('' || html_escape(crl.ERROR_MESSAGE) || ' [' || html_escape(crl.DISTRIBUTION_POINT_URL || ']')), '
')
INTO t_temp
FROM crl
WHERE crl.CA_ID = t_issuerCAID
AND crl.ERROR_MESSAGE IS NOT NULL;
IF t_temp IS NOT NULL THEN
t_temp0 := t_temp0 || '
' || coalesce(t_temp, '') || '';
END IF;
END IF;
SELECT 'Revoked [by ' || gr.ENTRY_TYPE || ']'
INTO t_temp
FROM google_revoked gr
WHERE gr.CERTIFICATE_ID = t_certificateID;
t_temp := coalesce(t_temp, 'Not Revoked');
SELECT 'Revoked [by MD5(PublicKey)]'
INTO t_temp2
FROM microsoft_disallowedcert mdc
WHERE mdc.CERTIFICATE_ID = t_certificateID;
t_temp2 := coalesce(t_temp2, 'Not Revoked');
SELECT 'Revoked [by Issuer Name, Serial Number] | '
|| to_char(mo.CREATED, 'YYYY-MM-DD') || ' '
|| to_char(mo.CREATED, 'HH24:MI:SS UTC') || ''
INTO t_temp3
FROM mozilla_onecrl mo
WHERE mo.CERTIFICATE_ID = t_certificateID;
t_temp3 := coalesce(t_temp3, 'Not Revoked | n/a');
t_output := t_output ||
' |
Revocation';
IF lower(',' || t_opt) NOT LIKE '%,problemreporting,%' THEN
t_output := t_output || '
';
END IF;
t_output := t_output || ' |
| Mechanism |
Provider |
Status |
Revocation Date |
Last Observed in CRL |
Last Checked (Error) |
| CRL |
The CA |
' || t_temp0 || ' |
| CRLSet/Blacklist |
Google |
' || t_temp || ' |
n/a |
n/a |
n/a |
| disallowedcert.stl |
Microsoft |
' || t_temp2 || ' |
n/a |
n/a |
n/a |
| OneCRL |
Mozilla |
' || t_temp3 || ' |
n/a |
n/a |
|
';
IF lower(',' || t_opt) LIKE '%,problemreporting,%' THEN
SELECT cco.PROBLEM_REPORTING
INTO t_temp3
FROM ca_certificate cac, ccadb_certificate cc, ccadb_caowner cco, ca_trust_purpose ctp, certificate c
WHERE cac.CA_ID = t_issuerCAID
AND cac.CERTIFICATE_ID = cc.CERTIFICATE_ID
AND cc.INCLUDED_CERTIFICATE_OWNER = cco.CA_OWNER_NAME
AND cac.CA_ID = ctp.CA_ID
AND cac.CERTIFICATE_ID = c.ID
GROUP BY cco.PROBLEM_REPORTING
ORDER BY min(ctp.SHORTEST_CHAIN), max(x509_notAfter(c.CERTIFICATE)) DESC
LIMIT 1;
IF trim(coalesce(t_temp3, '')) = '' THEN
t_temp3 := 'Unknown';
END IF;
t_output := t_output ||
'
Problem Reporting
Mechanism(s) disclosed via the
CCADB |
' || replace(html_escape(t_temp3), '. ', '.
') || ' |
';
END IF;
END IF;
t_output := t_output ||
'
| SHA-256(Certificate) |
'
|| coalesce(upper(encode(t_certificateSHA256, 'hex')), 'Not found') || ' |
| SHA-1(Certificate) |
' || coalesce(upper(encode(t_certificateSHA1, 'hex')), 'Not found') || ' |
';
t_showCABLint := (',' || t_opt) LIKE '%,cablint,%';
IF t_showCABLint THEN
t_output := t_output ||
'
CA/B Forum lint
|
';
FOR l_record IN (
SELECT substr(CABLINT, 4) ISSUE_TEXT,
CASE substr(CABLINT, 1, 2)
WHEN 'B:' THEN 1
WHEN 'I:' THEN 2
WHEN 'N:' THEN 3
WHEN 'F:' THEN 4
WHEN 'E:' THEN 5
WHEN 'W:' THEN 6
ELSE 5
END ISSUE_TYPE,
CASE substr(CABLINT, 1, 2)
WHEN 'B:' THEN ' BUG:'
WHEN 'I:' THEN ' INFO:'
WHEN 'N:' THEN ' NOTICE:'
WHEN 'F:' THEN ' FATAL:'
WHEN 'E:' THEN ' ERROR:'
WHEN 'W:' THEN ' WARNING:'
ELSE ' ' || substr(CABLINT, 1, 2)
END ISSUE_HEADING
FROM cablint_embedded(t_certificate) CABLINT
ORDER BY ISSUE_TYPE, ISSUE_TEXT
) LOOP
t_output := t_output ||
' ' || l_record.ISSUE_HEADING || ' ' || l_record.ISSUE_TEXT || '
';
END LOOP;
t_output := t_output ||
' |
';
END IF;
t_showX509Lint := (',' || t_opt) LIKE '%,x509lint,%';
IF t_showX509Lint THEN
IF NOT x509_canIssueCerts(t_certificate) THEN
t_certType := 0;
ELSIF t_caID != t_issuerCAID THEN
t_certType := 1;
ELSE
t_certType := 2;
END IF;
t_output := t_output ||
'
X.509 lint
|
';
FOR l_record IN (
SELECT substr(X509LINT, 4) ISSUE_TEXT,
CASE substr(X509LINT, 1, 2)
WHEN 'B:' THEN 1
WHEN 'I:' THEN 2
WHEN 'N:' THEN 3
WHEN 'F:' THEN 4
WHEN 'E:' THEN 5
WHEN 'W:' THEN 6
ELSE 5
END ISSUE_TYPE,
CASE substr(X509LINT, 1, 2)
WHEN 'B:' THEN ' BUG:'
WHEN 'I:' THEN ' INFO:'
WHEN 'N:' THEN ' NOTICE:'
WHEN 'F:' THEN ' FATAL:'
WHEN 'E:' THEN ' ERROR:'
WHEN 'W:' THEN ' WARNING:'
ELSE ' ' || substr(X509LINT, 1, 2)
END ISSUE_HEADING
FROM x509lint_embedded(t_certificate, t_certType) X509LINT
ORDER BY ISSUE_TYPE, ISSUE_TEXT
) LOOP
t_output := t_output ||
' ' || l_record.ISSUE_HEADING || ' ' || l_record.ISSUE_TEXT || '
';
END LOOP;
t_output := t_output ||
' |
';
END IF;
t_showZLint := (',' || t_opt) LIKE '%,zlint,%';
IF t_showZLint THEN
t_output := t_output ||
'
ZLint
|
';
FOR l_record IN (
SELECT substr(ZLINT, 4) ISSUE_TEXT,
CASE substr(ZLINT, 1, 2)
WHEN 'B:' THEN 1
WHEN 'I:' THEN 2
WHEN 'N:' THEN 3
WHEN 'F:' THEN 4
WHEN 'E:' THEN 5
WHEN 'W:' THEN 6
ELSE 5
END ISSUE_TYPE,
CASE substr(ZLINT, 1, 2)
WHEN 'B:' THEN ' BUG:'
WHEN 'I:' THEN ' INFO:'
WHEN 'N:' THEN ' NOTICE:'
WHEN 'F:' THEN ' FATAL:'
WHEN 'E:' THEN ' ERROR:'
WHEN 'W:' THEN ' WARNING:'
ELSE ' ' || substr(ZLINT, 1, 2)
END ISSUE_HEADING
FROM zlint_embedded(t_certificate) ZLINT
ORDER BY ISSUE_TYPE, ISSUE_TEXT
) LOOP
t_output := t_output ||
' ' || l_record.ISSUE_HEADING || ' ' || l_record.ISSUE_TEXT || '
';
END LOOP;
t_output := t_output ||
' |
';
END IF;
t_output := t_output ||
'
';
IF t_type = 'Certificate ASN.1' THEN
t_output := t_output ||
' Certificate | ASN.1
Powered by asn1js
';
IF t_showMetadata THEN
t_output := t_output ||
'
Hide metadata
';
ELSE
IF t_opt = 'nometadata,' THEN
t_temp := '';
ELSE
t_temp := '&opt=' || rtrim(replace(t_opt, 'nometadata,', ''), ',');
END IF;
t_output := t_output ||
'
Show metadata
';
END IF;
IF NOT t_showCABLint THEN
t_output := t_output ||
'
Run cablint
';
END IF;
IF NOT t_showX509Lint THEN
t_output := t_output ||
'
Run x509lint
';
END IF;
IF NOT t_showZLint THEN
t_output := t_output ||
'
Run zlint
';
END IF;
t_output := t_output ||
'
Download Certificate: PEM
|
';
ELSE
t_output := t_output ||
' | Certificate | ASN.1
';
IF t_showMetadata THEN
t_output := t_output ||
'
Hide metadata
';
ELSE
IF t_opt = 'nometadata,' THEN
t_temp := '';
ELSE
t_temp := '&opt=' || rtrim(replace(t_opt, 'nometadata,', ''), ',');
END IF;
t_output := t_output ||
'
Show metadata
';
END IF;
IF NOT t_showCABLint THEN
t_output := t_output ||
'
Run cablint
';
END IF;
IF NOT t_showX509Lint THEN
t_output := t_output ||
'
Run x509lint
';
END IF;
IF NOT t_showZLint THEN
t_output := t_output ||
'
Run zlint
';
END IF;
t_output := t_output ||
'
Download Certificate: PEM
|
' || coalesce(t_text, 'Not found');
END IF;
t_output := t_output ||
' |
';
ELSIF t_type IN ('CA ID', 'CA Name') THEN
t_output := t_output || '
| crt.sh CA ID |
' || t_caID::text || ' |
| CA Name/Key |
' || t_text || ' |
| Certificates |
';
IF t_showMozillaDisclosure THEN
t_output := t_output ||
' Mozilla Disclosure
(id-kp-serverAuth) |
';
END IF;
t_output := t_output ||
' crt.sh ID |
Not Before |
Not After |
Issuer Name |
';
FOR l_record IN (
SELECT x509_issuerName(c.CERTIFICATE) ISSUER_NAME,
c.ID,
c.ISSUER_CA_ID,
c.CERTIFICATE,
x509_notBefore(c.CERTIFICATE) NOT_BEFORE,
x509_notAfter(c.CERTIFICATE) NOT_AFTER
FROM ca_certificate cac, certificate c
LEFT OUTER JOIN ca ON (c.ISSUER_CA_ID = ca.ID)
WHERE cac.CA_ID = t_caID
AND cac.CERTIFICATE_ID = c.ID
ORDER BY ISSUER_NAME, NOT_BEFORE
) LOOP
t_output := t_output ||
'
';
IF t_showMozillaDisclosure THEN
t_temp3 := 'Not Trusted';
t_ctp.SHORTEST_CHAIN := NULL;
ELSIF NOT t_ctp.IS_TIME_VALID THEN
t_temp3 := t_temp3 || '888888>Expired';
ELSE
SELECT cc.MOZILLA_DISCLOSURE_STATUS
INTO t_temp2
FROM ccadb_certificate cc
WHERE cc.CERTIFICATE_ID = l_record.ID;
IF FOUND AND (t_temp2 LIKE 'Revoked%') THEN
t_temp3 := t_temp3 || 'CC0000>Revoked';
ELSIF is_technically_constrained(l_record.CERTIFICATE) THEN
t_temp3 := t_temp3 || '00CC00>Constrained';
ELSIF t_ctp.ALL_CHAINS_REVOKED_IN_SALESFORCE OR t_ctp.ALL_CHAINS_REVOKED_VIA_ONECRL THEN
t_temp3 := t_temp3 || 'CC0000>All Paths Revoked';
ELSIF t_ctp.ALL_CHAINS_TECHNICALLY_CONSTRAINED THEN
t_temp3 := t_temp3 || '00CC00>All Paths Constrained';
ELSE
t_temp3 := t_temp3 || '00CC00>Valid';
END IF;
END IF;
IF t_ctp.SHORTEST_CHAIN IS NOT NULL THEN
t_temp3 := t_temp3 || ' ' || (t_ctp.SHORTEST_CHAIN + 1)::text || '';
END IF;
t_output := t_output ||
' | ' || t_temp3 || ' |
';
END IF;
t_output := t_output ||
' ' || l_record.ID::text || ' |
' || to_char(l_record.NOT_BEFORE, 'YYYY-MM-DD') || ' |
' || to_char(l_record.NOT_AFTER, 'YYYY-MM-DD') || ' |
' || html_escape(l_record.ISSUER_NAME) || ' |
';
END LOOP;
t_output := t_output ||
'
|
| |
';
t_showCABLint := (',' || coalesce(get_parameter('opt', paramNames, paramValues), '') || ',') LIKE '%,cablint,%';
IF t_showCABLint THEN
t_output := t_output ||
'
| CA/B Forum lint |
| For Issued Certificates with notBefore >= ' || to_char(t_minNotBefore, 'YYYY-MM-DD') || ': |
|---|
| Issue |
# Affected Certs |
';
FOR l_record IN (
SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
CASE li.SEVERITY
WHEN 'F' THEN 1
WHEN 'E' THEN 2
WHEN 'W' THEN 3
ELSE 4
END ISSUE_TYPE,
CASE li.SEVERITY
WHEN 'F' THEN ' FATAL:'
WHEN 'E' THEN ' ERROR:'
WHEN 'W' THEN ' WARNING:'
ELSE ' ' || li.SEVERITY || ':'
END ISSUE_HEADING
FROM lint_summary ls, lint_issue li
WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore
AND ls.ISSUER_CA_ID = t_value::integer
AND ls.LINT_ISSUE_ID = li.ID
AND li.LINTER = 'cablint'
GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT
ORDER BY ISSUE_TYPE, NUM_CERTS DESC
) LOOP
t_output := t_output ||
'
| ' || l_record.ISSUE_HEADING || ' ' || l_record.ISSUE_TEXT || ' |
' || l_record.NUM_CERTS::text || ' |
';
END LOOP;
t_output := t_output ||
'
|
';
END IF;
t_showX509Lint := (',' || coalesce(get_parameter('opt', paramNames, paramValues), '') || ',') LIKE '%,x509lint,%';
IF t_showX509Lint THEN
t_output := t_output ||
'
| X.509 lint |
| For Issued Certificates with notBefore >= ' || to_char(t_minNotBefore, 'YYYY-MM-DD') || ': |
|---|
| Issue |
# Affected Certs |
';
FOR l_record IN (
SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
CASE li.SEVERITY
WHEN 'F' THEN 1
WHEN 'E' THEN 2
WHEN 'W' THEN 3
ELSE 4
END ISSUE_TYPE,
CASE li.SEVERITY
WHEN 'F' THEN ' FATAL:'
WHEN 'E' THEN ' ERROR:'
WHEN 'W' THEN ' WARNING:'
ELSE ' ' || li.SEVERITY || ':'
END ISSUE_HEADING
FROM lint_summary ls, lint_issue li
WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore
AND ls.ISSUER_CA_ID = t_value::integer
AND ls.LINT_ISSUE_ID = li.ID
AND li.LINTER = 'x509lint'
GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT
ORDER BY ISSUE_TYPE, NUM_CERTS DESC
) LOOP
t_output := t_output ||
'
| ' || l_record.ISSUE_HEADING || ' ' || l_record.ISSUE_TEXT || ' |
' || l_record.NUM_CERTS::text || ' |
';
END LOOP;
t_output := t_output ||
'
|
';
END IF;
t_showZLint := (',' || coalesce(get_parameter('opt', paramNames, paramValues), '') || ',') LIKE '%,zlint,%';
IF t_showZLint THEN
t_output := t_output ||
'
| ZLint |
| For Issued Certificates with notBefore >= ' || to_char(t_minNotBefore, 'YYYY-MM-DD') || ': |
|---|
| Issue |
# Affected Certs |
';
FOR l_record IN (
SELECT sum(ls.NO_OF_CERTS) NUM_CERTS, li.ID, li.SEVERITY, li.ISSUE_TEXT,
CASE li.SEVERITY
WHEN 'F' THEN 1
WHEN 'E' THEN 2
WHEN 'W' THEN 3
ELSE 4
END ISSUE_TYPE,
CASE li.SEVERITY
WHEN 'F' THEN ' FATAL:'
WHEN 'E' THEN ' ERROR:'
WHEN 'W' THEN ' WARNING:'
ELSE ' ' || li.SEVERITY || ':'
END ISSUE_HEADING
FROM lint_summary ls, lint_issue li
WHERE ls.NOT_BEFORE_DATE >= t_minNotBefore
AND ls.ISSUER_CA_ID = t_value::integer
AND ls.LINT_ISSUE_ID = li.ID
AND li.LINTER = 'zlint'
GROUP BY li.ID, li.SEVERITY, li.ISSUE_TEXT
ORDER BY ISSUE_TYPE, NUM_CERTS DESC
) LOOP
t_output := t_output ||
'
| ' || l_record.ISSUE_HEADING || ' ' || l_record.ISSUE_TEXT || ' |
' || l_record.NUM_CERTS::text || ' |
';
END LOOP;
t_output := t_output ||
'
|
';
END IF;
t_output := t_output ||
'
| Issued Certificates |
|
| |
| Trust |
| Purpose |
';
t_text := '';
t_count := 0;
FOR l_record IN (
SELECT *
FROM trust_context tc
ORDER BY tc.DISPLAY_ORDER
) LOOP
t_text := t_text ||
' ' || l_record.CTX || '';
IF l_record.VERSION IS NOT NULL THEN
t_text := t_text || '
';
IF l_record.VERSION_URL IS NOT NULL THEN
t_text := t_text || '';
END IF;
t_text := t_text || '(' || l_record.VERSION || ')';
IF l_record.VERSION_URL IS NOT NULL THEN
t_text := t_text || '';
END IF;
END IF;
t_text := t_text || ' |
';
t_count := t_count + 1;
END LOOP;
t_output := t_output ||
' Context (Version) Shortest Path |
';
t_purposeOID := '';
FOR l_record IN (
SELECT trustsrc.TRUST_CONTEXT_ID,
trustsrc.PURPOSE,
trustsrc.PURPOSE_OID,
(ctp.CA_ID IS NOT NULL) HAS_TRUST,
(ap.PURPOSE IS NOT NULL) IS_APPLICABLE,
ctp.IS_TIME_VALID,
ctp.SHORTEST_CHAIN,
ctp.ALL_CHAINS_REVOKED_VIA_ONECRL,
ctp.ALL_CHAINS_REVOKED_VIA_CRLSET,
ctp.ALL_CHAINS_REVOKED_VIA_DISALLOWEDSTL,
ctp.ALL_CHAINS_TECHNICALLY_CONSTRAINED
FROM (SELECT tc.DISPLAY_ORDER CTX_DISPLAY_ORDER,
tc.ID TRUST_CONTEXT_ID,
tp.ID TRUST_PURPOSE_ID,
tp.DISPLAY_ORDER,
tp.PURPOSE,
tp.PURPOSE_OID
FROM trust_purpose tp, trust_context tc
WHERE tp.PURPOSE != 'EV Server Authentication'
UNION
SELECT tc.DISPLAY_ORDER CTX_DISPLAY_ORDER,
tc.ID TRUST_CONTEXT_ID,
tp.ID TRUST_PURPOSE_ID,
tp.DISPLAY_ORDER,
tp.PURPOSE,
tp.PURPOSE_OID
FROM ca_trust_purpose ctp_ev, trust_purpose tp, trust_context tc
WHERE ctp_ev.CA_ID = t_caID
AND ctp_ev.TRUST_PURPOSE_ID = tp.ID
AND tp.PURPOSE = 'EV Server Authentication'
GROUP BY tc.CTX, tc.ID, tp.ID, tp.DISPLAY_ORDER, tp.PURPOSE, tp.PURPOSE_OID
) trustsrc
LEFT OUTER JOIN ca_trust_purpose ctp ON (
ctp.CA_ID = t_caID
AND trustsrc.TRUST_CONTEXT_ID = ctp.TRUST_CONTEXT_ID
AND trustsrc.TRUST_PURPOSE_ID = ctp.TRUST_PURPOSE_ID
)
LEFT OUTER JOIN applicable_purpose ap ON (
trustsrc.TRUST_CONTEXT_ID = ap.TRUST_CONTEXT_ID
AND trustsrc.PURPOSE = ap.PURPOSE
)
ORDER BY trustsrc.DISPLAY_ORDER, trustsrc.PURPOSE_OID, trustsrc.CTX_DISPLAY_ORDER
) LOOP
IF (t_purposeOID != l_record.PURPOSE_OID) OR (t_purpose != l_record.PURPOSE) THEN
t_purposeOID := l_record.PURPOSE_OID;
t_purpose := l_record.PURPOSE;
t_text := t_text ||
'
| ' || l_record.PURPOSE;
IF l_record.PURPOSE = 'EV Server Authentication' THEN
t_text := t_text || ' (' || l_record.PURPOSE_OID || ')';
END IF;
t_text := t_text || ' |
';
END IF;
IF (l_record.TRUST_CONTEXT_ID = 6) AND (l_record.IS_APPLICABLE) THEN
SELECT true
INTO l_record.ALL_CHAINS_REVOKED_VIA_CRLSET
FROM ca_trust_purpose ctp
WHERE ctp.CA_ID = t_caID
AND ctp.ALL_CHAINS_REVOKED_VIA_CRLSET
LIMIT 1;
END IF;
t_text := t_text ||
' n/a';
l_record.SHORTEST_CHAIN := NULL;
ELSIF l_record.ALL_CHAINS_REVOKED_VIA_ONECRL AND (l_record.TRUST_CONTEXT_ID = 5) THEN
t_text := t_text || 'CC0000 style="font-weight:bold">Revoked
via OneCRL';
ELSIF l_record.ALL_CHAINS_REVOKED_VIA_CRLSET AND (l_record.TRUST_CONTEXT_ID = 6) THEN
t_text := t_text || 'CC0000 style="font-weight:bold">Revoked via
CRLSet / Blacklist';
ELSIF l_record.ALL_CHAINS_REVOKED_VIA_DISALLOWEDSTL AND (l_record.TRUST_CONTEXT_ID = 1) THEN
t_text := t_text || 'CC0000 style="font-weight:bold">Revoked via
disallowedcert.stl';
ELSIF (l_record.PURPOSE = 'Server Authentication') AND (l_record.TRUST_CONTEXT_ID = 6) THEN
t_text := t_text || '888888>Defer to OS';
ELSIF NOT l_record.HAS_TRUST THEN
t_text := t_text || '888888>No';
l_record.SHORTEST_CHAIN := NULL;
ELSIF NOT l_record.IS_TIME_VALID THEN
t_text := t_text || '888888>Expired';
ELSIF l_record.ALL_CHAINS_TECHNICALLY_CONSTRAINED THEN
t_text := t_text || '00CC00>Constrained';
ELSE
t_text := t_text || '00CC00>Valid';
END IF;
IF l_record.SHORTEST_CHAIN IS NOT NULL THEN
t_text := t_text || ' ' || l_record.SHORTEST_CHAIN || '';
END IF;
t_text := t_text || ' |
';
END LOOP;
t_output := t_output || t_text ||
'
|
| |
| Parent CAs |
';
t_text := NULL;
FOR l_record IN (
SELECT x509_issuerName(c.CERTIFICATE) ISSUER_NAME,
c.ISSUER_CA_ID
FROM ca_certificate cac, certificate c
LEFT OUTER JOIN ca ON (c.ISSUER_CA_ID = ca.ID)
WHERE cac.CA_ID = t_caID
AND cac.CERTIFICATE_ID = c.ID
AND c.ISSUER_CA_ID != t_caID
GROUP BY x509_issuerName(c.CERTIFICATE),
c.ISSUER_CA_ID
ORDER BY x509_issuerName(c.CERTIFICATE)
) LOOP
IF t_text IS NULL THEN
t_text := '
';
END IF;
t_text := t_text ||
'
| ';
IF l_record.ISSUER_CA_ID IS NULL THEN
t_text := t_text || html_escape(l_record.ISSUER_NAME);
ELSE
t_text := t_text || ''
|| html_escape(l_record.ISSUER_NAME) || '';
END IF;
t_text := t_text || ' |
';
END LOOP;
IF t_text IS NOT NULL THEN
t_text := t_text ||
'
';
END IF;
t_output := t_output || coalesce(t_text, 'None found') ||
' |
| Child CAs |
';
t_text := NULL;
FOR l_record IN (
SELECT x509_subjectName(c.CERTIFICATE) SUBJECT_NAME,
cac.CA_ID
FROM certificate c, ca_certificate cac
LEFT OUTER JOIN ca ON (cac.CA_ID = ca.ID)
WHERE x509_canIssueCerts(c.CERTIFICATE)
AND c.ISSUER_CA_ID = t_caID
AND c.ID = cac.CERTIFICATE_ID
AND cac.CA_ID != t_caID
GROUP BY x509_subjectName(c.CERTIFICATE),
cac.CA_ID
ORDER BY x509_subjectName(c.CERTIFICATE)
) LOOP
IF t_text IS NULL THEN
t_text := '
';
END IF;
t_text := t_text ||
'
| ';
IF l_record.CA_ID IS NULL THEN
t_text := t_text || html_escape(l_record.SUBJECT_NAME);
ELSE
t_text := t_text || ''
|| html_escape(l_record.SUBJECT_NAME) || '';
END IF;
t_text := t_text || ' |
';
END LOOP;
IF t_text IS NOT NULL THEN
t_text := t_text ||
'
';
END IF;
t_output := t_output || coalesce(t_text, 'None found') ||
' |
';
t_output := t_output ||
'
';
-- Search for (potentially) multiple CAs.
ELSE /* CA Name */
t_query := 'SELECT ca.ID, ca.NAME' || chr(10) ||
' FROM ca' || chr(10);
IF t_useReverseIndex THEN
t_query := t_query ||
' WHERE reverse(lower(ca.NAME)) LIKE reverse(lower($1))' || chr(10);
ELSE
t_query := t_query ||
' WHERE lower(ca.NAME) LIKE lower($1)' || chr(10);
END IF;
t_query := t_query ||
' ORDER BY ca.NAME';
FOR l_record IN EXECUTE t_query
USING t_value LOOP
IF t_text IS NULL THEN
t_text := '
| ';
IF coalesce(t_groupBy, '') = 'none' THEN
t_temp2 := t_temp2 || '' || l_record.MIN_CERT_ID::text || ' |
' || to_char(l_record.MIN_ENTRY_TIMESTAMP, 'YYYY-MM-DD') || ' |
' || to_char(l_record.NOT_BEFORE, 'YYYY-MM-DD');
ELSIF (l_record.NUM_CERTS = 1)
AND (l_record.MIN_CERT_ID IS NOT NULL) THEN
t_temp2 := t_temp2 || ''
|| l_record.NUM_CERTS::text || '';
ELSIF (l_record.ISSUER_CA_ID IS NOT NULL)
AND (l_record.MIN_CERT_ID IS NOT NULL) THEN
t_temp2 := t_temp2 || ''
|| l_record.NUM_CERTS::text || '';
ELSE
t_temp2 := t_temp2 || l_record.NUM_CERTS::text;
END IF;
t_temp2 := t_temp2 || ' |
';
IF t_showIdentity THEN
t_temp2 := t_temp2 || html_escape(l_record.NAME_VALUE) || ' |
';
END IF;
IF l_record.ISSUER_CA_ID IS NOT NULL THEN
t_temp2 := t_temp2 || ''
|| coalesce(html_escape(l_record.ISSUER_NAME), ' ')
|| '';
ELSE
t_temp2 := t_temp2 || coalesce(html_escape(l_record.ISSUER_NAME), '?');
END IF;
IF lower(t_type) LIKE '%lint' THEN
SELECT cc.INCLUDED_CERTIFICATE_OWNER
INTO t_temp
FROM ca_certificate cac, ccadb_certificate cc
WHERE cac.CA_ID = l_record.ISSUER_CA_ID
AND cac.CERTIFICATE_ID = cc.CERTIFICATE_ID
GROUP BY cc.INCLUDED_CERTIFICATE_OWNER
ORDER BY count(*) DESC
LIMIT 1;
t_temp2 := t_temp2 || ' |
' || coalesce(t_temp, ' ');
END IF;
t_temp2 := t_temp2 || ' |
';
END IF;
t_text := t_text || t_temp2;
END LOOP;
t_temp := replace(
urlEncode(t_cmd) || '=' || urlEncode(t_value) || coalesce(t_excludeExpired, '') || coalesce(t_excludeCAsString, ''),
'&', '&'
);
IF t_outputType = 'atom' THEN
t_output :=
'[BEGIN_HEADERS]
Cache-Control: max-age=' || t_cacheControlMaxAge::integer || '
Content-Type: application/atom+xml
[END_HEADERS]
| Issuer O';
IF t_sort = 1 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
IF t_groupBy != 'IssuerO' THEN
t_output := t_output || ' |
Issuer CN, OU or O';
IF t_sort = 2 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
END IF;
t_output := t_output || ' |
# Certs
Issued';
IF t_sort = 3 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
FATAL |
ERROR |
WARNING |
ALL |
| # Certs';
IF t_sort = 4 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
%';
IF t_sort = 5 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
# Certs';
IF t_sort = 7 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
%';
IF t_sort = 8 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
# Certs';
IF t_sort = 10 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
%';
IF t_sort = 11 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
# Certs';
IF t_sort = 16 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
%';
IF t_sort = 17 THEN
t_output := t_output || ' ' || t_dirSymbol;
END IF;
t_output := t_output || ' |
';
END IF;
IF t_groupBy = 'IssuerO' THEN
t_query := 'SELECT NULL::integer ISSUER_CA_ID,' || chr(10) ||
' (sum(l1s.CERTS_ISSUED))::bigint CERTS_ISSUED,' || chr(10) ||
' (sum(l1s.ALL_CERTS))::bigint ALL_CERTS,' || chr(10) ||
' ((sum(l1s.ALL_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric ALL_PERC,' || chr(10) ||
' (sum(l1s.FATAL_CERTS))::bigint FATAL_CERTS,' || chr(10) ||
' ((sum(l1s.FATAL_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric FATAL_PERC,' || chr(10) ||
' (sum(l1s.ERROR_CERTS))::bigint ERROR_CERTS,' || chr(10) ||
' ((sum(l1s.ERROR_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric ERROR_PERC,' || chr(10) ||
' (sum(l1s.WARNING_CERTS))::bigint WARNING_CERTS,' || chr(10) ||
' ((sum(l1s.WARNING_CERTS) * 100) / sum(l1s.CERTS_ISSUED))::numeric WARNING_PERC,' || chr(10) ||
' get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') ISSUER_ORGANIZATION_NAME,' || chr(10) ||
' NULL ISSUER_FRIENDLY_NAME' || chr(10);
ELSE
t_query := 'SELECT l1s.ISSUER_CA_ID::integer,' || chr(10) ||
' l1s.CERTS_ISSUED::bigint,' || chr(10) ||
' l1s.ALL_CERTS::bigint,' || chr(10) ||
' ((l1s.ALL_CERTS * 100) / l1s.CERTS_ISSUED::numeric) ALL_PERC,' || chr(10) ||
' l1s.FATAL_CERTS::bigint,' || chr(10) ||
' ((l1s.FATAL_CERTS * 100) / l1s.CERTS_ISSUED::numeric) FATAL_PERC,' || chr(10) ||
' l1s.ERROR_CERTS::bigint,' || chr(10) ||
' ((l1s.ERROR_CERTS * 100) / l1s.CERTS_ISSUED::numeric) ERROR_PERC,' || chr(10) ||
' l1s.WARNING_CERTS::bigint,' || chr(10) ||
' ((l1s.WARNING_CERTS * 100) / l1s.CERTS_ISSUED::numeric) WARNING_PERC,' || chr(10) ||
' get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') ISSUER_ORGANIZATION_NAME,' || chr(10) ||
' get_ca_name_attribute(l1s.ISSUER_CA_ID, ''_friendlyName_'') ISSUER_FRIENDLY_NAME' || chr(10);
END IF;
t_query := t_query ||
' FROM lint_1week_summary l1s' || chr(10) ||
' WHERE l1s.LINTER ';
IF t_linter IS NOT NULL THEN
t_query := t_query || '= ''' || t_linter || '''' || chr(10);
ELSE
t_query := t_query || 'IS NULL' || chr(10);
END IF;
IF t_issuerO IS NOT NULL THEN
t_query := t_query ||
' AND get_ca_name_attribute(l1s.ISSUER_CA_ID, ''organizationName'') LIKE $1' || chr(10);
END IF;
t_query := t_query || ' ';
IF t_groupBy = 'IssuerO' THEN
t_query := t_query || ' GROUP BY ISSUER_ORGANIZATION_NAME' || chr(10) ||
' ';
END IF;
IF t_sort = 1 THEN
t_query := t_query || 'ORDER BY ISSUER_ORGANIZATION_NAME ' || t_orderBy || ', ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 2 THEN
t_query := t_query || 'ORDER BY ISSUER_FRIENDLY_NAME ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME';
ELSIF t_sort = 3 THEN
t_query := t_query || 'ORDER BY CERTS_ISSUED ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 4 THEN
t_query := t_query || 'ORDER BY FATAL_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 5 THEN
t_query := t_query || 'ORDER BY FATAL_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 7 THEN
t_query := t_query || 'ORDER BY ERROR_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 8 THEN
t_query := t_query || 'ORDER BY ERROR_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 10 THEN
t_query := t_query || 'ORDER BY WARNING_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 11 THEN
t_query := t_query || 'ORDER BY WARNING_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 16 THEN
t_query := t_query || 'ORDER BY ALL_CERTS ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
ELSIF t_sort = 17 THEN
t_query := t_query || 'ORDER BY ALL_PERC ' || t_orderBy || ', ISSUER_ORGANIZATION_NAME, ISSUER_FRIENDLY_NAME';
END IF;
FOR l_record IN EXECUTE t_query USING t_issuerO LOOP
IF t_outputType = 'json' THEN
t_output := t_output || row_to_json(l_record, FALSE);
ELSIF t_outputType = 'html' THEN
t_output := t_output || '
| ';
IF l_record.ISSUER_ORGANIZATION_NAME IS NOT NULL THEN
t_output := t_output || ''
|| l_record.ISSUER_ORGANIZATION_NAME || '';
ELSE
t_output := t_output || ' ';
END IF;
t_output := t_output || ' |
';
IF t_groupBy != 'IssuerO' THEN
t_output := t_output ||
' ' || coalesce(l_record.ISSUER_FRIENDLY_NAME, ' ') || ' |
';
END IF;
t_output := t_output ||
' ' || l_record.CERTS_ISSUED::text || ' |
' || l_record.FATAL_CERTS::text || ' |
' || replace(round(l_record.FATAL_PERC, 2)::text, '.00', '') || ' |
' || l_record.ERROR_CERTS::text || ' |
' || replace(round(l_record.ERROR_PERC, 2)::text, '.00', '') || ' |
' || l_record.WARNING_CERTS::text || ' |
' || replace(round(l_record.WARNING_PERC, 2)::text, '.00', '') || ' |
' || l_record.ALL_CERTS::text || ' |
' || replace(round(l_record.ALL_PERC, 2)::text, '.00', '') || ' |
';
END IF;
END LOOP;
IF t_outputType = 'html' THEN
t_output := t_output ||
'
';
END IF;
END IF;
ELSIF lower(t_type) LIKE '%lint: issues' THEN
IF t_sort NOT BETWEEN 1 AND 3 THEN
t_sort := 1;
END IF;
t_temp := get_parameter('exclude', paramNames, paramValues);
IF lower(coalesce(',' || t_temp || ',', 'nothing')) LIKE ',affected_certs,' THEN
t_excludeAffectedCerts := '&exclude=affected_certs';
END IF;
IF t_outputType = 'html' THEN
t_output := t_output ||
' © COMODO CA Limited 2015-2017. All rights reserved.
Group';
ELSE
t_output := t_output || '&group=none">Ungroup';
END IF;
t_output := t_output || ' by Issuer
