Web programming - PHP Part II.

University of Stavanger, DAT310

Transcript

1. Web Programming PHP Part II. Krisztian Balog | University of Stavanger
2. Working with textfiles
3. Handling (text)files - Opening file - $handle = fopen($filename, $mode); - $mode can be a, r, w - Writing to file - fwrite($handle, $data); - Closing file - fclose($handle); - Reading from file - fread($handle, $length);
4. Example examples/php/data/textfiles.php // Read data from textfile (line-by-line)
 $fhandle = fopen($filename, "r");
 while (!feof($fhandle)) {
 $buffer = fgets($fhandle, 4096);
 $line = trim($buffer); // remove end line character
 echo $line . "\n";
 }
 fclose($fhandle); // Write data to textfile
 $filename = "testfile.txt";
 $fh = fopen($filename, 'w') or die("can't open file");
 fwrite($fh, "12\tJohn\t1981-04-02\n");
 fwrite($fh, "23\tMary\t1991-02-22\n");
 fwrite($fh, "44\tJacob\t1987-11-12\n");
 fclose($fh);
5. Exercises #1, #2 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
6. MySQL
7. MySQL Tools - PhpMyAdmin (web-based) - download from http://www.phpmyadmin.net - included in MAMP and WAMP by default - try http://localhost/phpMyAdmin - MySQLWorkbench - download from http://www.mysql.com/products/workbench/
8. Using MySQL in PHP - Extensions for PHP5 - MySQL deprecated since 2012 - MySQLi (MySQL improved) - Automatically installed in most cases - Offers both an object-oriented and a procedural API - PDO (PHP Data Objects) - Works with a number of different databases, not just MySQL - Object-oriented API
9. Examples - OOP style usage
 examples/php/data/mysqli_usage_oop.php - Procedural style usage
 examples/php/data/mysqli_usage_proc.php

10. Opening a connection
 OOP style // Create connection
 $mysqli = new mysqli($db_server, $db_username, $db_password, 
 $db_database);
 
 // Check connection
 if ($mysqli->connect_error) {
 die("Connection failed: " . $mysqli->connect_error);
 }
 echo "Connected successfully";
 
 // Do something
 
 // Disconnect
 $mysqli->close();
11. Opening a connection
 Procedural style // Create connection
 $conn = mysqli_connect($db_server, $db_username, $db_password, 
 $db_database);
 
 // Check connection
 if (!$conn) {
 die("Connection failed: " . mysqli_connect_error());
 }
 echo "Connected successfully";
 
 // Do something
 
 // Disconnect
 mysqli_close($conn);
12. Executing an SQL statement
 OOP style // Insert record
 $sql = "INSERT INTO test(name, age) VALUES ('John', 12)";
 if ($mysqli->query($sql) === TRUE) {
 echo "New record created successfully";
 } else {
 echo "Error: " . $sql . "<br>" . $mysqli->error;
 } // Update record(s)
 $sql = "UPDATE test SET age=15 WHERE name='John'";
 if ($mysqli->query($sql) === TRUE) {
 echo "Record(s) updated successfully";
 } else {
 echo "Error: " . $sql . "<br>" . $mysqli->error;
 }
13. Executing an SQL statement
 Procedural style // Insert record
 $sql = "INSERT INTO test(name, age) VALUES ('John', 12)";
 if (mysqli_query($conn, $sql) === TRUE) {
 echo "New record created successfully<br>";
 } else {
 echo "Error: " . $sql . "<br>" . mysqli_error($conn);
 } // Update record(s)
 $sql = "UPDATE test SET age=15 WHERE name='John'";
 if (mysqli_query($conn, $sql) === TRUE) {
 echo "Record(s) updated successfully<br>";
 } else {
 echo "Error: " . $sql . "<br>" . mysqli_error($conn);
 }
14. Executing a SELECT query
 OOP style // Query data
 $sql = "SELECT name, age FROM test";
 $result = $mysqli->query($sql);
 
 if ($result->num_rows > 0) {
 // output data of each row
 while($row = $result->fetch_assoc()) {
 echo "name: " . $row['name']
 . " - Age: " . $row['age']. "<br>";
 }
 } else {
 echo "0 results";
 }
15. Executing a SELECT query
 Procedural style // Query data
 $sql = "SELECT name, age FROM test";
 $result = mysqli_query($conn, $sql);
 
 if (mysqli_num_rows($result) > 0) {
 // output data of each row
 while($row = mysqli_fetch_assoc($result)) {
 echo "name: " . $row['name'] 
 . " - Age: " . $row['age']. "<br>";
 }
 } else {
 echo "0 results";
 }
16. Setting up local environment - See instructions on itslearning
17. Exercise #3 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
18. Exercises #4, #5 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
21. HTML special characters - Use the htmlspecialchars() function when processing user input - See http://php.net/manual/en/function.htmlspecialchars.php
22. SQL injection - http://php.net/manual/en/security.database.sql-injection.php - http://www.w3schools.com/sql/sql_injection.asp
23. Prepared statements
 UPDATE $stmt = $mysqli->prepare("UPDATE test SET age=? WHERE name=?");
 // bind parameters
 $age = 14;
 $name = "John";
 $stmt->bind_param('is', $age, $name);
 $stmt->execute();
 $res = $stmt->affected_rows;
 echo $res . " rows updated";
 $stmt->close();
24. Prepared statements
 SELECT $stmt = $mysqli->prepare("SELECT name, age FROM test WHERE age>=?");
 // bind parameters
 $minage = 14;
 $stmt->bind_param("i", $minage);
 // bind result variables
 $stmt->bind_result($name, $age);
 $stmt->execute();
 
 // iterate results
 while ($stmt->fetch()) {
 echo $name . ": " . $age . "<br>";
 }
 $stmt->close();
25. UTF-8 encoding - Use UTF8 encoding for the database - Set charset after connecting to MySQL in PHP $mysqli = new mysqli(…);
 if ($mysqli->connect_error) {
 die("Connection failed: " . $mysqli->connect_error);
 }
 $mysqli->set_charset("utf8");
26. Exercise #6 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
27. Superglobals - So far - $_GET - $_POST - $_REQUEST - Today - $_SERVER - $_COOKIE - $_SESSION
28. - $_SERVER['…'] - 'PHP_SELF' - filename of the currently executing script - relative to the document root - 'REMOTE_ADDR' - IP address of the user - 'REMOTE_HOST' - hostname of the user - 'REQUEST_METHOD' - request method (POST, GET, etc.) - 'HTTP_USER_AGENT' - user’s browser - See http://php.net/manual/en/reserved.variables.server.php Server and Environment Info examples/php/data/serverinfo.php
29. Storing data Client Server Internet Cookie Files Database Session
30. Cookies - Embedded on the user’s computer - Small, often encrypted text files, located in the browser directories - Cookies enable to aggregate requests around a particular user - Each time the same computer requests a page with a browser, it will send the cookie too - Many misconceptions around cookies - Transmit viruses - Install malware on your computer
31. Cookies - Within the context of a particular visit (always with respect to the domain that is shown in the brower’s address bar) - First-party cookie => belongs to the same domain - Third-party cookies => belong to a different domain - Typical usage - Tracking the user and her browsing activities (possibly for a long time) - Storing login information - Same origin policy - You (as a site) can only view or set your own (i.e., first-party) cookie
32. Cookies can be viewed/edited 
 (built-in browser functionality or extensions)
33. Example
 Cookie stored by Twitter
34. Third-party cookies - Belong to domains different from the one shown in the address bar - Typically used for "behind the scenes" tracking - So that advertisers can show you personalized banner ads - When a piece of information is displayed from a third-party (image, advertisement, etc.), that site is allowed to set a cookie - Each domain can only read the cookie it created! - Can be blocked in the browser’s privacy settings!
35. User profiling with third-party cookies - Suppose that a larger number of sites have banner adverts from www.advertiser.com - It is possible for the advertiser to use its third party cookie to identify you as you move from one site to another site - Even though it may not know your name, it can use the random ID number in the cookie to build up an anonymous profile of the sites you visit - “visitor 3E7ETW278UT regularly visits a music site, so show him/her adverts about music and music products”
36. Example
37. Example
 Third-party cookies sent to Twitter
38. Cookie consent - New EU rules governing the use of cookies - Websites need to specifically gain the consent of their visitors
39. Cookies in PHP
    examples/php/data/cookie.php - setcookie($name, $value, $expire) - must be called before any output is sent to the browser - read out previously stored values from $_COOKIE
40. Exercise #7 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
41. Solution - Mind that the cookie’s contents can be modified by the user! - Never store user_id-s in cookies, and especially not without encryption!
42. Sessions - Store information on the server temporarily - It will be deleted after the user leaves the website (or closes the browser) - Each browsing session is identified by a unique ID - sessionID can be stored in a cookie (default) - or propagated in the URL
43. Sessions in PHP
    examples/php/data/session.php - session_start() - must be called before any output is sent to the browser - simply read and write values to/from $_SESSION
44. Exercise #8 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
45. Exercise:
 Registration and login
46. Storing passwords - Never store raw passwords on the server! - Use hashing, not encryption (!) - Hashing is one-way, encryption is reversible - Avoid common hash functions (MD5, SHA1) - Recommended algorithm: Blowfish if (crypt($password_entered, $password_hash) == $password_hash) {
 // password is correct
 }
47. Use salt - String that is hashed together with the password to avoid the usage of lookup ("rainbow") tables - Use a random salt for each password as opposed to a single site-level one
48. Storing passwords - Options - PHP 5.3: crypt() function - PHP 5.5 provides a native password hashing API
 http://php.net/manual/en/book.password.php - References - http://alias.io/2010/01/store-passwords-safely-with-php-and-mysql/ - http://php.net/manual/en/faq.passwords.php
49. Example examples/php/data/password_blowfish.php // generate password hash (one-time, at registration) function salt() {
 $salt = "";
 $salt_chars = array_merge(range('A','Z'), range('a','z'), range(0,9));
 for($i = 0; $i < 22; $i++) {
 $salt .= $salt_chars[array_rand($salt_chars)];
 }
 return $salt;
 }
 
 $password_entered = "123"; // needs to be read from user input
 $password_hash = crypt($password_entered, "$2a$07$" . salt() . "$"); // check entered password (each time at login)
 $password_hash = '…'; // this should be read in from the DB
 
 if (crypt($password_entered, $password_hash) == $password_hash) {
 // check entered passw }
50. Additional tips on security - Limit the number of failed login attempts - Require strong passwords - Certain length - Mixing in special characters
51. Exercises #9, #10 https://github.com/kbalog/web-programming/tree/master/
 exercises/php/data
52. References - W3C PHP Tutorial
 http://www.w3schools.com/php/php_mysql_intro.asp - MySQLi reference
 http://php.net/manual/en/book.mysqli.php