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