Sequelize using SQLcipher to encrypt DB

juancampuzano commented 4 years ago

Issue Description

I am trying to encrypt the database using SQLCipher. In the Sequelize documentation it says that it is possible to do: "Supports SQLCipher encryption for SQLite." But in the documentation I can't find the steps of how to do. I am trying to follow the following example found in Stackoverflow but I get the error: "SQLITE_NOTAD"

StackOverflow / Slack attempts

Is this issue dialect-specific?

[ ] No. This issue is relevant to Sequelize as a whole.
[x] Yes. This issue only applies to the following dialect(s): SQLCipher on Sequelize
[ ] I don't know.

Would you be willing to resolve this issue by submitting a Pull Request?

[ ] Yes, I have the time and I know how to start.
[ ] Yes, I have the time but I don't know how to start, I would need guidance.
[ ] No, I don't have the time, although I believe I could do it if I had the time...
[x] No, I don't have the time and I wouldn't even know how to start.

papb commented 4 years ago

Hello, can you please try a few things:

Try with the first version of sequelize that had support for this feature (in this case, v4.0.0-1)
See if you can do it without sequelize using the sqlite3 package directly
Try to create a sequelize-sscce for it considering that I have no idea how SQLCipher works (so try to make it 100% ready for test)

Let me know, thanks

juancampuzano commented 4 years ago

I was testing with version v4.0.0-1 but I had the same problem.

However, I have the following question: is it possible to encrypt the database using sequelize in other way?

How can I run the following command PRAGMA key = 'mysecret' when starting sequelize?

papb commented 4 years ago

I am not familiar with database encryption unfortunately. I would suggest you try a direct raw query as early as possible in your code: sequelize.query("PRAGMA key = 'mysecret'") but I don't know if it should work.

juancampuzano commented 4 years ago

@papb it works.

Base on node-sqlcipher, I only have to put the following code:

  const sequelize = new Sequelize('database', '', 'your-encryption-key' {
    dialect: 'sqlite',
    dialectModulePath: '@journeyapps/sqlcipher',
    storage:  'path/to/db.sqlite',
  });

  // SQLCipher config
  sequelize.query('PRAGMA cipher_compatibility = 3');
  sequelize.query("PRAGMA key = 'your-encryption-key'");

papb commented 4 years ago

Fantastic!! I will leave it open as a docs issue :grimacing:

juancampuzano commented 4 years ago

Do you create the documentation?

rohit5ram commented 4 years ago

@juancampuzano @papb How can we default it to use sqlcipher 4 without cipher_compatibility? I have opened an issue in node-sqlcipher too https://github.com/journeyapps/node-sqlcipher/issues/33

github-actions[bot] commented 2 years ago

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

sequelize / website