PHP script to connect to MySQL database using PDO

Question

Introduction

I am preparing to use a basic script to cover how you can connect to a MySQL database using PDO. This script is meant for educational purposes (introductory class on PHP/MySQL) and does of course not cover all security aspects of database connections.

Points of focus

1. Conformity to the PSR-1 and PSR-2 standards.
2. Structure for connecting to database connection.
3. Security aspects that has not been covered.

Code (settings.php):

<?php
  // Defines database connection information
  $settings = [
    'host'     => '127.0.0.1',
    'name'     => 'c9',
    'port'     => '3306',
    'charset'  => 'utf8',
    'username' => 'admin',
    'password' => 'root'
  ];
?>

Code (db.php):

<?php
  // Includes database connection information
  require_once('../settings.php');

  // Establishes connection to database server
  try {
    $dbh = new PDO(
            sprintf(
              'mysql:host=%s;dbname=%s;port=%s;charset=%s',
              $settings['host'],
              $settings['name'],
              $settings['port'],
              $settings['charset']
            ),
            $settings['username'],
            $settings['password']
    );
    // Prevents emulated prepare statements and sets error mode
    // to PDO::ERRMODE_EXCEPTION
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }
  // Prints out errors raised by PDO
  catch (PDOException $e) {
    die('Error: ' . $e->getMessage());
  }
?>

The ../ for settings.php indicates that the file is outside document root for security purposes. Much appreciated in advance for any comments that could improve this code.

Answer 1

I don't use PHP often, but here are a few comments:

Use Objects to Manage Resources

Like any other language with object-oriented capabilities, PHP serves you best when you manage resources with objects. I'd strongly recommend holding your PDO object within a custom class that manages the connection. This makes the code easier to maintain and doesn't actually have to change much about what you did. The code will also be more extensible. Also, introducing OOP is a very good lesson for students starting out.***

You could then also show them lazy-loading, making sure the PDO isn't opened until there is actually communicating between the server and the database.

Password length

Of course you'd want to point out to your students that you'd need a decent password and not the one you're using for illustrative purposes here. For example, a password that was not a real world and more than 4 letters in length. This is more of a teaching point to make sure everyone has some common sense!

Error handling

Perhaps show your students what you would really do with the error message. For example, how could they wait and call the connection again? It could be helpful to show students they can do something in response to an error message apart from printing it out (like trying again, or waiting and trying again, or redirecting to a different page, or sending a warning email to the sys admin, etc).

***I first learned to make a dynamic website with Robin Nixon's much villified book. When I first learned from it, I didn't understand why people poo-poo the book so much. The reason is that it gets folks off the on the wrong foot. I resisted object-oriented design for websites for a long time because I started with that book and wanted to keep things simple, but of course nothing stays simple for long. If I ever teach coding to beginners, I will start them with objects on day 1!