Translate

Tuesday, February 24, 2015

Hashing with LibreOffice

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.

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
  • 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


Update


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.

4 comments:

  1. This works. Use Ctrl+Shift+End then Ctrl+D to paste it to all column

    ReplyDelete
  2. doesnt work that easy, this is me were talking about. ive never done this before...any of this. i honestly dont think ive even ever made a spreadsheet with functions

    ReplyDelete
    Replies
    1. Agree on the non easy thing.
      But it was useful for me :)

      Delete