Lecture 24 - The PHP SQL Connection

Today's Agenda

Allen School Colloquia on Accessibility Thursday 3:30-4:20, EEB 105

Exploration session Thursday on web security

Announcing HW 5 - Pokedex 2, will be released Tuesday

Accessing SQL from PHP

Quick check: what happens here?

print("1");

$host = 'localhost';
$port = '3306';
$user = 'root';
$password = '';
$dbname = 'Potluck';
$ds = "mysql:host={$host}:{$port};" .
      "dbname={$dbname};charset=utf8";

try {
  $db = new PDO($ds, $user, $password);
  $db->setAttribute(PDO::ATTR_ERRMODE,
                    PDO::ERRMODE_EXCEPTION);
  print ("2");
}
catch (PDOException $ex) {
  print ("3");
}

print ("4");

PHP

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 Potluck;");
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 ([id] => 1 [0] => 1 ...)
Array ([id] => 2 [0] => 2 ...)
         

output

Reminder: Set exceptions for PDO errors

         $db = new PDO($ds, $user, $password);
         $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $rows = $db->query("SELECT * FROM Potluck;");
         # 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.

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 ( [id] => 1 [0] => 1
       [name] => Lauren [1] => Lauren
       [dish] => Paleo Apple Tart [2] => Paleo Apple Tart
       [serves] => 8 [3] => 8
       [temperature] => cold [4] => cold)
Array ( [id] => 2 [0] => 2
       [name] => Stephen [1] => Stephen
       [dish] => Balsamic Brussels Sprouts
       [2] => Balsamic Brussels Sprouts
       [serves] => 8 [3] => 8
       [temperature] => hot [4] => hot )
         

simulated printed output

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 Potluck LIMIT 1;");
         $row = $rows->fetch(PDO::FETCH_ASSOC);
         print_r($row);       # Just get the first row
         

PHP

Array ( [id] => 1
[name] => Lauren
[dish] => Paleo Apple Tart
[serves] => 8
[temperature] => cold)
         

simulated printed output

Other fetch examples

Each assume $rows contains the results of a query

# PDO::FETCH_BOTH is the default, results are associative and numerical
while ($row = $rows->fetch()) {
 print_r($row) . "\n";
}

# PDO::FETCH_ASSOC only fetches the associative array
while ($row = $rows->fetch(PDO::FETCH_ASSOC)) {
 print_r($row) . "\n";
}

# PDO::FETCH_NUM only fetches by position
while ($row = $rows->fetch(PDO::FETCH_NUM)) {
  print_r($row) . "\n";
}
         

PDO and modifying DBs

Remember... With Great Power Comes Great Responsibility

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 Potluck (name, dish, serves, temperature, comment) 
        VALUES (:name, :dish, :serves, :temp, :comment );";
$stmt = $db->prepare($sql);
$params = array("name" => $_POST["name"],
                "dish" => $_POST["dish"],
                "serves" => $_POST["serves"],
                "temp" => $_POST["temperature"],
                "comment" => $_POST["comment"]);
$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