Serverside Webscripting [JLW322]

07.databases

Intro

Choose your flavor

Connecting to a database server from PHP possible by means of an extension
Extensions are available for most database servers
- MySQL, MS SQL, Oracle, …
To connect to a MySQL server, 3 extensions exist
- mysql — Classic extension, procedural (= based on methods)
- mysqli — Improved version of the mysql extension. Supports Prepared Statements, Multiple Statements, Transactions, etc. Both OO and procedural
- pdo_mysql — OO based; Implements PHP Data Objects, an interface to communicating with a database server.

We'll choose

DBMS? → MySQL
- We've already worked with this
- Free
Extension? → PDO
- The mysql and mysqli extensions are outdated
  - mysql is procedural. mysqli's OO form not robust as it clearly was an afterthought
- PDO is object oriented
- mysql and mysqli are MySQL only. PDO plays nice with multiple DBMS's such as MSSQL, PostgreSQL, etc.

Activating pdo_mysql

Activate via php.ini
- Remove the leading ; to activate
- Already activated in Wampserver

Verify activation

Use phpinfo() to check if PDO and pdo_mysql are activated

Connecting & Disconnecting

First of all

We'll use a config.php to store our credentials in

<?php

		define('DB_HOST', 'localhost');
		define('DB_USER', 'root');
		define('DB_PASS', 'Azerty123');

		define('DB_NAME_FF', 'fotofactory');
		define('DB_NAME_STATUS', 'status');

//EOF

The config is used throughout the next code examples
- Edit the file if needed (location: /assets/07/examples)
SQL Dumps of the used databases also in that folder

Connecting (1)

Connect by creating a new instance of PDO with params:
1. A DSN, containing the database host, schema, and charset
2. The username to use when connecting to the DBMS
3. The password to use when connecting to the DBMS

<?php

	require_once 'config.php';

	$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME_FF . ';charset=utf8mb4', DB_USER, DB_PASS);

	echo 'Connected to the database';

//EOF

Disconnecting?

Not needed
- When the script has finished, the variable dies with it

Catching Errors

Let's break things

PHP will tell you if something went wrong

<?php

	// Include config
	require_once 'config.php';

	// Make Connection
	$db = new PDO('mysql:host=' . DB_HOST .';dbname=does_not_exist;charset=utf8mb4', DB_USER, DB_PASS);

	echo 'Connected to the database';

	// ... your query magic here

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000] [1049] Unknown database 'does_not_exist'' in /Users/bramus/Dropbox/Kaho/.../assets/07/examples/02.php:7 Stack trace: #0 /Users/bramus/Dropbox/Kaho/.../assets/07/examples/02.php(7): PDO->__construct('mysql:host=loca...', 'root', 'Azerty123') #1 {main} thrown in /Users/bramus/Dropbox/Kaho/Lesactiviteiten/WS1 - Serverside/2013-2014/slidedeck/assets/07/examples/02.php on line 7

Don't show errors on screen!

Showing errors on screen is a huge security issue!
- Users can get a glimpse of your DB credentials
- Users can get a glimpse of your DB layout
- Users know the physical location of your website

Catching Errors

An exception will be thrown when PDO cannot connect to the database server or access database.

→ Use a try/catch struct

try {
	$db = new PDO('mysql:host=' . DB_HOST .';dbname=does_not_exist;charset=utf8mb4', DB_USER, DB_PASS);
} catch (Exception $e) {
	exit('Could not connect to database server or access database');
}

Safer, as we don't expose our DB credentials to potential hackers anymore

Userfriendly error messages

Better: Show user-friendly messages on screen

<?php

	function showDbError($type) {
		header('location: error.php?type=db&detail=' . $type);
		exit();
	}

	require_once 'config.php';

	try {
		$db = new PDO('mysql:host=' . DB_HOST .';dbname=does_not_exist;charset=utf8mb4', DB_USER, DB_PASS);
	} catch (Exception $e) {
		showDbError('connect');
	}

//EOF

Logging errors

As a developer I'm not interested in a userfriendly error message, I want to see the actual error in a logfile
- → Pass the Exception message into showDbError() and log it in a file/db

<?php

	function showDbError($type, $msg) {
		file_put_contents(
			__DIR__ . '/error_log_mysql',
			PHP_EOL . (new DateTime())->format('Y-m-d H:i:s') . ' : ' . $msg,
			FILE_APPEND
		);
		header('location: error.php?type=db&detail=' . $type);
		exit();
	}

	try {
		$db = new PDO('mysql:host=' . DB_HOST .';dbname=does_not_exist;charset=utf8mb4', DB_USER, DB_PASS);
	} catch (Exception $e) {
		showDbError('connect', $e->getMessage());
	}

Summary / Course Convention

Never ever show errors on screen on a published site
- Log them for the developer
- Present the user a userfriendly error message, in an understandable language and preferably in the layout of the site

Executing Queries

Types of Queries

Essentially, we have two types of queries. Queries that:
1. Yield a resultset (e.g. SELECT, SHOW, DESCRIBE, EXPLAIN, …)
2. Yield no resultset (e.g. INSERT, UPDATE, DELETE, DROP, …)

Executing Queries

Depending on the type of query, use one of these functions

Use PDO::query()⚑ for queries that yield a resultset

$stmt = $db->query('SELECT * FROM collections WHERE user_id = 2');
var_dump($stmt);

Use PDO::exec()⚑ for queries that yield no resultset

$stmt = $db->exec('DELETE FROM collections WHERE user_id = 10');
var_dump($stmt);

Depending on the type of query, we'll need to do some more things to get an actual result we can use (See further)

Executing Faulty Queries

When you write a faulty query (wrong syntax, inexistent table referenced, etc) it will fail silently
- As a developer, you don't want this; you want to know where you messed up and fix it.

Call PDO::setAttribute()⚑ to change the error mode so that exceptions are thrown when a faulty query is executed

try {
	$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME_FF . ';charset=utf8mb4', DB_USER, DB_PASS);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
	showDbError('connect', $e->getMessage());
}

$stmt = $db->exec('DELETE FROM inexistent_table WHERE user_id = 10');

Working with query parameters

When using user input as parameters for a query, don't pass it in directly, always secure the passed in data first
If you don't, users can perform SQL Injection
- Just as XSS, SQL Injection is a real world and big problem!
- “SQL injection is the most pernicious vulnerability in human computer history” (source)
Let's take a closer look on the next few slides …

(Insecure) Example

Detailpage of a collection. Url: detail.php?id=X

<?php

…

	// Get ID from URL
	$id = isset($_GET['id']) ? $_GET['id'] : 0;

	// Get collection from database
	$stmt = $db->query('SELECT * FROM collections WHERE id = ' . $id);

	// Handle result

…

Problem #1

Changing the URL param to a string will break the query
- When visiting detail.php?id=test the query will become broken: SELECT * FROM collections WHERE id = test is an invalid query
Fix: add quotes in your query around the value
- SELECT * FROM products WHERE id = "test" is a valid query
```
$stmt = $db->query('SELECT * FROM collections WHERE user_id = "' . $id . '"');
```

Problem #2

Changing the URL param to a string containing a quote breaks the query
- When visiting detail.php?id=te"st the query will become broken: SELECT * FROM products WHERE id = "te"st" is an invalid query

Possible fix: use addslashes()

SELECT * FROM products WHERE id = "te\"st" is a valid query

$stmt = $db->query('SELECT * FROM collections WHERE user_id = "' . addslashes($id) . '"');

Possible fix: cast $id to an integer
```
$stmt = $db->query('SELECT * FROM collections WHERE id = ' . (int) $id);
```
- Won't work if you expect a string though!

Problem #2 (variant)

Changing the URL param to a dangerous string that resembles SQL results in SQL injection

XKCD: Exploits of a mom
Fix: use addslashes()? Alas, no dice:
- Multibyte characters used with certain DB encodings break stuff:
  - PHP's addslashes() does not recognize the multibyte char 0xbf27 (뼧) and treats it as two single bytes: 0xBF (¿) and 0x27 (') → PHP will escape that 0x27 with a backslash (0x5c) and we end up with the char sequence 0xbf5c27
  - MySQL with a GBK charset connection recognizes 0xbf5c (뽜) as a multibyte char, leaving an unescaped 0x27 in the query (!) → Boom, you've just injected a single quote.

What now?

Sanitize the parameters using the MySQL connection
Two options
1. Manually escape all parameters
2. Use prepared statements

Manually escaping parameters

Use PDO::quote()⚑ to escape a parameter properly

$stmt = $db->query('SELECT * FROM collections WHERE id = ' . $db->quote($userId, PDO::PARAM_INT));

Note

Charset must be set on the DSN!

$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME_FF . ';charset=utf8mb4', DB_USER, DB_PASS);

PDO::quote() will add quotes around the value where needed

$stmt = $db->query('SELECT * FROM collections WHERE name = ' . $db->quote($name, PDO::PARAM_STR));

Prepared Statements (1)

Mingling parameters into your query will become confusing when having lots of parameters
- → Prepared statements overcome this

Prepared statements also prevent SQL Injection, but only if

You use it properly

You instruct it to disable emulated prepares using $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// Make Connection
try {
	$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME . ';charset=utf8mb4', DB_USER, DB_PASS);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (Exception $e) {
	showDbError('connect', $e->getMessage());
}

Prepared Statements (2)

Use PDO::prepare()⚑ to prepare a query with placeholders
```
$stmt = $db->prepare('SELECT * FROM collections WHERE id = ?');
```
- Everywhere you would add a parameter in the query, replace it by a ?
The result is an instance of PDOStatement⚑. With it:
- Bind values onto the placeholders
- Execute it

Using Prepared Statements (1)

Bind values onto the placeholders using PDOStatement::bindValue()⚑

$stmt = $db->prepare('SELECT * FROM collections WHERE id = ?');
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->execute();

Alternative: named placeholders

$stmt = $db->prepare('SELECT * FROM collections WHERE id = :id');
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();

Using Prepared Statements (2)

Also possible (shorter, preferred):

$stmt = $db->prepare('SELECT * FROM collections WHERE id = ?');
$stmt->execute(array($id));

Cannot be used with named placeholders

No matter what type of query is, the call is always PDOStatement::execute()⚑

$stmt = $db->prepare('INSERT INTO collections (name, user_id) VALUES (?, ?)');
$stmt->execute(array('bramus-test', 4));

Summary

Always sanitize & escape user input when constructing queries to prevent SQL Injection
Preference to using prepared statements
- More readable code
- One function to rule them all ™

Remember to manually force real prepared statements!

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Processing queries

We can now properly write queries
As said before: depending on the type of query, we'll need to do some more things to get an actual result we can use

Fetching an entire resultset

Use PDOStatement::fetchAll()⚑ to fetch the entire resultset into an array
- Pass in a fetch style to shape the contents of that array

$stmt =$db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));
$collections = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<pre>';
var_dump($collections);
echo '</pre>';

$collections = $stmt->fetchAll(PDO::FETCH_OBJ);

Fetching a single row

Use PDOStatement::fetch()⚑ to fetch the returned rows one bye one

$stmt =$db->prepare('SELECT * FROM collections WHERE user_id = ? OR name = ?');
$stmt->execute(array(2, 'russia'));

while ($collection = $stmt->fetch(PDO::FETCH_ASSOC)) {
	var_dump($collection);
}

Use case: queries that yield one row

$stmt = $db->prepare('SELECT * FROM collections WHERE id = ?');
$stmt->execute(array(1));

$collection = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($collection);

Fetching a single field

Use PDOStatement::fetchColumn()⚑ to fetch the value of a column from the first row

$stmt = $db->prepare('SELECT name, user_id FROM collections WHERE id = ?');
$stmt->execute(array(1));
var_dump($stmt->fetchColumn());

Use an index to define which column you want

$stmt = $db->prepare('SELECT name, user_id FROM collections WHERE id = ?');
$stmt->execute(array(1));
var_dump($stmt->fetchColumn(1));

Fetching meta information

Use PDO::lastInsertId()⚑ to know the last inserted id (AUTO_INCREMENT) after executing an INSERT query

$stmt = $db->prepare('INSERT INTO collections(name, user_id) VALUES (?,?)');
$stmt->execute(array('Trip to Amsterdam', 10));

echo 'Created album with ID ' . $db->lastInsertId();

Use PDOStatement::rowCount()⚑ to know the number of affected rows made by UPDATE and DELETE queries
```
$stmt = $db->prepare('DELETE FROM collections WHERE user_id = ?');
$stmt->execute(array(10));

echo 'Deleted ' . $stmt->rowCount() . ' rows';
```
- Note: matched rows which aren't updated are not included in this count!

Best Practices

Best Practices?

A set of often used methods / tips to help you working with databases
1. UTF-8
2. Working with dates and time
3. Joins over loops
4. Passwords

Best Practice #1

UTF-8

UTF-8

In short: Use UTF-8, everywhere.
1. DB Schema and Tables
2. DB Connection
3. HTML charset
If you omit one of them, it will not work as expected.

UTF-8, everywhere

DB Schema and Tables: use utf8mb4/utf8mb4_unicode_ci

CREATE DATABASE `mydb`
DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `records` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

DB Connection

$db = new PDO('mysql:host=' . DB_HOST .';dbname=' . DB_NAME_FF . ';charset=utf8mb4', DB_USER, DB_PASS);

HTML charset
```
<meta charset="utf-8" />
```

UTF-8 charset in MySQL, sidenote

MySQL sports multiple UTF-8 character sets
The original UTF-8 charset supported (utf8) was incomplete: it didn't support astral symbols (= emoji), which require 4 byte encoding
Fixed in MySQL 5.5.3 by the addition of utf8mb4 charset
→ Always use utf8mb4

Best Practice #2

Working with dates and time

Dates in MySQL

Current timestamp accessible with NOW()
From timestamp to a readable format with date_format()
- Example
```
SELECT date_format("%Y-%m-%d", publishTimestamp) AS readabledate FROM ...
```
- Most format directives the same as in PHP but with leading %
- See mysql docs for more info

From datestring to timestamp with unix_timestamp()

Example

SELECT unix_timestamp(publishDateTime) AS timestamp FROM ...

Date & time best practices (1)

Never combine PHP and MySQL time
- Funky situations where mysql server clock is ahead of webserver
- Our choice: let webserver (thus PHP) decide what time it is
Store dates in a readable format in your database
- Timestamps are not that readable when looking at raw dumps
- Use datetime as the field type
  - Beware: does not contain timezone information!
  - Sidenote: If you ever were to make a timezone aware app store all dates/times as UTC in your DB

Date & time best practices (2)

Convert dates in PHP, not in your queries (unless necessary)
- Prevents complex queries
- Examples
  - Storing dates: $data['publish_date'] = (new Datetime())->format('Y-m-d H:i:s');
  - Displaying dates: $month = (new Datetime($dbResult['publish_date']))->format('m')
Don't be too specific when retrieving items after/before a given date. Bad example:
```
$stmt = $db->prepare('SELECT * FROM blogposts WHERE publish_date ?');
$stmt->execute(array((new DateTime())->format('Y-m-d H:i:s')));
```
- MySQL Query Cache won't cache this
- Fix: Round your dates to the minute or 5 minutes if the script allows it

Best Practice #3

Joins over loops

Example

Detailpage of a blogpost
- Shows blogpost
- Allow comments on blogpost
- Author information stored separately in database

Bad execution

Execute queries inside a loop

$id = (int) isset($_GET['id']) ? $_GET['id'] : 0;

$stmt = $db->prepare('SELECT id, title, content FROM blogs WHERE id = ?');
$stmt->execute(array($id));
$blogpost = $stmt->fetch(PDO::FETCH_ASSOC);

$stmt = $db->prepare('SELECT id, blog_id, comment, publishdate, author_id FROM comments WHERE blog_id = ?');
$stmt->execute(array($id));
$comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($comments as $c) {
	$stmt = $db->prepare('SELECT * FROM authors WHERE id = ?');
	$stmt->execute(array($c['author_id']));
	$author = $stmt->fetch(PDO::FETCH_ASSOC);

	// … show comment + author info
}

With 50 comments, 52 queries are executed
- 1 x get blogpost, 1 x get comments, 50 x get authorinfo

Proper execution

Use a JOIN to minimize the number of executed queries

$id = (int) isset($_GET['id']) ? $_GET['id'] : 0;

$stmt = $db->prepare('SELECT id, title, content FROM blogs WHERE id = ?');
$stmt->execute(array($id));
$blogpost = $stmt->fetch(PDO::FETCH_ASSOC);

$stmt = $db->prepare('SELECT c.id, c.blog_id, c.comment, c.publishdate, c.author_id, a.name, a.email, a.url FROM comments AS c INNER JOIN authors AS a ON c.author_id = a.id WHERE c.blog_id = ?');
$stmt->execute(array($id));
$comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($comments as $c) {
	// … show comment + author info
}

With 50 comments, 2 queries are executed
- 1 x get blogpost, 1 x get comments with authorinfo

Best Practice #4

Encrypt passwords

Encrypt passwords (1)

You must always encrypt stored passwords. Never store them as plaintext
- If you don't, it's very easy for a hacker to steal lots of passwords if he ever got access to your database
Beware of old code/examples:
- md5()⚑ and sha1()⚑ are outdated/too weak, don't use them
- crypt()⚑ strong but no long preferred

Encrypt passwords (2)

Use password_hash()⚑ and password_verify()⚑
- Uses a strong one-way hashing algorithm

Encrypting a password:

echo password_hash('bramus', PASSWORD_DEFAULT); // store this into your DB

Verifying a password:

$pass = isset($_POST['password']) ? $_POST['password'] : '';
// Value as stored in database
$encrypted = '$2y$10$Ae/ZoEnFze9SZKcgbNBluOObABeejekNoJ2iNmyf4QM5IYSIyzFPe';

if (password_verify($pass, $encrypted)) { … }

Extra: PhpMyAdmin

Demo time!

Demo Recap

Import/Export databases
Create tables
Insert/Update data
Run Queries
Add users and set privileges

Serverside Webscripting [JLW322]

07.databases

Intro

Choose your flavor

We'll choose

Activating pdo_mysql

Verify activation

Connecting & Disconnecting

First of all

Connecting (1)

Disconnecting?

Catching Errors

Let's break things

Don't show errors on screen!

Catching Errors

Userfriendly error messages

Logging errors

Summary / Course Convention

Executing Queries

Types of Queries

Executing Queries

Executing Faulty Queries

Working with query parameters

Working with query parameters

(Insecure) Example

Problem #1

Problem #2

Problem #2 (variant)

What now?

Manually escaping parameters

Prepared Statements (1)

Prepared Statements (2)

Using Prepared Statements (1)

Using Prepared Statements (2)

Summary

Processing queries

Processing queries

Fetching an entire resultset

Fetching a single row

Fetching a single field

Fetching meta information

Best Practices

Best Practices?

Best Practice #1UTF-8

UTF-8

UTF-8, everywhere

UTF-8 charset in MySQL, sidenote

Best Practice #2Working with dates and time

Dates in MySQL

Date & time best practices (1)

Date & time best practices (2)

Best Practice #3Joins over loops

Example

Bad execution

Proper execution

Best Practice #4Encrypt passwords

Encrypt passwords (1)

Encrypt passwords (2)

Extra: PhpMyAdmin

Demo time!

Demo Recap

Questions?

Code summary

Sources

Best Practice #1

UTF-8

Best Practice #2

Working with dates and time

Best Practice #3

Joins over loops

Best Practice #4

Encrypt passwords