W11_SQL Injection 相關

hw1 實作_2. SQL Injection

利用 sql 語法的特性，在留言區輸入類似 '), ('admin', (select password from christy_w9_users where id=9))# 就可以成功的拿到資料庫裡面的密碼

• 修正方法：prepared statement
  • 這樣上面的惡意字串就會被當作字串，不會被執行了
  • 所有地方都要改！
• 基本架構就是

$stmt = $conn->prepare("SELECT * FROM christy_w9_comments ORDER BY id DESC");
$result = $stmt->execute();

// 新增留言
<?php
  session_start();
  require_once('conn.php');

  $username = $_SESSION['username'];

  if (!$_SESSION['username']) {
    header('Location: index.php?errCode=1');
    die();
  } else if (empty($_POST['content'])) {
    header('Location: index.php?errCode=2');
    die();
  }

  $content = $_POST['content'];
    // 從這裡開始改成 prepare stmt
  $sql = "INSERT INTO christy_w9_comments (username, content) VALUES (?, ?)";
  $stmt = $conn->prepare($sql);
  $stmt->bind_param('ss', $username, $content);
  $result = $stmt->execute();
    // 下面都跟以前一樣
  if (!$result) {
    die($conn->error);
  }

  header('Location: index.php');
?>

• 登入要多加一行 $result = $stmt->get_result();

<?php
  session_start();
  require_once('conn.php');

  if (empty($_POST['username']) || empty($_POST['password'])) {
    header('Location: login.php?errCode=1');
    die();
  }

  $username = $_POST['username'];
  $password = $_POST['password'];
    // 從這裡開始改
  $sql = "SELECT * FROM christy_w9_users WHERE username=?";
  $stmt = $conn->prepare($sql);
  $stmt->bind_param('s', $username);
  $result = $stmt->execute();

  if (!$result) {
    die($conn->error);
  }

  $result = $stmt->get_result();
    // 到這裡為止
  if ($result->num_rows === 0) {
    header('Location: login.php?errCode=2');
    exit();
  }

  $row = $result->fetch_assoc();
  if (password_verify($password, $row['password'])) {
    $_SESSION['username'] = $username;
    header('Location: index.php');
  } else {
    header('Location: login.php?errCode=2');
  }
?>

如何防止 SQL_injection 攻擊

利用這個攻擊，可以任意組合 sql 語意，去撈出想要的資料

在留言區輸入這個組合，就可以拿到資料庫裡面的密碼了
'), ('admin', (select password from users limit 1))#

修正方法：

prepared statement

所以不要用 sprintf() 這個函式了

$sql = sprintf("INSERT INTO comments(nickname, content) VALUES('%s', '%s')", $nickname, $content);

改用下面的形式：

$sql = "INSERT INTO comments(nickname, content) VALUES(?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $nickname, $content);
$result = $stmt->execute();

• 所有有用到 sql 語法的地方都要改

• 特別注意，如果有從資料庫拿資料，例如登入時去拿了 hash 過後的密碼，那再用 stmt 時，除了上面那四行以外，還要加上 $result = $stmt->get_result();，程式碼長這樣

$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $username);
$result = $stmt->execute();
if (!$result) {
  die($conn->error);
}

$result = $stmt->get_result();
if ($result->num_rows === 0) {
  header('Location: login.php?errCode=2');
  exit();
}

在 login.php 裡面，即使沒有用到 sprintf()，還是要改成下面的形式，記得最後要加上 get_result()

$stmt = $conn->prepare("SELECT * FROM comments ORDER BY id DESC");

$result = $stmt->execute(); 

if (!$result) {
  die('error:' . $conn->error);
}

$result = $stmt->get_result();

疑問：

既然 sprintf() 這個 sql 函式有安全疑慮，那為什麼我們還要用他？或者說有可能用在什麼地方？

code_handle_login.php

<?php
  session_start();
  require_once('conn.php');
  require_once('utils.php');

  if (empty($_POST['username']) || empty($_POST['password'])) {
    header('Location: login.php?errCode=1');
    die();
  }

  $username = $_POST['username'];
  $password = $_POST['password'];

  $sql = sprintf("SELECT * FROM users WHERE username = '%s'", $username);

  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  if ($result->num_rows === 0) {
    header('Location: login.php?errCode=2');
    exit();
  }

  $row = $result->fetch_assoc();
  if (password_verify($password, $row['password'])) {
    $_SESSION['username'] = $username;
    header('Location: index.php');
  } else {
    header('Location: login.php?errCode=2');
  }
?>