PHP and sqlite page 2

Continued from PHP and sqlite page 1

Query Database

  • Query the Database
  • Two parts – the query part and the results from the query.
    • query.html
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>query</title>
</head>
<body>
<h1>Query the Database</h1>
<p>Build the query.&nbsp; It will look like <i>Select What
&lt;content of what&gt; from Table&nbsp; where &lt;content of
where&gt;</i><br>
 </p>
<p>Fields are ID, Name, Image, Description<br>
</p>

<form method="post" action=result.php>
<h2>Select
<br>Fields &nbsp;&nbsp; <input name="what" type="text">
<br>From&nbsp;&nbsp;&nbsp;&nbsp; Minerals
<br>Where&nbsp; <input name="where" type="text">;<br> 
<input type="submit"></h2>
</form>

</body>
</html>
  • result.php
<?php

class MyDB extends SQLite3 {
  function __construct($dbname) {
    $this->open($dbname);
  }
}
// Open the DB
$db = new MyDB('minerals');

// Pick up the form fariables
$what = $_POST['what'];

// Look for anything fishy.  Punt if there is.
if (preg_match('/;|"|\'|insert|drop|join|alter|delete|select|update|where/i', $what)) {
  echo "<html><head><meta http-equiv=\"refresh\" content=\"0;URL='query.html'\" />";
  exit;
}

// Check if there should be a where clause.
if ($_POST['where'] != "") {
  $where = $_POST['where'];

// Check the where clause too:
if (preg_match('/;|"|insert|drop|join|alter|delete|select|update|where/i', $where)) {
  echo "<html><head><meta http-equiv=\"refresh\" content=\"0;URL='query.html'\" />";
  exit;
}

  $whereClause = "where ". $where;
} else {
  $whereClause = "";
}
// Build the query
$sql = "select $what from Minerals $whereClause";

// Execute the query
$result = $db->query($sql);

// Is there a result?
if ($result) {

  echo "<table border=\"2px\"><tr>";

// Get and display the field names for the result as a table
  $columns = $result->numColumns();
  for($i = 0; $i < $columns; $i++) {
    $fields[$i] = $result->columnName($i);
    echo "<td><b><center>$fields[$i]<center><b></td>";
  }
  echo "</tr>";
  
// Fetch rows of the result indexed by field name and display
  while($row = $result->fetchArray(SQLITE3_ASSOC) ) {
    $outval = "";
    echo "<tr>";
    foreach($fields as $field ) {
      if($field == "Image") {
        $outval = $outval .  "<td><img src=".$row[$field]."></td>";
      } else {
        $outval = $outval . "<td>$row[$field]</td>";
      }
    }
    echo $outval;
    echo "</tr>";
  }
  echo "</table>";
} else {
  echo "No results!";
}
?>

Things to note about what I have done to the  PHP Config:

  • Display Errors

    • Centos:  /etc/php.ini
    • Raspbian /etc/php5/apache2/php.ini
      • Default Value: On
        ; Development Value: On
        ; Production Value: Off
        ; http://php.net/display-errors
        display_errors = On
    • I turn display_errors on so that the errors are displayed on
      the web page.  As you can imagine a bad idea in a
      production machine.  Leave the errors in the log file.

  • Pretty Printing

    • Centos:
      • /etc/httpd/conf.d/php.conf
        • #
          # Uncomment the following line to allow PHP to
          pretty-print .phps
          # files as PHP source code:
          #
          AddType application/x-httpd-php-source .phps
    • Raspbian:
      • /etc/apache2/mods-enabled/php5.conf
        • FilesMatch”.+\.phps$”>
          SetHandler application/x-httpd-php-source
          # Deny access to raw php sources by default
          # To re-enable it’s recommended to enable access to the files
          # only in specific virtual host or directory
          ###DR #Require all denied

    • Again, this is a security issue.  You don’t want to
      leave .phps files laying about, or allow this feature to be
      enabled on production (facing the Internet) servers.