SQLインジェクション対策(PHP編)

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
</head>
<body>
  <h1>ログイン</h1>
  <form action="logincheck.php" method="post">
    ID:<input type="text" name="id"><br>
    Password:<input type="password" name="password"><br>
    <input type="submit" value="ログイン">
    <input type="reset" value="リセット">
  </form>
</body>
</html>

・logincheck.php

<?php
  session_start();
  
  //パラメーター取得
  $id = $_POST['id'];
  $password = $_POST['password'];
  
  $count = 0;
  
  try{ 
    //文字エンコーディングを必ず指定する
    $dbh = new PDO("mysql:host=192.168.11.8;dbname=Test;charset=utf8", "user01", "pass", 
         array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
               PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
               PDO::ATTR_EMULATE_PREPARES => false));

    $stmt = $dbh->prepare("SELECT COUNT(*) AS CNT from LOGIN WHERE ID = ? AND PASSWORD = ?;");
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    $stmt->bindParam(1, $id, PDO::PARAM_STR);
    $stmt->bindParam(2, $password, PDO::PARAM_STR);

    $stmt->execute();

    while ($row = $stmt->fetch()) {
      $count = $row["CNT"];
    }

    $stmt = null;

  } catch(PDOException $e){
    echo $e->getMessage();
  }
  
  $_SESSION['id'] = $id;
  
  if ($count == 1) {
   //ログイン成功
    header("HTTP/1.1 301 Moved Permanently");
    header("Location: welcome.php");
  } else {
   //ログイン失敗
    header("HTTP/1.1 301 Moved Permanently");
    header("Location: login.php");
  }
 
?>

・welcome.php

<?php
  session_start();
?>
<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
</head>
<body>
  <h1>ようこそ、<?php echo htmlspecialchars($_SESSION['id'], ENT_QUOTES, "UTF-8") ?>さん</h1>
</body>
</html>

MDB2 版

・login.php

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
</head>
<body>
  <h1>ログイン</h1>
  <form action="logincheck.php" method="post">
    ID:<input type="text" name="id"><br>
    Password:<input type="password" name="password"><br>
    <input type="submit" value="ログイン">
    <input type="reset" value="リセット">
  </form>
</body>
</html>

・logincheck.php

<?php
  require_once 'MDB2.php';
  session_start();
  
  //パラメーター取得
  $id = $_POST['id'];
  $password = $_POST['password'];
  
  $count = 0;
  
  //DB接続
  $db = MDB2::connect('mysql://user01:pass@localhost/Test?charset=utf8');
  
  if (MDB2::isError($db)) {
    throw new exception("DB connection error.");
  }
  
  //プレースホルダで SQL 作成
  $sql = "SELECT COUNT(*) AS CNT FROM LOGIN WHERE ID = ? AND PASSWORD = ?;";
  
  //パラメーターの型を指定
  $stmt = $db->prepare($sql, array('text','text'));
  
  //パラメーターを渡して SQL 実行
  $rs = $stmt->execute(array($id, $password));
  
  while ($row = $rs->fetchRow(MDB2_FETCHMODE_ASSOC)) {
  	$count = $row['cnt'];
  }
  
  $db->disconnect();
  
  $_SESSION['id'] = $id;
  
  if ($count == 1) {
  	//ログイン成功
    header("HTTP/1.1 301 Moved Permanently");
    header("Location: welcome.php");
  } else {
  	//ログイン失敗
    header("HTTP/1.1 301 Moved Permanently");
    header("Location: login.php");
  }

?>

・welcome.php

<?php
  session_start();
?>
<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
</head>
<body>
  <h1>ようこそ、<?php echo htmlspecialchars($_SESSION['id'], ENT_QUOTES, "UTF-8") ?>さん</h1>
</body>
</html>