Well Golly – Page 18

A Quick Note on Migrating to PDO

Migrating from mySQLi to PDO was really painless. I changed the database access as described previously. Then using the same query as previously, I added the database name `website` and then used the following code:


$qry = "SELECT * FROM product;
$res= $dbWG->query($qry);

if (!$res)
    die(mysqli_error($dbWG));

$numRows = mysqli_num_rows($res);

for ($i = 0; $i < $numRows; $i++) {
    $row   = $res->fetch_array();


$qry = "SELECT * FROM `website`.`product`;
$stmt = $dbWG->prepare($qry);
$stmt->execute();

$results = $stmt->fetchAll();
foreach($results as $row) {

One of the reasons to use PDO is that it has prepared statements that will automatically escape inputs so that injection attacks are mitigated.

It is simple to change the your code if you are using input from the user to construct the query.
If you do a simple convert, like above, to you end up with something like this:


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = $categoryID";
  
$stmt = $dbWG->prepare($qry);
$stmt->execute();

$results = $stmt->fetch();
categoryName = $results['name'];

A simple change in the query and a bind statement lets PHP automatically escape the user input.


// get the current category name
$qry  = "SELECT name FROM `website`.`book_category` ";
$qry .= "WHERE id = :categoryID";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':categoryID', $categoryID);
$stmt->execute();

$results = $stmt->fetch();
$categoryName = $results['name'];

Pure.css Miscellaneous Stuff

I’m exploring Pure.css for responsive design and I am running across things that I’ve never seen before. So I don’t have to look them up again, this post is a collection of them and some general observations about the css.

For some reason the GitHub repository for Pure.css doesn’t contain the actual base CSS. However, you can find it at the Yahoo CDN by removing the -min portion of the minified CSS link.
http://yui.yahooapis.com/pure/0.6.0/pure.css

The first 521 lines are from Normalize.css and some additions from the YUI team that basically clean up browser differences so that the Pure code will work the same on all browsers—even very old ones and obscure ones.

IE Hacks
If you browse through the code, you might notice lots of lines starting with asterisks. e.g.


    display: inline-block;
    *display: inline;

    width: 4.1667%;
    *width: 4.1357%;

Once again, StackOverflow provides an explanation.

It is a syntax error. So in CSS, it makes the property name invalid and stops it being parsed. Thanks to bugs in browsers, it is sometimes ignored.

[Therefore] the second declaration will be applied by IE7 and older (thus overriding the first declaration), while other browsers will ignore it and continue applying the first declaration instead.

While I was looking this up, I also found another IE hack.

\9 is a “CSS hack” specific to Internet Explorer 7, 8, & 9.

This simply means that the one specific line of CSS ending with a \9; in place of the ; is only valid in IE 7, 8, & 9.

width: 500px\9; means that a width of 500 pixels (same result as width: 500px;) will only be applied while using IE 7, 8, & 9.

All other browsers will ignore width: 500px\9; entirely, and therefore not apply width: 500px; to the element at all.

It turns out that \0 can be used for IE 10.

:not

Pseudo-class :not
The :not pseudo-class represents an element that is not represented by its argument.

There aren’t many occasions where this is used in the file. The major one is to give padding, display, and border parameters when the a form is not one of the 14 form types listed in the previous block of css. e.g. .pure-form input:not([type]) {

Attribute Wildcard Selector


.pure-g [class *= "pure-u"] {
    font-family: sans-serif;
}

The *= is an attribute wildcard selector. In the example above, it looks for any child element under .pure-g that has a class that contains pure-u. Since Pure Grids consist of two types of classes: the grid class (pure-g) and within the grid class the unit classes (pure-u or pure-u-*) it basically resets all of the unit classes to use sans-serf as the default.

From the documentation, suppose you want to change the font to a serif font. Use this code:


html, button, input, select, texture, .pure-g [class *= "pure-u"] {
    font-family: Georgia, Times, "Times New Roman", serif;
}

Media Queries
The responsive part of responsive design relies on media queries. In the past, there were only two that you had to worry about—print and screen. With the advent of smartphones and tablets, you need to be able to adapt the content to the screen size in ways that were not necessary before. Media queries can be used to check the width and height of the viewport, the width and height of the device, the device orientation, and the device resolution, and other properties that you might need for your design. A huge list can be found at W3Schools. Pure.css only uses the screen media type along with the screen width expressed in ems to provide styling for five sizes. You refer to the sizes using suffixes on the pure unit styles. No suffix is for screens less than 35.5 ems (568 pixels), -sm is for the next size up, followed by -md. -lg and -xl are for the largest screens.

As with the css for the grid, you can find it at the Yahoo CDN by removing the -min portion of the minified CSS link.
http://yui.yahooapis.com/pure/0.6.0/grids-responsive.css

As they explain on their site. Using ems for media queries is much better for people who set their browser default to a larger size and for users who zoom in to the content.

Font size
Pure uses the default font size for the browser for all of the elements. Combined with the media query above, this means that the font size will scale with the viewport and zoom level so you don’t need to worry about it. If you do want to make the sizes vary from the default, e.g. possibly with captions or super-emphasis, then use percentages to increase the size.

One interesting font definition that I had not seen before is assigning the font size and line height at the some time. This code makes italic 85% of the normal size for the element but keeps the line height at 1 em so that it doesn’t interrupt the flow.


font: italic 85%/1 arial, sans-serif;

Using the PDO MySQL extension

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'] . "&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

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.

Browser Cookies with PHP and JavaScript

I have a game that I am in the process of updating. Originally it passed scores in with the URL, but I decided to update it so that the scores are not visible. My first thought was to use session variables, but since I want to change the scores in response to calculations that are done in JavaScript, that isn’t feasible. A better way is to use cookies. When I first enter a page, I set or get the cookies with PHP. I want the game levels to persist from visit to visit but the scores are only kept for the session.


// Initialize the cookies with PHP. Keep the group and level around but make the scoring session cookies
// If the cookie is set by the code, it is not available until the next page load
$cookie_name = "VPA_group";
$cookie_value = "MW1";
if( !isset($_COOKIE[$cookie_name]) ) {
    setcookie($cookie_name,$cookie_value,$cookie_time,$cookie_path,$cookie_domain,$cookie_secure,$cookie_httponly);
}
$group  = isset($_COOKIE["VPA_group"])  ? $_COOKIE["VPA_group"]   : $cookie_value;

$cookie_name = "VPA_WFF";
$cookie_value = "Wide";
if( !isset($_COOKIE[$cookie_name]) ) {
    setcookie($cookie_name,$cookie_value,$cookie_time,$cookie_path,$cookie_domain,$cookie_secure,$cookie_httponly);
}
$WFF    = isset($_COOKIE["VPA_WFF"])    ? $_COOKIE["VPA_WFF"]     : $cookie_value;

$cookie_name = "VPA_cor";
$cookie_value = 0;
if( !isset($_COOKIE[$cookie_name]) ) {
    setcookie($cookie_name,$cookie_value);
}
$cor    = isset($_COOKIE["VPA_cor"])    ? $_COOKIE["VPA_cor"]     : $cookie_value;

$cookie_name = "VPA_inc";
$cookie_value = 0;
if( !isset($_COOKIE[$cookie_name]) ) {
    setcookie($cookie_name,$cookie_value);
}
$inc    = isset($_COOKIE["VPA_inc"])    ? $_COOKIE["VPA_inc"]     : $cookie_value;

$cookie_name = "VPA_screen";
$cookie_value = 0;
if( !isset($_COOKIE[$cookie_name]) ) {
 setcookie($cookie_name,$cookie_value);
}
$screen = isset($_COOKIE["VPA_screen"]) ? $_COOKIE["VPA_screen"]  : $cookie_value;

Once I have the values, I need to let JavaScript know what they are.


<script>
    <?php
        echo "var cor = $cor;\n";
        echo "var inc = $inc;\n";
        echo "var screen = $screen;\n";

        echo "var group = \"$group\";\n";
        echo "var WFF = \"$WFF\";\n";
        echo "var cookie_days = \"$cookie_days\";\n";
    ?>
</script>

Then I can use JavaScript to change them, as I described in the previous post.


function goNext() {
    setCookie('VPA_cor',cor,'N');
    setCookie('VPA_inc',inc,'N');
    setCookie('VPA_screen',screen,'N');
    window.location="ThisGame.php";
}

function changeLevel(cookieName, cookieValue) {
    cor = 0;
    inc = 0;
    screen = 0;
    setCookie('VPA_cor',cor,'N');
    setCookie('VPA_inc',inc,'N');
    setCookie('VPA_screen',screen,'N');
    setCookie(cookieName, cookieValue, 'Y');
}

function setCookie(cname, cvalue, set_time) {
    var expires = '';
    if ( set_time == 'Y' ) {
        var d = new Date();
        d.setTime(d.getTime() + (cookie_days*24*60*60*1000));
        expires = "expires="+d.toUTCString();
    }
    //alert('cname ' + cname + 'group ' + cvalue + 'time ' + expires);
    document.cookie = cname + "=" + cvalue + "; " + expires;
}
</script>