Description:
When the encryption is been activated for the JotForms, exports of the forms in CSV format are saved with almost all fields in RSA encrypted format. This script below allows to decrypt such CSV files.
Problem:
Since the private key is only saved at the JotForm user’s side, the JotForm server has no way of decrypting the forms content before they are exported in CSV format. Since the Key file is only usable by the user’s Browser, so far there has been no method found to let the user decrypt his saved CSV format files using his private key file.
Solution:
Each field of the database of the original data is encrypted separately using openSSL and RSA asymmetrical key encryption. In order to decrypt the CSV file, each field’s encrypted string need to be decrypted separately as follows: (automatized in the script)
echo "<encrypted string>"| openssl enc -d -a \ | openssl rsautl -decrypt -inkey <keyfile>
Difficulty:
Some fields data have been fragmented and each fragment separately encrypted. That means that some fields contain multiple separately encrypted parts separated by either ‘spaces‘ or ‘(…)’ or ‘–‘ or ‘#Jot#‘. The script replaces each of those ‘separators’ by a space and then processes the decryption of these individual encrypted strings separately, filling the results in its single appropriate field in the output file.
Failures:
Some encrypted fields are impossible to be decrypted because the encrypted string has been corrupted and don’t contain the normal ‘==’ needed characters at the end of the string. The script decrypts only the fields where such character combination are found. The result is that these corrupted fields will be transported to the output file ‘as is’ and will need to be cleaned up manually later. I tried to add “==” at the end of some of these strings, but the decryption still failed. Whether the corruption happened during the encryption or just in the creation of the CSV file is still unknown.
Remarks:
This script is not perfect. If any of you feels fit to improve it, I would be glad to get your version as a comment of this post. Thank you.
Script: jotcvsdecrypt.sh
#!/bin/bash
# Name: jotcvsdecrypt.sh
# Purpose: decrypt an encrypted CVS exported JotForm file using the private key file
# Depends: JotForm encryption private key file. Default: jotform.key in the current running directory
# Syntax: jotcvsdecrypt.sh -i {input_encrypted_filename} -o {output_decrypted_filename} -k {private_key_filename}
# Description: this script will:
# - Find the number of fields in the input CVS file from the first line
# - Copy the fields description line(first line of the input file) to the output file
# - Scan each subsequent line of the input file: decrypt the appropriate fields and fragments, and write the results into the output file
# Console output: Record numbers and fields decryption progress will be displayed.
# Legend: 'd' - Decrypted field
# '-' - Passthrough(Not decrypted) field
# Author: Michel Bisson (michel-at-itmatrix.eu)
# Changes: 19.12.2019 First implementation
#---------------------------------------------------------------
# Constants:
# Set default RSA Private key filename
defKEYFILE=jotform.key
# Functions
usage() { echo "Usage: $0 [-i inputCVSfile] [-o outputCVSfile] [-k jotformKEYfile]" 1>&2; exit 1; }
# Loading the arguments
while getopts ":i:o:k:" o; do
case "${o}" in
i)
INFILE=${OPTARG}
;;
o)
OUTFILE=${OPTARG}
;;
k)
KEYFILE=${OPTARG}
;;
*)
usage
;;
esac
done
shift $((OPTIND-1))
# Set the KYFILE to Default if not given
if [ -z $KEYFILE ]; then
echo "KEYFILE argument was not given. Setting to default KEYFILE: $defKEYFILE"
KEYFILE=$defKEYFILE
fi
# Exiting if no keyfile can be used
if ! [ -e $KEYFILE ]; then
echo "KEYFILE $KEYFILE does not exist"
usage
fi
# Make sure the input and output CVS files were given
if [ -z "${INFILE}" ] || [ -z "${OUTFILE}" ]; then
usage
fi
# Make sure the input file does exist
if ! [ -e $INFILE ]; then
echo "KEYFILE $KEYFILE does not exist"
usage
fi
# Things look good so far, lets start
IFS=","
# Find out the number of fields from the first line of the input SVS file
for field in $(head -1 $INFILE); do let nFIELDS++ ;done
unset IFS
# Good for debugging
echo "INFILE = ${INFILE}"
echo "OUTFILE = ${OUTFILE}"
echo "KEYFILE = ${KEYFILE}"
echo "Number of fields = $nFIELDS"
nRecords=$(wc -l $INFILE | awk '{print $1}')
nRecords=$[${nRecords}-1]
echo "Number of records = $nRecords"
length=${#nRecords}
# Create a new output file and copy the fields description line (first line) of the input file 'as is' to the output file
head -1 $INFILE > $OUTFILE
#---------- Start the decryption ------------------
# Decrypt one line at the time
# Skip the first fields descriptions line and start decrypting
record=0
sed 1d $INFILE | while read line ; do
let record++
echo -n "Record No. $(printf "%0${length}d" $record) : "
# Scan all fields of CVS line and decrypt only the encrypted fields otherwise copy them 'as is' to output file
for nFIELD in $(seq 1 $nFIELDS); do
data=$(echo $line | cut -d, -f${nFIELD})
if (echo $data | grep -q '=='); then
# Cleaning up some disturbing characters and replace encrypted string delimiters with a space char.
clnDATA=$(echo $data | sed -e "s/\'//g" -e 's/\"//g' -e 's/(//g' -e 's/)//g' -e 's/\-/ /g' -e 's/#Jot#/ /g')
# ************** DECRYPTION *****************
if $(echo $clnDATA | grep -q " "); then
# Fields with fragmented data (containing spaces as delimiters). Each fragment is decrypted here separately
allDATA=""
for sDATA in $clnDATA ; do
allDATA="$allDATA $(echo $sDATA | openssl enc -d -a | openssl rsautl -decrypt -inkey $KEYFILE)"
done
else
# Here the field has single encrypted data string without spaces
allDATA="$(echo $clnDATA | openssl enc -d -a | openssl rsautl -decrypt -inkey $KEYFILE)"
fi
echo -n "\"$allDATA\"," >> $OUTFILE
echo -n 'd'
else
echo -n "$data," >> $OUTFILE
echo -n '-'
fi
done
echo
echo >> $OUTFILE
done