Lecture 23 - The PHP SQL Connection

Today's Agenda

Reminders:

Homework 5 due today by 11pm

Exploration session tomorrow

Announcing Creative Project 8

Accessing SQL from PHP

PDO class

There are two ways to connect to a database through PHP

1. Using MySQLi - a procedural object-oriented connection scheme that only works on MySQL databases.
2. Using PHP Data Objects (PDO) - an object-oriented representation of the connection between PHP and the server that works on 12 different database system.

We are using PDO in this class

Connecting to a database

To connect to a database you need 4 pieces of information

• host
• database name
• the user name for the database
• the database password

Then create a PDO object to represent the database connection

PDO Connection

# Variables for connections to the database.
$host =  'localhost'; #fill in with server name
$dbname = 'todos';    #fill in with db name
$user = 'root';       #fill in with user name
$password = '';       #fill in with password

# Make a data source string that will be used in creating the PDO object
$ds = "mysql:host={$host};dbname={$dbname};charset=utf8";

# connect to the todos database and set some attributes
$db = new PDO($ds, $user, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

PHP (example)

try/catch

Database connections can have different problems: the database server could be down, the database could be missing or corrupted

try/catch helps us catch and identify when errors occur so we can handle the error correctly

try {
   # things to try that could throw an error
}
catch (PDOException $ex) {
  # code for handling the error here.
}
 

PHP (template)

try/catch example

try {
   $db = new PDO($ds, $user, $password);
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 catch (PDOException $ex) {
   header("Content-Type: text/plain");
   print ("Can not connect to the database. Please try again later.\n");
   print ("Error details: $ex \n");
   die();
 }
         

PHP (example)

PDO Query

Once you have the PDO object, you can use it to query the database.

  $rows = $db->query("SQL query string"); # fill in the query string
    

PHP (template)

  $rows = $db->query("SELECT * FROM todos;");
  foreach($rows as $row){
    print_r($row) . "\n";
  }
            

PHP (example)

PDO object's query function returns a PDOStatement object that contains the rows that match a query

  Array ( [task_num] => 1 [0] => 1 ...)
  Array ( [task_num] => 2 [0] => 2 ...)
            

output

PDO Query Real Output

Each row from the output is an associative array both with the column names AND positional indices as the default.

Array ( [task_num] => 1 [0] => 1
        [task_name] => post creative homework on course page
        [1] => post creative homework on course page
        [due_date] => 2018-05-16 06:40:03 [2] => 2018-05-16 06:40:03
        [task_type] => CSE154 [3] => CSE154 )
Array ( [task_num] => 2 [0] => 2 [
         task_name] => prepare the final exam [1] => prepare the final exam
         [due_date] => 2018-05-30 05:40:03 [2] => 2018-05-30 05:40:03
         [task_type] => CSE154 [3] => CSE154 )
          

output

Example: $row["task_type"] or $row[3] gives the contents of the task_type column.

Exceptions for PDO Errors

          $db = new PDO($ds, $user, $password);
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $rows = $db->query("SEELECT * FROM todos;");
          # kaboom!
          

PHP

Using setAttribute, you can tell PDO to throw (generate) a PDOException when an error occurs.

The exceptions will appear as error messages on the page output.

You can catch the exception to gracefully handle the error.

Using fetch

The query returns a PDOStatement

To get the data out of the rows you either use a foreach loop OR you can fetchAll to get all information from the statement (fetch will only get one row, whatever is "next" in the PDO object)

PDO::FETCH_BOTH is the default fetch style, but there are others

          $db = new PDO($ds, $user, $password);
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $rows = $db->query("SELECT * FROM todos LIMIT 1;");
          $row = $rows->fetch(PDO::FETCH_ASSOC);
          print_r($row);       # Just get the first row
          

PHP

Array ( [task_num] => 1
[task_name] => post creative homework on course page
[due_date] => 2018-05-16 21:06:49
[task_type] => CSE154 )
          

output

Inserting through PHP

$str = "INSERT INTO todos (task_name, due_date, task_type)
        VALUES ('$task', NOW(), '$task_type');";
$rows = $db->exec($str);
         

PHP (example)

exec runs the given SQL code and returns the number of rows affected.

NOTE: THIS CODE IS NOT SECURE!

It's vulnerable to SQL injection (demo)

Little Bobby Tables

XKCD 327

Another example of SQL injection

Hackaday

Using PDO prepare for security

$sql = "INSERT INTO todos (task_name, due_date, task_type)
         VALUES (:task, NOW(), :type );";
$stmt = $db->prepare($sql);
$params = array("task" => $_GET["task"], "type" => $_GET["type"]);
$stmt->execute($params);
         

PHP (example)

The "variables" with colons in the SQL statement are replaced with values from the associative array that is being passed in on execute