diff options
| author | Rob Stradling <rob@comodo.com> | 2017-05-05 23:06:09 +0100 |
|---|---|---|
| committer | Rob Stradling <rob@comodo.com> | 2017-05-05 23:06:09 +0100 |
| commit | 35d6da09d181190e68c1226f741b1a631072f571 (patch) | |
| tree | caa655cc2b213ef01f191ada38b4ae005cf727cb | |
| parent | edf771e7e6b88db7a89c15c8fbac1dcdb87f88cd (diff) | |
| download | certwatch_db-35d6da09d181190e68c1226f741b1a631072f571.zip certwatch_db-35d6da09d181190e68c1226f741b1a631072f571.tar.gz certwatch_db-35d6da09d181190e68c1226f741b1a631072f571.tar.bz2 | |
Handle malformed base64 and missing 'created' dates in OneCRL.
| -rw-r--r-- | update_browser_revocations.sql | 20 | ||||
| -rw-r--r-- | web_apis.fnc | 4 |
2 files changed, 20 insertions, 4 deletions
diff --git a/update_browser_revocations.sql b/update_browser_revocations.sql index f64d38f..97ca6f0 100644 --- a/update_browser_revocations.sql +++ b/update_browser_revocations.sql @@ -13,10 +13,26 @@ SELECT json_array_elements((o.onecrl_data->>'data')::json) CERT_ITEM DROP TABLE onecrl_import1; CREATE TABLE onecrl_import3 AS -SELECT decode(o.CERT_ITEM->>'issuerName', 'base64') ISSUER_NAME, +SELECT decode( + o.CERT_ITEM->>'issuerName' + || CASE length(o.CERT_ITEM->>'issuerName') % 4 + WHEN 2 THEN '==' + WHEN 3 THEN '=' + ELSE '' + END, + 'base64' + ) ISSUER_NAME, timestamp without time zone 'epoch' + ((o.CERT_ITEM->>'last_modified')::bigint * interval '1 millisecond') LAST_MODIFIED, - decode(o.CERT_ITEM->>'serialNumber', 'base64') SERIAL_NUMBER, + decode( + o.CERT_ITEM->>'serialNumber' + || CASE length(o.CERT_ITEM->>'serialNumber') % 4 + WHEN 2 THEN '==' + WHEN 3 THEN '=' + ELSE '' + END, + 'base64' + ) SERIAL_NUMBER, (((o.CERT_ITEM->>'details')::json)->>'created')::timestamp CREATED, ((o.CERT_ITEM->>'details')::json)->>'bug' BUG_URL, ((o.CERT_ITEM->>'details')::json)->>'name' SUMMARY diff --git a/web_apis.fnc b/web_apis.fnc index 9ad148a..ad673cb 100644 --- a/web_apis.fnc +++ b/web_apis.fnc @@ -2649,7 +2649,7 @@ Content-Type: application/json mo.ISSUER_CA_ID, x509_name_print(mo.ISSUER_NAME) ISSUER_NAME_TEXT, x509_name_print(mo.SUBJECT_NAME) SUBJECT_NAME_TEXT, mo.NOT_AFTER FROM mozilla_onecrl mo - ORDER BY mo.CREATED DESC, mo.SUMMARY, mo.BUG_URL, ISSUER_NAME_TEXT, mo.SERIAL_NUMBER + ORDER BY mo.CREATED DESC NULLS FIRST, mo.SUMMARY, mo.BUG_URL, ISSUER_NAME_TEXT, mo.SERIAL_NUMBER ) LOOP t_output := t_output || ' <TR> @@ -2660,7 +2660,7 @@ Content-Type: application/json t_output := t_output || ' '; END IF; t_output := t_output || '</TD> - <TD style="white-space:nowrap">' || TO_CHAR(l_record.CREATED, 'YYYY-MM-DD') || '</TD> + <TD style="white-space:nowrap">' || coalesce(TO_CHAR(l_record.CREATED, 'YYYY-MM-DD'), 'Unspecified') || '</TD> <TD>' || l_record.SUMMARY || '</TD> <TD><A href="' || l_record.BUG_URL || '" target="_blank">' || substring(l_record.BUG_URL from '[0-9]*$') || '</A></TD> <TD>' || encode(l_record.SERIAL_NUMBER, 'hex') || '</TD> |