Password Protect a SQLite DB. Is it possible?

Question

I have to face a new little project. It will have about 7 or 9 tables, the biggest of them will grow by a max rate of 1000 rows a month.

I thought about SQLite as my db... But i will need to protect the db in case anybody wants to change data from the db

My main question is:

Is it possible password protect a sqlite db as you would do on access?

The development would be on C#, but I'm searching something free.

Answer 1

You can password protect a SQLite3 DB. Before doing any operations, set the password as follows.

SQLiteConnection conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;"); conn.SetPassword("password"); conn.Open(); 

then next time you can access it like

conn = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;Password=password;"); conn.Open(); 

This wont allow any GUI editor to view your data. Some editors can decrypt the DB if you provide the password. The algorithm used is RSA.

Later if you wish to change the password, use

conn.ChangePassword("new_password"); 

To reset or remove password, use

conn.ChangePassword(String.Empty); 

Answer 2

You can use the built-in encryption of the sqlite .net provider (System.Data.SQLite). See more details at http://web.archive.org/web/20070813071554/http://sqlite.phxsoftware.com/forums/t/130.aspx

To encrypt an existing unencrypted database, or to change the password of an encrypted database, open the database and then use the ChangePassword() function of SQLiteConnection:

// Opens an unencrypted database SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3"); cnn.Open(); // Encrypts the database. The connection remains valid and usable afterwards. cnn.ChangePassword("mypassword"); 

To decrypt an existing encrypted database call ChangePassword() with a NULL or "" password:

// Opens an encrypted database SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3;Password=mypassword"); cnn.Open(); // Removes the encryption on an encrypted database. cnn.ChangePassword(null); 

To open an existing encrypted database, or to create a new encrypted database, specify a password in the ConnectionString as shown in the previous example, or call the SetPassword() function before opening a new SQLiteConnection. Passwords specified in the ConnectionString must be cleartext, but passwords supplied in the SetPassword() function may be binary byte arrays.

// Opens an encrypted database by calling SetPassword() SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3"); cnn.SetPassword(new byte[] { 0xFF, 0xEE, 0xDD, 0x10, 0x20, 0x30 }); cnn.Open(); // The connection is now usable 

By default, the ATTACH keyword will use the same encryption key as the main database when attaching another database file to an existing connection. To change this behavior, you use the KEY modifier as follows:

If you are attaching an encrypted database using a cleartext password:

// Attach to a database using a different key than the main database SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3"); cnn.Open(); cmd = new SQLiteCommand("ATTACH DATABASE 'c:\\pwd.db3' AS [Protected] KEY 'mypassword'", cnn); cmd.ExecuteNonQuery(); 

To attach an encrypted database using a binary password:

// Attach to a database encrypted with a binary key SQLiteConnection cnn = new SQLiteConnection("Data Source=c:\\test.db3"); cnn.Open(); cmd = new SQLiteCommand("ATTACH DATABASE 'c:\\pwd.db3' AS [Protected] KEY X'FFEEDD102030'", cnn); cmd.ExecuteNonQuery();