0

I'm storing a xls file in a MySQL table and before saving I've encrypted it.

The file is encrypted using PHP:

$key='------------'; // not the real key or iv.
$iv = "*********";
$file = file_get_contents('original.xls');
$file = openssl_encrypt($file, "AES-256-CBC", $key, 0, $iv);

and then inserted into an existing MySQL table.

In PHP I can download this using a select statement and then decrypt and save it as follows using the same key & iv:

$file = openssl_decrypt($file, "AES-256-CBC", $key, 0, $iv);
file_put_contents('downloaded.xls', $file);

I'm now trying to do the same via a bash script.

The encrypted data is read from MySQL using:

xls=$(mysql -N -u root --password=1234 test -e "SELECT foo FROM bar where id = '1'")

I'm then trying to decrypt and save it using:

key='------------'
iv = "*********"
ivHex=$(printf "%s" "$iv" | hexdump -ve '/1 "%x"')
keyHex=$(printf "%s" "$key" | hexdump -ve '/1 "%x"')
file=$(echo -n $xls| openssl aes-256-cbc -d -a -A -K $keyHex -iv $ivHex)
    
echo $file > bash.xls

Unfortunately this results in the error "command substitution: ignored null byte in input", the file is saved but it is smaller than the original file and corrupt.

If I do this using plain text in the MySQL database, then I can download and decrypt it fine using php or bash.

Can anyone advise what is causing this and how I can resolve it ?

thanks

I should have said I have tried using tr -d '\0' but that resulted in the same error message.

Tom
  • 1,436
  • 24
  • 50
  • @Amadan that may help, but to honest I'm not sure how to implement it. Could you offer some advise ? Thanks – Tom Jan 31 '22 at 10:28
  • 1
    The issue is that bash does not allow storing `\0` in a variable (or a command argument either, for that matter). And you can't simply ignore `\0`, as it would change the encrypted file. So you will want to do this without an intermediary variables `$file` and `$xls`, which could potentially contain it: `mysql .... | openssl .... > bash.xls` – Amadan Jan 31 '22 at 10:35
  • @Amadan - that makes sense. I've tested this and it is working. Could you create this as an answer for me to accept. Thanks – Tom Jan 31 '22 at 11:10

0 Answers0