// used after login is validated, no security in this other than simple sql illegals, validation should be implemented in controller
public async Task <string> SetLoginSessionIdAsync(User user, string tableName = UserTableName)
{
if (user == null)
{
return(null);
}
if (SqlSecurity.ContainsIllegals(user.UserId.ToString()))
{
return(null);
}
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
// Set user's session column to string sessionId and return so we can return Json
var _sessionId = KeyGeneration.GenerateSession();
user.LoginSession = _sessionId;
string sql = string.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = {4}", tableName, LoginSessKey, _sessionId, UserIdKey, user.UserId);
using (SqlCommand insertSession = new SqlCommand(sql, connection))
{
await insertSession.ExecuteNonQueryAsync();
return(_sessionId);
}
}
}
public async Task ServerWriteAsync(string logMsg, string tableName = UserSqlContext.DebugTable)
{
if (SqlSecurity.ContainsIllegals(logMsg))
{
logMsg = SqlSecurity.RemoveIllegals(logMsg);
}
string[] batchLog = BreakIntoBatch(logMsg);
try
{
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
foreach (var logItem in batchLog)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} ({1}, {2})", tableName, UserSqlContext.DebugIdKey, UserSqlContext.ConsoleWriteKey);
sb.AppendFormat("VALUES ('{0}', '{1}')", DebuggerContext, logMsg);
String sql = sb.ToString();
SqlCommand writeCmd = new SqlCommand(sql, connection);
await writeCmd.ExecuteNonQueryAsync();
}
}
}
catch (SqlException e)
{
Console.WriteLine("We're f****d the debuggers not even working." + " : " + e);
}
}
public async Task <ActionResult> Index(LoginRequestModel loginRequestModel)
{
try
{
var security = new SqlSecurity(ConfigurationManager.ConnectionStrings["WhosOnFirstDb"].ConnectionString);
var userValidation = await security.AuthenticateAsync(loginRequestModel.userName, loginRequestModel.password);
var person = new Person();
var personManager =
new PersonManager(ConfigurationManager.ConnectionStrings["WhosOnFirstDb"].ConnectionString);
if (userValidation.PersonId != 0)
{
person = await personManager.RetrieveAsync(userValidation.PersonId);
}
else
{
person.IsValid = false;
}
if (person.IsValid)
{
var userModel = new UserModel();
userModel.UserName = userValidation.UserName;
userModel.PersonId = person.PersonId;
userModel.FirstName = person.FirstName;
userModel.LastName = person.LastName;
userModel.PhoneNumber = person.PhoneNumber;
userModel.EMail = person.EMail;
userModel.IsPlayer = person.IsPlayer;
userModel.IsCoach = person.IsCoach;
userModel.IsValid = person.IsValid;
userModel.TeamId = person.TeamId;
Session["userModel"] = userModel;
if (person.IsPlayer)
{
return(RedirectToAction("PlayerIndex", "Player"));
}
else if (person.IsCoach)
{
return(RedirectToAction("CoachIndex", "Coach"));
}
}
else
{
ViewBag.Warning = "That was not a valid login. Try again.";
return(View());
}
}
catch (Exception)
{
ViewBag.Warning = "That was not a valid login. Try again.";
return(View());
}
return(View());
}
// Get User Methods \\
// grab user by phone, careful not very secure
public async Task <User> GetUserByPhoneAsync(string _phoneNumber, string tableName = UserTableName, string phoneSqlColumnName = PhoneSqlKey)
{
if (SqlSecurity.ContainsIllegals(_phoneNumber))
{
return(null);
}
try
{
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
string phoneType = string.Empty;
phoneType = phoneSqlColumnName;
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT * FROM {0} WHERE {1} = '{2}';", tableName, phoneType, _phoneNumber);
string sql = sb.ToString();
using (SqlCommand command = new SqlCommand(sql, connection))
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
User returnUser = null;
// Normal Users Table
if (tableName == UserTableName)
{
returnUser = await GetUserFromReaderAsync(reader);
}
// Registration Table
else if (tableName == SmsRegistrationTable)
{
returnUser = await GetTempUserFromReaderAsync(reader);
}
return(returnUser);
}
}
}
catch (SqlException e)
{
await SqlDebugger.Instance.WriteErrorAsync(e);
return(null);
}
}
public ActionResult RegisterUser(RegisterUserModel user)
{
try
{
var security = new SqlSecurity(ConfigurationManager.ConnectionStrings["WhosOnFirstDb"].ConnectionString);
var userValidation = new UserValidation();
var person = new Person();
userValidation.UserName = user.UserName;
userValidation.Password = user.Password;
if (user.UserRole == "Coach")
{
user.IsPlayer = false;
user.IsCoach = true;
}
else if (user.UserRole == "Player")
{
user.IsPlayer = true;
user.IsCoach = false;
}
else
{
user.IsPlayer = true;
user.IsCoach = false;
}
person.IsPlayer = user.IsPlayer;
person.IsCoach = user.IsCoach;
person.IsValid = true;
person.IsAdmin = false;
person.FirstName = user.FirstName;
person.LastName = user.LastName;
person.EMail = user.EMail;
person.PhoneNumber = user.PhoneNumber;
//TempData["RegistrationInfo"] = user;
security.RegisterUserAsync(userValidation, person);
return(RedirectToAction("Index"));
}
catch (Exception)
{
ViewBag.Warning = "Invalid or Blank registration! Try again.";
return(View());
}
}
// grab a user from their id, useful and fast when we already know the user we are dealing with has been secured and validated
private int GetUserId(SqlConnection connection, string session, string tableName = UserTableName)
{
if (SqlSecurity.ContainsIllegals(session))
{
return(-1);
}
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} = '{3}'", UserIdKey, tableName, LoginSessKey, session);
using (SqlCommand checkExists = new SqlCommand(sql, connection))
{
int?userId = (int)checkExists.ExecuteScalar();
if (userId == null)
{
return(-1);
}
return((int)userId);
}
}
// gets a user from the user table or registration table (user is default) based on reg/login session
public async Task <User> GetUserFromSessionAsync(string session, string tableName = UserTableName)
{
if (SqlSecurity.ContainsIllegals(session))
{
return(null);
}
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
string sessionKey = string.Empty;
if (tableName == UserTableName)
{
sessionKey = LoginSessKey;
}
else if (tableName == SmsRegistrationTable)
{
sessionKey = RegSessKey;
}
string sql = string.Format("SELECT * FROM {0} WHERE {1} = '{2}'", tableName, sessionKey, session);
using (SqlCommand command = new SqlCommand(sql, connection))
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
User user = null;
if (tableName == UserTableName)
{
user = await GetUserFromReaderAsync(reader);
}
else if (tableName == SmsRegistrationTable)
{
user = await GetTempUserFromReaderAsync(reader);
}
return(user);
}
}
}
// Grabs user from registration table in the registration/auth endpoint from a token that is posted
public async Task <User> GetTempUserFromTokenAsync(string token, string tableName = SmsRegistrationTable)
{
if (SqlSecurity.ContainsIllegals(token))
{
return(null);
}
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
string sql = string.Format("SELECT * FROM {0} WHERE {1} = '{2}'", tableName, TokenKey, token);
using (SqlCommand command = new SqlCommand(sql, connection))
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
User user = null;
user = await GetTempUserFromReaderAsync(reader);
return(user);
}
}
}
// create a temporary registration user with their contact method and token, not very secure, implement validation in controller.
public async Task <bool> CreateTempUserAsync(User user, string tableName = SmsRegistrationTable)
{
string[] sqlStrs = { user.PhoneNumber, user.Email, user.Token };
if (SqlSecurity.BatchContainsIllegals(sqlStrs))
{
return(false);
}
try
{
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} ({1}, {2}, {3}, {4})", tableName, RegSessKey, TokenKey, EmailSqlKey, PhoneSqlKey);
sb.AppendFormat("VALUES ('{0}', '{1}', '{2}', '{3}');", user.RegistrationSession, user.Token, user.Email, user.PhoneNumber);
string sql = sb.ToString();
using (SqlCommand createUser = new SqlCommand(sql, connection))
{
int rowsEff = await createUser.ExecuteNonQueryAsync();
if (rowsEff > 0)
{
return(true);
}
return(false);
}
}
}
catch (SqlException ex)
{
await SqlDebugger.Instance.WriteErrorAsync(ex);
return(false);
}
}
// used after registration is validated and before creation, no security in this other than simple sql illegals, validation should be implemented in controller
public async Task <string> SetRegistrationSessionAsync(User user, string tableName = SmsRegistrationTable)
{
try
{
if (user == null)
{
return("ERROR: User is null.");
}
if (SqlSecurity.ContainsIllegals(user.UserId.ToString()))
{
return("ERROR: contains illegals.");
}
using (SqlConnection connection = GetConnection())
{
await connection.OpenAsync();
// Set user's session column to string sessionId and return so we can return Json
var _sessionId = KeyGeneration.GenerateSession();
user.RegistrationSession = _sessionId;
string sql = string.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = {4}", tableName, RegSessKey, _sessionId, UserIdKey, user.UserId);
using (SqlCommand insertSession = new SqlCommand(sql, connection))
{
await insertSession.ExecuteNonQueryAsync();
return(_sessionId);
}
}
}
catch (Exception ex)
{
await SqlDebugger.Instance.WriteErrorAsync(ex);
throw;
}
}
/// <summary>
/// 以逗号分隔生成字符串,并加单引号
/// </summary>
/// <param name="source"></param>
/// <returns></returns>
public static string ToSqlString(this IEnumerable <string> source)
{
List <string> t = source.Select(p => string.Format("'{0}'", SqlSecurity.FilterInput(p))).ToList();
return(string.Join(",", t));
}
