Add SQLite and database operations OUT-308
authorThomas
Fri, 08 Sep 2017 16:34:43 +0200
branchOUT-308
changeset 1825a20960ee6982
parent 1824 0cd227b422af
child 1826 3c55cda7f645
Add SQLite and database operations
Extensions/MailItemExtensions.cs
FPPMessage.cs
pEpForOutlook.csproj
packages.config
     1.1 --- a/Extensions/MailItemExtensions.cs	Thu Sep 07 14:31:59 2017 +0200
     1.2 +++ b/Extensions/MailItemExtensions.cs	Fri Sep 08 16:34:43 2017 +0200
     1.3 @@ -105,7 +105,7 @@
     1.4          /// </summary>
     1.5          /// <param name="omi">The Outlook mail item to process with.</param>
     1.6          /// <returns>The message GUID or null if not set.</returns>
     1.7 -        public static string GetForceProtectionGuid(this Outlook.MailItem omi)
     1.8 +        public static string GetForceProtectionId(this Outlook.MailItem omi)
     1.9          {
    1.10              object propValue = null;
    1.11              string guid = null;
    1.12 @@ -140,6 +140,7 @@
    1.13          {
    1.14              Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
    1.15              Outlook.Attachments attachments = null;
    1.16 +            string key = null;
    1.17  
    1.18              try
    1.19              {
    1.20 @@ -172,6 +173,13 @@
    1.21                  }
    1.22  
    1.23                  // TODO: Encrypt saved message
    1.24 +                
    1.25 +                // dummy method
    1.26 +                key = Guid.NewGuid().ToString();
    1.27 +                if (string.IsNullOrEmpty(key))
    1.28 +                {
    1.29 +                    throw new Exception("Error encrypting message.");
    1.30 +                }
    1.31  
    1.32                  ///////////////////////////
    1.33                  // Step 2: Clear the content of the original message
    1.34 @@ -230,50 +238,18 @@
    1.35                  ///////////////////////////
    1.36  
    1.37                  // Read GUID from MAPI property
    1.38 -                string guid = omi.GetForceProtectionGuid();
    1.39 +                string id = omi.GetForceProtectionId();
    1.40  
    1.41                  // Save value to database
    1.42 -                if (string.IsNullOrEmpty(guid))
    1.43 +                if (string.IsNullOrEmpty(id))
    1.44                  {
    1.45                      throw new Exception("Error getting message GUID.");
    1.46                  }
    1.47                  else
    1.48                  {
    1.49 -                    // TODO: Save value to db 
    1.50 +                    FPPMessage.StoreMsgIdAndKeyInDb(id, key);
    1.51                  }
    1.52  
    1.53 -                ///////////////////////////
    1.54 -                // Step 5: Attach sender public key
    1.55 -                ///////////////////////////
    1.56 -
    1.57 -                //try
    1.58 -                //{
    1.59 -                //    PEPIdentity fromIdentity;
    1.60 -
    1.61 -                //    if (PEPIdentity.GetFromIdentity(omi, out fromIdentity) == Globals.ReturnStatus.Success)
    1.62 -                //    {
    1.63 -                //        var from = fromIdentity.ToCOMType();
    1.64 -                //        from = ThisAddIn.PEPEngine.UpdateIdentity(from);
    1.65 -
    1.66 -                //        string key = ThisAddIn.PEPEngine.ExportKey(from.fpr);
    1.67 -
    1.68 -                //        var ownKey = new PEPAttachment();
    1.69 -                //        ownKey.Data = Encoding.UTF8.GetBytes(key);
    1.70 -                //        ownKey.FileName = "pEpkey.asc";
    1.71 -                //        ownKey.MimeType = "application/octet-stream";
    1.72 -
    1.73 -                //        ownKey.AddTo(attachments);
    1.74 -                //    }
    1.75 -                //    else
    1.76 -                //    {
    1.77 -                //        throw new Exception("Error getting From identity.");
    1.78 -                //    }
    1.79 -                //}
    1.80 -                //catch (Exception ex)
    1.81 -                //{
    1.82 -                //    throw new Exception("Error attaching own public key. " + ex.ToString());
    1.83 -                //}
    1.84 -
    1.85                  status = Globals.ReturnStatus.Success;
    1.86              }
    1.87              catch (Exception ex)
     2.1 --- a/FPPMessage.cs	Thu Sep 07 14:31:59 2017 +0200
     2.2 +++ b/FPPMessage.cs	Fri Sep 08 16:34:43 2017 +0200
     2.3 @@ -1,5 +1,8 @@
     2.4  using pEpCOMServerAdapterLib;
     2.5  using System;
     2.6 +using System.Data.SQLite;
     2.7 +using System.IO;
     2.8 +using System.Text;
     2.9  using Outlook = Microsoft.Office.Interop.Outlook;
    2.10  
    2.11  namespace pEp
    2.12 @@ -10,13 +13,16 @@
    2.13      /// </summary>
    2.14      internal class FPPMessage
    2.15      {
    2.16 -        public const string PEP_FPP_MESSAGE_BODY            = "This message is part of p≡p's concept to synchronize. \n\nYou can safely ignore it. It will be deleted automatically.";
    2.17 -        public const string PEP_FPP_MESSAGE_SUBJECT         = "p≡p synchronization message - please ignore";
    2.18 -        public const string PEP_FPP_ATTACHMENT_FILE_NAME    = "ignore_this_attachment.pEp";
    2.19 -        public const string PEP_FPP_ATTACHMENT_MIME_TYPE    = "application/pEp.forceProtected";
    2.20 +        public const string PEP_FPP_DB_FOLDER_NAME                  = "pEp";
    2.21 +        public const string PEP_FPP_DB_NAME                         = "ForceProtection.db";
    2.22 +        public const string PEP_FPP_DB_TABLE_NAME                   = "Messages";
    2.23 +        public const string PEP_FPP_DB_MESSAGE_ID_COLUMN_NAME       = "MessageID";
    2.24 +        public const string PEP_FPP_DB_KEY_COLUMN_NAME              = "Key";
    2.25  
    2.26 -        public const byte PEP_FPP_ANSWER_MESSAGE           = 1;
    2.27 -        public const byte PEP_FPP_KEY_TRANSPORT_MESSAGE    = 2;
    2.28 +        public const string PEP_FPP_MESSAGE_BODY                    = "This message is part of p≡p's concept to synchronize. \n\nYou can safely ignore it. It will be deleted automatically.";
    2.29 +        public const string PEP_FPP_MESSAGE_SUBJECT                 = "p≡p synchronization message - please ignore";
    2.30 +        public const string PEP_FPP_ATTACHMENT_FILE_NAME            = "ignore_this_attachment.pEp";
    2.31 +        public const string PEP_FPP_ATTACHMENT_MIME_TYPE            = "application/pEp.forceProtected";
    2.32  
    2.33          /// <summary>
    2.34          /// The message types that are supported by the FPP.
    2.35 @@ -36,7 +42,7 @@
    2.36          /// <summary>
    2.37          /// The message guid of the message to decrypt.
    2.38          /// </summary>
    2.39 -        public string MessageGUID { get; set; } = null;
    2.40 +        public string MessageID { get; set; } = null;
    2.41  
    2.42          /// <summary>
    2.43          /// Constructor for a Force Protection Protocol Message
    2.44 @@ -45,7 +51,7 @@
    2.45          public FPPMessage(PEPMessage message)
    2.46          {
    2.47              this.MessageType = this.GetMessageType(message);
    2.48 -            this.MessageGUID = message.ForceProtection;
    2.49 +            this.MessageID = message.ForceProtection;
    2.50          }
    2.51  
    2.52          /// <summary>
    2.53 @@ -72,62 +78,30 @@
    2.54          }
    2.55  
    2.56          /// <summary>
    2.57 -        /// Processes this outgoing Force Protection Protocol message and determines
    2.58 -        /// whether it is a normal message that has to be converted or an automatic one
    2.59 -        /// that has just to be passed through the engine.
    2.60 +        /// Gets a key attached to a Force Protection Protocol Key Transport Message.
    2.61 +        /// Note: this may return null.
    2.62          /// </summary>
    2.63 -        /// <param name="omi">The Outlook mail item to process.</param>
    2.64 -        /// <returns>The status of this method.</returns>
    2.65 -        public static Globals.ReturnStatus ProcessOutgoing(Outlook.MailItem omi)
    2.66 +        /// <param name="message">The message to detach the key from.</param>
    2.67 +        /// <returns>The key if successful, otherwise false</returns>
    2.68 +        public string GetKeyFromMessage(PEPMessage message)
    2.69          {
    2.70 -            Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
    2.71 -            bool convertMessage = true;
    2.72 -            Outlook.Attachments attachments = null;
    2.73 -            Outlook.Attachment attachment = null;
    2.74 +            string key = null;
    2.75  
    2.76 -            /* Check if outgoing message is an automatically sent message.
    2.77 -             * In this case, do nothing. If not, convert message to forcefully
    2.78 -             * encrypted Initial Message.
    2.79 -             */ 
    2.80 -            try
    2.81 +            for (int i = 0; i < message?.Attachments?.Count; i++)
    2.82              {
    2.83 -                attachments = omi.Attachments;
    2.84 +                MessageTypes msgType = MessageTypes.InitialMessage;
    2.85 +                PEPAttachment attachment = message.Attachments[i];
    2.86  
    2.87 -                for (int i = 1; i <= attachments.Count; i++)
    2.88 +                if ((attachment.IsFPPAttachment(out msgType)) &&
    2.89 +                    (msgType == MessageTypes.KeyTransportMessage))
    2.90                  {
    2.91 -                    attachment = attachments[i];
    2.92 -                    PEPAttachment attach = new PEPAttachment(attachment);
    2.93 -                    MessageTypes type;
    2.94 -                    if (attach.IsFPPAttachment(out type))
    2.95 -                    {
    2.96 -                        convertMessage = false;
    2.97 -                        break;
    2.98 -                    }
    2.99 -
   2.100 -                    attachment = null;
   2.101 +                    //TODO: Convert attachment to string
   2.102 +                    
   2.103 +                    break;
   2.104                  }
   2.105              }
   2.106 -            catch (Exception ex)
   2.107 -            {
   2.108 -                Log.Error("FPPMessage.ProcessOutgoing: Error determining message type. " + ex.ToString());
   2.109 -            }
   2.110 -            finally
   2.111 -            {
   2.112 -                attachment = null;
   2.113 -                attachments = null;
   2.114 -            }
   2.115  
   2.116 -            // Convert message if necessary
   2.117 -            if (convertMessage)
   2.118 -            {
   2.119 -                status = omi.ConvertToForcefullyProtected();
   2.120 -            }
   2.121 -            else
   2.122 -            {
   2.123 -                status = Globals.ReturnStatus.Success;
   2.124 -            }
   2.125 -
   2.126 -            return status;
   2.127 +            return key;
   2.128          }
   2.129  
   2.130          /// <summary>
   2.131 @@ -148,22 +122,37 @@
   2.132              {
   2.133                  case FPPMessage.MessageTypes.InitialMessage:
   2.134                      {
   2.135 -                        status = this.CreateNextFPPMessage(currentMsg, out nextMsg);
   2.136 -                        if (status == Globals.ReturnStatus.Success)
   2.137 +                        // Check if key is in db and decrypt in this case
   2.138 +                        string key = null;
   2.139 +                        if ((FPPMessage.GetKeyFromDb(this.MessageID, out key) == Globals.ReturnStatus.Success) &&
   2.140 +                            (key != null))
   2.141                          {
   2.142 -                            Log.Verbose("FPPMessage.Process: Force Protection Protocol Answer Message created");
   2.143 +                            // TODO: Decrypt message
   2.144 +
   2.145 +                            status = Globals.ReturnStatus.Success;
   2.146                          }
   2.147 +                        // Else, send Force Protection Protocol Answer Message
   2.148                          else
   2.149                          {
   2.150 -                            nextMsg = null;
   2.151 -                            Log.Error("FPPMessage.Process: Error creating Force Protection Protocol Answer Message");
   2.152 +                            status = this.CreateNextFPPMessage(currentMsg, out nextMsg);
   2.153 +                            if (status == Globals.ReturnStatus.Success)
   2.154 +                            {
   2.155 +                                Log.Verbose("FPPMessage.Process: Force Protection Protocol Answer Message created");
   2.156 +                            }
   2.157 +                            else
   2.158 +                            {
   2.159 +                                nextMsg = null;
   2.160 +                                Log.Error("FPPMessage.Process: Error creating Force Protection Protocol Answer Message");
   2.161 +                            }
   2.162                          }
   2.163                      }
   2.164                      break;
   2.165                  case FPPMessage.MessageTypes.AnswerMessage:
   2.166                      {
   2.167 +                        // Update rating
   2.168                          currentRating = AdapterExtensions.ReevaluateMessageRating(currentMsg);
   2.169  
   2.170 +                        // If rating is green, send out Force Protection Protocol Key Transport Message
   2.171                          if (currentRating >= pEpRating.pEpRatingTrusted)
   2.172                          {
   2.173                              Log.Verbose("FPPMessage.Process: Trusted Force Protection Protocol Answer Message found.");
   2.174 @@ -171,14 +160,15 @@
   2.175                              status = this.CreateNextFPPMessage(currentMsg, out nextMsg);
   2.176                              if (status == Globals.ReturnStatus.Success)
   2.177                              {
   2.178 -                                Log.Verbose("FPPMessage.Process: Force Protection Protocol Key Request Message created");
   2.179 +                                Log.Verbose("FPPMessage.Process: Force Protection Protocol Key Transport Message created");
   2.180                              }
   2.181                              else
   2.182                              {
   2.183                                  nextMsg = null;
   2.184 -                                Log.Error("FPPMessage.Process: Error creating Force Protection Protocol Key Request Message");
   2.185 +                                Log.Error("FPPMessage.Process: Error creating Force Protection Protocol Key Transport Message");
   2.186                              }
   2.187                          }
   2.188 +                        // Else, show handshake
   2.189                          else
   2.190                          {
   2.191                              // Show handshake
   2.192 @@ -188,13 +178,39 @@
   2.193                      break;
   2.194                  case FPPMessage.MessageTypes.KeyTransportMessage:
   2.195                      {
   2.196 +                        // Update rating
   2.197                          currentRating = AdapterExtensions.ReevaluateMessageRating(currentMsg);
   2.198  
   2.199 +                        // If rating is green, import key from message and save to db
   2.200                          if (currentRating >= pEpRating.pEpRatingTrusted)
   2.201                          {
   2.202                              Log.Verbose("FPPMessage.Process: Trusted Force Protection Protocol Key Transport Message found.");
   2.203  
   2.204 -                            // Import key
   2.205 +                            // Get key from message
   2.206 +                            string key = this.GetKeyFromMessage(currentMsg);
   2.207 +
   2.208 +                            // Decrypt message and save id and key to database
   2.209 +                            if (string.IsNullOrEmpty(key) == false)
   2.210 +                            {
   2.211 +                                // TODO: Decrypt message
   2.212 +                                // ...
   2.213 +                                Log.Verbose("FPPMessage.Process: Message decrypted successfully.");
   2.214 +
   2.215 +                                // Save to database
   2.216 +                                status = FPPMessage.StoreMsgIdAndKeyInDb(this.MessageID, key);
   2.217 +                                if (status == Globals.ReturnStatus.Success)
   2.218 +                                {
   2.219 +                                    Log.Verbose("FPPMessage.Process: Message ID and key successfully stored in database.");
   2.220 +                                }
   2.221 +                                else
   2.222 +                                {
   2.223 +                                    Log.Error("FPPMessage.Process: Error storing message ID and key in database.");
   2.224 +                                }
   2.225 +                            }
   2.226 +                            else
   2.227 +                            {
   2.228 +                                Log.Error("FPPMessage.Process: Error getting key from message.");
   2.229 +                            }
   2.230                          }
   2.231                          else
   2.232                          {
   2.233 @@ -227,7 +243,7 @@
   2.234              {
   2.235                  // Set base properties
   2.236                  nextMsg.AutoConsume = "yes";
   2.237 -                nextMsg.ForceProtection = this.MessageGUID;
   2.238 +                nextMsg.ForceProtection = this.MessageID;
   2.239                  nextMsg.ShortMsg = FPPMessage.PEP_FPP_MESSAGE_SUBJECT;
   2.240                  nextMsg.LongMsg = FPPMessage.PEP_FPP_MESSAGE_BODY;
   2.241  
   2.242 @@ -275,5 +291,281 @@
   2.243  
   2.244              return status;
   2.245          }
   2.246 +
   2.247 +        /// <summary>
   2.248 +        /// Processes this outgoing Force Protection Protocol message and determines
   2.249 +        /// whether it is a normal message that has to be converted or an automatic one
   2.250 +        /// that has just to be passed through the engine.
   2.251 +        /// </summary>
   2.252 +        /// <param name="omi">The Outlook mail item to process.</param>
   2.253 +        /// <returns>The status of this method.</returns>
   2.254 +        public static Globals.ReturnStatus ProcessOutgoing(Outlook.MailItem omi)
   2.255 +        {
   2.256 +            Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
   2.257 +            bool convertMessage = true;
   2.258 +            Outlook.Attachments attachments = null;
   2.259 +            Outlook.Attachment attachment = null;
   2.260 +
   2.261 +            /* Check if outgoing message is an automatically sent message.
   2.262 +             * In this case, do nothing. If not, convert message to forcefully
   2.263 +             * encrypted Initial Message.
   2.264 +             */
   2.265 +            try
   2.266 +            {
   2.267 +                attachments = omi.Attachments;
   2.268 +
   2.269 +                for (int i = 1; i <= attachments.Count; i++)
   2.270 +                {
   2.271 +                    attachment = attachments[i];
   2.272 +                    PEPAttachment attach = new PEPAttachment(attachment);
   2.273 +                    MessageTypes type;
   2.274 +                    if (attach.IsFPPAttachment(out type))
   2.275 +                    {
   2.276 +                        convertMessage = false;
   2.277 +                        break;
   2.278 +                    }
   2.279 +
   2.280 +                    attachment = null;
   2.281 +                }
   2.282 +            }
   2.283 +            catch (Exception ex)
   2.284 +            {
   2.285 +                Log.Error("FPPMessage.ProcessOutgoing: Error determining message type. " + ex.ToString());
   2.286 +            }
   2.287 +            finally
   2.288 +            {
   2.289 +                attachment = null;
   2.290 +                attachments = null;
   2.291 +            }
   2.292 +
   2.293 +            // Convert message if necessary
   2.294 +            if (convertMessage)
   2.295 +            {
   2.296 +                status = omi.ConvertToForcefullyProtected();
   2.297 +            }
   2.298 +            else
   2.299 +            {
   2.300 +                status = Globals.ReturnStatus.Success;
   2.301 +            }
   2.302 +
   2.303 +            return status;
   2.304 +        }
   2.305 +
   2.306 +        #region Database operations
   2.307 +        /**************************************************************
   2.308 +         * 
   2.309 +         * Database operations
   2.310 +         * 
   2.311 +         *************************************************************/
   2.312 +
   2.313 +        /// <summary>
   2.314 +        /// Opens a SQLiteConnection to the Force Protection Protocol database. This can
   2.315 +        /// return null.
   2.316 +        /// IMPORTANT: Caller has to make sure to close the connection after using it.
   2.317 +        /// </summary>
   2.318 +        /// <returns>An open SQLiteConnection or null if an error occured.</returns>
   2.319 +        public static SQLiteConnection OpenFPPDatabase()
   2.320 +        {
   2.321 +            string dbPath = null;
   2.322 +            SQLiteConnection dbConnection = null;
   2.323 +
   2.324 +            // Get the path of the database file
   2.325 +            try
   2.326 +            {
   2.327 +                dbPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), PEP_FPP_DB_FOLDER_NAME, PEP_FPP_DB_NAME);
   2.328 +            }
   2.329 +            catch (Exception ex)
   2.330 +            {
   2.331 +                dbPath = null;
   2.332 +                Log.Error("OpenFPPDatabase: Error getting database path. " + ex.ToString());
   2.333 +            }
   2.334 +
   2.335 +            if (dbPath != null)
   2.336 +            {
   2.337 +                try
   2.338 +                {
   2.339 +                    // If database does not exist yet, create it
   2.340 +                    if (File.Exists(dbPath) == false)
   2.341 +                    {
   2.342 +                        SQLiteConnection.CreateFile(dbPath);
   2.343 +                    }
   2.344 +
   2.345 +                    // Open connection
   2.346 +                    string dbConnectionPath = "Data Source=" + dbPath;
   2.347 +                    dbConnection = new SQLiteConnection(dbConnectionPath);
   2.348 +                    dbConnection.Open();
   2.349 +                }
   2.350 +                catch (Exception ex)
   2.351 +                {
   2.352 +                    // Close connection
   2.353 +                    if (dbConnection != null)
   2.354 +                    {
   2.355 +                        dbConnection.Close();
   2.356 +                        dbConnection = null;
   2.357 +                    }
   2.358 +
   2.359 +                    Log.Error("OpenFPPDatabase: Error connecting to database. " + ex.ToString());
   2.360 +                }
   2.361 +            }
   2.362 +
   2.363 +            return dbConnection;
   2.364 +        }
   2.365 +
   2.366 +        /// <summary>
   2.367 +        /// Stores a message id and its corresponding key in the Force Protection database.
   2.368 +        /// </summary>
   2.369 +        /// <param name="msgId">The message id to store.</param>
   2.370 +        /// <param name="key">The key to store.</param>
   2.371 +        /// <returns>Success if key was stored or is already in the db, otherwise Failure</returns>
   2.372 +        public static Globals.ReturnStatus StoreMsgIdAndKeyInDb(string msgId, string key)
   2.373 +        {
   2.374 +            /* SQL statement to create the messages table.
   2.375 +             * The table is defined to not allow duplicates and is only created if not yet existing.
   2.376 +             * 
   2.377 +             *  messages
   2.378 +             * --------------------
   2.379 +             * | message_id | key |
   2.380 +             * --------------------
   2.381 +             * |            |     |
   2.382 +             * --------------------
   2.383 +             */
   2.384 +            string createMessagesTable = "create table if not exists " + PEP_FPP_DB_TABLE_NAME + " ("
   2.385 +                                                                       + PEP_FPP_DB_MESSAGE_ID_COLUMN_NAME + " varchar(50), "
   2.386 +                                                                       + PEP_FPP_DB_KEY_COLUMN_NAME + " varchar(50), unique("
   2.387 +                                                                       + PEP_FPP_DB_MESSAGE_ID_COLUMN_NAME + ", "
   2.388 +                                                                       + PEP_FPP_DB_KEY_COLUMN_NAME + "))";
   2.389 +
   2.390 +            /* SQL statement to insert a message id and a key into the messages table. If values are already
   2.391 +             * in the table, the insertion is omitted.
   2.392 +             */
   2.393 +            string storeMsgIdAndKey = "insert or ignore into " + PEP_FPP_DB_TABLE_NAME + " ("
   2.394 +                                                               + PEP_FPP_DB_MESSAGE_ID_COLUMN_NAME + ", "
   2.395 +                                                               + PEP_FPP_DB_KEY_COLUMN_NAME + ") values ('" + msgId + "', '" + key + "')";
   2.396 +
   2.397 +            Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
   2.398 +            SQLiteConnection dbConnection = null;
   2.399 +
   2.400 +            // Check first if message id and key are not null
   2.401 +            if (string.IsNullOrEmpty(msgId) ||
   2.402 +                string.IsNullOrEmpty(key))
   2.403 +            {
   2.404 +                Log.Error("StoreMsgIdAndKey: message id or key is null or empty.");
   2.405 +                return status;
   2.406 +            }
   2.407 +
   2.408 +            try
   2.409 +            {
   2.410 +                // Open a connection to the database
   2.411 +                dbConnection = FPPMessage.OpenFPPDatabase();
   2.412 +
   2.413 +                if (dbConnection != null)
   2.414 +                {
   2.415 +                    // Create table if it doesn't exist yet
   2.416 +                    SQLiteCommand createTableCmd = new SQLiteCommand(createMessagesTable, dbConnection);
   2.417 +                    createTableCmd.ExecuteNonQuery();
   2.418 +
   2.419 +                    // Insert values
   2.420 +                    SQLiteCommand insertValuesCmd = new SQLiteCommand(storeMsgIdAndKey, dbConnection);
   2.421 +                    int affectedRows = insertValuesCmd.ExecuteNonQuery();
   2.422 +
   2.423 +                    // If one row was modified, the entry was inserted correctly
   2.424 +                    if (affectedRows == 1)
   2.425 +                    {
   2.426 +                        Log.Verbose("StoreMsgIdAndKey: Message ID and key stored successfully in database.");
   2.427 +                        status = Globals.ReturnStatus.Success;
   2.428 +                    }
   2.429 +                    // If no row was modified, the values are already in the database
   2.430 +                    else if (affectedRows == 0)
   2.431 +                    {
   2.432 +                        Log.Verbose("StoreMsgIdAndKey: Message ID or key already in database.");
   2.433 +                        status = Globals.ReturnStatus.Success;
   2.434 +                    }
   2.435 +                    // In any other case, some kind of error occured
   2.436 +                    else
   2.437 +                    {
   2.438 +                        Log.Error("StoreMsgIdAndKey: Error storing message ID or key. Affected rows: " + affectedRows);
   2.439 +                        status = Globals.ReturnStatus.Failure;
   2.440 +                    }
   2.441 +                }
   2.442 +            }
   2.443 +            catch (Exception ex)
   2.444 +            {
   2.445 +                Log.Error("StoreMsgIdAndKey: Error storing msg id and key. " + ex.ToString());
   2.446 +            }
   2.447 +            finally
   2.448 +            {
   2.449 +                // Always close connection
   2.450 +                if (dbConnection != null)
   2.451 +                {
   2.452 +                    dbConnection.Close();
   2.453 +                }
   2.454 +            }
   2.455 +
   2.456 +            return status;
   2.457 +        }
   2.458 +
   2.459 +        /// <summary>
   2.460 +        /// Gets a message's key from the Force Protection database.
   2.461 +        /// </summary>
   2.462 +        /// <param name="messageId">The id of the message to retrieve its key.</param>
   2.463 +        /// <param name="key">The retrieved key or null if an error occured.</param>
   2.464 +        /// <returns>Success if key was stored or is already in the db, otherwise Failure</returns>
   2.465 +        public static Globals.ReturnStatus GetKeyFromDb(string messageId, out string key)
   2.466 +        {
   2.467 +            key = null;
   2.468 +            SQLiteConnection dbConnection = null;
   2.469 +            Globals.ReturnStatus status = Globals.ReturnStatus.Failure;
   2.470 +
   2.471 +            try
   2.472 +            {
   2.473 +                // Open a connection to the database
   2.474 +                dbConnection = FPPMessage.OpenFPPDatabase();
   2.475 +
   2.476 +                if (dbConnection != null)
   2.477 +                {
   2.478 +                    // Look up key that corresponds to message id
   2.479 +                    string getKey = "select " + PEP_FPP_DB_KEY_COLUMN_NAME +
   2.480 +                                            " from " + PEP_FPP_DB_TABLE_NAME +
   2.481 +                                            " where " + PEP_FPP_DB_MESSAGE_ID_COLUMN_NAME + "='" + messageId + "'";
   2.482 +
   2.483 +                    SQLiteCommand getKeyCmd = new SQLiteCommand(getKey, dbConnection);
   2.484 +                    SQLiteDataReader getKeyReader = getKeyCmd.ExecuteReader();
   2.485 +                    while (getKeyReader.Read())
   2.486 +                    {
   2.487 +                        // Check also for duplicates
   2.488 +                        if (key != null)
   2.489 +                        {
   2.490 +                            // If two different keys are found for the same message, assert error and return
   2.491 +                            if (string.Equals(key, getKeyReader[PEP_FPP_DB_KEY_COLUMN_NAME] as string) == false)
   2.492 +                            {
   2.493 +                                Log.Error("GetKeyFromDb: Two different keys found for same message.");
   2.494 +                                status = Globals.ReturnStatus.Failure;
   2.495 +                                key = null;
   2.496 +                                break;
   2.497 +                            }
   2.498 +                        }
   2.499 +                        else
   2.500 +                        {
   2.501 +                            key = getKeyReader[PEP_FPP_DB_KEY_COLUMN_NAME] as string;
   2.502 +                            status = (key != null) ? Globals.ReturnStatus.Success : Globals.ReturnStatus.Failure;
   2.503 +                        }
   2.504 +                    }
   2.505 +                }
   2.506 +            }
   2.507 +            catch (Exception ex)
   2.508 +            {
   2.509 +                Log.Error("GetKeyFromDb: Error getting key. " + ex.ToString());
   2.510 +            }
   2.511 +            finally
   2.512 +            {
   2.513 +                if (dbConnection != null)
   2.514 +                {
   2.515 +                    dbConnection.Close();
   2.516 +                }
   2.517 +            }
   2.518 +
   2.519 +            return status;
   2.520 +        }
   2.521      }
   2.522 +    #endregion
   2.523  }
     3.1 --- a/pEpForOutlook.csproj	Thu Sep 07 14:31:59 2017 +0200
     3.2 +++ b/pEpForOutlook.csproj	Fri Sep 08 16:34:43 2017 +0200
     3.3 @@ -52,6 +52,8 @@
     3.4      <FriendlyName>pEp</FriendlyName>
     3.5      <OfficeApplicationDescription />
     3.6      <LoadBehavior>3</LoadBehavior>
     3.7 +    <NuGetPackageImportStamp>
     3.8 +    </NuGetPackageImportStamp>
     3.9    </PropertyGroup>
    3.10    <ItemGroup>
    3.11      <BootstrapperPackage Include=".NETFramework,Version=v4.0">
    3.12 @@ -241,6 +243,10 @@
    3.13      <Reference Include="PresentationFramework" />
    3.14      <Reference Include="System" />
    3.15      <Reference Include="System.Data" />
    3.16 +    <Reference Include="System.Data.SQLite, Version=1.0.105.2, Culture=neutral, PublicKeyToken=db937bc2d44ff139, processorArchitecture=MSIL">
    3.17 +      <HintPath>..\packages\System.Data.SQLite.Core.1.0.105.2\lib\net40\System.Data.SQLite.dll</HintPath>
    3.18 +      <Private>True</Private>
    3.19 +    </Reference>
    3.20      <Reference Include="System.Drawing" />
    3.21      <Reference Include="System.Security" />
    3.22      <Reference Include="System.Windows.Forms" />
    3.23 @@ -628,4 +634,11 @@
    3.24        </FlavorProperties>
    3.25      </VisualStudio>
    3.26    </ProjectExtensions>
    3.27 +  <Import Project="..\packages\System.Data.SQLite.Core.1.0.105.2\build\net40\System.Data.SQLite.Core.targets" Condition="Exists('..\packages\System.Data.SQLite.Core.1.0.105.2\build\net40\System.Data.SQLite.Core.targets')" />
    3.28 +  <Target Name="EnsureNuGetPackageBuildImports" BeforeTargets="PrepareForBuild">
    3.29 +    <PropertyGroup>
    3.30 +      <ErrorText>This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them.  For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.</ErrorText>
    3.31 +    </PropertyGroup>
    3.32 +    <Error Condition="!Exists('..\packages\System.Data.SQLite.Core.1.0.105.2\build\net40\System.Data.SQLite.Core.targets')" Text="$([System.String]::Format('$(ErrorText)', '..\packages\System.Data.SQLite.Core.1.0.105.2\build\net40\System.Data.SQLite.Core.targets'))" />
    3.33 +  </Target>
    3.34  </Project>
    3.35 \ No newline at end of file
     4.1 --- a/packages.config	Thu Sep 07 14:31:59 2017 +0200
     4.2 +++ b/packages.config	Fri Sep 08 16:34:43 2017 +0200
     4.3 @@ -1,4 +1,5 @@
     4.4  <?xml version="1.0" encoding="utf-8"?>
     4.5  <packages>
     4.6    <package id="MimeKitLite" version="1.10.0" targetFramework="net40-client" />
     4.7 +  <package id="System.Data.SQLite.Core" version="1.0.105.2" targetFramework="net40-client" />
     4.8  </packages>
     4.9 \ No newline at end of file