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