Zabezpieczenia SQL Injection - PHP MYSQL

Question

Siemka, w jaki sposób ten kod dobrze zabezpieczyć przed atakami SQL injection?

Jakiś easy to use tutorial bo nie zbyt to ogarniam ;[[

1) https://throwbin.io/d4cGSVl
2 ) https://throwbin.io/Sk3uKNY

<?
 $roll = rand(1,99998);  // Roll number
    echo $roll; // Display number
    $date = date('Y-m-d');  // Var date
    $datestamp = date('Y-m-d H:i:s'); // Var date & time
    // Update date in faucet
    $sql = "UPDATE users SET last_faucet_claim='$datestamp' WHERE id =  ".$_SESSION['id'];
    mysqli_query($db, $sql);
    // Update count in faucet
    $faucet_claims = $_SESSION['faucet_claims'] + 1;
    $sql_claim = "UPDATE users SET faucet_claims='$faucet_claims' WHERE id =  ".$_SESSION['id'];
    mysqli_query($db, $sql_claim);

    // Select ref points
    $sql = "SELECT points FROM users WHERE username = '".$_SESSION['ref']."'";
    $result = $db->query($sql);

    if ($result->num_rows > 0) {
      // output data of each row
      while($row = $result->fetch_assoc()) {
        $_SESSION['reg_points'] = $row['points'];
      }
    } else {
      $emptyref == TRUE;
    }

    function AddPointsToRef($clear_reward = 12) {
        if (!$emptyref == TRUE) {
         // Add points to referral
        $date = date('Y-m-d');
        $db = mysqli_connect('localhost', 'root', '', 'app');
        $db_activity = mysqli_connect('localhost', 'root', '', 'activity');
        $update_points_from_ref = $_SESSION['reg_points'] + $clear_reward * 0.25;
        $update_activity_ref = $clear_reward * 0.25;
        $sql_update_points_ref = "UPDATE users SET points='$update_points_from_ref' WHERE username = '".$_SESSION['ref']."'";
        mysqli_query($db, $sql_update_points_ref);
        // Add roll to the activity for ref
        $sql_add_activity_to_ref = "INSERT INTO root (title, description, timestamp, count_points) VALUES ('Faucet', 'Bonus from referral', '$date', '$update_activity_ref')";
        mysqli_query($db_activity, $sql_add_activity_to_ref);
        }
      }
    switch ($roll) {
    case in_array($roll, range(1,69999)):
      $faucet_reward = $_SESSION['points'] + 12;
      $points_earned = $_SESSION['points_earned'] + 12;
      // Update the points
      $sql = "UPDATE users SET points='$faucet_reward' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql);
      // Update the total points earned
      $sql_points_earned = "UPDATE users SET points_earned='$points_earned' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_points_earned);
      // Add roll to the activity
      $sql_activity_roll = "INSERT INTO ".$_SESSION['username']." (title, description, timestamp, count_points) VALUES ('Faucet', 'You roll $roll on the faucet', '$date', 12)";
      mysqli_query($db_activity, $sql_activity_roll);
      echo "<div class='notification is-success is-size-6 mt-4'>
     <button class='delete' onclick='deletenot()'></button>
      Congratulations, you drew 12 points
      </div>";
      // LEVEL
      $xp = $_SESSION['xp'] + 12;
      $sql_lvl = "UPDATE users SET xp='$xp' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_lvl);
      AddPointsToRef($clear_reward = 12);
    break;
    case in_array($roll, range(70000, 89999)):
      $faucet_reward = $_SESSION['points'] + 16;
      $points_earned = $_SESSION['points_earned'] + 16;
      // Update the points
      $sql = "UPDATE users SET points='$faucet_reward' WHERE id = ".$_SESSION['id'];
      mysqli_query($db, $sql);
      // Update the total points earned
      $sql_points_earned = "UPDATE users SET points_earned='$points_earned' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_points_earned);
      // Add roll to the activity
      $sql_activity_roll = "INSERT INTO ".$_SESSION['username']." (title, description, timestamp, count_points) VALUES ('Faucet', 'You roll $roll on the faucet', '$date', 16)";
      mysqli_query($db_activity, $sql_activity_roll);
      echo "<div class='notification is-success is-size-6 mt-4'>
     <button class='delete' onclick='deletenot()'></button>
      Congratulations, you drew 16 points
      </div>";
      // LEVEL
      $xp = $_SESSION['xp'] + 16;
      $sql_lvl = "UPDATE users SET xp='$xp' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_lvl);
      AddPointsToRef($clear_reward = 16);
    break;
    case in_array($roll, range(90000, 96999)):
      $faucet_reward = $_SESSION['points'] + 24;
      $points_earned = $_SESSION['points_earned'] + 24;
      // Update the points
      $sql = "UPDATE users SET points='$faucet_reward' WHERE id = ".$_SESSION['id'];
      mysqli_query($db, $sql);
      // Update the total points earned
      $sql_points_earned = "UPDATE users SET points_earned='$points_earned' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_points_earned);
      // Add roll to the activity
      $sql_activity_roll = "INSERT INTO ".$_SESSION['username']." (title, description, timestamp, count_points) VALUES ('Faucet', 'You roll $roll on the faucet', '$date', 24)";
      mysqli_query($db_activity, $sql_activity_roll);
      echo "<div class='notification is-success is-size-6 mt-4'>
     <button class='delete' onclick='deletenot()'></button>
      Congratulations, you drew 24 points
      </div>";
      // LEVEL
      $xp = $_SESSION['xp'] + 24;
      $sql_lvl = "UPDATE users SET xp='$xp' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_lvl);
      AddPointsToRef($clear_reward = 24);
    break;
    case in_array($roll, range(97000, 98999)):
      $faucet_reward = $_SESSION['points'] + 56;
      $points_earned = $_SESSION['points_earned'] + 56;
      // Update the points
      $sql = "UPDATE users SET points='$faucet_reward' WHERE id = ".$_SESSION['id'];
      mysqli_query($db, $sql);
      // Update the total points earned
      $sql_points_earned = "UPDATE users SET points_earned='$points_earned' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_points_earned);
      // Add roll to the activity
      $sql_activity_roll = "INSERT INTO ".$_SESSION['username']." (title, description, timestamp, count_points) VALUES ('Faucet', 'You roll $roll on the faucet', '$date', 56)";
      mysqli_query($db_activity, $sql_activity_roll);
      echo "<div class='notification is-success is-size-6 mt-4'>
     <button class='delete' onclick='deletenot()'></button>
      Congratulations, you drew 56 points
      </div>";
      // LEVEL
      $xp = $_SESSION['xp'] + 56;
      $sql_lvl = "UPDATE users SET xp='$xp' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_lvl);
      AddPointsToRef($clear_reward = 56);
    break;
    case in_array($roll, range(99000, 99998)):
      $faucet_reward = $_SESSION['points'] + 100;
      $points_earned = $_SESSION['points_earned'] + 100;
      // Update the points
      $sql = "UPDATE users SET points='$faucet_reward' WHERE id = ".$_SESSION['id'];
      mysqli_query($db, $sql);
      // Update the total points earned
      $sql_points_earned = "UPDATE users SET points_earned='$points_earned' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_points_earned);
      // Add roll to the activity
      $sql_activity_roll = "INSERT INTO ".$_SESSION['username']." (title, description, timestamp, count_points) VALUES ('Faucet', 'You roll $roll on the faucet', '$date', 100)";
      mysqli_query($db_activity, $sql_activity_roll);
      echo "<div class='notification is-success is-size-6 mt-4'>
     <button class='delete' onclick='deletenot()'></button>
      Congratulations, you drew 100 points
      </div>";
      // LEVEL
      $xp = $_SESSION['xp'] + 100;
      $sql_lvl = "UPDATE users SET xp='$xp' WHERE id =  ".$_SESSION['id'];
      mysqli_query($db, $sql_lvl);
      AddPointsToRef($clear_reward = 100);
    break;
    }
    // LEVEL SYSTEM
      if ($xp >= $_SESSION['xp_needed']) {
          $new_xp = $xp - $_SESSION['xp_needed'];
          $next_lvl = $_SESSION['lvl'] + 1;
          $xp_needed = $_SESSION['xp_needed'] + 100;
          $sql_next_lvl = "UPDATE users SET lvl='$next_lvl' WHERE id =  ".$_SESSION['id'];
          mysqli_query($db, $sql_next_lvl);
          $sql_xp_needed = "UPDATE users SET xp_needed='$xp_needed' WHERE id =  ".$_SESSION['id'];
          mysqli_query($db, $sql_xp_needed);
          $sql_lvl = "UPDATE users SET xp='$new_xp' WHERE id =  ".$_SESSION['id'];
          mysqli_query($db, $sql_lvl);
      }
}
?>

 

Comments

Nie wystarczy że otworze połączenie, wykonam tego inserta czy coś i zamknę połączenie?

Answer 1

Wydaje mi się, że może nie wystarczyć, zwłaszcza jeżeli dane trzymane w tych sesjach pochodzą bezpośrednio od użytkownika. Tam gdzie masz możliwość ograniczaj zbędne znaki. Np. Jak w danym miejscu użytkownik może wpisać cyfrę to przed wykonaniem zapytania sprawdź czy rzeczywiście tak jest.

Możesz użyć też funkcji mysqli_real_escape_string() w celu dodatkowego zabezpieczenia.

Ekspertem nie jestem, ale wydaje mi się, że jeżeli ktoś sprawiłby, że np w tej zmiennej $_SESSION['ref'] znalazłoby się coś takiego: ' OR 1 = 1 --

to zwróciłoby wyniki wszystkich użytkowników, a odpowiednio łącząc zapytania można by nawet usunąć wszystkich z bazy danych.

Comments

Co jeśli użytkownik nie ma możliwości wpisania żadnej nigdzie liczby? Czy nadal strona jest podatna pod SQL Injection? Użytkownik jedynie to po kliknięciu buttona odpala się request post i losuje liczbę i dodaje daną jego wartość tak, wszystko jest na stałych zależnie co użytkownik wylosuje jedynie tylko ID do którego dodaje to ta która sesja jest aktywna cn

Answer 2

Czy takie łączenie z bazą obroni przed sql injection?