Hashing with LibreOffice (Updates)

For a project, I needed to hash some data from a spreadsheet.
I'm using LibreOffice Calc (4.3.1.2 for Windows - 4.2.7.2 for Linux) and there is no direct support for the hash.

 

Update !! Notes

I wrote this piece back in 2015 !!! 10 years ago !
Things changed a lot since then and amazingly I'm still using  this function.  Lately though, installing and using new versions of Libreoffice is more and more problematic since the macro was never updated.

Here some notes to try to create a working version of Libreoffice capable to run the crypto macro.

First of all, the latest version of Libreoffice capable to work with the macro is the 7.0.6.2 as determined from some experiments I did.
So forget to install the latest version in a normal Linux distro.

It is possible to download the version from here.

Then is possible to install it also if newer versions of Libreoffice are present, at least on Linux is possible.

Docker experiment

I'm exploring the possibility to run this version somehow in docker.
In order  to try this route is of course necessary to have docker and some utilities installed on the machine.

I assume to have installed :

• docker
• docker compose
• docker desktop

It is necessary to prepare few things before to start.

1. create a directory -- it will be used to store settings and access to the computer
   I usually call it : dockerPS
   Under this directory create a specific subdirectory for libreoffice, for example : libreoffice
   
   Note !!!  It is possible to use another place where spreadsheets need to be accessed, for example in my case I have some under dropbox.
   So the directory I use is actually the local Dropbox one.
   
   What happens is that the directory pointed in the -v parameter is accessible as "config" in the docker container !!!
   
   In any case in the place pointed by the script (line -v) should be present the downloaded crypto extension.
   
   
2. Create a script somewhere, remember to give execution permission.
   I called the script ... start-libreoffice ☺
   
   The line -e TZ=Etc/UTC can be omitted to keep the machine time
   The line --mount should point to a place where is possible to access files and the spreadsheets to work on and where I suggest to store also the extension for hashing to be used in the spreadsheets.
   
   
docker run -d \
--name=libreoffice \
--security-opt seccomp=unconfined \
-e PUID=1000 \
-e PGID=1000 \
-e TZ=Etc/UTC \
-p 3000:3000 \
-p 3001:3001 \
-v <user>/dockerPS/libreoffice:/config \
--mount type=bind,src=<directory where documents are>,dst=documents-mount \
--restart unless-stopped \
lscr.io/linuxserver/libreoffice:latest


3. Be sure docker is running
   
   
4. Execute the script : ./start-libreoffice
   Docker will build the container using the latest version of libreoffice
   
   
5. Open the browser and type this URL : localhost:3000
   Libreoffice will be accessible from there.
   
   
6. Follow the instructions below to add the extension and the macro.

Note !!!  I left the "latest" version for libreoffice and the macro is happily working.
Probably the docker environment has everything needed.

So the "latest" version installed directly on many Linux machines is NOT working with the crypto extension, but the docker one yes !

VERY VERY IMPORTANT !!!
Once the extension is installed in the Libreoffice on the container, IT REMAINS THERE !!
If you stop the container and then restart it, everything will still work.
HOWEVER, if the container is destroyed or rebuilt, it will be necessary to reinstall the extension and the macro as described above !!

Let see how is possible to obtain the hash using LibreOffice Calc.

Cryptographic extension

It is available a third party extension that supports the hash.

I didn't find much direct and clean information about how to use it, so here my experience with it.

Installation

• Download the file cryptographic.oxt from the link
• Open LibreOffice Calc
• Go on Tools/Extension Manager
• Add the extension downloaded
  
• Be sure the extension is Enabled !!  (right click on the extension, be sure is enabled)
• Create a macro to support the extension

To create the macro, follow these steps :

• Go on the Tools/Macros/Organize Macros/LibreOffice Basics
• Select MyMacros
• Click on the New button
• Copy this macro (available in the Example.zip file on the same link) :

REM Propósito: Computes hashes (message digests) of text and files using following hashing algorithms: MD2, MD5, SHA-1, SHA-256, SHA-384 and SHA-512

REM Informações sobre o Módulo: Módulo Padrão = basMain

REM Convenção para nomeclatura das versões: a.mm[.dd]: Nome do Revisor (e-mail)

REM Autor: Claudio Gomes Riella (cgriella@ufrgs.br)

REM Versões:

REM 1.10.01 (1º/10/2009): Claudio Gomes Riella (cgriella@ufrgs.br)

REM Parâmetros<-Argumentos: Main

REM FileHash(strFullPathName as String, strAlgorithm As String) <- Calc function FILEHASH(Cell; Algorithm)

REM TextHash(strText as String, strAlgorithm As String) <- Calc function TEXTHASH(Cell; Algorithm)

REM Resultados: return message digests

REM Requisitos mínimos: BrOffice.org 3.1.0 ou OpenOffice.org 3.1.0 + Cryptographic Service 1.10.01

REM Licença: LGPL

Sub Main

Dim strFullPathName as String, sbNewLine as String

sbNewLine = Chr(13) + Chr(10)

strFullPathName = inputbox ("Insert the path of file")

msgbox "File is """ & strFullPathName & """" & sbNewLine & "FileHash: " & FileHash(strFullPathName, "MD5") & sbNewLine & "TextHash: " & TextHash(strFullPathName, "MD5")

End Sub

Function FileHash(strFullPathName as String, strAlgorithm As String)

Dim objDecrypt As Object

Set objDecrypt = CreateUnoService("org.openoffice.Cryptographic.CryptographicService")

FileHash = objDecrypt.GetFileHash(strFullPathName, strAlgorithm)

End Function

Function TextHash(strText as String, strAlgorithm As String)

Dim objDecrypt As Object

Set objDecrypt = CreateUnoService("org.openoffice.Cryptographic.CryptographicService")

TextHash = objDecrypt.GetTextHash(strText, strAlgorithm)

End Function

The functionality should be present.
To test, open a sheet and in a cell write : =TEXTHASH("test", "MD5")
If is working the hash :
98f6bcd4621d373cade4e832627b4f6
should be calculated.

Use

There are two main functionalities:

• TEXTHASH
• FILEHASH 

TEXTHASH

It calculate the hash using a text input.
Is possible to use different fileds concatenting them.
The macro requires two input:

• the input text
• the required algorithm from this list : MD2, MD5, SHA-1, SHA-256, SHA-384 and SHA-512

Valid Examples :
=TEXTHASH("Test string", "MD5")

=TEXTHASH(A1, "MD5")
=TEXTHASH(CONCATENATE(A1, A2), "SHA-256")

FILEHASH

It calculate the hash using a file. The input is the path of the file.
The macro requires two input:

• the file path/name
• the required algorithm from this list : MD2, MD5, SHA-1, SHA-256, SHA-384 and SHA-512

Valid Examples :
=FILEHASH("/testpath/file", "MD5")
=TEXTHASH(A1, "SHA-256") where A1 contains a valid path and file name

Problems

There are two problems I found so far.

1. Sometime the result are 39 digits instead of 40.
2. The hash calculation differs from the Linux version vs. the Windows version.

39 digit problem

The problem seems related to removing a hash number leading zero.

Different Hash calculation 

Didn't find yet an explanation. It could be related to a different Java version and thus different Java basic functions.

Resources

• https://docs.linuxserver.io/images/docker-libreoffice/