public void WriteAndReadData_EncryptedColumn_IsSuccessful()
{
//Arrange
var cryptEngine = new SampleAesCryptEngine("myTestPassword");
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
int dataItemIndex = 0;
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
{
//Creating my table & adding encrypted column
using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
{
db.SafeOpen();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(addColumnSql, tableName);
cmd.ExecuteNonQuery();
}
//Act
using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db))
{
//Adding my records
foreach (Tuple <DateTime, string> dataItem in _sampleData)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@date", dataItem.Item1);
cmd.Parameters.Add("@text", dataItem.Item2);
cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption
db.SafeOpen();
cmd.ExecuteNonQuery();
}
}
//Assert
using (var cmd = new SqliteCommand(String.Format(selectSql1, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetString("TextColumn"));
Assert.AreNotEqual(_sampleData[dataItemIndex].Item2, reader.GetString("EncryptedColumn")); //The encrypted text should not match the unencrypted text
Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text
dataItemIndex++;
}
Assert.AreEqual(3, dataItemIndex); //Should have read three records
}
}
}
public void WriteAndReadData_EncryptedColumnWithWrongPassword_CausesException()
{
//Arrange
string rightPassword = "******";
string wrongPassword = "******";
var cryptEngine = new SampleAesCryptEngine(rightPassword);
string dbPath = TempDatabaseFiles.GetNewDatabasePath(this);
Exception decryptException = null;
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
{
//Creating my table & adding encrypted column
using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db))
{
db.SafeOpen();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format(addColumnSql, tableName);
cmd.ExecuteNonQuery();
}
//Act
using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db))
{
//Adding my records
foreach (Tuple <DateTime, string> dataItem in _sampleData)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@date", dataItem.Item1);
cmd.Parameters.Add("@text", dataItem.Item2);
cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption
db.SafeOpen();
cmd.ExecuteNonQuery();
}
}
}
//Assert
cryptEngine = new SampleAesCryptEngine(wrongPassword); //Testing first with the wrong password
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
try
{
//This should fail, because the value that is read from the column cannot be properly decrypted without the correct password
Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn"));
}
catch (Exception e)
{
decryptException = e;
}
}
Assert.IsNotNull(decryptException as CryptographicException);
}
//Just checking to make sure we can still decrypt the value with the correct password
cryptEngine = new SampleAesCryptEngine(rightPassword); //Should work correctly with the right password
using (var db = new SqliteConnection(dbPath, cryptEngine, true))
using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db))
using (var reader = new SqliteDataReader(cmd))
{
while (reader.Read())
{
Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text
}
}
}
