Encrypted CSPro DB Data Source

Overview

The Encrypted CSPro DB data source allows reading and writing data to a SQLite database. This is an encrypted version of the CSPro DB data source, and more information about this data source is documented on that page.

The Encrypted CSPro DB data source is used when a file has the extension .csdbe. A version of this data source without encryption, CSPro DB, is used if a file has the extension .csdb.

Functionality

The Encrypted CSPro DB data source supports the following features:

Feature	Supported
Reading cases	✔
Writing cases	✔
Notes, case labels, and case statuses	✔
Storage of more than one kind of record	✔
Binary data items	✔
Deleting cases	✔
Undeleting cases	✔
Syncing data	✔
Cases with duplicate keys	✔
Case identification via UUID	✔
Contains an embedded dictionary	✔
Allows record sorts	✘

Encryption Password

Upon opening an Encrypted CSPro DB data source, CSPro requires the specification of a password. There are two ways to specify a password:

Password entry: A dialog box will appear allowing the user to enter the password. The password must be at least four characters. If the data file does not exist and is being created for the first time, the user must enter the password twice to ensure that the password is entered correctly. You can reduce the number of times that a user must enter the password by allowing the password to be cached for a specified duration on the machine. When opening an existing file, if the password is not correct, the user will be prompted to enter the password again.

Connection string: The password can be specified in a connection string used in a PFF file or by setfile. If the password is specified in the connection string, then the user will not be prompted to enter a password. If the data file does not exist and is being created for the first time, the specified password will be used to encrypt the file. When opening an existing file, if the password is not correct, the opening of the file will fail. For example, the following connection string opens the file pilot-data.csdbe with the password jiw~d_fpF9.

While the data in the file is encrypted, it is also a good idea:

To encrypt the entire drive where the CSPro application and data files reside, using encryption such as Windows BitLocker or Android's full-disk encryption.
Not to store passwords in plaintext anywhere (such as in a CSPro logic file or in a PFF file, as is done in the above image).
To synchronize your data using a secure protocol (such as CSWeb over https). If you encrypt your data file on a tablet but then transfer the data over http using syncdata, that defeats much of the purpose of encrypting.

Encryption Technical Details

Encrypted CSPro DB files are SQLite files encrypted using the SQLite Encryption Extension (SEE) using "AES-256 in OFB mode." The specified password is not used as the key input to SEE but is instead hashed to create a 256-byte key that is used to encrypt the file. If allowed, this hash, not the password, is cached on the machine. A fixed salt is used during the hashing process because there is no suitable place to store a dynamic salt. This means that the same password will always result in the same encryption key.

Customizable Behavior

The following behavior can be customized by specifying properties in the connection string. The default behavior is marked with ⁺⁺⁺.

Property Name and Values	Description

"cache"	Determines if cases are cached in memory. This may be useful to advanced users who want to optimize programs that do a lot of case lookups.
true	Cases are cached, meaning that a case is only read from the data source once.
false ⁺⁺⁺	Cases are not cached.

"password"	The password used to encrypt the file.

For example, the following connection string, specified in a data entry PFF, would attach to an external dictionary an Encrypted CSPro DB file with caching enabled and a password specified:

CENSUS_2024_DICT=.\Census.csdbe|cache=true&password=jiw~d_fpF9