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; }
private bool NameIsValid(string username) { bool valid = (username.Length >= 5 && username.Length <= 20); if (valid) { // Check against the database SQLiteDatabase db = new SQLiteDatabase(); string result = db.ExecuteScalar("select 1 from cycli_riders where username=@u", "@u", username); valid = string.IsNullOrEmpty(result); db.Close(); } return valid; }
public static Race LoadLastRace(string userId) { Race[] rs = new Race[] { }; SQLiteDatabase db = new SQLiteDatabase(); string lastSql = "select r.raceId from cycli_race_riders rr, cycli_races r " + "where rr.userId = @u " + "and rr.raceId = r.raceId " + "and (r.Status = 'Finished' or r.Status = 'Abandoned') " + "order by r.StartDateTime desc limit 1"; string raceId = db.ExecuteScalar(lastSql, "@u", userId); if (!string.IsNullOrEmpty(raceId)) { string sql = "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " + "r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " + "r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " + "r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " + "u.ThresholdPower as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " + "rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " + "rr1.RiderType as RiderType, rr1.Handicap as Handicap " + "From cycli_races r, cycli_race_riders rr1, cycli_riders u " + "where u.UserId = rr1.UserId " + "and r.RaceId = rr1.RaceId " + "and r.RaceId = @r " + "and not rr1.Status ='Invited' " + "union " + "select r.RaceId as RaceId, r.Name as Name, r.Description as Description, " + "r.StartDateTime as StartDateTime, r.TargetType as TargetType, r.Configuration as Configuration , r.RaceDirectorId as RaceDirectorId, " + "r.PNS as PNS, r.Drafting as Drafting, r.HandicapType as HandicapType, r.ProfileId as ProfileId, r.ProfileType as ProfileType, " + "r.Minimum as Minimum, r.Maximum as Maximum,r.Status as RaceStatus, u.UserId as UserId , u.UserName as UserName, " + "u.Power1Hr as ThresholdPower,rr1.Status as Status, rr1.Position as Position, rr1.Distance as Distance, rr1.Time as Time, rr1.Energy as Energy, " + "rr1.PedalStrokes as PedalStrokes, rr1.Heartbeats as Heartbeats, rr1.TSS as TSS, " + "rr1.RiderType as RiderType, rr1.Handicap as Handicap " + "From cycli_races r, cycli_race_riders rr1, " + "cycli_virtual_riders u " + "where u.UserId = rr1.UserId " + "and r.RaceId = rr1.RaceId " + "and r.RaceId = @r " + " order by Position"; DataTable dtRaces = db.GetDataTable(sql, "@r", raceId); rs = BuildRaceArray(dtRaces); } db.Close(); return rs.Length > 0 ? rs[0] : null; }
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 { } }
public static VirtualRider New(string userId) { VirtualRider r = new VirtualRider(); UserRider u = UserRider.Load(userId); r.UserId = Guid.NewGuid().ToString(); r.OwnerId = userId; r.UserName = "******"; r.BikeWheelSizeMm = 700; r.EstimatedPower = false; r.TurboIsCalibrated = false; r.CurrentTurbo = ""; r.Aggression = 50; r.PowerMin = 150; r.Power1Hr = 200; r.Power5Min = 250; r.Power1Min = 300; r.Power5Sec = 500; // It's a new profile string sqlCount = @"select count(*) from cycli_virtual_riders where OwnerId=@o"; string sqlRider = @"insert into cycli_virtual_riders (UserId, OwnerId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression) " + "values (@u, @o, @n, @p4, @p3, @p2, @p1, @p0, @a) "; SQLiteDatabase db = new SQLiteDatabase(); try { // This is a backstop test - the front end should prevent extra riders if (int.Parse(db.ExecuteScalar(sqlCount, "@o", userId)) < u.MaximumVirtualRiders) { db.ExecuteNonQuery(sqlRider, "@u", r.UserId, "@o", r.OwnerId, "@n", r.UserName, "@p4", r.PowerMin, "@p3", r.Power1Hr, "@p2", r.Power5Min, "@p1", r.Power1Min, "@p0", r.Power5Sec, "@a", r.Aggression); } } finally { db.Close(); } return r; }
public static void Delete(string userId, string riderId) { // Has this rider been employed in a race string sqlPlayed = @"select count(*) from cycli_race_riders where userid=@u and (Status='Finished' or Status='Abandoned')"; string sqlRiderDelete = @"delete from cycli_virtual_riders where ownerid = @o and userid = @u"; string sqlRiderRaceDelete = @"delete from cycli_race_riders where userid = @u"; string sqlRiderInactive = @"update cycli_virtual_riders set status='Inactive' where ownerid = @o and userid = @u"; bool riderPlayed = false; SQLiteDatabase db = new SQLiteDatabase(); try { riderPlayed = (int.Parse(db.ExecuteScalar(sqlPlayed,"@u",riderId)) > 0); } finally { db.Close(); } // Now reopen for transactions db = new SQLiteDatabase(true); try { if (riderPlayed) { db.ExecuteNonQuery(sqlRiderInactive, "@o", userId, "@u", riderId); } else { // Get rid of rider from any pending races db.ExecuteNonQuery(sqlRiderRaceDelete, "@u", riderId); db.ExecuteNonQuery(sqlRiderDelete, "@o", userId, "@u", riderId); } // Commit closes db db.CommitTransaction(); } catch (Exception ex) { db.RollbackTransaction(); } }