2
\$\begingroup\$

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.

\$\endgroup\$
3
\$\begingroup\$

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!

\$\endgroup\$
  • \$\begingroup\$ Thanks for the comments. I will take them into consideration upon reviewing the code. By a decent password you mean something like BCRYPT that includes hash/salt? Would you elaborate on "what you would really do with the error message"? Regarding objects I have given it a try but the students are in the area of design and have zero experience with classes/methods etc. It is second semester students and experience have taught me to find a middle path when it comes to PHP. It would have been different if they were in the computer science area. \$\endgroup\$ – kexxcream Sep 4 '15 at 18:57
  • 1
    \$\begingroup\$ I am no security expert (or even a good amateur), I just meant pointing out to the students that no responsible person has a 4 character password. As to 'what you would really do with the password', I mean walking them through ways they might be able to retry the database connection. Finally, I have not taught non-technical students, so I'll take your word for it. Mostly I want to kick myself for all the time I wasted on spaghetti code! \$\endgroup\$ – sunny Sep 4 '15 at 18:58
  • \$\begingroup\$ +1 for use of objects, passing around an instance of a database connection class gives you all sorts of options \$\endgroup\$ – samrap Sep 4 '15 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.