{"action":"opened","issue":{"url":"https://api.github.com/repos/crtsh/certwatch_db/issues/61","repository_url":"https://api.github.com/repos/crtsh/certwatch_db","labels_url":"https://api.github.com/repos/crtsh/certwatch_db/issues/61/labels{/name}","comments_url":"https://api.github.com/repos/crtsh/certwatch_db/issues/61/comments","events_url":"https://api.github.com/repos/crtsh/certwatch_db/issues/61/events","html_url":"https://github.com/crtsh/certwatch_db/issues/61","id":420179519,"node_id":"MDU6SXNzdWU0MjAxNzk1MTk=","number":61,"title":"Time outs on querying large CAs","user":{"login":"RufusJWB","id":1633499,"node_id":"MDQ6VXNlcjE2MzM0OTk=","avatar_url":"https://avatars0.githubusercontent.com/u/1633499?v=4","gravatar_id":"","url":"https://api.github.com/users/RufusJWB","html_url":"https://github.com/RufusJWB","followers_url":"https://api.github.com/users/RufusJWB/followers","following_url":"https://api.github.com/users/RufusJWB/following{/other_user}","gists_url":"https://api.github.com/users/RufusJWB/gists{/gist_id}","starred_url":"https://api.github.com/users/RufusJWB/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/RufusJWB/subscriptions","organizations_url":"https://api.github.com/users/RufusJWB/orgs","repos_url":"https://api.github.com/users/RufusJWB/repos","events_url":"https://api.github.com/users/RufusJWB/events{/privacy}","received_events_url":"https://api.github.com/users/RufusJWB/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2019-03-12T20:01:38Z","updated_at":"2019-03-12T20:01:38Z","closed_at":null,"author_association":"NONE","body":"I wrote a little query to find all certificates with 63 bits or shorter serial numbers:\r\n\r\n```\r\nSELECT concat('https://crt.sh/?id=', cert_lc.certificate_id,'&opt=ocsp'), cert_lc.subject_distinguished_name, not_before, not_after, substr(encode(cert.certificate, 'hex'), 27, 2) datatype, substr(encode(cert.certificate, 'hex'), 29, 2) snlength\r\nFROM certificate_lifecycle cert_lc\r\nJOIN certificate cert\r\nON cert.id = cert_lc.certificate_id\r\nwhere cert_lc.ca_id = 52410 AND cert_lc.expired = false AND cert_lc.revoked = 0 AND cert_lc.not_before < '2018-03-01' and cert_lc.certificate_type = 'Precertificate'and  ('x' || lpad(substr(encode(cert.certificate, 'hex'), 29, 2), 8, '0'))::bit(32)::int <= 8\r\norder by not_before desc\r\n```\r\n\r\nIt works very good on small CAs but gets time outs for CAs with more than a couple of thousand certificates. Does anybody has an idea how to improve this?"}}
{"actor":{"display_login":"RufusJWB"}}