search

crtsh / certwatch_db

Database schema
https://crt.sh/
GNU General Public License v3.0
199 stars 36 forks source link

SQL error displayed when viewing the list of LINT errors of a CA #77

Closed RufusJWB closed 4 years ago

RufusJWB commented 4 years ago

This URL https://crt.sh/?cablint=923&iCAID=52410&minNotBefore=1-1-2017 leads to the following error:

column "c.certificate" must appear in the GROUP BY clause or be used in an aggregate function

PL/pgSQL function web_apis(text,text[],text[]) line 3747 at FOR over EXECUTE statement

SELECT c.ID, c.ISSUER_CA_ID, x509_subjectName(c.CERTIFICATE) SUBJECT_NAME, x509_notBefore(c.CERTIFICATE) NOT_BEFORE, x509_notAfter(c.CERTIFICATE) NOT_AFTER, encode(x509_serialNumber(c.CERTIFICATE), 'hex') SERIAL_NUMBER FROM certificate c, lint_cert_issue lci, lint_issue li WHERE c.ISSUER_CA_ID = $1::integer AND c.ID = lci.CERTIFICATE_ID AND lci.ISSUER_CA_ID = $1::integer AND lci.NOT_BEFORE_DATE >= $3 AND lci.LINT_ISSUE_ID = $2::integer AND lci.LINT_ISSUE_ID = li.ID AND li.LINTER = 'cablint' GROUP BY c.ID, c.ISSUER_CA_ID, SUBJECT_NAME, NOT_BEFORE, NOT_AFTER ORDER BY NOT_BEFORE DESC OFFSET 0 LIMIT 100

It was working before

robstradling commented 4 years ago

Fixed by https://github.com/crtsh/certwatch_db/commit/2fbabf0ce9f543c767e7a971bd1f1d2a83da4906