I need disable encryption when save skrooge file.
I have a process using PHP and i need append records automatically in .skg file and i need read some records from this file too.
I have looked in settings but i can find something as "disable encryption" checkbox.
I removed password but the output file is using sqlcipher but i need direct sqlite db access.

Hi,

For security reason, Skrooge 2.x uses sqlcipher instead sqlite.
It means that documents are always encrypted.
Moreover, the skg file is not directly a sqlcipher file, this is sqlcipher file with a special header.

If you want to develop an automatic treatment in PHP, you have to:
1- Copy the skg document and remove the header (The 15 first characters).
2- Use SQLCIPHER for PHP: https://www.zetetic.net/sqlcipher/sqlcipher-for-php/.
3- Use you password to open the document, if no password set, then the password is "DEFAULTPASSWORD".
4- Do the modifications.
5- Don't forget to add the header to the file.

I hope this will help you.

Thanks for your answer.

I have installed sqlcipher in PHP but i can't connect to database.

I think that the signature length in skg files is 18 (SKROOGE_ENCRYPTE2-) not 15 so i am doing:

Code: Select all

$length = 18; // Tested from 15 to 18
// Save original file without header.
file_put_contents(
    '/tmp/skg.db',
    substr(
        // Read original file
        file_get_contents('/path/to/original.skg'),
        $length
    )
);
$file_db = new PDO('sqlite:/tmp/test.skg');
// When i save file with password 'abc'
$file_db->exec("PRAGMA key='abc'");
// When i save file with empty password
$file_db->exec("PRAGMA key='DEFAULTPASSWORD'");
$file_db->query('SELECT * FROM skg.sqlite_master');
print_r($file_db->errorInfo());


Code: Select all

Array
(
    [0] => HY000
    [1] => 26
    [2] => file is encrypted or is not a database
)


I have done the tests:

- Using length from 15 to 18
- Save skrooge file with empty password and with 'abc' as password.

And i get the same error.

I tested with SQLiteBrowser with SQLCipher support and i get 'Invalid format' error.
I understand security reasons for to encrypt files, but i think that the user must be able to select if encrypt or not their files.

I like Skrooge (i come from KMyMoney) but i can't use it now if i am not able to integrate with my webapp.

Thanks for your work.

Oups, I forgot: there is a double encryption, so the process is more complex.
But, you can apply it easy by using skroogeconvert like this:

Code: Select all

skroogeconvert --in file.skg --out file.sqlite

Then you just have to establish the connection with the sqlite database named file.sqlite.

I hope this will help you.

Yes, i know about double encryption. I was looking in skrooge source code.

skroogeconvert could not convert the files:

Code: Select all

$ skroogeconvert --in test.abc.skg --out /tmp/skg.db --param password --value abc

kf5.kcoreaddons.kaboutdata: Could not initialize the equivalent properties of Q*Application: no instance (yet) existing.
################################################
## Import parameters
## password=abc
################################################
## Imported file:file:///path/to/test.abc.skg
################################################
## Exported file:file:///tmp/skg.db
[ERR-1]: This export mode is not yet implemented
################################################
## FAILED
################################################


Export mode seems not implemented yet
The notice in first line may seem interesting to you.

After reading all your code, mainly SKGDocument and SKGServices, i was able to open skrooge file using this code (list all tables in skg file):

Code: Select all

<?php
/**
 * Basic class for open unencrypted skrooge files
 *
 * @author joaquinfernandez
 */
class Skrooge
{
    /**
     * Header for unencrypted skrooge files.
     */
    const HEADER = 'SKROOGE_DECRYPTE2-SQLCipher format';

    /**
     * Default password used in skrooge files.
     */
    const PASS = 'DEFAULTPASSWORD';

    /**
     * SQLite DB handler.
     *
     * @var null
     */
    private $_db = NULL;

    /**
     * Path to skrooge file.
     *
     * @var string
     */
    private $_skgFile = '';

    /**
     * Class constructor.
     *
     * @param $skgFile string Path to skrooge file.
     */
    public function __construct($skgFile)
    {
        $this->_skgFile = $skgFile;
    }

    /**
     * Open skrooge file.
     *
     * @return null|SQLite3
     */
    public function open()
    {
        $_skgFile = $this->_skgFile;
        if (strncasecmp('.skg', substr($_skgFile, -4, 4), 4))
        {
            throw new \InvalidArgumentException('Skrooge file must end in .skg');
        }
        else
        {
            $_tmpdb = $_skgFile . '.db';
            file_put_contents(
                $_tmpdb,
                substr(
                    file_get_contents($_skgFile),
                    strlen(self::HEADER)
                )
            );
            $this->_db = new SQLite3($_tmpdb);
            $this->_db->exec(
                sprintf(
                    "PRAGMA key='%s'",
                    self::PASS
                )
            );
        }

        return $this->_db;
    }
}
//------------------------------------------------------------------------------
// Script main
//------------------------------------------------------------------------------
$skrooge = new Skrooge('/path/to/db.skg');
$db = $skrooge->open();
$rows = $db->query("SELECT * FROM sqlite_master WHERE type = 'table'");
if ($rows)
{
    echo "TABLES:\n";
    while ($row = $rows->fetchArray(SQLITE3_ASSOC))
    {
        echo $row['name'] . PHP_EOL;
    }
}
else
{
    echo $db->lastErrorMsg() . PHP_EOL;
}
 

Result:

Code: Select all

TABLES:
parameters
sqlite_sequence
node
doctransaction
doctransactionitem
doctransactionmsg
unit
unitvalue
bank
account
interest
category
operation
operationbalance
refund
payee
suboperation
recurrentoperation
rule
budget
budgetsuboperation
budgetrule
vm_budget_tmp
sqlite_stat1


I put the code here because somebody may need it.

Regards.

Try this:

Code: Select all

skroogeconvert --in test.abc.skg --out /tmp/test.abc.sqlite --param password --value abc

The extension of the file is important to know what kind of conversion is requested.