function update_match_stats_entered($team_id1, $team_id2, $team1_points, $team2_points, $site, $connection) { // increase match count for teams that participated $query = 'UPDATE `teams_profile` SET `num_matches_total`=`num_matches_total`+1'; $query .= ' WHERE (`teamid`=' . sqlSafeStringQuotes($team_id1) . ' OR `teamid`=' . sqlSafeStringQuotes($team_id2) . ')'; if (!($result = $site->execute_query('teams_overview', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('The match count for the teams with id' . sqlSafeString($team_id1) . ' and ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!'); } // mark both participating teams as active $query = 'UPDATE `teams_overview` SET `deleted`=' . sqlSafeStringQuotes('1') . ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id1) . ' OR `teamid`=' . sqlSafeStringQuotes($team_id2) . ' LIMIT 2'; if (!($result = @$site->execute_query('teams_overview', $query, $connection))) { $site->dieAndEndPage('Could not mark team with id ' . sqlSafeString($teamid) . ' as active!'); } // increase match win count for teams that participated if ($team1_points > $team2_points) { // team 1 won $query = 'UPDATE `teams_profile` SET `num_matches_won`=`num_matches_won`+1'; $query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id1); if (!($result = $site->execute_query('teams_profile', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('The match win count for team ' . sqlSafeString($team_id1) . ' could not be updated due to a sql problem!'); } // team 2 lost $query = 'UPDATE `teams_profile` SET `num_matches_lost`=`num_matches_lost`+1'; $query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id2); if (!($result = $site->execute_query('teams_profile', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('The match lose count for team ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!'); } } if ($team1_points < $team2_points) { // team 2 won $query = 'UPDATE `teams_profile` SET `num_matches_won`=`num_matches_won`+1'; $query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id2); if (!($result = $site->execute_query('teams_profile', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('The match win count for team ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!'); } // team 1 lost $query = 'UPDATE `teams_profile` SET `num_matches_lost`=`num_matches_lost`+1'; $query .= ' WHERE `teamid`=' . sqlSafeStringQuotes($team_id1); if (!($result = $site->execute_query('teams_profile', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('The match lose count for team ' . sqlSafeString($team_id1) . ' could not be updated due to a sql problem!'); } } // match entered ended in a draw if ((int) $team1_points === (int) $team2_points) { $query = 'UPDATE `teams_profile` SET `num_matches_draw`=`num_matches_draw`+1'; $query .= ' WHERE (`teamid`=' . sqlSafeStringQuotes($team_id1) . ' OR `teamid`=' . sqlSafeStringQuotes($team_id2) . ')'; if (!($result = $site->execute_query('teams_profile', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('The match draw count for the teams with id' . sqlSafeString($team_id1) . ' and ' . sqlSafeString($team_id2) . ' could not be updated due to a sql problem!'); } } }
// the range of shown matches is set by the GET variable i if (isset($_GET['i'])) { if ((int) $_GET['i'] > 0) { $view_range = (int) $_GET['i']; $query .= $view_range . ','; } else { // force write 0 for value 0 (speed saving due to no casting to string) // and 0 for negative values (security: DBMS error handling prevention) $query .= '0,'; } } else { // no special value set -> write 0 for value 0 (speed) $query .= '0,'; } // limit the number of displayed rows regarding the user's wish $query .= sqlSafeString($num_results + 1); if (!($result = @$site->execute_query('matches', $query, $connection))) { $site->dieAndEndPageNoBox('The list of matches could not be displayed because of an SQL/database connectivity problem.'); } $rows = (int) mysql_num_rows($result); $show_next_matches_button = false; // more than wished match entries per page available in total if ($rows > $num_results) { $show_next_matches_button = true; } if ($rows === (int) 0) { echo '<p>No matches have been played yet.</p>' . "\n"; setTableUnchanged($site, $connection); $site->dieAndEndPageNoBox(); } unset($rows);
private function sanityCheck(&$confirmed) { global $randomkey_name; global $team_id1; global $team_id2; global $team1_caps; global $team2_caps; global $timestamp; global $match_id; global $similarMatchFound; global $db; // sanitise match id if (isset($_GET['edit'])) { $match_id = intval($_GET['edit']); } if (isset($_GET['delete'])) { $match_id = intval($_GET['delete']); } // sanitise team variables if (isset($_POST['match_team_id1'])) { $team_id1 = intval($_POST['match_team_id1']); } elseif (isset($_POST['team_id1'])) { $team_id1 = intval($_POST['team_id1']); } else { $team_id1 = 0; } if ($team_id1 < 1) { $team_id1 = 0; } if (isset($_POST['match_team_id2'])) { $team_id2 = intval($_POST['match_team_id2']); } elseif (isset($_POST['team_id2'])) { $team_id2 = intval($_POST['team_id2']); } else { $team_id2 = 0; } if ($team_id2 < 1) { $team_id2 = 0; } // do the teams exist? // teams specified? if (!isset($_GET['delete']) && ($team_id1 > 0 && $team_id2 > 0)) { $team_exists = 0; $query = $db->prepare('SELECT COUNT(`id`) as `team_exists` FROM `teams` WHERE `id`=? LIMIT 1'); if (!($result = $db->execute($query, $team_id1))) { $db->logError('Could not find out name of team #' . $team_id1 . '.'); } while ($row = $db->fetchRow($query)) { $team_exits = intval($row['team_exists']); } $db->free($query); if ($team_exits === 0) { echo '<p>Error: The specified team #1 does not exist</p>'; $confirmed = 'checkTeam1'; return; } // reset variable for team 2 $team_exits = 0; $query = $db->prepare('SELECT COUNT(`id`) as `team_exists` FROM `teams` WHERE `id`=? LIMIT 1'); if (!($result = $db->execute($query, $team_id2))) { $db->logError('Could not find out name of team #' . sqlSafeString($team_id2) . '.'); } while ($row = $db->fetchRow($query)) { $team_exits = intval($row['team_exists']); } $db->free($query); if ($team_exits === 0) { echo '<p>Error: The specified team #2 does not exist</p>'; $confirmed = 'checkTeam2'; return; } // teams are the same (and chosen by user) if ($team_id1 > 0 && $team_id2 > 0 && $team_id1 === $team_id2) { echo '<p>In order to be an official match, teams would have to be different!</p>'; $confirmed = 'checkDifferentTeams'; return; } } // sanitise score variables if (isset($_POST['team1_points'])) { $team1_caps = intval($_POST['team1_points']); } else { $team1_caps = 0; } if (isset($_POST['team2_points'])) { $team2_caps = intval($_POST['team2_points']); } else { $team2_caps = 0; } // sanitise day and time variables if (isset($_POST['match_day'])) { $match_day = $_POST['match_day']; } else { $match_day = date('Y-m-d'); } if (isset($_POST['match_time'])) { $match_time = $_POST['match_time']; } else { $match_time = date('H:i:s'); } if (isset($_POST['match_day']) && isset($_POST['match_time'])) { $timestamp = $_POST['match_day'] . ' ' . $_POST['match_time']; } // user wants to edit match data again if (isset($_POST['match_cancel'])) { $confirmed = 'edit'; return; } if (isset($_POST['$match_id'])) { $match_id = intval($_POST['$match_id']); } // does the match exit? if (isset($match_id)) { $query = $db->prepare('SELECT `id` FROM `matches` WHERE `id`=?'); if (!($result = $db->execute($query, $match_id))) { $db->logError('Could not find out id for team 1 given match id ' . $match_id . ' due to a sql problem!'); } if (intval($db->rowCount($query)) < 1) { // match did not exist! $confirmed = 'checkMatch'; } } // sanitise date and time specified // sanity checks regarding day format // sample day: 2009-12-15 if (!preg_match('/(2)(0|1|2|3|4|5|6|7|8|9){3,}-(0|1)(0|1|2|3|4|5|6|7|8|9)-(0|1|2|3)(0|1|2|3|4|5|6|7|8|9)/', $match_day)) { echo '<p>Please make sure your specified date is in correct format. Do not forget leading zeros.</p>' . "\n"; $confirmed = 'no'; return; } // sanity checks regarding time format // sample time: 15:21:35 if (!preg_match('/(0|1|2)([0-9]):([0-5])([0-9]):([0-5])([0-9])/', $match_time)) { echo '<p>Please make sure your specified time is in correct format. Do not forget leading zeros.</p>' . "\n"; $confirmed = 'no'; return; } // get the unix timestamp from the date and time if (!($specifiedTime = strtotime($match_day . ' ' . $match_time))) { echo '<p>Please make sure your specified date and time is valid!</p>' . "\n"; $confirmed = 'no'; return; } // look up if the day does exist in Gregorian calendar // checkdate expects order to be month, day, year if (!checkdate(date('m', $specifiedTime), date('d', $specifiedTime), date('Y', $specifiedTime))) { echo '<p>Please make sure your specified date and time is a valid Gregorian date.</p>' . "\n"; $confirmed = 'no'; return; } // is match in the future? if (isset($timestamp)) { $curTime = (int) strtotime('now'); if ((int) $specifiedTime - $curTime >= 0) { echo '<p>You tried to enter, edit or delete a match that would have been played in the future.'; echo ' Only matches in the past can be entered, edited or deleted.</p>' . "\n"; $confirmed = 'no'; return; } } // is match older than 2 months? $eightWeeksAgo = (int) strtotime('now -8 weeks'); if ((int) $specifiedTime <= $eightWeeksAgo) { echo '<p>You tried to enter, edit or delete a match that is older than 8 weeks.' . 'Only matches played in the last 8 weeks can be entered, edited or deleted.</p>' . "\n"; $confirmed = 'no'; return; } // check if there is already a match entered at that time // scores depend on the order, two matches done at the same time lead to undefined behaviour $query = $db->prepare('SELECT `timestamp` FROM `matches` WHERE `timestamp`=?'); if (!($result = $db->execute($query, $timestamp))) { unlock_tables(); $db->logError('Unfortunately there seems to be a database problem' . ' and thus comparing timestamps (using equal operator) of matches failed.'); } $rows = (int) $db->rowCount($query); $db->free($query); if ($rows > 0 && !isset($_GET['edit']) && !isset($_GET['delete'])) { // go back to the first step of entering a match echo '<p>There is already a match entered at that exact time.'; echo ' There can be only one finished at the same time because the scores depend on the order of the played matches.</p>' . "\n"; // just warn them and let them enter it all again by hand echo 'Please enter the match with a different time.</p>' . "\n"; echo '<form enctype="application/x-www-form-urlencoded" method="post" action="?enter">' . "\n"; echo '<div>'; $site->write_self_closing_tag('input type="hidden" name="confirmed" value="0"'); echo '</div>' . "\n"; // pass the match values to the next page so the previously entered data can be set default for the new form show_form($team_id1, $team_id2, $team1_caps, $team2_caps, $readonly = false); echo '<div>'; $site->write_self_closing_tag('input type="submit" name="match_cancel" value="Cancel and change match data" id="send"'); echo '</div>' . "\n"; echo '</form>' . "\n"; $site->dieAndEndPage(); } // random key validity check if ($confirmed === 'action') { $new_randomkey_name = ''; if (isset($_POST['key_name'])) { $new_randomkey_name = html_entity_decode($_POST['key_name']); } $randomkeysmatch = $site->compare_keys($randomkey_name, $new_randomkey_name); if (!$randomkeysmatch) { echo '<p>The magic key did not match. It looks like you came from somewhere else. Going back to compositing mode.</p>'; // reset the confirmed value $confirmed = 'no'; } } // check for similar match in database and warn user if at least one was found // skip warning if already warned (no infinite warning loop) if ($confirmed === 'action' && !isset($_POST['similar_match'])) { // find out if there are similar matches $similarMatchFound = false; $similarMatchFound = similarMatchEntered(true); if (!$similarMatchFound) { // look for a possible last show stopper $similarMatchFound = similarMatchEntered(false); } else { // add space between last similar match and the one probably following $site->write_self_closing_tag('br'); // only call the function for user information, ignore result similarMatchEntered(false); } if ($similarMatchFound) { // ask for confirmation again and do not go ahead automatically $confirmed = 'no'; } } // no double confirmation about deletion - user saw confirmation step with $confirmed = 0 already if ($confirmed === 'action' && isset($_GET['delete'])) { $confirmed = 'action'; } }
function sqlSafeStringQuotes($param) { // use sqlSafeString and append quotes before and after the result return "'" . sqlSafeString($param) . "'"; }
function decrease_draw_match_count($teamid) { global $connection; global $site; $query = 'UPDATE `teams_profile` SET '; $query .= '`num_matches_draw`=`num_matches_draw`-' . sqlSafeStringQuotes('1'); $query .= ' WHERE (`teamid`=' . sqlSafeStringQuotes($teamid) . ')'; // only one team needs to be updated $query .= ' LIMIT 1'; if (!($result = $site->execute_query('teams_profile', $query, $connection))) { unlock_tables($site, $connection); $site->dieAndEndPage('Could not update win/play count for team with id ' . sqlSafeString($teamid) . ' due to a sql problem!'); } }
} } echo '<p>The player profile has been updated successfully.</p>' . "\n"; $site->dieAndEndPage(''); } // display editing form echo '<form enctype="application/x-www-form-urlencoded" method="post" action="?edit=' . $profile . '">' . "\n"; echo '<div><input type="hidden" name="confirmed" value="1"></div>' . "\n"; $new_randomkey_name = $randomkey_name . microtime(); $new_randomkey = $site->set_key($new_randomkey_name); echo '<div><input type="hidden" name="key_name" value="' . htmlspecialchars($new_randomkey_name) . '"></div>' . "\n"; echo '<div><input type="hidden" name="' . htmlspecialchars($randomkey_name) . '" value="'; echo urlencode($_SESSION[$new_randomkey_name]) . '"></div>' . "\n"; $query = 'SELECT `location`, `UTC`'; $query .= ', `raw_user_comment`, `raw_admin_comments`'; $query .= ', `logo_url` FROM `users_profile` WHERE `id`=' . "'" . sqlSafeString($profile) . "'"; $query .= ' LIMIT 1'; if (!($result = @$site->execute_query('users_profile', $query, $connection))) { // query was bad, error message was already given in $site->execute_query(...) $site->dieAndEndPage(''); } $location = 0; $timezone = 0; $user_comment = ''; $admin_comments = ''; while ($row = mysql_fetch_array($result)) { $location = (int) $row['location']; $timezone = (int) $row['UTC']; $user_comment = $row['raw_user_comment']; $admin_comments = $row['raw_admin_comments']; $logo_url = $row['logo_url'];
if ($site->use_xtml()) { echo '<br />' . "\n"; } else { echo '<br>' . "\n"; } echo '<a class="button" href="../PM/?add&userid=' . intval($profile) . '">Send private message to player</a>' . "\n"; $allow_invite_in_any_team = false; if (isset($_SESSION['allow_invite_in_any_team'])) { if ($_SESSION['allow_invite_in_any_team'] === true) { $allow_invite_in_any_team = true; } } // 0 is a reserved value and stands for no team $leader_of_team_with_id = 0; if (!$allow_invite_in_any_team) { $query = 'SELECT `id` FROM `teams` WHERE `leader_userid`=' . "'" . sqlSafeString($viewerid) . "'" . ' LIMIT 1'; if (!($result = @$site->execute_query('teams', $query, $connection))) { $site->dieAndEndPage('A database related problem prevented to find out if the viewer of this site is the leader of a team.'); } // if the viewer is leader of a team, a value other than 0 will be the result of the query // and that value will be the id of the team the viewer is leader while ($row = mysql_fetch_array($result)) { $leader_of_team_with_id = $row['id']; } } // users are not supposed to invite themselves if (($allow_invite_in_any_team || $leader_of_team_with_id > 0 && $viewerid !== $profile) && strcmp($suspended_status, 'deleted') !== 1) { echo '<a class="button" href="?invite=' . htmlspecialchars(urlencode($profile)) . '">Invite player to team</a>' . "\n"; } if (isset($_SESSION['allow_view_user_visits']) && $_SESSION['allow_view_user_visits'] === true && strcmp($suspended_status, 'deleted') !== 1) { echo '<a class="button" href="../Visits/?profile=' . htmlspecialchars($profile) . '">View visits log</a>' . "\n";
function delete_match($match_id) { global $site; global $connection; global $tables_locked; global $viewerid; lock_tables(); // who entered/edited the match before? $userid = 0; // find out the appropriate team id list for the edited match (to modify total/win/draw/loose count) $query = 'SELECT `userid`, `timestamp`, `team1_id`, `team2_id`,' . ' `team1_points`, `team2_points`, `team1_new_score`, `team2_new_score`, `duration` FROM `matches`' . ' WHERE `id`=' . sqlSafeStringQuotes($match_id); if (!($result = $site->execute_query('matches', $query, $connection))) { unlock_tables(); $site->dieAndEndPage('Could not find out id for team 1 given match id ' . sqlSafeString($match_id) . ' due to a sql problem!'); } while ($row = mysql_fetch_array($result)) { $userid = intval($row['userid']); $timestamp = $row['timestamp']; $team_id1 = intval($row['team1_id']); $team_id2 = intval($row['team2_id']); $team1_caps = intval($row['team1_points']); $team2_caps = intval($row['team2_points']); $duration = intval($row['duration']); } mysql_free_result($result); // prepare to update win/draw/loose count // create array that keeps track of team score changes $team_stats_changes = array(); // mark the participating teams as potentially having a changed score $team_stats_changes[$team_id1] = ''; $team_stats_changes[$team_id2] = ''; // save old match into edit history table $query = 'INSERT INTO `matches_edit_stats` (`match_id`, `userid`, `timestamp`, `team1_id`,' . ' `team2_id`, `team1_points`, `team2_points`, `duration`) VALUES (' . sqlSafeStringQuotes($match_id) . ', ' . sqlSafeStringQuotes($userid) . ', ' . sqlSafeStringQuotes($timestamp) . ', ' . sqlSafeStringQuotes($team_id1) . ', ' . sqlSafeStringQuotes($team_id2) . ', ' . sqlSafeStringQuotes($team1_caps) . ', ' . sqlSafeStringQuotes($team2_caps) . ', ' . sqlSafeStringQuotes($duration) . ')'; if (!($result = $site->execute_query('matches_edit_stats', $query, $connection))) { unlock_tables(); $site->dieAndEndPage('The match reported by user #' . sqlSafeString($viewerid) . ' could not be entered due to a sql problem!'); } // we saved the old match in the editing stats thus we can now delete the actual match // update match table (perform the actual editing) // use current row id to access the entry // only one row needs to be updated $query = 'DELETE FROM `matches` WHERE `id`=' . sqlSafeStringQuotes($match_id) . ' LIMIT 1'; if (!($result = $site->execute_query('matches', $query, $connection))) { unlock_tables(); $site->dieAndEndPage('The match reported by user #' . sqlSafeString($viewerid) . ' could not be deleted due to a sql problem!'); } // update win/draw/loose count require_once 'team_match_count.php'; // both teams didn't play decrease_total_match_count($team_id1); decrease_total_match_count($team_id2); // team 1 won if ($team1_caps > $team2_caps) { // update team 1 data decrease_won_match_count($team_id1); // update team 2 data decrease_lost_match_count($team_id2); } // team 2 won if ($team1_caps < $team2_caps) { // update team 1 data decrease_lost_match_count($team_id1); // update team 2 data decrease_won_match_count($team_id2); } // the match ended in a draw if ($team1_caps === $team2_caps) { // update team 1 data decrease_draw_match_count($team_id1); // update team 2 data decrease_draw_match_count($team_id2); } // old match data variables no longer needed unset($userid_old); // trigger score updates for newer matches update_later_matches($team_id1, $team_id2, $team1_caps, $team2_caps, $timestamp, $team_stats_changes, $viewerid, $duration); show_score_changes($team_stats_changes, array_keys($team_stats_changes)); // done with deleting that match unlock_tables(); require_once '../CMS/maintenance/index.php'; echo '<p>The match was deleted successfully.</p>' . "\n"; $site->dieAndEndPage(); }