## Archive for the ‘PHP’ Category

### Using the PDO MySQL extension

Sunday, April 3rd, 2016

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');

while($row =$result->fetch()) {
echo $row['id'] . " "; echo$row['name'] . "&mdash;   ";
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. ### PHP include files Sunday, April 3rd, 2016 When you install PHP, the installation process puts a file, php5.conf, in the mods-available directory of Apache. This file tells Apache to process files ending in php instead of displaying them directly. So the end-user never sees the code that is in these files, they just see the final result of the programming. (Actually, it is more general than this. The default installation also lets you use files ending in php3, php4, php5, pht, and phtml. In practice, I don’t think anyone uses anything but php.) One of the nice things about php is that you can create include files for things like headers, footers, and menus. Rather than duplicating the same information in each file on your website, you create one file and every page has the same setup. A common practice is to name these with the suffix inc. However, if you do that, they are viewable by anyone who guesses the name. If you have access to the apache2.conf file, you can add these lines to deny access to the files.  # We don't want people to see .inc files <Files ~ "\.inc$">
Order allow,deny
Deny from all
</Files>

Alternatively, you can put your include files in a directory and deny access to the directory. In your document root, create a directory called include.php and then add a file called .htaccess that includes the following lines:


Order deny, allow
Deny from all

If the .htaccess method doesn’t work with your configuration, you can always add .php to your include files.

I am a little paranoid about files that contain database passwords. I have several dozen databases and for both organization purposes and security I put them in a directory that is outside of the document root. The default configuration of Apache does not allow access to files outside the document root so no one should be able to see them. The php processor can see them so they are available to your web pages.

To access them, use the full path or add a line to your /php5/apache2/php.ini file to tell Apache where to look for include files. Mine looks something like this:


include_path = ".:/usr/share/php5:/usr/share/php:./include.php:..:../include.php:../../include.php:../../../include.php:/srv/include.php"

The first two paths are the default php paths. Then it looks for files in include.php directories located in the current path and several paths up the chain. The last path is outside the document root and is where I put my sensitive information and things that are common to all sites on the machine e.g sidebar ads, styles, nav menus, etc. You can name these directories anything you want. Mine are called include.php because that’s how they’ve been since they were set up in 1998.