Instructions on How to for de-encrypting SMAT DB from the command line.

Do you have Charlie Bursell’s hcismatdb utility?

Tcl syntax to open an encrypted DB file:

<sqlite command> <dbHandle> <dbFilePath> -readonly <boolean> -key <encryptKey>

Once open, you can query the DB using dbHandle as usual

Lawrence,

Well I would simply place a command line entry for hcismatdb in a O/S script Korn shell or whatever you are using with the appropriate arguments- or – you can exec hcismatdb from inside a Tcl proc with the appropriate arguments. Your choice.

But since I think you said the goal was to create a file of extracted entries from SMATDB I suspect you just want to place the file in a directory for subsequent processing so a O/S script would probably work well.

Maybe we should discuss off-line – email me.

You want to copy the database as unencrypted to another location?   Remember an SQLite database is simply a file and can easily copied.

As for the encryption; 2 options.

Use as unencrypted on Cloverleaf or open the SMAT Database using command line logic provided and copy to another database.  Here, Google is your friend 🙂

From the web:

<b>SQLite</b> doesn’t support <b>encrypting</b> database files by default. Instead, you need to use a modified version of <b>SQLite</b> like SEE, SQLCipher, SQLiteCrypt, or wxSQLite3.

The Cloverleaf version uses SQLCipher

Probably what you are using has a different encoding method with the sqlite version.

From the command line using the version of sqlite installed with Cloverleaf, with or without a root set I can easily do the following with an encrypted SMAT file:

sqlite myob.smatdb
SQLCipher version 3.15.2 2016-11-28 19:13:37
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite> PRAGMAY key = “cbtest”;
Error: near “PRAGMAY”: syntax error
sqlite> PRAGMA key = “cbtest”;
sqlite> .tables
smat_info smat_msgs

sqlite> .quit;

Maybe move the encrypted DB to unencrypted for your use?  See site below:

I have not personally tested this method but give it a try and let us know

https://stackoverflow.com/questions/25132477/how-to-decrypt-an-encrypted-sqlcipher-database-file-on-command-line#:~:text=Decrypt%20the%20database%20to%20a%20plaintext%20database&text=db%20sqlite%3E%20PRAGMA%20key%20%3D%20&#8242;,decrypted%20database%20at%20~%2Fplaintext.

More info:
I just installed DB Browser for Windows from: https://sqlitebrowser.org/dl/

It installed two executables;  DB Browser (SQLite) and DB Browser (SQLCipher)

I was able to decrypt and view a SMAT file using SQLCipher.

I had to change default Encryption Settings from “SQLCyphe 4 defaults” to “SQLCypher 3 defaults”

But I was then able to enter my site name for password and view and or manipulate the DB.

I hope this helps.

What version of Cloverleaf were these SMAT DB files created under?

If they were created pre-19.1 you will have to use hcismatcrypt as I outlined in the post about using hcidbscript to migrate them to SQLCipher.  You can then open them using DB Browser(SQLCipher).  I have tried and know this works.

If you do not have Cloverleaf 19.1+ you may need to update.  I am not sure what encryption methods were used by previous versions of Cloverleaf.

hcismatcrypt  is a utility provided with Cloverleaf.  Maybe some misunderstanding about hcidbscript , probably a typo.  Here I was referring to the script hcismatdb which you have.  Again, please see my post about using hcismatdb for more info on hcismatcrypt.

The hcismatcrypt command is available in version 6.2 but no migrate option.  You can use this command to validate the password or change the password of your SMAT files. Look for the command in your Cloverleaf documentation.

Lawrence:

I was playing around with this on my old version of Cloverleaf 6.2.  Here is what you do

setroot and setsite to where SMAT files are located

First, run:  hcismatcrypt validatekey  <Name of SMAT File> <Site Name>

It should return “Successful for command validatekey” if key is correct else it will return:
“Validation failed.
Incorrect password”

If key is not correct and you cannot open as unencrypted, I am at a loss.  you will have to get your key.  Call Support, they have methods.

If the key is correct then run:
hcismatcrypt.htc rekey <sitename> <key>  <SMAT FILE>  “” <NEW FILE>

Note that sitename and key are probably the same.  SMAT FILE is the file to copy
“” means no key.  NEW FILE is name of file to create, i.e., xxx.db.  The give the new file to your analyst.

The new file will be a copy of your SMAT file unencrypted.  I tried it and it works for me.

Good Luck!!

The double quotes represent a null for encryption key.  It should result in a file with no encryption.

If the SMAT file is in use it would make sense to stop it first.

Glad you got it working.

Works for me.  Did you try it?