// 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);
}
