Whmcs Ssl Renewal Reminder Script

2013-02-13

Okay, so this post is going to be a bit more technical than my recent posts and it's also going to be pretty long. If you aren't interested in serious server scripting, then just hang on for another game related update (likely tomorrow)! Hopefully though, this will help others in a similar situation, or 
provide some kind of help when dealing with OpenSSL and / or general SSL renewal automation -- you may even discover something about general Bash scripting in there somewhere.

So, I had to create a script that reliably told me when a SSL certificate was due -- WHMCS has a pretty poor implementation for how it handles SSL certificates and there is no ssl renewal implementations for the vast majority of authorities. Almost all of our certificates are GeoTrust and we generally only get 
two different types both of which are not supported properly.

So instead we have to do things the hard way. Previously the certificates on disk were enough, and checking the the domain for those certificates against WHMCS has worked out fine. However, those on disk could be for any kind of certificate. Most commonly for terminated accounts (thus the certificate doesn't 
need to be renewed) or sometimes self signed certificates for people that don't know what they are doing, or on occasion certificates from elsewhere (which is totally fine, but I don't want to have to work that out by looking up an account).

On top of that, you generally use WHM (cPanel) to generate the CSR and KEY files and this takes a lot of effort (for what you are actually doing) -- but then where do you store them? Particularly the KEY which, once lost, is unrecoverable. Do you send them on to an email account? But that would mean that a 
private key, something which should be -- you know, private -- is potentially floating around the ether; not very secure. How about store them locally on your PC? Probably not wise for similar reasons, using Windows means you are vulnerable to more root kits, keyloggers and general malware -- though Linux and 
Mac do not get away from that entirely. The main issue with doing that is you would, potentially, need a way of getting that file too and from work stations / computers. The most sensible option is to keep the file on the server -- the easiest way of doing this in my case was to send a local email account (as 
in not touching the outside world).

So below I've included the whole script in it's entirety -- I've done my best to make everything configurable (with all the settings at the top), but you can use the "help" switch for more information. I've also tried to comment on everything worth noting, but I'll include a brief summary of some of the key 
elements below the code.

Just remember that as with all my scripts, I'm not responsible for breaking anything and this is still very much a work in progress -- if I change it significantly enough, I'll make a (more condensed) post about it.

#!/bin/bash #BEGIN GLOBAL CONFIGURATION# #Debug Mode -- change to 1 to leave all temporary files and report back on cancelled / terminated certificates debugMode="0" #Test Mode -- change to 1 to print the email (only) to stdout instead of sending testMode="0" #Email address to send to (used with the "mail" switch) mailAddress="EMAIL ADDRESS" #Number of days in advance of ssl renewal to send the email dayAdvance="10" #Number of days after a new order to send the email dayAfter="1" #WHMCS Full URL location -- do not include files, include trailing slash whmcsAddress="https://YOURSITE.COM/WHMCS-LOCATION/" #Temporary files errorLog="sslRenewal/sslRenewal.err" activeLog="sslRenewal/sslRenewal.act" dueLog="sslRenewal/sslRenewal.due" tmpLog="sslRenewal/sslRenewal.tmp" #Database settings -- you should be using a my.cnf file to store the password dbHost="DATABASE HOSTNAME/IP" dbName="WHMCS DATABASE NAME" #SSL Product ID's (from WHMCS) -- separate with commas sslProducts="WHMCS PRODUCT IDS FOR SSL CERTIFICATES" #Dedicated IP ID's (from WHMCS) -- separate with commas dedicatedIPs="WHMCS DEDICATED IP IDS" #Custom Field ID's for SSL Certificate related fields -- separate with commas customFields="WHMCS CUSTOM FIELD IDS" #CA Bundle Types (ID's should not be separated with commas) sslType1Name="SSLType1" sslType1File="sslRenewal/sslType1.ca" sslType1ID=( ID ID ID ID ) sslType2Name="SSLType2" sslType2File="sslRenewal/sslType2.ca" sslType2ID=( ID ID ID ID ) #Location to store any KEY / CSR files (include trailing slash) keyStore="sslRenewal/keys/" #END GLOBAL CONFIGURATION -- No need to edit below this line# #Check for root if [[ $UID != "0" ]]; then echo "Error: You need root permissions to run this script." exit 1 fi #Show brief help message and exits if [[ ${1,,} == "help" || ${2,,} == "help" ]]; then echo -e "Tool to fetch SSL renewal data from a WHMCS database" echo -e "Usage: evo-sslrenewal.sh [SWITCH] [SECONDARY]" echo -e "\t -- default functionality is to output a list of due, active and erroring certificates and generate any KEY/CSR files" echo -e "\tmail -- used to send any certificates due for renewal to the email address specified." echo -e "\tmail debug -- outputs all the debug information along with a print out to stdout of the email that would be sent" echo -e "\tmail test -- prints the output of the email to stdout rather than sending directly" echo -e "debug -- enables debug mode with the standard output" echo -e "help -- shows this help message" exit 1 fi #Check for debug switch override (for one time only debugging) if [[ ${1,,} == "debug" || ${2,,} == "debug" ]]; then debugMode="1" fi #Check for test switch override (for one time only testing -- requires the mail parameter) if [[ ${2,,} == "test" ]]; then testMode="1" fi #Output debug warning if [[ $debugMode != "0" ]]; then echo "**********" echo "WARNING! You are running this script in debug mode." echo "**********" fi #Send email or not if [[ $1 == "mail" ]]; then sendMail=1 else sendMail=0 fi #Set global variables curDate=$(date --date="" +%Y-%m-%d) #Cleanup Temporary files for new run -- Debugging wont stop this, otherwise logs would overflow quickly if [[ $sendMail == "0" ]]; then echo "Housecleaning..." fi if [[ -f $errorLog ]]; then rm $errorLog fi if [[ -f $activeLog ]]; then rm $activeLog fi if [[ -f $tmpLog ]]; then rm $tmpLog fi if [[ -f $dueLog ]]; then rm $dueLog fi #Check if the key store location is present -- if not create it if [[ ! -d $keyStore ]]; then mkdir $keyStore fi #Get all certificates from database mysql -h $dbHost -D $dbName -e "SELECT id,packageid,domain,nextduedate,domainstatus,regdate FROM tblhosting WHERE packageid IN ($sslProducts);" > $tmpLog #Process each line -- we need to go deeper! while read line; do [[ $line =~ ^([0-9]+)[[:space:]]([0-9]+)[[:space:]](.+)[[:space:]]([-0-9]+)[[:space:]]([[:alpha:]]+)[[:space:]]([[-0-9]]+)$ ]] && lineFail=0 || lineFail=1 id=${BASH_REMATCH[1]} package=${BASH_REMATCH[2]} domain=${BASH_REMATCH[3]} duedate=${BASH_REMATCH[4]} status=${BASH_REMATCH[5]} regdate=${BASH_REMATCH[6]} #Determine what should fail if [[ $lineFail == "1" || $duedate == "0000-00-00" ]]; then #Cleanup whatever output the error gives us, so we can read it a bit better and process the ID (as that is gauranteed) errorID=${line%% *} errorEnd=${line#* } #Strip column headers if [[ $errorID != "id" ]]; then #Output errors to file -- also generates a URL to go straight to that product and sort it out echo $errorID "-" $errorEnd ":: FIX THIS! -- $whmcsAddress""clientshosting.php?&id="$errorID | egrep -v "Terminated|Cancelled" >> $errorLog else #Yo dawg; I heard you like errors, so I put an error inside your error, so you can error while you error. errorCatch=1 fi else if [[ $status == "Active" ]]; then #Log the certificate to the active log echo $id -- $domain -- $duedate >> $activeLog fi #Check what day we should be told the certificate is due checkdate=$(date --date="$duedate -$dayAdvance"days +%Y-%m-%d) newdate=$(date --date="$regdate +$dayAfter"days +%Y-%m-%d) if [[ $curDate == $checkdate || $curDate == $newdate ]]; then if [[ $status != "Active" && $debugMode == "0" ]]; then if [[ $sendMail == "0" ]]; then #Catch for terminated / cancelled certificates -- we don't need to print them to the due list certificateCancelled=1 fi else #Output due certificates to file if [[ $status == "Active" ]]; then if [[ $curDate == $regdate ]]; then echo "id -- $domain -- $regdate" >> $dueLog newSSL="***This is a new certificate***" else echo "id -- $domain -- $duedate" >> $dueLog newSSL="***This is a certificate renewal***" fi fi if [[ $sendMail == "0" ]]; then echo "$id -- $domain - $duedate" fi #Get SSL information from the database -- reverse ordering for domain catching mysql -h $dbHost -D $dbName -s -e "SELECT fieldid, value FROM tblcustomfieldsvalues WHERE relid = $id AND fieldid IN ($customFields) ORDER BY fieldid DESC;" > $tmpLog.$domain sslValues=() while read line; do [[ $line =~ ([0-9]+)[[:space:]](.+)$ ]] || infoFail=1 fieldid=${BASH_REMATCH[1]} value=${BASH_REMATCH[2]} if [[ $value != "" ]]; then #Insert values into an array sslValues=("${sslValues[@]}" "$value") fi done < $tmpLog.$domain rm $tmpLog.$domain #Create CSR and KEY values for certificate cdomain=${sslValues[5]} city=${sslValues[4]} county=${sslValues[3]} country=${sslValues[2]} company=${sslValues[1]} department=${sslValues[0]} #Check for correct country code -- auto corrects GB certificates only (fixes code to length of 2 in all cases) if [[ ${country,,} == "england" || ${country,,} == "scotland" || ${country,,} == "wales" || ${country,,} == "northern ireland" || ${country,,} == "n ireland" || ${country,,} == "uk" || ${country,,} == "united kingdom" ]]; then ccFix="GB" ccFixText=" -- CSR auto corrected to $ccFix ***Fix in WHMCS Manually***" elif [[ ${#country} != "2" ]]; then ccStrip=${country:0:2} ccFix=${ccStrip^^} ccFixText=" -- Country code was too long; stripped to first two characters ($ccFix) and hoping for the best ***CHECK WHMCS!***" else ccFix=${country^^} ccFixText="" fi if [[ $debugMode != "0" ]]; then echo "DEBUG :: $country$ccFixText" fi #Check if SSL domain field has a domain name, if not fall back to the product domain #This is a weird catch for how WHMCS handles certificates -- not all provide the option to edit the field for domain name if [[ ! $cdomain ]]; then cnDomain=$domain else cnDomain=$cdomain fi #Make sure the KEY / CSR are new if [[ -f $keyStore$domain.key ]]; then rm $keyStore$domain.key fi if [[ -f $keyStore$domain.csr ]]; then rm $keyStore$domain.csr fi #Debug mode -- output CSR details if [[ $debugMode != "0" ]]; then echo "DEBUG :: $cnDomain -- $city -- $county -- $country -- $company -- $department" fi #Generate the KEY and CSR #openssl req -nodes -newkey rsa:2048 -nodes -keyout $domain.key -out $domain.csr -subj "/C=$ccFix/ST=$county/L=$city/O=$company/OU=$department/CN=$cnDomain" openssl req -nodes -newkey rsa:2048 -nodes -keyout $domain.key -out $domain.csr -subj "/C=$ccFix/ST=$county/L=$city/O=$company/OU=$department/CN=$cnDomain" if [[ -f $domain.key ]]; then mv $domain.key $keyStore fi if [[ -f $domain.csr ]]; then mv $domain.csr $keyStore fi #Check MX records (to see if we can approve the CSR ourselves) aRecords=$(dig +short $cnDomain A) mxRecords=$(dig +short $cnDomain MX) #Package matching if [[ $package == "$sslType1ID[0]" || $package == "$sslType1ID[1]" || $package == "$sslType1ID[2]" ]]; then sslPackage="$sslType1Name" sslCA="$(cat $sslType1File)" elif [[ $package == "$sslType2ID[0]" ]]; then sslPackage="$sslType2Name" sslCA="$(cat $sslType2File)" else sslPackage="Other SSL Type" fi #Find the most likely server they are on domainPrefix=${cnDomain:0:4} if [[ $domainPrefix == "www." ]]; then domainStrip=${cnDomain:4} domainMiddle=${domainStrip%%.*} else domainMiddle=${cnDomain%%.*} fi if [[ $debugMode != "0" ]]; then echo "DEBUG :: $domainPrefix -- $domainMiddle" fi hostingGet=$(mysql -h $dbHost -D $dbName -s -e "SELECT server FROM tblhosting WHERE domain LIKE '%$domainMiddle%' AND domainstatus = 'Active' AND packageid NOT IN ($sslProducts, $dedicatedIPs) ORDER BY id DESC") if [[ ! -z $hostingGet ]]; then serverGet=$(mysql -h $dbHost -D $dbName -s -e "SELECT hostname FROM tblservers WHERE id = $hostingGet") if [[ $debugMode != "0" ]]; then echo "DEBUG :: $serverGet" fi else serverGet="Unknown" fi #Send email -- only used with the "mail" switch if [[ $sendMail == "1" ]]; then mailSubject=$(echo "SSL Certificate Renewal -" $cnDomain) keyFile="$(cat $keyStore$domain.key)" csrFile="$(cat $keyStore$domain.csr)" mailBody="$newSSL\n\nWHMCS Page: $whmcsAddress""clientshosting.php?id=$id\nHostname: $cnDomain\nProbable Server: $serverGet\nDue Date: $duedate\nA/CNAME Record(s): $aRecords\nMX Record(s): $mxRecords\nSSL Type: $sslPackage\n\n===User Input Details===\nCity: $city\nCounty: $county\nCountry Code: $country$ccFixText\nCompany: $company\nDeparment: $department\n\n===CSR===\n$csrFile\n\n===KEY===\n$keyFile\n\n===CA Bundle===\n$sslCA\n" #Debug mode will output email results to stdout if [[ $debugMode != "0" || $testMode != "0" ]]; then echo -e "===================\n$mailBody" else /bin/mail -s "$mailSubject" "$mailAddress" < <(echo -e "$mailBody") rm $keyStore$domain.key rm $keyStore$domain.csr fi fi fi fi fi done < $tmpLog #Output number of due certificates and check for debugging touch $dueLog if [[ -f $dueLog && $sendMail == "0" ]]; then echo -ne "=====\nNumber of due certificates: " dueCount=$(wc -l $dueLog) dueCount=${dueCount%% *} echo $dueCount if [[ $debugMode == "0" ]]; then rm $dueLog fi fi #Output number of active certificates and check for debugging if [[ -f $activeLog && $sendMail == "0" ]]; then echo -ne "=====\nNumber of active certificates: " activeCount=$(wc -l $activeLog) activeCount=${activeCount%% *} echo $activeCount if [[ $debugMode == "0" ]]; then rm $activeLog fi fi #Output number of errors and check for debugging if [[ -f $errorLog && $sendMail == "0" ]]; then echo -ne "=====\nNumber of erroring certificates: " errorCount=$(wc -l $errorLog) errorCount=${errorCount%% *} echo $errorCount if [[ $errorCount == "0" && $debugMode == "0" ]]; then rm $errorLog fi fi #Cleanup temp log if [[ $debugMode == "0" ]]; then rm $tmpLog fi #Exit nicely exit 0
One of the main elements to this script is to pull information out of a MySQL database -- fortunately, MySQL provides the handy command line interface and allows execution of commands right from the terminal (without having to go into interactive mode).
mysql -h $dbHost -D $dbName -e "SELECT id,packageid,domain,nextduedate,domainstatus,regdate FROM tblhosting WHERE packageid IN ($sslProducts);" > $tmpLog
Hopefully that's pretty straight forward to follow it connects to $dbHost and opens the database $dbName then using -e executes a MySQL query. Finally it writes the output to a temp file $tmpLog. I could have created a variable with a lot of my queries and loops, but I much prefer having the output on disk for logging sake. One of the main thing people tend to miss is that you can easily use arrays in MySQL queries; the WHERE something IN () command will take the values inside the brackets, and check against each one. So, rather than doing a lot of WHERE something = something OR something = somethingelse, you can simply do WHERE something IN ('something', 'somethingelse'). Possibly the next unusual part of my script is the couple of lines that look like this:
[[ $line =~ ^([0-9]+)[[:space:]]([0-9]+)[[:space:]](.+)[[:space:]]([-0-9]+)[[:space:]]([[:alpha:]]+)[[:space:]][[-0-9]]+$ ]]
Anyone who knows regular expressions will be able to pick up on what I'm doing here (very crudely, I know) -- this isn't something I've dealt with before, but using string substitution / pattern matching just wouldn't cut it for flexibility. So I had to do a bit of research around (and I can't say I found much in the way of definitive sources, just bits and pieces from all over) before I took this on. Because the MySQL query is quite broad, but it just outputs to a single line; I had to filter all the output down to what I needed. So we run an if statement to see if each line matches against the regular expression. Now, I condensed my if statements down on these lines purely for the sake readability -- I could do it across the board, but sometimes it's not flexible enough to do that. In these cases, it makes much more sense and looks neater (I know I have terrible coding habits). The very first thing we have to do is tell the if statement it's a regular expression, so we use the =~ comparison. We then start the "filter" by using the hat (^). Now, the [[:space:]] is looking for the first bit of whitespace, which is -- fortunately -- exactly what MySQL uses between columns. So the first match we are looking for (all the separate bracketed expressions) is a number of varying length (the + at the end of the square brackets means any number) -- this is the product ID. The next is the same type of item a number with varying lengths for the product ID -- the third item is a bit different, this is any *character* (so not including whitespace) and any length, which sums up a domain good enough. The forth item is the date and requires an extra hypen at the start to declare we want to include hyphens in the search. The fifth item is the status of the product (active, cancelled etc) so it's only letters (alpha) -- then the final part is another date. We then close the regular expression with the $, to say this is the end of the filter and close the if statement. In case you don't know how these condensed if statements work these are equivalent:
word="something" if [ $word == "something" ]; then echo "Match!" else echo "No match!" fi [[ $word == "something" ]] && echo "Match!" || echo "No Match!"
The end of the condensed if statement is equivalent to the "then" part of a normal if statement. Though you need to use && to signify you want to run another command after the if statement (much like the new line after "then"). The double pipe (||) is the same as else, or more specifically like the OR type of condition (if this OR that, if this || that). So, back to the regular expression. I always try and assign friendly(er) variable names where possible, and with the bash rematch option, this is determinately needed to stop you going loopy. To output parts of the expression you are matching, you need to use ${BASH_REMATCH[item]} -- HOWEVER, something to note -- unlike Arrays, 0 (zero) is *not* the first item. 0 (zero) is the entire match, so be careful using it. Everything else is exactly like an array. The last major part of this script is generating an SSL Certificate KEY and CSR (certificate signing request). if you wanted to just make self signed certificates you can go one step further, but this was unnecessary for me as we only manage certificates that have been paid for (ie. are issued from an authority, like GeoTrust).
openssl req -nodes -newkey rsa:2048 -nodes -keyout $domain.key -out $domain.csr -subj "/C=$ccFix/ST=$county/L=$city/O=$company/OU=$department/CN=$cnDomain"
OpenSSL is an amazing tool, that most distros have installed by default, which allows you to generate certificates. The most important parts of this command are the -newkey switch which tells openssl to generate a new key based on the next command, rsa:2048 tells it that the key should be an RSA encrypted key using a 2048 bit hash size. We then say we want the key to be called $domain.key and finally we generate a CSR with the -out switch and call it $domain.csr. Now, you can leave it at that and then it will open an interactive prompt that allows you to fill in all the details for the certificate by hand -- but that's boring and takes time. Instead we can use the -subj switch and tell it exactly what details we want. C is the country code, ST is the county / state, L is the city / town, O is the company, OU is the department and CN is the domain name itself. And as simple as entering that in and running it -- we get a new KEY and CSR file generated for out domain, based on it's corresponding details. Whoop. I wont go through the rest of the script as it's pretty self explanatory if you understand the basic principles, but I thought I'd explain the main function a little, just in case you were looking for those specifically.