We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How To: Decrypting MSQL Fields

asked 2019-02-14 02:01:31 +0200

kasloman gravatar image

I have this MySql query:

SELECT IDno, Company, des_decrypt(AccountNo, "myPassWord") AS 'AccountNo' FROM account;

How do i do this in LibreOffice Base with its HSQLDB engine?

Thanks, R

edit retag flag offensive close merge delete


Your question doesn't seem clear. Are you using MySQL from Base or are you switching to HSQLDB? HSQLDB doesn't appear to have des_decrypt function.

Ratslinger gravatar imageRatslinger ( 2019-02-14 02:27:40 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2019-02-14 07:19:20 +0200

kasloman gravatar image

updated 2019-02-14 08:03:49 +0200

I am connected to a MySql database.

SELECT "IDno", "Company", cast(DES_DECRYPT("AccountNo", "myPW")AS char(50)) AS "AccountNo" FROM "account";

Returns - "The data content could not be loaded. Unknown Column myPW in field list.

edit flag offensive delete link more


@kasloman Please do not respond by using an answer unless it is an answer to the original question. Instead use add a comment which is below each answer. If clarity is to be added to question, then simply edit the question & note the edited portion.

Ratslinger gravatar imageRatslinger ( 2019-02-14 08:55:51 +0200 )edit

Many thanks. Worked like a charm changing "myPW" to 'myPW' in single quotes.


kasloman gravatar imagekasloman ( 2019-02-14 17:44:14 +0200 )edit

answered 2019-02-14 04:00:46 +0200

Ratslinger gravatar image


While waiting for comment response, ran some simple tests using Mint 18.3, LO v6.2.0.3 and MySQL connection. Only problem encountered was returned item was an object. Cleared this problem by casting the to a Char field then worked OK.

Data entered without password:

SELECT cast(DES_DECRYPT(testField) AS CHAR(100)) AS "DeCrypted" FROM test;

Data entered with password:

SELECT cast(DES_DECRYPT(testField, 'XXXX') AS CHAR(100)) AS "DeCrypted" FROM test;

If you have Base connected to MySQL, then HSQLDB is not involved.

edit flag offensive delete link more


@kasloman Yes the error is because you have indicated a column name instead of a literal. If you look at the example in my answer, the field name is surrounded by quotes and the password is surrounded by apostrophes. DES_DECRYPT is looking for the field to decrypt & if originally password protected, the password literal used in the encrypt function.

Ratslinger gravatar imageRatslinger ( 2019-02-14 09:00:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-14 02:01:31 +0200

Seen: 55 times

Last updated: Feb 14 '19