PHP Cookie/Session MySQL 資料庫操作

Transcript

1. PHP Cookie/Session MySQL 資料庫操作 102403024 郭⼦子德 資管⼆二 me@davy.tw, 102403024@cc.ncu.edu.tw
2. Outline • Cookie • Session • MySQL
3. Cookie
4. 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.
5. 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.
6. 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.
7. Cookie
8. 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)
9. In PHP way
10. <?php setcookie("user", "Davy", time() + 3600); ?>
11. <?php setcookie("user", "Davy", time() + 3600); ?>
12. <?php setcookie("user", "Davy", time() + 3600); ?> name value expire
13. bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 )
14. 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)
15. bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) the path cookie good for
16. bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) the domain cookie good for
17. bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) only in https
18. bool setcookie ( 
 string $name
 [, string $value
 [, int $expire = 0
 [, string $path
 [, string $domain
 [, bool $secure = false
 [, bool $httponly = false
 ) only through http header
19. How about read?
20. <?php $user = getcookie("user"); ?>
21. <?php $user = getcookie("user"); ?>
22. <?php $user = $_COOKIE["user"]; ?>
23. And delete?
24. <?php unset($_COOKIE["user"]); ?>
25. <?php unset($_COOKIE["user"]); ?> 獑
26. <?php unset($_COOKIE["user"]);
 setcookie("user", "", time() - 3600); ?>
27. <?php unset($_COOKIE["user"]);
 setcookie("user", "", time() - 3600); ?> expired in browser
28. Session
29. 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)
30. In PHP way
31. <?php session_start(); ?>
32. <?php session_start(); ?>
33. <?php session_start(); $_SESSION['name'] = "Davy"; ?>
34. <?php session_start(); $_SESSION['name'] = "Davy"; ?> ౫őԟჿᔊఊ
35. <?php session_start(); $user = $_SESSION['name']; ?>
36. <?php session_start(); unset($_SESSION['name']); ?>
37. MySQL
38. MySQL • Relational database management system • Using `Structured Query Language’ /maɪ ˌɛskjuːˈɛl/, "My S-Q-L"
39. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";
40. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";
41. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy"; 選取欄位（逗號分隔） 選取資料表 選取條件
42. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";
43. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";
44. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";
45. SELECT `*`
 FROM `table` 
 WHERE `username` = "Davy";
46. DELETE
 FROM `table`
 WHERE `username` = "Bob";
47. DELETE
 FROM `table`
 WHERE `username` = "Bob";
48. DELETE
 FROM `table`
 WHERE `username` = "Bob"; 選取資料表 選取條件
49. DELETE
 FROM `table`
 WHERE `username` = "Bob";
50. DELETE
 FROM `table`
 WHERE `username` = "Bob";
51. DELETE
 FROM `table`
 WHERE `username` = "Bob";
52. UPDATE `table`
 SET `bio` = "Hi",
 `email` = "fake@mail.org"
 WHERE `username` = "Ann";
53. UPDATE `table`
 SET `bio` = "Hi",
 `email` = "fake@mail.org"
 WHERE `username` = "Ann";
54. UPDATE `table`
 SET `bio` = "Hi",
 `email` = "fake@mail.org"
 WHERE `username` = "Ann"; 選取資料表 修改內容 選取條件
55. UPDATE `table`
 SET `bio` = "Hi",
 `email` = "fake@mail.org"
 WHERE `username` = "Ann";
56. UPDATE `table`
 SET `bio` = "Hi",
 `email` = "fake@mail.org"
 WHERE `username` = "Ann";
57. UPDATE `table`
 SET `bio` = "Hi",
 `email` = "fake@mail.org"
 WHERE `username` = "Ann";
58. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", "");
59. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", "");
60. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", ""); 選取資料表 指定欄位 對應值
61. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", ""); 選取資料表 指定欄位 對應值
62. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", ""); 選取資料表 指定欄位 對應值
63. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", "");
64. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", "");
65. INSERT INTO `table`
 (`name`, `email`, `bio`)
 VALUES
 ("Sandy", "mom@home.tw", "");
66. In PHP way
67. <?php $database = new mysqli(
 "localhost", 
 "r00t", 
 "p455w0rd", 
 "db_test"
 ); ?>
68. <?php $database = new mysqli(
 "localhost", 
 "r00t", 
 "p455w0rd", 
 "db_test"
 ); ?>
69. <?php $database = new mysqli(
 "localhost", 
 "r00t", 
 "p455w0rd", 
 "db_test"
 ); ?> host username password database
70. 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")
 )
71. 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
72. /usr/local/etc/php.ini
73. 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
74. <?php $database = new mysqli(); $database->select_db("db_test"); ?>
75. <?php $database = new mysqli(); $database->select_db("db_test"); ?>
76. <?php $database = new mysqli(); $database->select_db("db_test"); ?> database
77. <?php $result = $database->query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?>
78. <?php $result = $database->query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?>
79. <?php $result = $database->query(
 'SELECT `*` FROM `table` WHERE `username` = "Davy"'
 ); ?> SQL
80. <?php $result = $database->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
81. 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
83. Homework
84. 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
85. Examples http://svr.saru.moe/sec_tests/board/
86. Bonus • Welcome to add some features for bonus points • e.g. admin center, paginate, security, points system, activating account, etc.
87. Examples http://ncucc.davy.tw/guestbook/
88. Examples http://w181496.twbbs.org/board/
89. Deadline • Demo on Dec. 8 • Mail the source code and the link to your guestbook 
 to me@davy.tw or 102403024@cc.ncu.edu.tw 
 before 12/8 24:00
 remember to tell me who you are
90. 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