Secure Leaderboard Updates

TL;DR

Prevent cheating on your online leaderboard by validating all update requests against server-side data and using prepared statements to avoid SQL injection. Implement user authentication and rate limiting.

Solution Guide

1. User Authentication: Always verify who is submitting the score.
   • Implement a secure login system (username/password, OAuth, etc.).
   • Store user IDs with scores in your database.
   • Never trust data directly from the client-side; always authenticate the request.
2. Server-Side Validation: Check all incoming score submissions.
   • Game/Level Check: Ensure the submitted score is for a valid game or level that exists in your database.
   • Score Range Check: Verify the score falls within reasonable limits (e.g., not negative, not excessively high).
   • Duplicate Submission Check: Prevent users from submitting the same score multiple times.

     SELECT COUNT(*) FROM leaderboard WHERE user_id = ? AND game_id = ? AND score = ?

3. Prepared Statements (SQL Injection Prevention): Use prepared statements with parameterized queries.
   • This is the most important step to prevent SQL injection attacks.
   • Instead of directly embedding user input into your SQL query, use placeholders that are filled in by the database driver.
   • Example (PHP PDO):

     $stmt = $pdo->prepare('INSERT INTO leaderboard (user_id, game_id, score) VALUES (?, ?, ?)');
     $stmt->execute([$userId, $gameId, $score]);

4. Rate Limiting: Limit how often a user can submit scores.
   • This prevents users from rapidly submitting scores to overwhelm the server or bypass other validation checks.
   • Implement a system that tracks submission attempts per user within a specific time window (e.g., 5 submissions per minute).
   • Example (basic PHP session-based rate limiting):

     if (isset($_SESSION['last_submission']) && (time() - $_SESSION['last_submission'] < 60)) {
       // Too many submissions recently.
     }
     $_SESSION['last_submission'] = time();

5. Database Security: Secure your database server.
   • Use strong passwords for the database user.
   • Restrict database access to only necessary users and permissions.
   • Keep your database software up-to-date with security patches.
6. Input Sanitization (Defense in Depth): While prepared statements are primary, sanitize input as an extra layer.
   • Remove or escape potentially harmful characters from user input before validation. This is less important than prepared statements but adds another level of protection.
   • PHP example:

     $score = filter_var($score, FILTER_SANITIZE_NUMBER_INT);
     $gameId = filter_var($gameId, FILTER_SANITIZE_NUMBER_INT);

7. Logging and Monitoring: Track suspicious activity.
   • Log all score submissions, including user ID, game ID, score, timestamp, and IP address.
   • Monitor logs for unusual patterns or attempts to submit invalid scores.