public static bool Delete(string action)
{
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase(true);
bool deleted = false;
try
{
// This query checks that the user has not already been invited
string sql = "delete from cycli_content where lower(_action) = lower(@a) ";
deleted = (db.ExecuteNonQuery(sql, "@a", action) == 1);
string deletelinksql = "delete from cycli_content_links where sourceaction = @s";
db.ExecuteNonQuery(deletelinksql, "@s", action);
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
finally
{
}
return deleted;
}
public HttpResponseMessage ChangePassword()
{
string success = "no_user";
// Check old password
var u = Request.Content.ReadAsAsync<ChangeCredentials>().Result;
// Need to check that we're logged on
var userId = CredentialController.Authenticate();
if (!string.IsNullOrEmpty(userId))
{
string sql = @"select password from cycli_riders where UserId=@u and AccountStatus='Active'";
// Check against the database
SQLiteDatabase db = new SQLiteDatabase();
string oldHashedPassword = db.ExecuteScalar(sql, "@u", userId);
if (!string.IsNullOrEmpty(oldHashedPassword) && PasswordHash.ValidatePassword(u.oldPassword, oldHashedPassword))
{
string newHashPassword = PasswordHash.CreateHash(u.newPassword);
// Check against the database
sql = @"update cycli_riders set password=@new where userid=@u and AccountStatus='Active'";
if (db.ExecuteNonQuery(sql, "@new", newHashPassword, "@u", userId, "@old", newHashPassword) > 0)
{
success = "ok";
// Emailer.SendRecoveryConfirmation(u.username, userId, code, u.email);
}
else
{
success = "db_failed";
}
}
else
{
success = "wrong_password";
}
db.Close();
}
var response = Request.CreateResponse<string>(HttpStatusCode.OK, success,
new System.Net.Http.Formatting.JsonMediaTypeFormatter());
return response;
}
private string ValidateRecoveryCredentials(RegisterCredentials u)
{
string sql = @"select UserId from cycli_riders where UserName=@username and Email=@email and (AccountStatus='Active' or AccountStatus='Reset')";
// Check against the database
SQLiteDatabase db = new SQLiteDatabase();
string userId = db.ExecuteScalar(sql, "@username", u.username, "@email", u.email);
if (!string.IsNullOrEmpty(userId))
{
string hash = PasswordHash.CreateHash(u.password);
string code = Guid.NewGuid().ToString();
sql = @"update cycli_riders set activationcode=@a, AccountStatus='Reset', password=@p where userid=@u and AccountStatus='Active'";
if (db.ExecuteNonQuery(sql, "@a", code,"@p", hash, "@u", userId) > 0)
{
Emailer.SendRecoveryConfirmation(u.username, userId, code, u.email);
}
}
db.Close();
return userId;
}
public void Save()
{
Updated = DateTime.UtcNow;
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
// This query checks that the user has not already been invited
string sql = "select _action from cycli_content where lower(_action) = lower(@a) ";
string a = db.ExecuteScalar(sql, "@a", this.Action);
if (string.IsNullOrEmpty(a))
{
// It's a new one
sql = "insert into cycli_content (title, body, updated, footerLink, _action) values (@t, @b, @u, @f, @a) ";
}
else
{
sql = "update cycli_content set title=@t, body=@b, updated=@u, footerLink=@f where _action=@a";
}
db.ExecuteNonQuery(sql, "@t", Title, "@b", Body, "@u",
DbTime.ToDbSecs(Updated), "@f", FooterLink.ToString(), "@a", this.Action);
// Remove any links
string deletelinksql = "delete from cycli_content_links where sourceaction = @s";
db.ExecuteNonQuery(deletelinksql, "@s", this.Action);
// and add new ones
string insertlinksql = "insert into cycli_content_links (sourceaction, destinationaction) values (@s, @d)";
foreach (KeyValuePair<string, string> l in Links)
{
db.ExecuteNonQuery(insertlinksql, "@s", this.Action, "@d", l.Key);
}
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
finally
{
}
}
