MariaDB SQL request to list certificates expiration dates

[XOF83]

Hello,

I cannot extract the list of certificates expiration dates with HeidiSQL.
I would like a report like in the certificate tab of XCA (expiry date column)

Thank you in advance.
best regards.

[MarcoM82]

Hi I have the same wish. Someone could tell me how I can read from the DB the expiration date?

BR

[chris2511]

The expiry date is not stored explicitly in the database. You need to extract the certificate and pipe it through openssl.

$ sqlite3 xca.xdb "SELECT cert FROM view_certs where id=42" | base64 -d | openssl x509 -inform der -noout -enddate
notAfter=May 22 16:08:00 2026 GMT

Alternatively you could use the XCA commandline to export the certificate information in an index file:

$ xca --database=youruser@10.1.0.1/QMYSQL3:yourdbname --index=xca-index.txt

The index is used and specified by OpenSSL and documented here. It contains among others the serial, subject and expiry date.

[XOF83]

Thanks, have a good day

[KenK73]

So this works amazingly, but... on Windows there is always a dialog box that pops up and informs me the export was successful and expects me to click "OK". Even with the "--no-gui" option in use. Is there some way to make this quiet or pass the success message on the console?

[AnonATXDO]

You need to suppress import messages within the XCA software (settings). That should to the trick.