1 connect to database¶

In [26]:

import pymysql
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123456',database='CertificateDB',charset='utf8')
conn

Out[26]:

<pymysql.connections.Connection at 0x1eb8ec96f08>

2 query data¶

In [27]:

import pandas as pd
pd.read_sql("show tables",con=conn)

Out[27]:

	Tables_in_CertificateDB
0	certificate

In [28]:

pd.read_sql("desc certificate",con=conn)

Out[28]:

	Field	Type	Null	Key	Default	Extra
0	id	int(11)	NO	PRI	None	auto_increment
1	host	varchar(256)	NO		None
2	open443	varchar(256)	YES		None
3	error	varchar(256)	YES		None
4	ssl_error	varchar(256)	YES		None
5	certificate_version	varchar(10)	YES		None
6	certificate_algorithm	varchar(256)	YES		None
7	issuer_country	varchar(256)	YES		None
8	issued_organization	varchar(256)	YES		None
9	public_key_type	varchar(256)	YES		None
10	public_key_bits	varchar(256)	YES		None
11	expired	varchar(256)	YES		None
12	valid_from	varchar(256)	YES		None
13	valid_to	varchar(256)	YES		None
14	validity_days	varchar(256)	YES		None
15	valid_days_left	varchar(256)	YES		None
16	ocsp_status	varchar(256)	YES		None
17	ocsp_error	varchar(256)	YES		None
18	crl_status	varchar(256)	YES		None
19	crl_reason	varchar(256)	YES		None

In [29]:

def exec_sql(conn, sql):
    with conn.cursor() as cursor:
        cursor.execute(sql)
    conn.commit()

    
def query_sql(conn,sql):
    with conn.cursor() as cursor:
        cursor.execute(sql)
        result = cursor.fetchall()
    return result

3 Analysis the data¶

3.1 check whether host443 open¶

In [30]:

pd.read_sql("""SELECT open443, COUNT(open443) AS num
    FROM certificate
    GROUP BY open443""",con=conn)

Out[30]:

	open443	num
0	0	4318
1	1	112997
2	null	2685

3.2 analysis error¶

In [31]:

pd.read_sql("""SELECT open443,error,COUNT(*) AS num
    FROM certificate
    WHERE NOT (error = 'null')
    GROUP BY open443,error
    ORDER BY open443,num DESC""",con=conn)

Out[31]:

	open443	error	num
0	0	[Errno 111] Connection refused	3767
1	0	[Errno -5] No address associated with hostname	529
2	0	[Errno -2] Name or service not known	10
3	0	[Errno -3] Temporary failure in name resolution	5
4	0	[('SSL routines', 'tls_process_server_certific...	1
5	0	(-1, 'Unexpected EOF')	1
6	1	[('SSL routines', 'tls_process_server_certific...	3527
7	1	(-1, 'Unexpected EOF')	750
8	1	[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...	240
9	1	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...	151
10	1	[('SSL routines', 'ssl3_get_record', 'wrong ve...	142
11	1	[Errno 111] Connection refused	100
12	1	[('SSL routines', 'tls_process_server_certific...	63
13	1	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...	49
14	1	[('rsa routines', 'RSA_padding_check_PKCS1_typ...	2
15	1	[('SSL routines', 'ssl3_read_bytes', 'invalid ...	1
16	1	[('SSL routines', 'tls_process_ske_dhe', 'dh k...	1
17	1	[('SSL routines', 'tls_process_server_certific...	1
18	null	[Errno -2] Name or service not known	2098
19	null	[Errno -3] Temporary failure in name resolution	533
20	null	[Errno -5] No address associated with hostname	54

In [32]:

pd.read_sql("""SELECT error,COUNT(error) AS num
    FROM certificate
    WHERE NOT (error = 'null')
    GROUP BY error
    ORDER BY num DESC""",con=conn)

Out[32]:

	error	num
0	[Errno 111] Connection refused	3867
1	[('SSL routines', 'tls_process_server_certific...	3528
2	[Errno -2] Name or service not known	2108
3	(-1, 'Unexpected EOF')	751
4	[Errno -5] No address associated with hostname	583
5	[Errno -3] Temporary failure in name resolution	538
6	[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...	240
7	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...	151
8	[('SSL routines', 'ssl3_get_record', 'wrong ve...	142
9	[('SSL routines', 'tls_process_server_certific...	63
10	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...	49
11	[('rsa routines', 'RSA_padding_check_PKCS1_typ...	2
12	[('SSL routines', 'tls_process_server_certific...	1
13	[('SSL routines', 'ssl3_read_bytes', 'invalid ...	1
14	[('SSL routines', 'tls_process_ske_dhe', 'dh k...	1

In [33]:

pd.read_sql("""SELECT open443,error,ssl_error, COUNT(*) AS num
    FROM certificate
    GROUP BY open443,error,ssl_error
    ORDER BY open443, num DESC""",con=conn)

Out[33]:

	open443	error	ssl_error	num
0	0	[Errno 111] Connection refused	0	3545
1	0	[Errno -5] No address associated with hostname	0	495
2	0	[Errno 111] Connection refused	10	88
3	0	[Errno 111] Connection refused	20	74
4	0	[Errno 111] Connection refused	18	59
...	...	...	...	...
56	null	[Errno -3] Temporary failure in name resolution	10	7
57	null	[Errno -3] Temporary failure in name resolution	20	6
58	null	[Errno -5] No address associated with hostname	18	2
59	null	[Errno -5] No address associated with hostname	20	1
60	null	[Errno -2] Name or service not known	19	1

61 rows × 4 columns

In [34]:

pd.read_sql("""SELECT ssl_error,COUNT(ssl_error) AS num
    FROM certificate 
    WHERE NOT (ssl_error = 'null' or ssl_error = '0')
    GROUP BY ssl_error
    ORDER BY num DESC""",con=conn)

Out[34]:

	ssl_error	num
0	20	1532
1	10	1222
2	18	1193
3	19	50

In [35]:

pd.read_sql("""SELECT error, ssl_error,COUNT(*) AS num
    FROM certificate
    WHERE NOT (error = 'null')
    GROUP BY error,ssl_error
    ORDER BY ssl_error,num DESC""",con=conn)

Out[35]:

	error	ssl_error	num
0	[Errno 111] Connection refused	0	3641
1	[Errno -2] Name or service not known	0	2005
2	(-1, 'Unexpected EOF')	0	707
3	[Errno -5] No address associated with hostname	0	546
4	[Errno -3] Temporary failure in name resolution	0	515
5	[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...	0	226
6	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...	0	146
7	[('SSL routines', 'ssl3_get_record', 'wrong ve...	0	131
8	[('SSL routines', 'tls_process_server_certific...	0	59
9	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...	0	47
10	[('rsa routines', 'RSA_padding_check_PKCS1_typ...	0	2
11	[('SSL routines', 'ssl3_read_bytes', 'invalid ...	0	1
12	[('SSL routines', 'tls_process_ske_dhe', 'dh k...	0	1
13	[('SSL routines', 'tls_process_server_certific...	0	1
14	[('SSL routines', 'tls_process_server_certific...	10	1040
15	[Errno 111] Connection refused	10	89
16	[Errno -2] Name or service not known	10	36
17	[Errno -5] No address associated with hostname	10	18
18	(-1, 'Unexpected EOF')	10	16
19	[Errno -3] Temporary failure in name resolution	10	8
20	[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...	10	7
21	[('SSL routines', 'ssl3_get_record', 'wrong ve...	10	5
22	[('SSL routines', 'tls_process_server_certific...	10	1
23	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...	10	1
24	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...	10	1
25	[('SSL routines', 'tls_process_server_certific...	18	1053
26	[Errno 111] Connection refused	18	61
27	[Errno -2] Name or service not known	18	39
28	(-1, 'Unexpected EOF')	18	14
29	[Errno -3] Temporary failure in name resolution	18	9
30	[Errno -5] No address associated with hostname	18	8
31	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 al...	18	4
32	[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...	18	2
33	[('SSL routines', 'ssl3_get_record', 'wrong ve...	18	2
34	[('SSL routines', 'ssl3_read_bytes', 'tlsv1 un...	18	1
35	[('SSL routines', 'tls_process_server_certific...	19	48
36	[Errno 111] Connection refused	19	1
37	[Errno -2] Name or service not known	19	1
38	[('SSL routines', 'tls_process_server_certific...	20	1387
39	[Errno 111] Connection refused	20	75
40	[Errno -2] Name or service not known	20	27
41	(-1, 'Unexpected EOF')	20	14
42	[Errno -5] No address associated with hostname	20	11
43	[Errno -3] Temporary failure in name resolution	20	6
44	[('SSL routines', 'ssl3_read_bytes', 'sslv3 al...	20	5
45	[('SSL routines', 'ssl3_get_record', 'wrong ve...	20	4
46	[('SSL routines', 'tls_process_server_certific...	20	3

3.3 analysis issuers(country and organization)¶

In [36]:

pd.read_sql("""SELECT issuer_country,
       COUNT(issuer_country) AS num,
       COUNT(issuer_country)*100.0/(SELECT COUNT(*) 
                                    FROM certificate
                                    WHERE NOT (issued_organization = 'null')) AS percentage
    FROM certificate
    WHERE NOT (issuer_country = 'null')
    GROUP BY issuer_country
    ORDER BY num DESC
    LIMIT 10""",con=conn)

Out[36]:

	issuer_country	num	percentage
0	US	88424	81.89303
1	GB	8998	8.33341
2	BE	4555	4.21857
3	CN	1863	1.72540
4	PL	794	0.73536
5	AT	638	0.59088
6	NL	539	0.49919
7	JP	536	0.49641
8	FR	315	0.29173
9	LV	314	0.29081

In [37]:

pd.read_sql("""SELECT issued_organization,
       COUNT(issued_organization) AS num,
       COUNT(issued_organization)*100.0/(SELECT COUNT(*) 
                                         FROM certificate
                                         WHERE NOT (issued_organization = 'null')) AS percentage
    FROM certificate
    WHERE NOT (issued_organization = 'null')
    GROUP BY issued_organization
    ORDER BY num DESC
    LIMIT 10""",con=conn)

Out[37]:

	issued_organization	num	percentage
0	Let's Encrypt	30834	28.55661
1	Cloudflare, Inc.	26860	24.87613
2	DigiCert Inc	13655	12.64645
3	Sectigo Limited	8835	8.18245
4	Amazon	7000	6.48298
5	GlobalSign nv-sa	4555	4.21857
6	GoDaddy.com, Inc.	3160	2.92660
7	cPanel, Inc.	3064	2.83769
8	Google Trust Services LLC	1494	1.38365
9	TrustAsia Technologies, Inc.	1451	1.34383

3.4 analysis algorithm and public key & bits¶

In [38]:

pd.read_sql("""SELECT certificate_algorithm,
       COUNT(certificate_algorithm) AS num,
       COUNT(certificate_algorithm)*100.0/(SELECT COUNT(*) 
                                           FROM certificate 
                                           WHERE NOT (certificate_algorithm = 'null')) AS percentage
    FROM certificate
    WHERE NOT (certificate_algorithm = 'null')
    GROUP BY certificate_algorithm
    ORDER BY num DESC""",con=conn)

Out[38]:

	certificate_algorithm	num	percentage
0	sha256WithRSAEncryption	76032	70.41630
1	ecdsa-with-SHA256	26972	24.97986
2	ecdsa-with-SHA384	3603	3.33688
3	sha384WithRSAEncryption	1315	1.21787
4	sha512WithRSAEncryption	53	0.04909

In [39]:

pd.read_sql("""SELECT public_key_type,
       public_key_bits,
       COUNT(*) AS num,
       COUNT(*)*100.0/(SELECT COUNT(*) 
                                     FROM certificate 
                                     WHERE NOT (public_key_bits = 'null')) AS percentage
    FROM certificate
    WHERE NOT (public_key_bits = 'null')
    GROUP BY public_key_type,public_key_bits
    ORDER BY public_key_type DESC,num DESC""",con=conn)

Out[39]:

	public_key_type	public_key_bits	num	percentage
0	6	2048	69091	63.98796
1	6	4096	4656	4.31211
2	6	3072	318	0.29451
3	6	3096	1	0.00093
4	408	256	33004	30.56633
5	408	384	905	0.83816

3.5 check expire status¶

In [40]:

pd.read_sql("""SELECT expired,COUNT(expired) AS num
    FROM certificate
    GROUP BY expired
    ORDER BY num DESC""",con=conn)

Out[40]:

	expired	num
0	0	107975
1	null	12025

In [41]:

pd.read_sql("""SELECT validity_days, COUNT(validity_days) AS num
    FROM certificate
    WHERE NOT (validity_days = 'null')
    GROUP BY validity_days
    ORDER BY num DESC""",con=conn)

Out[41]:

	validity_days	num
0	89	31741
1	364	24674
2	365	13107
3	396	6614
4	394	6006
...	...	...
436	636	1
437	190	1
438	120	1
439	101	1
440	88	1

441 rows × 2 columns

3.6 CRL check¶

In [42]:

pd.read_sql("""SELECT crl_status, COUNT(crl_status) AS num
    FROM certificate
    WHERE NOT (crl_status = 'null')
    GROUP BY crl_status
    ORDER BY num DESC""",con=conn)

Out[42]:

	crl_status	num
0	GOOD	64143
1	FAILED	43824
2	REVOKED	8

In [43]:

pd.read_sql("""SELECT crl_reason, COUNT(crl_reason) AS num
    FROM certificate
    WHERE NOT (crl_reason = 'null')
    GROUP BY crl_reason
    ORDER BY num DESC""",con=conn)

Out[43]:

	crl_reason	num
0	CRL ERROR: Not Found CRL Extension	43233
1	CRL ERROR: No connection adapters were found f...	25
2	CRL ERROR: No connection adapters were found f...	19
3	CRL ERROR: No connection adapters were found f...	13
4	CRL ERROR: Invalid URL '<Name(C=JP,O=SECOM Tru...	11
...	...	...
499	CRL ERROR: HTTPConnectionPool(host='crl4.digic...	1
500	CRL ERROR: HTTPConnectionPool(host='crl3.digic...	1
501	CRL ERROR: HTTPConnectionPool(host='crl4.digic...	1
502	CRL ERROR: HTTPConnectionPool(host='crl3.digic...	1
503	CRL ERROR: HTTPConnectionPool(host='crl3.digic...	1

504 rows × 2 columns

3.7 OCSP check¶

In [44]:

pd.read_sql("""SELECT ocsp_status, COUNT(ocsp_status) AS num
    FROM certificate
    WHERE NOT (ocsp_status = 'null')
    GROUP BY ocsp_status
    ORDER BY num DESC""",con=conn)

Out[44]:

	ocsp_status	num
0	GOOD	107387
1	REVOKED	28

In [45]:

pd.read_sql("""SELECT ocsp_error, COUNT(ocsp_error) AS num
    FROM certificate
    WHERE NOT (ocsp_error = 'null')
    GROUP BY ocsp_error
    ORDER BY num DESC""",con=conn)

Out[45]:

	ocsp_error	num
0	Error: get_ocsp_response: Request timeout for ...	268
1	Error: timed out	31
2	Error: get_ocsp_response: Unknown Connection E...	15
3	Error: build_ocsp_request: Unable to load x509...	2
4	Error: get_certificate_chain: Connection to be...	1
...	...	...
243	Error: get_certificate_chain: Connection to ti...	1
244	Error: get_certificate_chain: www.leqee.com di...	1
245	Error: get_certificate_chain: Client Certifica...	1
246	Error: get_certificate_chain: Connection to ww...	1
247	Error: get_certificate_chain: www.wanxue.cn di...	1

248 rows × 2 columns