public string CheckUserPassword(string user)
{
var database = new SqlDatabaseConnector();
database.Connector.Open();
string checkPasswordQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); CREATE TABLE IF NOT EXISTS Dogs(DogID int NOT NULL PRIMARY KEY AUTO_INCREMENT, OwnerID int, Name varchar(255), Race varchar(255), Notes varchar(255)); ALTER TABLE Dogs ADD FOREIGN KEY(OwnerID) REFERENCES Persons(PersonID); SELECT * FROM Dogs WHERE OwnerID IN(SELECT PersonID FROM Persons WHERE Nick = '{user}')";
var cmd = new MySqlCommand(checkPasswordQuery, database.Connector);
MySqlDataReader rdr = cmd.ExecuteReader();
var buff = new List <object>();
while (rdr.Read())
{
buff.Add(new {
DogId = rdr["DogID"],
OwnerId = rdr["OwnerID"],
Name = rdr["Name"],
Race = rdr["Race"],
Notes = rdr["Notes"]
});
}
var returnString = JsonConvert.SerializeObject(buff);
rdr.Close();
database.Connector.Close();
return(returnString);
}
private Version GetDatabaseVersion(SqlDatabaseConnector sqlDatabaseConnector)
{
Version databaseVersion;
SqlDataReader sqlDataReader = null;
try
{
var sqlCommand = new SqlCommand("SELECT * FROM tblRoot", sqlDatabaseConnector.SqlConnection);
sqlDataReader = sqlCommand.ExecuteReader();
if (!sqlDataReader.HasRows)
{
return(new Version()); // assume new empty database
}
else
{
sqlDataReader.Read();
}
databaseVersion = new Version(Convert.ToString(sqlDataReader["confVersion"], CultureInfo.InvariantCulture));
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, $"Retrieving database version failed. {ex}");
throw;
}
finally
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
}
return(databaseVersion);
}
private void UpdateRootNodeTable(RootNodeInfo rootTreeNode, SqlDatabaseConnector sqlDatabaseConnector)
{
var cryptographyProvider = new LegacyRijndaelCryptographyProvider();
string strProtected;
if (rootTreeNode != null)
{
if (rootTreeNode.Password)
{
_password = Convert.ToString(rootTreeNode.PasswordString).ConvertToSecureString();
strProtected = cryptographyProvider.Encrypt("ThisIsProtected", _password);
}
else
{
strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", _password);
}
}
else
{
strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", _password);
}
var sqlQuery = new SqlCommand("DELETE FROM tblRoot", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
sqlQuery = new SqlCommand("INSERT INTO tblRoot (Name, Export, Protected, ConfVersion) VALUES(\'" + MiscTools.PrepareValueForDB(rootTreeNode.Name) + "\', 0, \'" + strProtected + "\'," + ConnectionsFileInfo.ConnectionFileVersion.ToString(CultureInfo.InvariantCulture) + ")", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
}
public SqlConnectionsUpdateChecker()
{
_sqlConnector = new SqlDatabaseConnector();
_sqlQuery = new SqlCommand("SELECT * FROM tblUpdate", _sqlConnector.SqlConnection);
_lastUpdateTime = default(DateTime);
_lastDatabaseUpdateTime = default(DateTime);
}
private void UpdateUpdatesTable(SqlDatabaseConnector sqlDatabaseConnector)
{
var sqlQuery = new SqlCommand("DELETE FROM tblUpdate", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
sqlQuery = new SqlCommand("INSERT INTO tblUpdate (LastUpdate) VALUES(\'" + MiscTools.DBDate(DateTime.Now) + "\')", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
}
public SqlDatabaseVersionVerifier(SqlDatabaseConnector sqlDatabaseConnector)
{
if (sqlDatabaseConnector == null)
{
throw new ArgumentNullException(nameof(sqlDatabaseConnector));
}
_sqlDatabaseConnector = sqlDatabaseConnector;
}
public SqlVersion23To24Upgrader(SqlDatabaseConnector sqlDatabaseConnector)
{
if (sqlDatabaseConnector == null)
{
throw new ArgumentNullException(nameof(sqlDatabaseConnector));
}
_sqlDatabaseConnector = sqlDatabaseConnector;
}
private void UpdateConnectionsTable(ContainerInfo rootTreeNode, SqlDatabaseConnector sqlDatabaseConnector)
{
var sqlQuery = new SqlCommand("DELETE FROM tblCons", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
var serializer = new DataTableSerializer(_saveFilter);
var dataTable = serializer.Serialize(rootTreeNode);
var dataProvider = new SqlDataProvider(sqlDatabaseConnector);
dataProvider.Save(dataTable);
}
private bool VerifyDatabaseVersion(SqlDatabaseConnector sqlDatabaseConnector)
{
var isVerified = false;
try
{
var databaseVersion = GetDatabaseVersion(sqlDatabaseConnector);
SqlCommand sqlCommand;
if (databaseVersion.Equals(new Version()))
{
return(true);
}
if (databaseVersion.CompareTo(new Version(2, 2)) == 0) // 2.2
{
Runtime.MessageCollector.AddMessage(MessageClass.InformationMsg, $"Upgrading database from version {databaseVersion} to version 2.3.");
sqlCommand = new SqlCommand("ALTER TABLE tblCons ADD EnableFontSmoothing bit NOT NULL DEFAULT 0, EnableDesktopComposition bit NOT NULL DEFAULT 0, InheritEnableFontSmoothing bit NOT NULL DEFAULT 0, InheritEnableDesktopComposition bit NOT NULL DEFAULT 0;", sqlDatabaseConnector.SqlConnection);
sqlCommand.ExecuteNonQuery();
databaseVersion = new Version(2, 3);
}
if (databaseVersion.CompareTo(new Version(2, 3)) == 0) // 2.3
{
Runtime.MessageCollector.AddMessage(MessageClass.InformationMsg, $"Upgrading database from version {databaseVersion} to version 2.4.");
sqlCommand = new SqlCommand("ALTER TABLE tblCons ADD UseCredSsp bit NOT NULL DEFAULT 1, InheritUseCredSsp bit NOT NULL DEFAULT 0;", sqlDatabaseConnector.SqlConnection);
sqlCommand.ExecuteNonQuery();
databaseVersion = new Version(2, 4);
}
if (databaseVersion.CompareTo(new Version(2, 4)) == 0) // 2.4
{
Runtime.MessageCollector.AddMessage(MessageClass.InformationMsg, $"Upgrading database from version {databaseVersion} to version 2.5.");
sqlCommand = new SqlCommand("ALTER TABLE tblCons ADD LoadBalanceInfo varchar (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, AutomaticResize bit NOT NULL DEFAULT 1, InheritLoadBalanceInfo bit NOT NULL DEFAULT 0, InheritAutomaticResize bit NOT NULL DEFAULT 0;", sqlDatabaseConnector.SqlConnection);
sqlCommand.ExecuteNonQuery();
databaseVersion = new Version(2, 5);
}
if (databaseVersion.CompareTo(new Version(2, 5)) == 0) // 2.5
{
isVerified = true;
}
if (isVerified == false)
{
Runtime.MessageCollector.AddMessage(MessageClass.WarningMsg, string.Format(Language.strErrorBadDatabaseVersion, databaseVersion, GeneralAppInfo.ProductName));
}
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, string.Format(Language.strErrorVerifyDatabaseVersionFailed, ex.Message));
}
return(isVerified);
}
public bool AddUser(string user, string email, string password)
{
var database = new SqlDatabaseConnector();
database.Connector.Open();
string addUserQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); INSERT INTO Persons VALUES(null, '{user}', '{email}', '{password}')";
var data = new DataSet();
var recreate = new MySqlCommand(addUserQuery, database.Connector).ExecuteNonQuery();
database.Connector.Close();
return(true);
}
public bool AddDog(int ownerId, string dogName, string dogRace, string notes)
{
var database = new SqlDatabaseConnector();
database.Connector.Open();
string addUserQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); CREATE TABLE IF NOT EXISTS Dogs(DogID int NOT NULL PRIMARY KEY AUTO_INCREMENT, OwnerID int, Name varchar(255), Race varchar(255), Notes varchar(255)); ALTER TABLE Dogs ADD FOREIGN KEY(OwnerID) REFERENCES Persons(PersonID); INSERT INTO Dogs VALUES(null, '{ownerId}','{dogName}', '{dogRace}', '{notes}')";
var data = new DataSet();
var recreate = new MySqlCommand(addUserQuery, database.Connector).ExecuteNonQuery();
database.Connector.Close();
return(true);
}
public string GetResetDataBase()
{
var database = new SqlDatabaseConnector();
database.Connector.Open();
string resetDatabaseQuery = "CREATE DATABASE IF NOT EXISTS Swierzaki; USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); DROP TABLE IF EXISTS Dogs; CREATE TABLE Dogs(DogID int NOT NULL PRIMARY KEY AUTO_INCREMENT, OwnerID int, Name varchar(255), Race varchar(255), Notes varchar(255)); ALTER TABLE Dogs ADD FOREIGN KEY(OwnerID) REFERENCES Persons(PersonID); INSERT INTO Dogs VALUES(null, 1, 'Burek', 'Mieszaniec', 'AAA'); INSERT INTO Dogs VALUES(null, 2, 'Mialek', 'Quint', 'BBB'); ";
var data = new DataSet();
var recreate = new MySqlCommand(resetDatabaseQuery, database.Connector).ExecuteNonQuery();
database.Connector.Close();
return("Zresetowano bazę danych");
}
public SqlConnectionListMetaData GetDatabaseMetaData(SqlDatabaseConnector sqlDatabaseConnector)
{
SqlConnectionListMetaData metaData;
SqlDataReader sqlDataReader = null;
try
{
var sqlCommand = new SqlCommand("SELECT * FROM tblRoot", sqlDatabaseConnector.SqlConnection);
if (!sqlDatabaseConnector.IsConnected)
{
sqlDatabaseConnector.Connect();
}
sqlDataReader = sqlCommand.ExecuteReader();
if (!sqlDataReader.HasRows)
{
return(null); // assume new empty database
}
else
{
sqlDataReader.Read();
}
metaData = new SqlConnectionListMetaData
{
Name = sqlDataReader["Name"] as string ?? "",
Protected = sqlDataReader["Protected"] as string ?? "",
Export = (bool)sqlDataReader["Export"],
ConfVersion = new Version(Convert.ToString(sqlDataReader["confVersion"], CultureInfo.InvariantCulture))
};
}
catch (Exception ex)
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, $"Retrieving database version failed. {ex}");
throw;
}
finally
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
}
return(metaData);
}
public string GetResetDataBase()
{
try
{
var database = new SqlDatabaseConnector();
database.Connector.Open();
string resetDatabaseQuery = "CREATE DATABASE IF NOT EXISTS Swierzaki; USE Swierzaki; DROP TABLE IF EXISTS Persons; CREATE TABLE Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); INSERT INTO Persons VALUES(null, 'Muffy', '*****@*****.**', 'AAA'); INSERT INTO Persons VALUES(null, 'Admin', '*****@*****.**', 'AAA');";
var data = new DataSet();
var recreate = new MySqlCommand(resetDatabaseQuery, database.Connector).ExecuteNonQuery();
database.Connector.Close();
}
catch (Exception e)
{
return(e.Message);
}
return("Zresetowano bazę danych");
}
public ConnectionTreeModel LoadConnections(bool import)
{
IDeserializer deserializer;
if (UseDatabase)
{
var connector = new SqlDatabaseConnector();
var dataProvider = new SqlDataProvider(connector);
var dataTable = dataProvider.Load();
deserializer = new DataTableDeserializer(dataTable);
}
else
{
var dataProvider = new FileDataProvider(ConnectionFileName);
var xmlString = dataProvider.Load();
deserializer = new XmlConnectionsDeserializer(xmlString)
{
AuthenticationRequestor = PromptForPassword
};
}
var connectionTreeModel = deserializer.Deserialize();
if (connectionTreeModel != null)
{
frmMain.Default.ConnectionsFileName = ConnectionFileName;
}
else
{
connectionTreeModel = new ConnectionTreeModel();
}
if (import)
{
return(connectionTreeModel);
}
PuttySessionsManager.Instance.AddSessions();
connectionTreeModel.RootNodes.AddRange(PuttySessionsManager.Instance.RootPuttySessionsNodes);
return(connectionTreeModel);
}
public void WriteDatabaseMetaData(RootNodeInfo rootTreeNode, SqlDatabaseConnector sqlDatabaseConnector)
{
var cryptographyProvider = new LegacyRijndaelCryptographyProvider();
string strProtected;
if (rootTreeNode != null)
{
if (rootTreeNode.Password)
{
var password = rootTreeNode.PasswordString.ConvertToSecureString();
strProtected = cryptographyProvider.Encrypt("ThisIsProtected", password);
}
else
{
strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", Runtime.EncryptionKey);
}
}
else
{
strProtected = cryptographyProvider.Encrypt("ThisIsNotProtected", Runtime.EncryptionKey);
}
var sqlQuery = new SqlCommand("DELETE FROM tblRoot", sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
if (rootTreeNode != null)
{
sqlQuery =
new SqlCommand(
"INSERT INTO tblRoot (Name, Export, Protected, ConfVersion) VALUES(\'" +
MiscTools.PrepareValueForDB(rootTreeNode.Name) + "\', 0, \'" + strProtected + "\'," +
ConnectionsFileInfo.ConnectionFileVersion.ToString(CultureInfo.InvariantCulture) + ")",
sqlDatabaseConnector.SqlConnection);
sqlQuery.ExecuteNonQuery();
}
else
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, $"UpdateRootNodeTable: rootTreeNode was null. Could not insert!");
}
}
private void SaveToSql()
{
var sqlConnector = new SqlDatabaseConnector();
sqlConnector.Connect();
if (!VerifyDatabaseVersion(sqlConnector))
{
Runtime.MessageCollector.AddMessage(MessageClass.ErrorMsg, Language.strErrorConnectionListSaveFailed);
return;
}
var rootTreeNode = Runtime.ConnectionTreeModel.RootNodes.OfType <RootNodeInfo>().First();
UpdateRootNodeTable(rootTreeNode, sqlConnector);
UpdateConnectionsTable(rootTreeNode, sqlConnector);
UpdateUpdatesTable(sqlConnector);
sqlConnector.Disconnect();
sqlConnector.Dispose();
}
public bool CheckUserPassword(string user, string password)
{
var database = new SqlDatabaseConnector();
database.Connector.Open();
string checkPasswordQuery = $"USE Swierzaki; CREATE TABLE IF NOT EXISTS Persons (PersonID int NOT NULL PRIMARY KEY AUTO_INCREMENT, Nick varchar(255), Email varchar(255), Password varchar(255) ); select PersonID from Persons where Nick = '{user}' and Password = '******'";
var cmd = new MySqlCommand(checkPasswordQuery, database.Connector);
MySqlDataReader rdr = cmd.ExecuteReader();
string buff = null;
while (rdr.Read())
{
buff = rdr[rdr.GetName(0)].ToString();
}
rdr.Close();
database.Connector.Close();
return(!String.IsNullOrEmpty(buff));
}
private SqlConnectionListMetaData HandleFirstRun(SqlDatabaseMetaDataRetriever metaDataRetriever, SqlDatabaseConnector connector)
{
metaDataRetriever.WriteDatabaseMetaData(new RootNodeInfo(RootNodeType.Connection), connector);
return(metaDataRetriever.GetDatabaseMetaData(connector));
}
public SqlDataProvider(SqlDatabaseConnector sqlDatabaseConnector)
{
SqlDatabaseConnector = sqlDatabaseConnector;
}
