Tweet
Tweet

Slide 1

Slide 1 text

PHP Cookie/Session MySQL 資料庫操作 102403024 郭⼦子德 資管⼆二 [email protected], [email protected]

Slide 2

Slide 2 text

Outline • Cookie • Session • MySQL

Slide 3

Slide 3 text

Cookie

Slide 4

Slide 4 text

Cookie • a.k.a. HTTP cookie, web cookie, or browser cookie • Tracking cookies — commonly used as ways to compile long-term records of individuals' browsing histories. • Authentication cookies — the most common method used by web servers to know whether the user is logged in or not, and which account they are logged in with.

Slide 5

Slide 5 text

Tracking cookies • Used to track internet users' web browsing. 1. When user requests a page of the site with no cookie, the server presumes that this is the first page visited by the user, and creates a token and sends it as a cookie back. 2. The cookie will automatically be sent to the server every time; the server will stores the requested URL the time of the request, and the cookie into log. • By analyzing the log, it is possible to find out which pages the user has visited, in what sequence, and for how long. Then, figure out the trending of users’ habit.

Slide 6

Slide 6 text

Personalization cookies • Used to remember the information about the user who has visited a website in order to show relevant content in the future. • Used to remember users' preferences. Users select their preferences by submitting to the server. The server stores the preferences in a cookie. This way, every time the user accesses a page, the server can personalize the page according to the user preferences.

Slide 7

Slide 7 text

Cookie

Slide 8

Slide 8 text

Structure of Cookie • Cookie has a size of 4KB • (name, value) pair of the cookie (i.e. name=value) • Expiry of the cookie • Path the cookie is good for • Domain the cookie is good for • Need for a secure connection to use the cookie • Whether or not the cookie can be accessed through other means than HTTP (i.e., JavaScript)

Slide 9

Slide 9 text

In PHP way

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Slide 12

Slide 12 text

name value expire

Slide 13

Slide 13 text

bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 )

Slide 14

Slide 14 text

bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) expire at 
 (0 for this session)

Slide 15

Slide 15 text

bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) the path cookie good for

Slide 16

Slide 16 text

bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) the domain cookie good for

Slide 17

Slide 17 text

bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) only in https

Slide 18

Slide 18 text

bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) only through http header

Slide 19

Slide 19 text

How about read?

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

And delete?

Slide 24

Slide 24 text

Slide 25

Slide 25 text

獑

Slide 26

Slide 26 text

Slide 27

Slide 27 text

expired in browser

Slide 28

Slide 28 text

Session

Slide 29

Slide 29 text

Session • Storage session on server • Session has no size limit • There is a cookie to save session ID • (name, value) pair of the session (i.e. name=value)

Slide 30

Slide 30 text

In PHP way

Slide 31

Slide 31 text

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

౫őԟჿᔊఊ

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

MySQL

Slide 38

Slide 38 text

MySQL • Relational database management system • Using `Structured Query Language’ /maɪ ˌɛskjuːˈɛl/, "My S-Q-L"

Slide 39

Slide 39 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";

Slide 40

Slide 40 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";

Slide 41

Slide 41 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy"; 選取欄位（逗號分隔） 選取資料表 選取條件

Slide 42

Slide 42 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";

Slide 43

Slide 43 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";

Slide 44

Slide 44 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";

Slide 45

Slide 45 text

SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";

Slide 46

Slide 46 text

DELETE
 FROM `table`
 WHERE `username` = "Bob";

Slide 47

Slide 47 text

DELETE
 FROM `table`
 WHERE `username` = "Bob";

Slide 48

Slide 48 text

DELETE
 FROM `table`
 WHERE `username` = "Bob"; 選取資料表 選取條件

Slide 49

Slide 49 text

DELETE
 FROM `table`
 WHERE `username` = "Bob";

Slide 50

Slide 50 text

DELETE
 FROM `table`
 WHERE `username` = "Bob";

Slide 51

Slide 51 text

DELETE
 FROM `table`
 WHERE `username` = "Bob";

Slide 52

Slide 52 text

UPDATE `table`
 SET `bio` = "Hi",
 `email` = "[email protected]"
 WHERE `username` = "Ann";

Slide 53

Slide 53 text

UPDATE `table`
 SET `bio` = "Hi",
 `email` = "[email protected]"
 WHERE `username` = "Ann";

Slide 54

Slide 54 text

UPDATE `table`
 SET `bio` = "Hi",
 `email` = "[email protected]"
 WHERE `username` = "Ann"; 選取資料表 修改內容 選取條件

Slide 55

Slide 55 text

UPDATE `table`
 SET `bio` = "Hi",
 `email` = "[email protected]"
 WHERE `username` = "Ann";

Slide 56

Slide 56 text

UPDATE `table`
 SET `bio` = "Hi",
 `email` = "[email protected]"
 WHERE `username` = "Ann";

Slide 57

Slide 57 text

UPDATE `table`
 SET `bio` = "Hi",
 `email` = "[email protected]"
 WHERE `username` = "Ann";

Slide 58

Slide 58 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", "");

Slide 59

Slide 59 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", "");

Slide 60

Slide 60 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", ""); 選取資料表 指定欄位 對應值

Slide 61

Slide 61 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", ""); 選取資料表 指定欄位 對應值

Slide 62

Slide 62 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", ""); 選取資料表 指定欄位 對應值

Slide 63

Slide 63 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", "");

Slide 64

Slide 64 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", "");

Slide 65

Slide 65 text

INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "[email protected]", "");

Slide 66

Slide 66 text

In PHP way

Slide 67

Slide 67 text

Slide 68

Slide 68 text

Slide 69

Slide 69 text

host username password database

Slide 70

Slide 70 text

mysqli::__construct (
 [ string $host = ini_get("mysqli.default_host")
 [, string $username = ini_get("mysqli.default_user")
 [, string $passwd = ini_get("mysqli.default_pw")
 [, string $dbname = ""
 [, int $port = ini_get("mysqli.default_port")
 [, string $socket = ini_get("mysqli.default_socket")
 )

Slide 71

Slide 71 text

mysqli::__construct (
 [ string $host = ini_get("mysqli.default_host")
 [, string $username = ini_get("mysqli.default_user")
 [, string $passwd = ini_get("mysqli.default_pw")
 [, string $dbname = ""
 [, int $port = ini_get("mysqli.default_port")
 [, string $socket = ini_get("mysqli.default_socket")
 ) k

Slide 72

Slide 72 text

/usr/local/etc/php.ini

Slide 73

Slide 73 text

mysqli::__construct (
 [ string $host = ini_get("mysqli.default_host")
 [, string $username = ini_get("mysqli.default_user")
 [, string $passwd = ini_get("mysqli.default_pw")
 [, string $dbname = ""
 [, int $port = ini_get("mysqli.default_port")
 [, string $socket = ini_get("mysqli.default_socket")
 ) k

Slide 74

Slide 74 text

select_db("db_test"); ?>

Slide 75

Slide 75 text

select_db("db_test"); ?>

Slide 76

Slide 76 text

select_db("db_test"); ?> database

Slide 77

Slide 77 text

query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?>

Slide 78

Slide 78 text

query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?>

Slide 79

Slide 79 text

query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?> SQL

Slide 80

Slide 80 text

query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?> http://php.net/manual/en/mysqli.query.php • bool • mysqli_result • http://php.net/manual/en/class.mysqli-result.php • fetch_all(……) • fetch_array(……) • fetch_assoc(……) • int $field_count

Slide 81

Slide 81 text

mysqli::prepare • http://php.net/manual/en/mysqli.prepare.php • http://stackoverflow.com/questions/732561/why-is- using-a-mysql-prepared-statement-more-secure- than-using-the-common-escape • http://mattbango.com/notebook/code/prepared- statements-in-php-and-mysqli/ • http://php.net/manual/en/ mysqli.quickstart.prepared-statements.php

Slide 82

Slide 82 text

No content

Slide 83

Slide 83 text

Homework

Slide 84

Slide 84 text

Guestbook • Write a simple guestbook in PHP with MySQL • Use mysqli::prepare to build your statements • Build a user system that everyone can register, login and logout • Every user can create an new post, and edit or delete their own post • Every user can reply on any post, and also can edit or delete their own reply • A post or a reply must show the informations of `Author`, `Title`, `Content`, `Time`, etc

Slide 85

Slide 85 text

Examples http://svr.saru.moe/sec_tests/board/

Slide 86

Slide 86 text

Bonus • Welcome to add some features for bonus points • e.g. admin center, paginate, security, points system, activating account, etc.

Slide 87

Slide 87 text

Examples http://ncucc.davy.tw/guestbook/

Slide 88

Slide 88 text

Examples http://w181496.twbbs.org/board/

Slide 89

Slide 89 text

Deadline • Demo on Dec. 8 • Mail the source code and the link to your guestbook 
 to [email protected] or [email protected] 
 before 12/8 24:00
 remember to tell me who you are

Slide 90

Slide 90 text

Need Help? • PHP.net, http://php.net/docs.php • Google, http://lmgtfy.com/ • Facebook group, 
 https://www.facebook.com/groups/1476116432662453/ • Or contact us directly