// This is used in the Login form // These accounts are automatically stored in the database, when // connection is succesful (by InsertUser) public static List <mailaccount> getAccounts() { SQLiteCommand cmd = new SQLiteCommand("SELECT mailaddress,password,pop_hostname,pop_port,pop_ssl,smtp_hostname,smtp_port,smtp_ssl FROM users", sqlCon); SQLiteDataReader reader = cmd.ExecuteReader(); List <mailaccount> rtn = new List <mailaccount>(); while (reader.Read()) { mailaccount tmp = new mailaccount(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt16(3), reader.GetBoolean(4), reader.GetString(5), reader.GetInt16(6), reader.GetBoolean(7), ""); rtn.Add(tmp); } return(rtn); }
/* SQLite functions */ // Insert user to database // This is only called, if the user could connect and authenticate to the server public static int insertUser(string mailaddr, string password, string pop_hostname, int pop_port, bool pop_ssl, string smtp_hostname, int smtp_port, bool smtp_ssl) { // Now that we have encryption, we have to know if the user really exists. // If we generate a new key, the data, we already have, is invalid bool userExist = false; string sqlCountQuery="SELECT COUNT(*) FROM users WHERE mailaddress=@_mailaddr"; SQLiteCommand sqlCountCMD = new SQLiteCommand(sqlCountQuery, sqlCon); sqlCountCMD.Parameters.AddWithValue("@_mailaddr", mailaddr); int count=Convert.ToInt16(sqlCountCMD.ExecuteScalar()); string user_key = ""; if (count > 0) { userExist = true; string getKeyQuery = "SELECT encryption_key FROM users WHERE mailaddress=@_mailaddr"; SQLiteCommand getKeyCMD = new SQLiteCommand(getKeyQuery, sqlCon); getKeyCMD.Parameters.AddWithValue("@_mailaddr", mailaddr); user_key = Convert.ToString(getKeyCMD.ExecuteScalar()); } // Insert/replace the user in the user database string insertQuery = "INSERT OR REPLACE INTO users (mailaddress,password,pop_hostname,pop_port,pop_ssl,smtp_hostname,smtp_port,smtp_ssl,encryption_key) "; insertQuery += "VALUES (@_mailaddress,@_password,@_pop_hostname,@_pop_port,@_pop_ssl,@_smtp_hostname,@_smtp_port,@_smtp_ssl,@_encryption_key)"; SQLiteCommand insertCmd = new SQLiteCommand(insertQuery, sqlCon); insertCmd.Parameters.AddWithValue("@_mailaddress", mailaddr); insertCmd.Parameters.AddWithValue("@_password", password); insertCmd.Parameters.AddWithValue("@_pop_hostname", pop_hostname); insertCmd.Parameters.AddWithValue("@_pop_port", pop_port); insertCmd.Parameters.AddWithValue("@_pop_ssl", Convert.ToInt16(pop_ssl)); insertCmd.Parameters.AddWithValue("@_smtp_hostname", smtp_hostname); insertCmd.Parameters.AddWithValue("@_smtp_port", smtp_port); insertCmd.Parameters.AddWithValue("@_smtp_ssl", Convert.ToInt16(smtp_ssl)); if (!userExist) { // If the user didn't exist before, make a key to him/her user_key = MerMail.Symmetric.generateKey(); } insertCmd.Parameters.AddWithValue("@_encryption_key", user_key); insertCmd.ExecuteNonQuery(); // Now, we define the currentUser global, so that we know who the // current user is currentUser = new mailaccount(mailaddr,password,pop_hostname,pop_port,pop_ssl,smtp_hostname,smtp_port,smtp_ssl,user_key); // Is used for making a name of the message database file for the user string userMD5 = genMd5(mailaddr); // set up SQLite connection for message database // (the separate-per-user database) SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder(); conStr.DataSource = appdata + "/mermail/userdata/"+ userMD5 + ".db"; conStr.Version = 3; msgSqlCon = new SQLiteConnection(conStr.ConnectionString); msgSqlCon.Open(); SQLiteCommand crCMD = new SQLiteCommand("CREATE TABLE IF NOT EXISTS messagetable (id CHAR(32) PRIMARY KEY NOT NULL,sender TEXT NOT NULL, subject TEXT, body TEXT, date DATETIME NOT NULL)", msgSqlCon); crCMD.ExecuteNonQuery(); return 0; }
// This is used in the Login form // These accounts are automatically stored in the database, when // connection is succesful (by InsertUser) public static List<mailaccount> getAccounts() { SQLiteCommand cmd = new SQLiteCommand("SELECT mailaddress,password,pop_hostname,pop_port,pop_ssl,smtp_hostname,smtp_port,smtp_ssl FROM users", sqlCon); SQLiteDataReader reader = cmd.ExecuteReader(); List<mailaccount> rtn = new List<mailaccount>(); while (reader.Read()) { mailaccount tmp = new mailaccount(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt16(3), reader.GetBoolean(4), reader.GetString(5), reader.GetInt16(6), reader.GetBoolean(7),""); rtn.Add(tmp); } return rtn; }
/* SQLite functions */ // Insert user to database // This is only called, if the user could connect and authenticate to the server public static int insertUser(string mailaddr, string password, string pop_hostname, int pop_port, bool pop_ssl, string smtp_hostname, int smtp_port, bool smtp_ssl) { // Now that we have encryption, we have to know if the user really exists. // If we generate a new key, the data, we already have, is invalid bool userExist = false; string sqlCountQuery = "SELECT COUNT(*) FROM users WHERE mailaddress=@_mailaddr"; SQLiteCommand sqlCountCMD = new SQLiteCommand(sqlCountQuery, sqlCon); sqlCountCMD.Parameters.AddWithValue("@_mailaddr", mailaddr); int count = Convert.ToInt16(sqlCountCMD.ExecuteScalar()); string user_key = ""; if (count > 0) { userExist = true; string getKeyQuery = "SELECT encryption_key FROM users WHERE mailaddress=@_mailaddr"; SQLiteCommand getKeyCMD = new SQLiteCommand(getKeyQuery, sqlCon); getKeyCMD.Parameters.AddWithValue("@_mailaddr", mailaddr); user_key = Convert.ToString(getKeyCMD.ExecuteScalar()); } // Insert/replace the user in the user database string insertQuery = "INSERT OR REPLACE INTO users (mailaddress,password,pop_hostname,pop_port,pop_ssl,smtp_hostname,smtp_port,smtp_ssl,encryption_key) "; insertQuery += "VALUES (@_mailaddress,@_password,@_pop_hostname,@_pop_port,@_pop_ssl,@_smtp_hostname,@_smtp_port,@_smtp_ssl,@_encryption_key)"; SQLiteCommand insertCmd = new SQLiteCommand(insertQuery, sqlCon); insertCmd.Parameters.AddWithValue("@_mailaddress", mailaddr); insertCmd.Parameters.AddWithValue("@_password", password); insertCmd.Parameters.AddWithValue("@_pop_hostname", pop_hostname); insertCmd.Parameters.AddWithValue("@_pop_port", pop_port); insertCmd.Parameters.AddWithValue("@_pop_ssl", Convert.ToInt16(pop_ssl)); insertCmd.Parameters.AddWithValue("@_smtp_hostname", smtp_hostname); insertCmd.Parameters.AddWithValue("@_smtp_port", smtp_port); insertCmd.Parameters.AddWithValue("@_smtp_ssl", Convert.ToInt16(smtp_ssl)); if (!userExist) { // If the user didn't exist before, make a key to him/her user_key = MerMail.Symmetric.generateKey(); } insertCmd.Parameters.AddWithValue("@_encryption_key", user_key); insertCmd.ExecuteNonQuery(); // Now, we define the currentUser global, so that we know who the // current user is currentUser = new mailaccount(mailaddr, password, pop_hostname, pop_port, pop_ssl, smtp_hostname, smtp_port, smtp_ssl, user_key); // Is used for making a name of the message database file for the user string userMD5 = genMd5(mailaddr); // set up SQLite connection for message database // (the separate-per-user database) SQLiteConnectionStringBuilder conStr = new SQLiteConnectionStringBuilder(); conStr.DataSource = appdata + "/mermail/userdata/" + userMD5 + ".db"; conStr.Version = 3; msgSqlCon = new SQLiteConnection(conStr.ConnectionString); msgSqlCon.Open(); SQLiteCommand crCMD = new SQLiteCommand("CREATE TABLE IF NOT EXISTS messagetable (id CHAR(32) PRIMARY KEY NOT NULL,sender TEXT NOT NULL, subject TEXT, body TEXT, date DATETIME NOT NULL)", msgSqlCon); crCMD.ExecuteNonQuery(); return(0); }