PHP and sqlite page 2 – Victoria PiMakers And Others

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.

Query Database

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

Display Errors

Pretty Printing