// <summary> Adds a new user to the database </summery>
public void AddUser()
{
// Run model through sql injection prevention
var fullName = SqlInjection.SafeSqlLiteral(Name);
var username = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Username));
var salt = Crypt.GetRandomSalt();
// TFA code
var buffer = new byte[9];
using (var rng = RandomNumberGenerator.Create())
{
rng.GetBytes(buffer);
}
// Generates a 10 character string of A-Z, a-z, 0-9 Don't need to worry about any =
// padding from the Base64 encoding, since our input buffer is divisible by 3
var secret = Convert.ToBase64String(buffer).Substring(0, 10).Replace('/', '0').Replace('+', '1');
// MySql query
const string insertStatement = "INSERT INTO users " +
"(Name, Username, Password, Salt, Secret) " +
"VALUES (?, ?, ?, ?, ?)";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("Name", MySqlDbType.VarChar).Value = fullName;
insertCommand.Parameters.Add("Username", MySqlDbType.VarChar).Value = username;
insertCommand.Parameters.Add("Password", MySqlDbType.VarChar).Value = Crypt.HashPassword(Password,
salt);
insertCommand.Parameters.Add("Salt", MySqlDbType.VarChar).Value = salt;
insertCommand.Parameters.Add("Secret", MySqlDbType.VarChar).Value = secret;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
Done = true;
}
public bool Login()
{
var email = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Email));
var password = Password;
var savedPassword = String.Empty;
var savedSalt = String.Empty;
var savedId = String.Empty;
// MySQL query
const string result = "SELECT id, password, salt, admin " +
"FROM meok2_bibliotheek_gebruikers " +
"WHERE email = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
// Bind parameters
showresult.Parameters.Add("email", MySqlDbType.VarChar).Value = email;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (myDataReader.Read())
{
// Save the values
savedId = myDataReader.GetValue(0).ToString();
savedPassword = myDataReader.GetString(1);
savedSalt = myDataReader.GetString(2);
Admin = Convert.ToInt16(myDataReader.GetValue(3));
}
}
// Hash the password and check if the hash is the same as the saved password
if (Crypt.ValidatePassword(password, savedPassword, savedSalt))
{
Cookies.MakeCookie(email, savedId, Admin.ToString(CultureInfo.InvariantCulture));
return(true);
}
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
return(false);
}
public string MailCheck(string input)
{
// Validate email
if (ValidateEmail.IsValidEmail(input))
{
return(RegisterModel.CheckMail(SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(input))) > 0
? "Deze email is al bezet"
: "Deze email is nog niet bezet");
}
return("Dit is geen geldig email adres");
}
// <summary>
// Add book to the database
// </summary>
public bool AddBook()
{
// Run model through sql prevention and save them to vars
var title = SqlInjection.SafeSqlLiteral(Title);
var author = SqlInjection.SafeSqlLiteral(Author);
var genre = Genre;
var isbn = SqlInjection.SafeSqlLiteral(Isbn);
var floor = Floor;
var rack = Rack;
var dateAdded = StringManipulation.DateTimeToMySql(DateTime.Now);
// MySQL query Insert book in the database
const string insertStatement = "INSERT INTO meok2_bibliotheek_boeken " +
"(titel, auteur, genre, isbn, verdieping, rek, dateadded) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("titel", MySqlDbType.VarChar).Value = title;
insertCommand.Parameters.Add("auteur", MySqlDbType.VarChar).Value = author;
insertCommand.Parameters.Add("genre", MySqlDbType.Int16).Value = genre;
insertCommand.Parameters.Add("isbn", MySqlDbType.VarChar).Value = isbn;
insertCommand.Parameters.Add("verdieping", MySqlDbType.Int16).Value = floor;
insertCommand.Parameters.Add("rek", MySqlDbType.Int16).Value = rack;
insertCommand.Parameters.Add("dateadded", MySqlDbType.Date).Value = dateAdded;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
// Return
return(true);
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
// Make sure to close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
}
// <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 SavePage(int id)
{
// Run model through sql injection prevention
var title = SqlInjection.SafeSqlLiteral(Title);
var description = SqlInjection.SafeSqlLiteral(Description);
// MySQL query
const string updateStatement = "UPDATE pages " +
"SET Title = ?, " +
"Description = ?, " +
"Blog = ?," +
"Menu = ?," +
"Content = ? " +
"WHERE Id = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var updateCommand = new MySqlCommand(updateStatement, empConnection))
{
updateCommand.Parameters.Add("Title", MySqlDbType.VarChar).Value = title;
updateCommand.Parameters.Add("Description", MySqlDbType.VarChar).Value = description;
updateCommand.Parameters.Add("Blog", MySqlDbType.VarChar).Value = Type;
updateCommand.Parameters.Add("Menu", MySqlDbType.VarChar).Value = Menu;
updateCommand.Parameters.Add("Content", MySqlDbType.VarChar).Value = Content;
updateCommand.Parameters.Add("Id", MySqlDbType.Int16).Value = id;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
updateCommand.ExecuteNonQuery();
Done = true;
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
}
// <summary>
// Select author from database
// </summary>
public static List <String> SelectAuthors(String name)
{
// Initial vars
var list = new List <String>();
// MySQL query
const string result = "SELECT id, titel, genre " +
"FROM meok2_bibliotheek_boeken " +
"WHERE auteur = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
// Bind parameters
showresult.Parameters.Add("auteur", MySqlDbType.VarChar).Value = SqlInjection.SafeSqlLiteral(name);
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var myDataReader = showresult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (myDataReader.Read())
{
// Save the values
list.Add(myDataReader.GetString(0));
list.Add(myDataReader.GetString(1));
list.Add(myDataReader.GetString(2));
}
}
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
return(list);
}
public ActionResult Activate()
{
// Redirect if the user is logged in already
if (IdentityModel.CurrentUserLoggedIn)
{
return(RedirectToAction("Account", "Logged"));
}
var model = new ActivateModel
{
// Set default
Gender = 0
};
string token;
try
{
// Get the token from the RouteData
token = SqlInjection.SafeSqlLiteral(Url.RequestContext.RouteData.Values["id"].ToString());
}
// ReSharper disable EmptyGeneralCatchClause
catch (Exception)
// ReSharper restore EmptyGeneralCatchClause
{
return(RedirectToAction("Index", "Home"));
}
// Redirect if the token is invalid or missing
if (String.IsNullOrEmpty(token) || token.Length != 32)
{
return(RedirectToAction("Index", "Home"));
}
if (!ActivateModel.CheckAccount(token))
{
return(RedirectToAction("Account", "Logged"));
}
// Get values form the database
model.GetValues(token);
return(View(model));
}
// <summary>
// Update website settings
// </summary>
public void SaveSettings()
{
// MySQL query
const string result = "UPDATE site " +
"SET " +
"Header = ?, " +
"Footer = ?, " +
"FooterText = ?, " +
"ColorScheme = ? " +
"WHERE id = 1";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showresult = new MySqlCommand(result, empConnection))
{
// Bind parameters
showresult.Parameters.Add("Header", MySqlDbType.Int16).Value = Header;
showresult.Parameters.Add("Footer", MySqlDbType.Int16).Value = Footer;
showresult.Parameters.Add("FooterText", MySqlDbType.VarChar).Value =
SqlInjection.SafeSqlLiteral(FooterText);
showresult.Parameters.Add("ColorScheme", MySqlDbType.VarChar).Value = ColorScheme;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
showresult.ExecuteNonQuery();
}
catch (MySqlException)
{
// MySqlException bail out
return;
}
finally
{
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
Done = true;
}
public void NewPage()
{
// Run model through sql injection prevention
var title = SqlInjection.SafeSqlLiteral(Title);
var description = SqlInjection.SafeSqlLiteral(Description);
// MySql query
const string insertStatement = "INSERT INTO pages " +
"(Title, Description, Blog, Menu) " +
"VALUES (?, ?, ?, ?)";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("Title", MySqlDbType.VarChar).Value = title;
insertCommand.Parameters.Add("Description", MySqlDbType.VarChar).Value = description;
insertCommand.Parameters.Add("Blog", MySqlDbType.VarChar).Value = Type;
insertCommand.Parameters.Add("Menu", MySqlDbType.VarChar).Value = Menu;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
Done = true;
}
public ActionResult Activate(ActivateModel model)
{
string token;
try
{
// Get the token from the RouteData
token = SqlInjection.SafeSqlLiteral(Url.RequestContext.RouteData.Values["id"].ToString());
}
// ReSharper disable EmptyGeneralCatchClause
catch (Exception)
// ReSharper restore EmptyGeneralCatchClause
{
return(RedirectToAction("Index", "Home"));
}
if (String.IsNullOrEmpty(token) || token.Length != 32)
{
return(RedirectToAction("Index", "Home"));
}
// Load in values from database
model.GetValues(token);
// Make Postal code upperCase, remove spaces and encrypt the string
model.PostalCode =
Crypt.StringEncrypt(
SqlInjection.SafeSqlLiteral(StringManipulation.ToUpperFast(model.PostalCode))
.Replace(" ", string.Empty), model.Pepper);
model.HouseNumber = Crypt.StringEncrypt(SqlInjection.SafeSqlLiteral(model.HouseNumber), model.Pepper);
// If UpdateAccount fails show error page
if (!model.UpdateAccount())
{
return(View("Error"));
}
// Make cookie for user
Cookies.MakeCookie(model.Mail, model.Id.ToString(CultureInfo.InvariantCulture), "0");
return(RedirectToAction("Account", "Logged"));
}
public void NewPost()
{
// Run model through sql injection prevention
var title = SqlInjection.SafeSqlLiteral(Title);
// MySql query
const string insertStatement = "INSERT INTO posts " +
"(Title, Post, Author) " +
"VALUES (?, ?, ?)";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("Title", MySqlDbType.VarChar).Value = title;
insertCommand.Parameters.Add("Post", MySqlDbType.VarChar).Value = Content;
insertCommand.Parameters.Add("Author", MySqlDbType.VarChar).Value = IdentityModel.CurrentUserName.CaptalizeFirstLetter();
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
Done = true;
}
public string UsernameCheck(string input)
{
return(UserModel.UsernameCheck(SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(input))) > 0
? "taken"
: String.Empty);
}
public string TfaCheck(string input)
{
return(UserModel.TfaCheck(SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(input))) > 0
? String.Empty
: "tfa");
}
// <summary> Check if the username and password are the same as in the database </summery>
public void Login()
{
// Run model through sql injection prevention
var username = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Username));
var savedPassword = String.Empty;
var savedSalt = String.Empty;
var savedId = String.Empty;
var code = String.Empty;
// MySql query
const string result = "SELECT Id, Password, Salt, Owner, Secret, Tfa " +
"FROM users " +
"WHERE Username = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var showResult = new MySqlCommand(result, empConnection))
{
// Bind parameters
showResult.Parameters.Add("Username", MySqlDbType.VarChar).Value = username;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
using (var myDataReader = showResult.ExecuteReader(CommandBehavior.CloseConnection))
{
while (myDataReader.Read())
{
savedId = myDataReader.GetValue(0).ToString();
savedPassword = myDataReader.GetString(1);
savedSalt = myDataReader.GetString(2);
Owner = Convert.ToInt16(myDataReader.GetValue(3));
code = myDataReader.GetString(4);
TwoFactorEnabled = Convert.ToInt16(myDataReader.GetValue(5));
}
}
// Hash the password and check if the hash is the same as the saved password
if (Crypt.ValidatePassword(Password, savedPassword, savedSalt))
{
if (TwoFactorEnabled == 0 && PluginModel.PluginStatus("1"))
{
if (TimeBasedOneTimePassword.IsValid(code, TwoFactorCode))
{
Cookies.MakeCookie(username, savedId, Owner.ToString(CultureInfo.InvariantCulture));
Done = true;
}
else
{
ErrorCode = true;
}
}
else
{
Cookies.MakeCookie(username, savedId, Owner.ToString(CultureInfo.InvariantCulture));
Done = true;
}
}
}
catch (MySqlException)
{
// MySqlException bail out
Error = true;
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
Error = true;
}
// <summary>
// Add account to the database and send a mail to the user
// </summary>
public bool AddAccount()
{
// Run model through sql prevention and save them to vars
var firstName = SqlInjection.SafeSqlLiteral(Firstname);
var affix = SqlInjection.SafeSqlLiteral(Affix);
var lastName = SqlInjection.SafeSqlLiteral(Lastname);
var mail = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Mail));
var pepper = Crypt.GetRandomSalt();
// Validate email using regex since HTML5 validation doesn't handle some cases
if (!ValidateEmail.IsValidEmail(mail))
{
return(false);
}
// MySQL query
const string countStatement = "SELECT COUNT(*) " +
"FROM meok2_bibliotheek_gebruikers " +
"WHERE email = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
int count;
using (var countCommand = new MySqlCommand(countStatement, empConnection))
{
// Bind parameters
countCommand.Parameters.Add("email", MySqlDbType.VarChar).Value = mail;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
count = Convert.ToInt32(countCommand.ExecuteScalar());
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
// Make sure to close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
if (count > 0)
{
// Email already in the database bail out
return(false);
}
// Insert user in the database
const string insertStatement = "INSERT INTO meok2_bibliotheek_gebruikers " +
"(voornaam, tussenvoegsel, achternaam, email, pepper) " +
"VALUES (?, ?, ?, ?, ?)";
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("voornaam", MySqlDbType.VarChar).Value =
Crypt.StringEncrypt((firstName), pepper);
insertCommand.Parameters.Add("tussenvoegsel", MySqlDbType.VarChar).Value =
Crypt.StringEncrypt((affix), pepper);
insertCommand.Parameters.Add("achternaam", MySqlDbType.VarChar).Value =
Crypt.StringEncrypt((lastName), pepper);
insertCommand.Parameters.Add("email", MySqlDbType.VarChar).Value = mail;
insertCommand.Parameters.Add("pepper", MySqlDbType.VarChar).Value = pepper;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
// Send mail bail out if mail fails
return(Message.SendMail(firstName, Mail) == "False");
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
// Make sure to close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
}
public bool AddAccount()
{
// Run model through sql prevention and save them to vars
var mail = SqlInjection.SafeSqlLiteral(StringManipulation.ToLowerFast(Email));
var salt = Crypt.GetRandomSalt();
// Validate email using regex since HTML5 validation doesn't handle some cases
if (!ValidateEmail.IsValidEmail(mail))
{
return(false);
}
// MySQL query
const string countStatement = "SELECT COUNT(*) " +
"FROM gebruikers " +
"WHERE Email = ?";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
int count;
using (var countCommand = new MySqlCommand(countStatement, empConnection))
{
// Bind parameters
countCommand.Parameters.Add("Email", MySqlDbType.VarChar).Value = mail;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
count = Convert.ToInt32(countCommand.ExecuteScalar());
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
// Make sure to close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
if (count > 0)
{
// Email already in the database bail out
return(false);
}
// Insert user in the database
const string insertStatement = "INSERT INTO gebruikers " +
"(Email, Password, Salt) " +
"VALUES (?, ?, ?)";
using (var insertCommand = new MySqlCommand(insertStatement, empConnection))
{
// Bind parameters
insertCommand.Parameters.Add("Email", MySqlDbType.VarChar).Value = mail;
insertCommand.Parameters.Add("Password", MySqlDbType.VarChar).Value = Crypt.HashPassword(Password, salt);
insertCommand.Parameters.Add("Salt", MySqlDbType.VarChar).Value = salt;
try
{
DatabaseConnection.DatabaseOpen(empConnection);
// Execute command
insertCommand.ExecuteNonQuery();
return(true);
}
catch (MySqlException)
{
// MySqlException bail out
return(false);
}
finally
{
// Make sure to close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
}
// <summary> Adds a new user to the database </summery>
public static bool SaveEmail(string input)
{
// MySQL query
const string updateStatment = "UPDATE contactplugin " +
"SET Email = ? " +
"Where Id = 1";
using (var empConnection = DatabaseConnection.DatabaseConnect())
{
using (var updateCommand = new MySqlCommand(updateStatment, empConnection))
{
updateCommand.Parameters.Add("Email", MySqlDbType.VarChar).Value = SqlInjection.SafeSqlLiteral(input);
try
{
DatabaseConnection.DatabaseOpen(empConnection);
updateCommand.ExecuteScalar();
return(true);
}
catch (MySqlException)
{
// MySqlException bail out
}
finally
{
// Always close the connection
DatabaseConnection.DatabaseClose(empConnection);
}
}
}
return(false);
}
