Share
Embed
Transcript
52 slides
-
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); -
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); -
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/ -
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 -
Examples - OOP style usage examples/php/data/mysqli_usage_oop.php - Procedural style usage
examples/php/data/mysqli_usage_proc.php -
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(); -
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); -
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; } -
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); } -
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"; } -
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"; } -
HTML special characters - Use the htmlspecialchars() function when processing
user input - See http://php.net/manual/en/function.htmlspecialchars.php -
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(); -
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(); -
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"); -
Superglobals - So far - $_GET - $_POST - $_REQUEST
- Today - $_SERVER - $_COOKIE - $_SESSION -
- $_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 -
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 -
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 -
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! -
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” -
Cookie consent - New EU rules governing the use of
cookies - Websites need to specifically gain the consent of their visitors -
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 -
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! -
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 -
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 -
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 } -
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 -
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 -
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 } -
Additional tips on security - Limit the number of failed
login attempts - Require strong passwords - Certain length - Mixing in special characters