As I mentioned in previous posts, my server is continuously being attacked by hackers attempting to exploit MySQL injection vulnerabilities. I use input validation and mysql_real_escape_string to keep them from getting into the databases. Another way to prevent injection is to use stored procedures. Both PHP and perl allow stored procedures. To use them with PHP you use the PHP Data Objects extension. It should be automatically installed with your PHP and MySQL installation.
To test if PDO is enabled, at the command line type:
php -i|grep PDO
You should get something like this.
PDO
PDO support => enabled
PDO drivers => mysql, sqlite
PDO Driver for MySQL => enabled
PDO Driver for SQLite 3.x => enabled
As I mentioned in a previous post, I like to put the database setup info in a separate directory outside of the document root and then include it in the php file where it is used. The file would look like this:
<?php
$user = 'username';
$pass = 'password';
$host = 'localhost';
$db_name = 'website';
try {
$dbWG = new PDO("mysql:host=$host;db_name=$dbname", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") );
$dbWG->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$dbWG->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$dbWG->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch(PDOException $e) {
$e->getMessage();
file_put_contents('/var/log/PDO_WG_errors.log', $e->getMessage(), FILE_APPEND);
}
?>
Don’t forget to use start and end with the php markers or the code will show up on your page. To keep the naming convention consistent with what I have been using, this file would be called wg_db_pdo.inc, since the original PEAR version of the files was called wg.db.inc, and the MySQL versions have been called wg.db.my.inc.
I used try-catch pattern so that I could find out if errors were happening while debugging, but it also is useful in production. Writing the errors to a file also prevents hackers from seeing errors on your site and giving them ideas about how to break in. To see if it was working, I changed the password to an invalid password and got this in the error log, so I know it works.
SQLSTATE[28000] [1045] Access denied for user 'username'@'localhost' (using password: YES)
I was getting this error in my normal php error logs.
Invalid catalog name: 1046 No database selected
until I realized that the full database name is required when accessing a table.
SELECT * FROM `database`.`table`
not
SELECT * FROM `table`
When setting up the database access there are three attributes that I generally want on the database. There are several ways that the data can be returned. I usually want it to be in an associative array so I set the ATTR_DEFAULT_FETCH_MODE to FETCH_ASSOC. The data is in an associative array indexed by column name. As shown in the sample code below, it is straightforward to access the data with this method. Sometimes it is useful to have the data indexed by column number and you can override the default when you make your query. All of the options are listed on php.net.
I’ve been exploring various options and the code might be useful to someone, so I’ve included it below.
<!DOCTYPE html>
<html lang="en">
<head>
<title>PDO Test</title>
</head>
<body>
<?php
echo "Available drivers: ";
print_r(PDO::getAvailableDrivers());
require_once('wg_db_pdo.inc');
// Don't use this construct. It works but gives an error on the next query
// 2014 Cannot execute queries while other unbuffered queries are active.
//$status = $dbWG->exec('SELECT * FROM `database`.`product`');
//echo "The result of the select statement was $status";
// Check to see if the MySQL driver is available
// http://php.net/manual/en/ref.pdo-mysql.php
if ($dbWG->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') {
$stmt = $dbWG->prepare('SELECT * FROM `database`.`product` LIMIT 10',
array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
$stmt->execute();
echo "<p>Connected to database</p>\n";
} else {
die("Failed to open database");
}
# using the shortcut ->query() method here since there are no variable
# values in the select statement.
$result = $dbWG->query('SELECT name, id, tagline FROM `database`.`product` LIMIT 10');
echo "<h3>Read from the result</h3>";
while($row = $result->fetch()) {
echo $row['id'] . " ";
echo $row['name'] . "— ";
echo $row['tagline'] . "<br />";
}
// Resetting the cursor position is not supported.
echo "<h3>Reading from the same result yields nothing</h3>";
echo "<p>";
while($row = $result->fetch()) {
echo $row['id'] . " ";
echo $row['name'];
echo $row['tagline'] . "<br />";
}
echo "</p>";
//If you want to iterate twice over the results, fetch to an array and iterate over this array:
$stmt = $dbWG->prepare('SELECT * FROM `database`.`product` LIMIT 10');
$stmt->execute();
echo "<h3>You can get the row count in MySQL after the statement is executed</h3>";
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';
$results = $stmt->fetchAll();
echo "<h3>First iteration</h3>";
foreach($results as $row) {
echo $row['name'] . "<br />";
}
echo "<h3>Second iteration</h3>";
foreach($results as $row) {
echo $row['name'] . "<br />";
}
// If you load the results into an array, you can get the row count by counting on the array.
echo "<h3>Get the number of rows from array</h3>";
$numberOfRows = count($results);
echo "The number of rows is $numberOfRows";
// Alternatively, you need to do two queries. One to count and one for the data
echo "<h3>Get number of rows using count(*)</h3>";
$numRows = $dbWG->query('SELECT count(*) FROM `database`.`product` LIMIT 10')->fetchColumn();
echo $numRows;
echo "<h3>Fetch as number: FETCH_NUM</h3>";
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
echo "$row[0] $row[1] <br />";
}
echo "<h3>Alternative Fetch as number: FETCH_NUM</h3>";
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_NUM);
foreach($result as $key=>$val) {
echo $key.' - '.$val[3].'<br />';
}
echo "<h3>Prepared Statement with Parameters</h3>";
$stmt = $dbWG->prepare("SELECT * FROM `database`.`product` WHERE id > :id AND name LIKE :name");
$product_id = 1;
$input_from_user = "Artic";
$name = "%" . $input_from_user . "%";
$stmt->bindParam(':id', $product_id, PDO::PARAM_INT);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
// Alternatively
$stmt->bindValue(':name', "%{$input_from_user}%", PDO::PARAM_STR);
$stmt->execute();
$results = $stmt->fetchAll();
foreach($results as $row) {
echo $row['name'] . "<br />";
}
// These examples use question mark placeholders
echo "<h3>Insert</h3>";
$stmt = $dbWG->prepare('INSERT INTO `database`.`product_log` (`product_id`) VALUES (?)');
for ($i = 0; $i < 10; $i++) {
$product_id = $i + 10;
$stmt->bindParam(1, $product_id);
$stmt->execute();
}
// Insert with Array and placeholders
// The product_log has a source field called src
echo "<h3>Insert with Array and Placeholders</h3>";
$values = array('page1.php', 'page2.php', 'page3.php', 'page4.php');
$source = '';
$stmt = $dbWG->prepare('INSERT INTO `database`.`product_log` (`src`) VALUES (:source)');
$stmt->bindParam(':source', $source, PDO::PARAM_STR);
foreach($values as $source) {
echo "The value to be inserted is $source<br />";
$stmt->execute();
}
$insertId = $dbWG->lastInsertId();
echo "The ID of the last row is $insertId";
echo "<h3>Delete the last two rows</h3>";
$stmt = $dbWG->prepare('DELETE FROM `database`.`product_log` WHERE id >= ? AND id <= ?');
$min = $insertId - 1;
$max = $insertId;
$stmt->bindParam(1, $min, PDO::PARAM_INT);
$stmt->bindParam(2, $max, PDO::PARAM_INT);
$stmt->execute();
$count = $stmt->rowCount();
echo "The number of rows deleted is $count";
# close the connection
$dbWG = null;
?>
</body>
For further reading I suggest hashPHP to start and the PDO Book at php.net.
