import database about standardfile

[cyberb]

The peoblem is that we publish this app into our Syncloud Store and most of the users will probably just upgrade without realising they are losing their notes.

[mdouchement]

In my previous comment I thought that re-authentication would fix this issue but it's more complex.

First the password field from sqlite3 is a sha256 of the original password (the same sha256 password that is stored in plaintext in the pw_hash claim of the JWT token). sha256 does not salt the password so they can be retreived using rainbow tables or hashcat like md5 was back then.

On the mdouchement/standardfile side, the password is stored in database using the argon2id algorithm which is a more secure way to store password (plus mdouchement/standardfile doesn't expose the pw_hash claim in the token since it's not really needed. See JWT revocation strategy after password update).

There is no way to convert the sha256 hash to an argon2id since we need to know the orginial password (maybe with a rainbow table or hashcat but that's not legal).
We cannot change the password since the orginal password is tightly linked to the keys that encrypt items in the client side.
Users can update their passwords but the procedure requires both a valid argon2id password in database and a valid token.

As far as I think, there are 3 solutions:

• The user should "export notes" before running the migration and restore them after.
• Provide a form during the migration process where the user can provide its StandardNotes' password
  • From that password, we need to generate the symetric key pair and extract the password used to authenticate to the server
    • NB: This action also exposes all the keys used to decrypt notes
  • This password should be hashed with argon2id
  • Then stored in database
• In the Syncloud's App Center, provide a new "mdouchement/standardfile" app and explain to the user how to migrate
  • Deprecate the previous app

A dirty solution is to run a hacked version of mdouchement/standardfile wich allows JWT tokens' pw_hash claim that match with the one from sqlite3 AND allows users to update their password without checking the original password.
But there is an issue with that, the user doesn't know that he have to update its password. It also requires the same JWT's SigningKey. IMHO it's not a reliable way to perform the migration.

[sentriz]

I think it's easiest to use the "export notes" feature in standardnotes web, then importing again with ya-standardfile

[mdouchement]

As sentriz said, it's the easiest way but seems not possible in your case.

The models' fields are pretty the same so it should be possible to perform a migration by coding a custom tool that queries sqlite3 source database and creates entries in the new database.

For each table, I will instanciate the corresponding model and save in the new database. Then i'll start the new server and check everything is OK by using https://app.standardnotes.org with the new API endpoint.

NB1: This project only supports V2 and V3 versions.
NB2: After the migration, users should have to re-authenticate

[cyberb]

Is there any way to tell which version is the data for?

[mdouchement]

At the begining of the item's content or enc_item_key the version should be present.
It looks like tectiv3/standardfile is not storing the version in the user table.

[cyberb]

Tried with this code setting to v4, but cannot login with this error:

could not validate password: argon2: the encoded hash is not in the correct format

Do you have any idea?
Should I try v3/v2?

Importer code:

type User struct {
	UUID      string    `json:"uuid"`
	Email     string    `json:"email"`
	Password  string    `json:"password,omitempty"`
	PwFunc    string    `json:"pw_func"     sql:"pw_func"`
	PwAlg     string    `json:"pw_alg"      sql:"pw_alg"`
	PwCost    int       `json:"pw_cost"     sql:"pw_cost"`
	PwKeySize int       `json:"pw_key_size" sql:"pw_key_size"`
	PwNonce   string    `json:"pw_nonce,omitempty"    sql:"pw_nonce"`
	PwAuth    string    `json:"pw_auth,omitempty"     sql:"pw_auth"`
	PwSalt    string    `json:"pw_salt,omitempty"     sql:"pw_salt"`
	CreatedAt time.Time `json:"created_at"  sql:"created_at"`
	UpdatedAt time.Time `json:"updated_at"  sql:"updated_at"`
}

type Item struct {
	UUID        string    `json:"uuid"`
	UserUUID    string    `json:"user_uuid"    sql:"user_uuid"`
	Content     string    `json:"content"`
	ContentType string    `json:"content_type" sql:"content_type"`
	EncItemKey  string    `json:"enc_item_key" sql:"enc_item_key"`
	AuthHash    string    `json:"auth_hash"    sql:"auth_hash"`
	Deleted     bool      `json:"deleted"`
	CreatedAt   time.Time `json:"created_at" sql:"created_at"`
	UpdatedAt   time.Time `json:"updated_at" sql:"updated_at"`
}

func Import(fromDb string, toDb string) error {

	db.Init(fromDb)

	targetDb, err := database.StormOpen(toDb)
	if err != nil {
		return err
	}
	defer targetDb.Close()

	var users []User
	err = db.Select("SELECT * FROM `users`", &users)
	if err != nil {
		return err
	}
	for _, user := range users {
		err := targetDb.Save(&model.User{
			Base: model.Base{
				ID:        user.UUID,
				CreatedAt: &user.CreatedAt,
				UpdatedAt: &user.UpdatedAt,
			},
			Email:             user.Email,
			Password:          user.Password,
			PasswordCost:      user.PwCost,
			PasswordNonce:     user.PwNonce,
			PasswordAuth:      user.PwAuth,
			Version:           libsf.ProtocolVersion4,
			PasswordSalt:      user.PwSalt,
			PasswordUpdatedAt: time.Now().Unix(),
		})
		if err != nil {
			return err
		}
	}

	var items []Item
	err = db.Select("SELECT * FROM `items`", &items)
	if err != nil {
		return err
	}
	for _, item := range items {
		err := targetDb.Save(&model.Item{
			Base: model.Base{
				ID:        item.UUID,
				CreatedAt: &item.CreatedAt,
				UpdatedAt: &item.UpdatedAt,
			},
			UserID: item.UserUUID,
			//ItemsKeyID:     item.ItemsKeyID,
			//AuthHash:       item.AuthHash,
			Content:          item.Content,
			ContentType:      item.ContentType,
			EncryptedItemKey: item.EncItemKey,
			Deleted:          item.Deleted,
		})
		if err != nil {
			return err
		}
	}

	return nil
}

[cyberb]

Version 3 produced the same error:

Nov 13 12:17:26 syncloud notes.server[26868]: [200] GET /v1/login-params?email=user%40example.com&api=20200115 (0) 817.999
Nov 13 12:17:27 syncloud notes.server[26868]: 2021/11/13 12:17:27 Error [69813b25-0ac6-44e6-b1f6-d722f814ee3d]: could not validate password: argon2: the encoded hash is not in the correct format

[cyberb]

Example users record (password: pass1234):

> select * from users;
uuid|email|password|pw_func|pw_alg|pw_cost|pw_key_size|pw_nonce|pw_auth|pw_salt|created_at|updated_at
22d22af9-bcdf-4882-b801-42b17769c137|[email protected]|0b6ec69ddddf139041b569bad12546475ba0c3aa93ad45da0eba69598e95f8f6|pbkdf2|sha512|110000|512|b2d8ce702696901f9b4fd4a37d57a16d27a864619420ace872a4bdc5f8626202|||2021-11-13 11:32:49.624540009+00:00|2021-11-13 11:32:49.623506052+00:00

[cyberb]

Ok, I think I will go with a new app StandardNotes as it is a correct name anyway.
Thanks for the help!