Skip to content

Commit f5bb3ad

Browse files
lanratlanrat
committed
fixed crt.sh driver
1 parent 40ab604 commit f5bb3ad

1 file changed

Lines changed: 55 additions & 51 deletions

File tree

‎driver/crtsh/crtsh.go‎

Lines changed: 55 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -4,14 +4,14 @@
44
//
55
// As the API is unofficial and has been reverse engineered it may stop working
66
// at any time and comes with no guarantees.
7+
// view SQL excample: https://crt.sh/?showSQL=Y&exclude=expired&q=
78
//
89
package crtsh
910

10-
// TODO running in verbose gives error: pq: unnamed prepared statement does not exist
11-
1211
import (
1312
"database/sql"
1413
"fmt"
14+
"log"
1515
"path"
1616
"time"
1717

@@ -21,9 +21,11 @@ import (
2121
_ "github.com/lib/pq" // portgresql
2222
)
2323

24-
const connStr = "postgresql://guest@crt.sh/certwatch?sslmode=disable"
24+
const connStr = "postgresql://guest@crt.sh/certwatch?sslmode=disable&fallback_application_name=certgraph&binary_parameters=yes"
2525
const driverName = "crtsh"
2626

27+
const debug = false
28+
2729
func init() {
2830
driver.AddDriver(driverName)
2931
}
@@ -99,56 +101,60 @@ func (d *crtsh) QueryDomain(domain string) (driver.Result, error) {
99101
driver: d,
100102
}
101103

102-
queryStr := ""
103-
104-
if d.includeSubdomains {
105-
if d.includeExpired {
106-
queryStr = `SELECT digest(certificate.certificate, 'sha256') sha256
107-
FROM certificate_identity, certificate
108-
WHERE certificate.id = certificate_identity.certificate_id
109-
AND (reverse(lower(certificate_identity.name_value)) LIKE reverse(lower('%%.'||$1))
110-
OR reverse(lower(certificate_identity.name_value)) LIKE reverse(lower($1)))
111-
LIMIT $2`
112-
} else {
113-
queryStr = `SELECT digest(certificate.certificate, 'sha256') sha256
114-
FROM certificate_identity, certificate
115-
WHERE certificate.id = certificate_identity.certificate_id
116-
AND x509_notAfter(certificate.certificate) > statement_timestamp()
117-
AND (reverse(lower(certificate_identity.name_value)) LIKE reverse(lower('%%.'||$1))
118-
OR reverse(lower(certificate_identity.name_value)) LIKE reverse(lower($1)))
119-
LIMIT $2`
120-
}
121-
} else {
122-
if d.includeExpired {
123-
queryStr = `SELECT digest(certificate.certificate, 'sha256') sha256
124-
FROM certificate_identity, certificate
125-
WHERE certificate.id = certificate_identity.certificate_id
126-
AND reverse(lower(certificate_identity.name_value)) LIKE reverse(lower($1))
127-
LIMIT $2`
128-
} else {
129-
queryStr = `SELECT digest(certificate.certificate, 'sha256') sha256
130-
FROM certificate_identity, certificate
131-
WHERE certificate.id = certificate_identity.certificate_id
132-
AND x509_notAfter(certificate.certificate) > statement_timestamp()
133-
AND reverse(lower(certificate_identity.name_value)) LIKE reverse(lower($1))
134-
LIMIT $2`
135-
}
136-
}
137-
138-
if d.includeSubdomains {
139-
domain = fmt.Sprintf("%%.%s", domain)
140-
}
104+
queryStr := `WITH myconstants (include_expired, include_subdomains) as (
105+
values ($1::bool, $2::bool)
106+
),
107+
ci AS (
108+
SELECT digest(sub.CERTIFICATE, 'sha256') sha256, -- added
109+
min(sub.CERTIFICATE_ID) ID,
110+
min(sub.ISSUER_CA_ID) ISSUER_CA_ID,
111+
array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES
112+
FROM (SELECT *
113+
FROM certificate_and_identities cai, myconstants
114+
WHERE plainto_tsquery('certwatch', $4) @@ identities(cai.CERTIFICATE)
115+
AND (
116+
-- domain only
117+
(NOT myconstants.include_subdomains AND cai.NAME_VALUE ILIKE ($4))
118+
OR
119+
-- include sub-domains
120+
(myconstants.include_subdomains AND (cai.NAME_VALUE ILIKE ($4) OR cai.NAME_VALUE ILIKE ('%.' || $4)))
121+
)
122+
AND (
123+
-- added
124+
cai.NAME_TYPE = '2.5.4.3' -- commonName
125+
OR
126+
cai.NAME_TYPE = 'san:dNSName' -- dNSName
127+
)
128+
AND
129+
-- include expired?
130+
(myconstants.include_expired OR (coalesce(x509_notAfter(cai.CERTIFICATE), 'infinity'::timestamp) >= date_trunc('year', now() AT TIME ZONE 'UTC')
131+
AND x509_notAfter(cai.CERTIFICATE) >= now() AT TIME ZONE 'UTC'))
132+
LIMIT $3
133+
) sub
134+
GROUP BY sub.CERTIFICATE
135+
)
136+
SELECT
137+
ci.sha256 -- added
138+
--array_to_string(ci.name_values, chr(10)) name_value,
139+
--ci.id id
140+
FROM ci;`
141141

142142
try := 0
143143
var err error
144144
var rows *sql.Rows
145145
for try < 5 {
146146
// this is a hack while crt.sh gets there stuff togeather
147147
try++
148-
rows, err = d.db.Query(queryStr, domain, d.queryLimit)
148+
if debug {
149+
log.Printf("QueryDomain try %d: %s", try, queryStr)
150+
}
151+
rows, err = d.db.Query(queryStr, d.includeExpired, d.includeSubdomains, d.queryLimit, domain)
149152
if err == nil {
150153
break
151154
}
155+
if debug {
156+
log.Printf("crtsh pq error on domain %q: %s", domain, err.Error())
157+
}
152158
}
153159
/*if try > 1 {
154160
fmt.Println("QueryDomain try ", try)
@@ -166,6 +172,10 @@ func (d *crtsh) QueryDomain(domain string) (driver.Result, error) {
166172
results.fingerprints.Add(domain, fingerprint.FromHashBytes(hash))
167173
}
168174

175+
if debug {
176+
log.Printf("crtsh: got %d results for %s.", len(results.fingerprints[domain]), domain)
177+
}
178+
169179
return results, nil
170180
}
171181

@@ -174,11 +184,7 @@ func (d *crtsh) QueryCert(fp fingerprint.Fingerprint) (*driver.CertResult, error
174184
certNode.Fingerprint = fp
175185
certNode.Domains = make([]string, 0, 5)
176186

177-
queryStr := `SELECT DISTINCT certificate_identity.name_value
178-
FROM certificate, certificate_identity
179-
WHERE certificate.id = certificate_identity.certificate_id
180-
AND certificate_identity.name_type in ('dNSName', 'commonName')
181-
AND digest(certificate.certificate, 'sha256') = $1`
187+
queryStr := `SELECT DISTINCT name_value FROM certificate_and_identities WHERE digest(certificate, 'sha256') = $1;`
182188

183189
try := 0
184190
var err error
@@ -209,9 +215,7 @@ func (d *crtsh) QueryCert(fp fingerprint.Fingerprint) (*driver.CertResult, error
209215

210216
if d.save {
211217
var rawCert []byte
212-
queryStr = `SELECT certificate.certificate
213-
FROM certificate
214-
WHERE digest(certificate.certificate, 'sha256') = $1`
218+
queryStr = `SELECT certificate FORM certificate_and_identities WHERE digest(certificate, 'sha256') = $1;`
215219
row := d.db.QueryRow(queryStr, fp[:])
216220
err = row.Scan(&rawCert)
217221
if err != nil {

0 commit comments

Comments
 (0)