The stock PHP MySQL functions are fine and all, though they tend to be a bit insecure, and as such you have to be very careful about what user input is used in them and make sure that it’s all escaped.  It’s easy to forget or mess up on one little input and leave a security hole big enough to drop your tables through.  That’s why it’s always been wise to find or code your own class for abstracting the use of the stock MySQL functions, so that you can be sure that all inputs are going through the same escaping process before the query.

However, it’s probably best now to use PDO.  PDO is a data access abstraction class which works for multiple database types and directly uses its database drivers rather than going through the MySQL functions, and it supports prepared statements and bound parameters and transactions, and has been available since PHP 5.0 as a PECL extension and comes natively in PHP 5.1+, so odds are it’s available on just about any web server you come across.  It’s pretty simple, and when using prepared statements it is practically invulnerable to SQL injection.  It is, however, a bit different from using the stock functions.

PDO Class Manual

Connecting

So, to get a connection to the DB, you’d do something like this:

$dbhost = 'localhost';  //Note that PDO can take a lot of potential hosts, from sockets to files to a direct server connection
$dbname = 'DatabaseName';
$dbuser = 'MySQLUserName';
$dbpass = 'MySQLPassword';

$dsn = "mysql:dbname=" . $dbname . ";host=" . $dbhost;  //Data source name string

try
{
     $dbh = new PDO($dsn, $dbuser, $dbpass);  //Get a new instance of PDO and connect
}
catch(PDOException $e)
{
     die($e->getMessage());  //Not very good exception handling, but works for a simple demonstration
}

So, this will get you a new PDO object or it will die if it fails to connect. Note the use of the try/catch block.  PDO issues a PDOException if something goes wrong, and the catch block will process if it does, allowing for better error handling such as trying a different method to connect to the DB, redirecting to an error page, logging the error in detail, etc., rather than outright killing the script and displaying basic error output.  Try/catch blocks can be used when handling errors with any PDO method.

The PDO object identifies the connection and has a number of useful methods itself.  You can handle transactions with it, do basic queries (PDO::query()), escape strings (PDO::quote()), but what we want to use right now are prepared statements, which we get using the prepare (PDO::prepare()) method.

PDOStatement Class Manual

$sql = 'SELECT *
        FROM `Users`
        WHERE `Username` = :username
        AND `Password` = :password
        LIMIT 1';  //SQL query with named placeholders

$stmt = $dbh->prepare($sql);  //Returns a PDOStatement class object

Now that we have a PDOStatement Object, we can bind our parameters. Note the use of the named placeholders “:username” and “:password” in the query, we’ll use those to insert our parameters into the query.  There is another method which uses question marks as placeholders, but I prefer to use named placeholders.

//Ideally, this would have been done first, and nothing would have been
//done with the DB before checking that the inputs were there.
//Just showing where the inputs come from with some basic data validation.
if( isset($_POST['username'],$_POST['password'])
   && !empty($_POST['username']) && !empty($_POST['password']) )
{
    $username = $_POST['username'];
    $password = hash('sha512',$_POST['password']);  //Needs salt.
}

//The first parameter of the bindParam method is the placeholder string
//The second parameter is the value/variable to insert into the placeholder
//The third parameter is the input type.  Check PDO Defined Constants in the manual for list of types.  Most common are PARAM_STR and PARAM_INT.
//The fourth parameter is the maximum length of the input

$stmt->bindParam(':username',$username,PDO::PARAM_STR,20);
$stmt->bindParam(':password',$password,PDO::PARAM_STR,128);

Now that we’ve bound the values to the placeholders, we can execute the query and fetch the result.

$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);  //Fetch result into an associative array with keys equal to column names
//Result should be an array containing, at the very least, the keys ['Username'] and ['Password'] and probably ['ID'] as well, populated from the DB.
//That is, if it found a matching row, else it should be false

Now, I’ll do a couple short examples:

//Select News Story by ID, echo Content
$id = $_GET['id'];
$sql = 'SELECT `Title`,`Content`
        FROM `News`
        WHERE `ID` = :id
        LIMIT 1';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':id',$id,PDO::PARAM_INT);
$stmt->execute();
$news = $stmt->fetch(PDO::FETCH_ASSOC);
echo '<h3>' . $news['Title'] . '</h3>' . PHP_EOL;
echo $news['Content'];

//Select last 5 Recent News articles in multiple categories, echo listing
$categories = array('News','Computers','Entertainment','Miscellaneous');
$sql = 'SELECT `ID`,`Title`, `Excerpt`
        FROM `News`
        WHERE `Category` = :category
        ORDER BY `Date` DESC
        LIMIT 5';
$stmt = $dbh->prepare($sql);

//Now for one of the neat features of prepared statements in PDO,
//once a statement is prepared, it may be bound to and executed multiple times.
foreach($categories as $category)
{
     $stmt->bindParam(':category',$category,PDO::PARAM_STR,30);
     $stmt->execute();
     $resultSet = $stmt->fetchAll(PDO::FETCH_ASSOC);  //fetchAll() fetches an array containing arrays of all results found.
     echo '<h2 class="category">' . $category . '</h2>' . PHP_EOL;
     echo '<ul class="catlist">' . PHP_EOL;

     foreach($resultSet as $result)
     {
         echo '<li><a href="http://www.site.com/?id=' . $result['ID'] . '"><b>' . $result['Title'] . '</b> - ' . $result['Excerpt'] . '</a></li>' . PHP_EOL;
     }
     echo '</ul>' . PHP_EOL;
}

Since I just got done doing one earlier, I figured I might as well include how to code a contact form, input validation, and mail handler.  This will be a small one, with a name, email, and comments fields, but is quite expandable.  It will also be organized in a table, which, while not exactly the best HTML coding to handle layout what with CSS positioning and all, is still the easiest method of handling layout.  This will be a free form php script capable of being included on any page.

Form Validation

Since we know what our form is going to have on it, we can go ahead and handle the validation code.  It’s kind of backwards to code for validation before the form, but validation will usually come at the top of the script anyway.  For this form, all fields are required, and the email must be a valid format.

Read more

Aside from obfuscation, which is naming the included files some odd name, or in an oddly named directory and hiding it’s existence from the world through various arcane methods, there are simpler methods to protect php code from being run outside of the overall program structure, and causing unforeseen effects or security holes.

Lock and Key

This is a simple method.  Define a named constant in the index, or standalone pages which use the same includes, with any name and a password or phrase associated with it, like so:

<?php
     define('NAME','This is a Pass Phrase');
?>

Read more

A web site usually consists of at least four areas, but can sometimes have five or more or as few as two.  These areas are:

1. The Header – Site title or logo usually goes here, with perhaps some
   

   Common Site Layout

   site navigation.

2. The Left or Right Column – or both at once, usually used as a navigation or minor content/ad area.
3. The Content – The center block where the majority of information is displayed.
4. The Footer – Closing area.  Usually the copyright or other legal information goes here, or ads, or another site navigation panel.

Each of these areas can be modularized and stitched together based on user input with PHP.  Instead of recreating the header, footer, and left or right panes on each page in the website, they can be created once and have the Content pane switch out to the needed Content.

Read more