Hi,
We have a small Python app that sends HTTP requests to a CFSSL (Cloudflare PKI) server “revoke” endpoint that we host internally (see CFSSL Github at GitHub - cloudflare/cfssl: CFSSL: Cloudflare's PKI and TLS toolkit), to revoke expired certificates.
The CFSSL server uses a MYSQL DB to store information in a table named “certificates”, and one of the fields in that able is “serial_number”, which is a VARBINARY(128) field, and also an “expiry” field, which is timestamp field. There is also a “status” field, which is also VARBINARY(128).
Our Python app uses the pymysql import:
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb
import subprocess
import os
import datetime
import sys
import json
import requests
The original app creates a Mysql connection, then a Mysql cursor, then did a simple “SELECT” command to pull the serial_number and expiry rows:
curr.execute("SELECT serial_number, expiry FROM certificates WHERE status='good'");
row = curr.fetchone()
The app would then loop through the “row” and check if the expiry timestamp was in the past, and if it was it would construct a json BODY to do a POST request to the CFSSL revoke endpoint.
Here’s an example of a POST body that the app WAS sending to the revoke endpoint:
{'serial': "b'224639216869932457233728691183801201691388550902'", 'authority_key_id': '96ae5a532c31edaee9cce853ad2a4dda03681873', 'reason': 'superseded'}
As mentioned above, those requests are now getting failed responses, and after some testing, I think it is because the CFSSL endpoint is now expecting the “serial” parameter in the request BODY to be in decimal.
I am not sure if CFSSL changed or whatever, but I was able to do mysql test commands and was able to confirm if I sent in a request to the CFSSL revoke endpoint, with the “serial” being the DECIMAL serial number, then the request worked. Here’s an example of a BODY that works now:
{"serial": "718546686554824987844204878670877677207684326234", "authority_key_id": "96ae5a532c31edaee9cce853ad2a4dda03681873", "reason": "superseded"}
So I’ve been trying to modify the Python app to fix the problem. I have tried changing the SELECT line to:
curr.execute("SELECT CAST(serial_number as CHAR), expiry FROM certificates WHERE status='good'")
but when I run the app after that change, from the debug output:
message Just did curr.execute() with CAST!!
['/apps/products/cfssl/cfssl_crl.py', '96ae5a532c31edaee9cce853ad2a4dda03681873']
serial 224639216869932457233728691183801201691388550902
authority_key_id 96ae5a532c31edaee9cce853ad2a4dda03681873
{'serial': '224639216869932457233728691183801201691388550902', 'authority_key_id': '96ae5a532c31edaee9cce853ad2a4dda03681873', 'reason': 'superseded'}
the “serial” in the request body STILL looks exactly the same as before I added the CAST to the SELECT, except it now no longer has the “b” in front of the “serial”, and I cannot figure out why?
Is there something other than just changing the cursor execute SELECT to using the Mysql CAST function that is needed to get the Python app to send the decimal “serial”?
My apologies in advance for the king of longish post 
!!
Thanks,
Jim
