程式導師實驗計畫 BE101

XAMPP (Apache + MariaDB + PHP + Perl)

MySQL 被甲骨文公司收購，因擔心有閉源的風險，社群就用開新 branch 的方式發展 MariaDB，維持開源。MariaDB 完全相容 MySQL，使其能夠完全替代 MySQL。

XAMPP 的網址與檔案結構是對應的，但其他程式語言或其他 php 框架不見得一樣。

Apache 與 php 原理簡介

request(test.php) => apache(server) => php => output => apache => response

// apache
function run(request) {
  response = php(request)
  send response
}

在 Response Header 裡面可看見 server 相關內容，例如透過 Apache 這個 server 來跑，但背後還是 php 程式碼，但一般而言會隱藏 server 相關資訊。

資料庫系統簡介

server：專門處理 request, response 的程式。
資料庫系統：專門處理資料的程式。

• SQL (Sturcture Query Language)
  專門來查詢關聯式資料庫裡面的資料的語言。

• 關聯式資料庫
  不同 table 存放相關的資料，藉由相同的欄位資料，讓不同 table 間的資料有所關聯。

  ex: MySQL, PostgreSQL, MSSQL。每一套資料庫系統有不盡相同的 SQL 語法，但主要功能差不多。

• NoSQL
  NoSQL 或者 Not only SQL，存成像 JSON, Object 格式。

  非關聯式資料庫的優點之一是當想存 log (日誌)時，直接存放在物件裡即可，但在關聯式資料庫則需要新增欄位。

  ex: MongoDB。

phpMyAdmin

phpMyAdmin 是一整套 php 的網頁，管理資料庫的介面。

其他可管理資料庫的介面(程式)有 Adminer, SquelPro。

Table Schema

每個表格都有結構，結構就是資料庫的 schema

• 屬性
  在格式是 INT 的情況下，若數字必為正整數，將屬性存成 unsigned，能存的範圍多一倍。

通常在做一個產品前須先想好資料庫的 schema 要怎麼開。

Index Unique 是什麼？

• Priamry Key (PK)
  設定為主鍵的欄位不可為空、不可重複。設定為 PK 的欄位一定是 unique，用於 user name, user account，避免重複。
• Index
  建立索引後，資料庫會建立查詢的方法，只要搜尋條件明確，可在查詢某個欄位時較快，也可將複合的欄位放在一起建立索引，例如 user name, password。

php 執行流程

MySQL 與 PHP 的互動 (conn.php)

<?php
  $server_name = 'localhost';
  $username = 'yong';
  $password = 'yong';
  $db_name = 'yong';

  $conn = new mysqli($server_name, $username, $password, $db_name);

  if ($conn->connect_error) {
    die('資料庫連線錯誤:' . $conn->connect_error);
  }

  $conn->query('SET NAMES UTF8');
  $conn->query('SET time_zone = "+8:00"');
?>

MySQL 與 PHP 的互動 (index.php)

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

  $result = $conn->query("SELECT * FROM users ORDER BY id ASC");
  if (!$result) {
    die($conn->error);
  }

  while ($row = $result->fetch_assoc()) {
    echo "id:" . $row['id'];
    echo " <a href='delete.php?id=" . $row['id'] ."'>刪除</a>";
    echo '<br>';
    echo "username:" . $row['username'] . '<br>';
  }
?>

<h2>新增 user</h2>
<form method="POST" action="add.php">
  username: <input name="username" />
  <input type="submit" />
</form>

<h2>編輯 user</h2>
<form method="POST" action="update.php">
  id: <input name="id" />
  username: <input name="username" />
  <input type="submit" />
</form>

MySQL 與 PHP 的互動 (add.php)

• 變數命名與字串拼接 (較差)

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

  $username = 'apple';
  $sql = "insert into users(username) values('". $username ."')";
  echo $sql;
  exit();
  $result = $conn->query($squl);
  if (!$result) {
    die($conn->error);
  }

  print_r($result);
?>

• 完整版

  <?php
  require_once('conn.php');
  
  if (empty($_POST['username'])) {
    die('請輸入 username');
  }
  
  $username = $_POST['username'];
  
  $sql = sprintf(
    "insert into users(username) values('%s')",
    $username
  );
  echo 'SQL: ' . $sql . '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }
  header('Location: index.php');
  ?>
  

  
  

MySQL 與 PHP 的互動 (delete.php)

<?php
  require_once('conn.php');
  if (empty($_GET['id'])) {
    die('請輸入 id');
  }
  $id = $_GET['id'];
  $sql = sprintf(
    "delete from users where id=%d",
    $id
  );
  echo 'SQL: ' . $sql . '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

  if ($conn->affected_rows) {
    echo "刪除成功";
  } else {
    echo "查無資料";
  }
  // header('Location: index.php');
?>

MySQL 與 PHP 的互動 (update.php)

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

  if (empty($_POST['id']) || empty($_POST['username'])) {
    die('請輸入 id 與 username');
  }

  $id = $_POST['id'];
  $username = $_POST['username'];
  $sql = sprintf(
    "update users set username='%s' where id=%d",
    $username,
    $id
  );
  echo $sql . '<br>';
  $result = $conn->query($sql);
  if (!$result) {
    die($conn->error);
  }

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

Cookie 簡介

XSS 攻擊

Cross-Site Scripting

把一段程式碼作為輸入資料

在沒有做 XSS 攻擊防範的網站，可輸入程式碼獲取 cookie 或資料

<script>alert(document.cookie)</script>
// PHPSESSID=v071tind06kvn91fje9d0cn28q

防範 XSS: htmlspecialchars

// utils.php
function escape($str) {
  return htmlspecialchars($str, ENT_QUOTES);
}

永遠不要相信來自 client 端的資料

SQL injection

• #### 只要知道 username 就能登入
  改變 query 的意思，變成從 db 把 username select 出來，不檢查密碼，以下方法只要知道 username，即可登入。
  ```php=
  SELET * from users
  WHERE username = '%s', password = '%s'

// 使用者輸入帶有特殊符號的 username
username: 'aa'#'
password: 'bbb'

// aa 後面的字符都被註解掉
SELECT * from users
WHERE username = 'aa'#', passworwd = 'bbb'

- #### 把所有資料取出來
  把惡意構造的字串注入到原本的 sql query 當中

```php=
SELET * from users
WHERE username = '%s', password = '%s'

// 使用者輸入帶有特殊符號的 username
username: '' or 1=1#
password: 'bbb'

// 1 = 1 為 true，把資料庫所有東西取出來
SELECT * from users
WHERE username = '' or 1=1#, passworwd = 'bbb'

• #### INSERT INTO 可新增多筆資料

  // 新增兩筆資料
  INSERT INTO comments(nickname, content)
  VALUES ('aa', 'bb'), ('aa2', 'bb2')
  

• #### 改變使用者名稱並新增兩筆資料，可模仿任何人發文
  ```php=
  // 原本的 sql query
  INSERT INTO comments(nickname, content)
  VALUES ('%s', '%s')

// 惡意的 content
content: '), ('admin', 'test)#

INSERT INTO comments(nickname, content)
VALUES ('aa', ''), ('admin', 'test')

- #### 在內容新增 sql query

```php=
content: '), ('我是駭客', (SELECT password from yongchen_users3 WHERE id = 80))#

content: '), ((SELECT username from yongchen_users3 WHERE id = 30), (SELECT password from yongchen_users3 WHERE id = 30))#

INSERT INTO yongchen_comments3(nickname, content)
VALUES ('cc', ''), ((SELECT username from yongchen_users3 WHERE id = 30), (SELECT password from yongchen_users3 WHERE id = 30))#

修正 SQL INJECTION: PREPARE STATEMENT

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

資料庫正規化

First Normal Form (1NF)

讓資料庫有關聯但去除依賴

例如：

在 Table playlists，Table songs 之間再建立 Table playlist_song
一個 playlist 裡有包含多首歌，一首歌被包含多個 playlist