// <summary> // Get values for the giving account // </summary> public void GetValues(string token) { // MySQL query const string result = "SELECT id, voornaam, tussenvoegsel, achternaam, email, pepper " + "FROM meok2_bibliotheek_gebruikers"; using (var empConnection = DatabaseConnection.DatabaseConnect()) { using (var showresult = new MySqlCommand(result, empConnection)) { try { DatabaseConnection.DatabaseOpen(empConnection); // Execute command using (var reader = showresult.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { // Check if the MD5 hash mathes using (var md5Hash = MD5.Create()) { if (!Crypt.VerifyMd5Hash(md5Hash, reader.GetValue(4).ToString(), token)) { continue; } // Save the values var id = reader.GetValue(0).ToString(); var pepper = reader.GetValue(5).ToString(); if (id != "-1") { // Save values to the model Id = Convert.ToInt16(id); Firstname = SqlInjection.SafeSqlLiteralRevert( Crypt.StringDecrypt(reader.GetValue(1).ToString(), pepper)); Affix = SqlInjection.SafeSqlLiteralRevert( Crypt.StringDecrypt(reader.GetValue(2).ToString(), pepper)); Lastname = SqlInjection.SafeSqlLiteralRevert( Crypt.StringDecrypt(reader.GetValue(3).ToString(), pepper)); Mail = SqlInjection.SafeSqlLiteralRevert(reader.GetValue(4).ToString()); Pepper = pepper; } } } } } catch (MySqlException) { // MySqlException bail out } finally { DatabaseConnection.DatabaseClose(empConnection); } } } }
// <summary> // Select all users from database // </summary> public static List <String> AllUsers() { // Initial vars var list = new List <String>(); // MySQL query const string result = "SELECT id, voornaam, tussenvoegsel, achternaam, pepper, email " + "FROM meok2_bibliotheek_gebruikers"; using (var empConnection = DatabaseConnection.DatabaseConnect()) { using (var showresult = new MySqlCommand(result, empConnection)) { try { DatabaseConnection.DatabaseOpen(empConnection); // Execute command using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection)) { while (myDataReader.Read()) { // Save the values var id = myDataReader.GetString(0); var pepper = myDataReader.GetString(4); var name = SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(myDataReader.GetString(1), pepper)); var affix = myDataReader.GetString(2); var lastname = SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(myDataReader.GetString(3), pepper)); var email = myDataReader.GetString(5); if (!String.IsNullOrEmpty(affix)) { name = name + " " + SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(affix, pepper)); } name = name + " " + lastname; list.Add(id); list.Add(name); list.Add(email); } } } catch (MySqlException) { // MySqlException bail out } finally { DatabaseConnection.DatabaseClose(empConnection); } } } return(list); }
// <summary> // select book from the database // </summary> public static List <String> SelectBookById(String id) { // Initial vars var list = new List <String>(); // MySQL query const string result = "SELECT titel, auteur, genre, isbn, verdieping, rek, amount " + "FROM meok2_bibliotheek_boeken " + "WHERE id = ?"; using (var empConnection = DatabaseConnection.DatabaseConnect()) { using (var showresult = new MySqlCommand(result, empConnection)) { // Bind parameters showresult.Parameters.Add("id", MySqlDbType.VarChar).Value = SqlInjection.SafeSqlLiteral(id); try { DatabaseConnection.DatabaseOpen(empConnection); // Execute command using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection)) { while (myDataReader.Read()) { // Save the values list.Add(SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(0))); list.Add(SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(1))); list.Add(myDataReader.GetString(2)); list.Add(SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(3))); list.Add(myDataReader.GetString(4)); list.Add(myDataReader.GetString(5)); list.Add(myDataReader.GetString(6)); } } } catch (MySqlException) { // MySqlException bail out } finally { DatabaseConnection.DatabaseClose(empConnection); } } } return(list); }
public void GetSinglePage(int id) { // Initial vars var list = new List <String>(); // MySQL query const string selectStatment = "SELECT Title, Description, Blog, Menu " + "FROM pages " + "WHERE Id = ?"; using (var empConnection = DatabaseConnection.DatabaseConnect()) { using (var selectCommand = new MySqlCommand(selectStatment, empConnection)) { selectCommand.Parameters.Add("Id", MySqlDbType.Int16).Value = id; try { DatabaseConnection.DatabaseOpen(empConnection); // Execute command using (var myDataReader = selectCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (myDataReader.Read()) { // Save the values Title = SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(0)); Description = SqlInjection.SafeSqlLiteralRevert(myDataReader.GetString(1)); Type = myDataReader.GetInt16(2); Menu = myDataReader.GetInt16(3); } } } catch (MySqlException) { // MySqlException bail out } finally { // Always close the connection DatabaseConnection.DatabaseClose(empConnection); } } } }
public static List <String> GetAccountDetails() { // Initial vars var list = new List <String>(); // MySQL query Select book in the database const string result = "SELECT voornaam, tussenvoegsel, achternaam, postcode, huisnummer, geslacht, dob, email, pepper " + "FROM meok2_bibliotheek_gebruikers " + "WHERE email = ?"; using (var empConnection = DatabaseConnection.DatabaseConnect()) { using (var showresult = new MySqlCommand(result, empConnection)) { showresult.Parameters.Add("id", MySqlDbType.VarChar).Value = IdentityModel.CurrentUserId; try { DatabaseConnection.DatabaseOpen(empConnection); // Execute command using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection)) { while (myDataReader.Read()) { // Save the values var pepper = myDataReader.GetString(8); var name = SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(myDataReader.GetString(0), pepper)); var affix = myDataReader.GetString(1); var gender = myDataReader.GetString(5); var dob = myDataReader.GetDateTime(6); var mail = myDataReader.GetString(7); if (!String.IsNullOrEmpty(affix)) { name = name + " " + SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(affix, pepper)); } name = name + " " + SqlInjection.SafeSqlLiteralRevert(Crypt.StringDecrypt(myDataReader.GetString(2), pepper)); var today = DateTime.Today; var age = today.Year - dob.Year; if (dob > today.AddYears(-age)) { age--; } var request = WebRequest.Create("http://geonl.ict-lab.nl/ajax/checkq1.php?p=" + SqlInjection.SafeSqlLiteralRevert( Crypt.StringDecrypt(myDataReader.GetString(3), pepper))); var response = request.GetResponse(); var data = response.GetResponseStream(); string html; if (data == null) { continue; } using (var sr = new StreamReader(data)) { html = sr.ReadToEnd(); } var address = html.Split('"')[3] + " " + SqlInjection.SafeSqlLiteralRevert( Crypt.StringDecrypt(myDataReader.GetString(4), pepper)) + "," + SqlInjection.SafeSqlLiteralRevert( Crypt.StringDecrypt(myDataReader.GetString(3), pepper)) + "," + html.Split('"')[15]; list.Add(name); list.Add(address); list.Add(gender == "0" ? "Man" : "Vrouw"); list.Add(age.ToString(CultureInfo.InvariantCulture)); list.Add(mail); } } } catch (MySqlException) { // MySqlException } finally { DatabaseConnection.DatabaseClose(empConnection); } } } return(list); }