How to Query a MySQL Database in Plain PHP

• Download source - 24.2 KB

Introduction

Running the code starts with the select database page and then the other pages shown in the illustration. You can use the code "as-is," but the main purpose of this article is to show how to build a query tool. The PHP is just plain PHP without additional libraries. The code uses classes, cookies, HTML and CSS, SQL select statements, SQL injection (well, an attempt to prevent that) and encryption. All on a fairly basic level.

The article assumes you have seen some PHP, HTML, CSS and MySQL, at least from a distance. If not, Step-by-Step PHP Tutorials for Beginners is an excellent introduction. Or scan the W3schools website.

I am using PHP 7.1.22 with PHP’s built-in web server php.exe on a Windows 10 PC, and MySQL 8.0.12 (and MariaDB 10.3.28-MariaDB), but the code should work on MySQL 5.x databases. Obviously, you need select privilege on the tables and views of the database. In php.ini enable extension=php_pdo_mysql.dll.

Information on the tables, views and their fields is found in two MySQL tables:

select * from information_schema.tables
	where table_schema = <your_database> and table_type in ('BASE TABLE', 'VIEW')
		order by TABLE_TYPE, TABLE_NAME

select * from information_schema.columns
	where table_Schema = <your_database> and table_name = <your_table>
		order by ORDINAL_POSITION

(MySQL calls it a "schema", almost everybody else calls it a "database").

The Code

The code consists primarily of code for five pages:

• querymysql_select_db.php: page to select the database
• querymysql_select_tables.php: page to select a table or view
• querymysql_query.php: page to enter query parameters; some data types are not supported, mainly because it is not clear how to query and present them
• querymysql_browse.php: page to browse the list of resulting rows
• querymysql_detail.php: page to see all data of a specific row: this page is only available if the table has a primary key, so not on views

And some supporting stuff:

• querymysql_config.php: a config file included on all pages
• code for 4 supporting classes
• querymysql.css: the css file
• querymysql_selection.php: a page to change the sort and the fields displayed on the browse page
• querymysql_logout.php: a logout page

The four supporting classes are:

• querymysql_class_Settings.php: Settings class used to preserve data between pages
• querymysql_class_Db.php: Db class where all database interaction is concentrated
• querymysql_class_H.php: H class of various static functions
• querymysql_class_MyList.php: MyList class to handle lists of values.

Using the Code

Copy the source code to the same directory and load querymysql_select_db.php in your browser.

Notes

• On the query and browse page, you find buttons to select the fields to show on the browse page (default: all fields of the table) and the sort (default: the primary key fields in order of appearance in the table; none if no primary key). Clicking those buttons directs to the selection page querymysql_selection.php
• Not all SQL datatypes are supported: you cannot query on fields with unsupported datatype, and on the browse and detail page they are marked as not supported
• If on the query page you fill in more than one field, all conditions must be met in the retrieve (AND between the conditions)
• The browse page shows max 1000 rows (the MAXROWS setting in querymysql_config.php)
• on the browse page, max 30 characters of a field are shown (the MAXWIDTH setting in querymysql_config.php); hovering the mouse over the field shows the full content
• If there is no primary key (as is the case for at least all views), the detail page is not available
• the decimal point is . (point)
• Dates are presented (and queried!) in the SQL format as ccyy/mm/dd
• The TABLE_ROWS value shown on the select table page is not always exact and is not available for views
• I expect all fields of your own databases will have collation utf8mb4_unicode_ci. There may be query issues when fields are not.

Explanation of the Code

I’ll first explain the select database page in detail, along with the supporting code. After that I will highlight features of the other pages.

A long time ago, in my first job, the ideal was egoless programming: meaning that you couldn’t see from the code who wrote it. It was at a time when both the programming language and the application requirements were limited. I have tried to make the code as simple as possible (one page per step) and gave every page the same structure, but please realize this is only just one way to solve the requirements in PHP. Coding should be fun: if you have a better way, adapt the code to your own preferences.

Select Database Page

The purpose of this page is to set and save the parameters to connect to the database.

The page is implemented as two files: the main file querymysql_select_db.php shows the page, after submit the Processor class in querymysql_select_db_processor.php is used to process the input. All pages have this same structure, even if the Processor class only handles a page back (as in querymysql_detail_processor.php). Data we need to preserve are stored in the Sessions class.

Select Database Main Page

The first part of querymysql_select_db.php is:

// include the config file
require_once 'querymysql_config.php';;
require_once 'querymysql_select_db_Processor.php';

// instantiate the Settings class, which is the store for parameters
$Settings = new Settings();

// run the Processor if this page was submitted
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $Processor = new Processor($Settings);
}

// retrieve the previously entered data
$dataRowArray = array();
$dataRowArray['server'] = $Settings->get('server'); // empty string if not found
$dataRowArray['database'] = $Settings->get('database');
$dataRowArray['user'] = $Settings->get('user');
$dataRowArray['password'] = $Settings->get('password');

followed by the HTML to show the data. So here we need some explanation of the config file, the HTML, and of the Settings and Processor class in the above code. Let's start with the HTML.

HTML and CSS

The HTML starts with some more or less standard code, followed by four sections:

• header
• (a section with class) field section
• button section
• footer

We need sections because the field section and button section are presented side-by-side. And because it makes the code clearer! The properties of these sections are defined in the CSS file querymysql_css.css. I am not a CSS expert and found the settings by trial and error and a lot of help from the Internet.

Back to the HTML

The HTML part of the code contains a lot of PHP. It is a bit arbitrary where you write direct HTML -- maybe with some <?PHP my code ?> thrown in -- and where you use echo within PHP. A few remarks on this HTML:

• If an error (like connect failed) was detected in the processor class or in another page, the $errorMessage was set (and saved in Settings) and is now displayed in the header:
  HTML

  <p>.<b>
  	<?PHP echo $Settings->getAndClearError(); ?> 
  </b></p>

• Note the ShowCookies button at the end of the HTML code:
  HTML

  <div style="position:relative; text-align:center">
  	<input type="submit" <?PHP if (DEBUG === 'NO') {echo 'hidden';} ?>
  		class="smallbutton" name="ShowCookies" value="ShowCookies">
  </div>

DEBUG is set in the config file. If YES, this button is shown so you can check the content of the cookies.

The processor() class of select db

The code for the processor class is:

class Processor
{
	private $Settings;
	
	public function __construct(Settings $aSettings) 
	{
		// most Processor classes have more methods
		// using $this->Settings to be consistent
		$this->Settings = $aSettings;

		if (isset($_POST['ShowCookies'])) { // for debugging
			$this->Settings->showCookies();
			die;
		}

		if (isset($_POST['Clear'])) {
			$this->Settings->set('server', '');
			$this->Settings->set('database', '');
			$this->Settings->set('user', '');
			$this->Settings->set('password', '');
			H::redirect('querymysql_select_db.php');
		}

		// save the data
		$this->Settings->set('server', $_POST['server']);
		$this->Settings->set('database', $_POST['database']);
		$this->Settings->set('user', $_POST['user']);
		$this->Settings->set('password', $_POST['password']);

		if (isset($_POST['Go'])) { // not really any other option possible, but be consistent
			// try to connect; because "false", normal error message if fails
			$Db = new Db($this->Settings, false); 
			if ($Db->errorMessage !== '') { // always check $Db->errorMessage
				$this->Settings->set('errorMessage', $Db->errorMessage);
				H::redirect('querymysql_select_db.php');
			}
			$resultArray = $Db->getVersion(); // to show on next pages
			if ($Db->errorMessage !== '') {
				$this->Settings->set('errorMessage', $Db->errorMessage);
				H::redirect('querymysql_select_db.php');
			}
			$version = $resultArray[0]['version'];
			$this->Settings->set('version', $version);

			$this->Settings->clear(); // clear stuff from previous db
			H::redirect('querymysql_select_table.php');
		}

		H::fatalError('unhandled POST');
	}
}

This class saves the input in Settings and then tries to connect to the database, using the Db class. If the connect succeeds, we retrieve the version of the database and go to the next select table page, but first we clear all cookies set in case you queried another database first. In the case of an error, we redirect to the Select db page.

We also see the H class in this code, so now we have seen supporting classes Settings, Db and H. I'll explain them after some remarks on the config file.

The config File

The config file querymysql_config.php is included in every page and contains application wide constants and includes. Note that DEBUG is set to 'NO', you may want to change that to 'YES':

//	DEBUG must exist. if YES: 
//		button to show cookies on select db page
//		shows trace on fatalError

	define('DEBUG', 'NO');
//	define('DEBUG', 'YES');

The Supporting Classes

Settings Class

Cookies are used to preserve data between pages. From the manual: "Once the cookies have been set, they can be accessed on the next page load". The $settingsArray is used to have access to the set parameters within the same page, before the next page load.

The cookie names have prefix "querymysql_": there may be other applications setting cookies (especially on localhost). If you query a table, go to a different table and go back to the first table, it can be handy if you see the previously used query parameters in that first table. In other words, should we store query parameters per table (and clear all when changing db) or set just one set for the current table (and clear them when changing table)? I used the first approach, so query parameters also need the table name as prefix (if this is not clear, it will become clear when we see the query page).

The constructor reads all cookies with prefix querymysql_ into the $settingsArray:

public function __construct()
{
    foreach ($_COOKIE as $name => $value) {
        if (H::aStartsWithB($name, MYAPP)) {
            setcookie($name, $value, time() + TIMEOUT, '/'); // refresh the cookie's timeout

            // the settingsArray keys are without the MYAPP.'_' prefix
            $name = H::removeLeftAfromB(MYAPP.'_', $name);
            $this->settingsArray[$name] = H::decrypt($value);
        }
    }
}

The cookie values are encrypted. The reason is explained in the section SQL injection and encryption below. get() is used to retrieve a parameter from this array and set() to change, add or remove a parameter from this array AND to update the cookie with that parameter name. It is important to set the cookie right away. Otherwise we would have to use something like a save() at the end of every page. Which I would forget and would not work if you go to a previous page with the left arrow of your browser.

When getting a parameter, it will return an empty string when not found. When you set a parameter to empty string, it is removed from the $settingsArray and the cookie with that name is removed.

Db class

All database interaction is concentrated in this class. A database retrieve returns zero or more numbered rows of data, but things can go wrong, in which case $errorMessage is set. So after every method call, we must check if this $errorMessage is not empty space.

The constructor of the class does the connect to the database. When called from the Select db page, an error (usually "wrong password") is a normal situation, and we redirect to that page, where we show the error message (the $Settings->getAndClearError() mentioned above). When called from another page, an error is pretty fatal: the database night not be available anymore, but the most likely reason is the cookies expired, so the connect parameters are not available anymore. So in this specific case, we assume that is the error and redirect to the Select db page.

Besides checking the connection, the Processor of the select database page also does the first retrieve: the version of the database via $Db->getVersion().

public function getVersion() : array
{
    $sql = "select version() as version";
    return $this->retrieve($sql);
}

This function uses the private function retrieve() for the real work:

private function retrieve(string $aSql, array $aParameterArray = null) : array
{
    try {
        $stmt = $this->conn->prepare($aSql);
        // execute, binding the values of $this->whereParmsArray[fieldName]
        // to the :fieldNames in the select clause
        $stmt->execute($aParameterArray);
        $tableArray = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
        $this->errorMessage = 'Retrieve failed: ' . $e->getMessage().'; SQL: '.$aSql;
        $tableArray = array();
    }
    return $tableArray;
}

Note how the errorMessage is set in the catch. For this demonstration code, the complete error message and the sql is shown in the error message, in a more realistic scenario you would log the error message and sql, and return a more neutral message. Further note that retrieve() returns zero or more numbered rows, each row an associative array of field names and values (there are more fetch options, but this is the most db-like return of a retrieve).

In getVersion() there are no parameters. For consistency, we always use a prepared statement, see the section at the end of this article on sql injection.

The function getTables() retrieves the list of tables and views and getTableFields() retrieves the list of fields of a table. More interesting are getTableData() together with getWhere(): they are used on the browse page and discussed there. getRowData() is used on the detail page to retrieve a single row from the given table, with the given primary key.

Note that instead of the separate $errorMessage, we could return either an array or an error message. I make too many mistakes if a function can return two totally different things, but admit that I sometimes forget to check the error message.

Helper class

The helper class in querymysql_class_H.php contains static functions. First the redirect() to another page, then show(), which is not used, but will be handy if you change the code.

fatalError() is used when there is an error that (1) makes further processing impossible and (2) should be solved in the test phase. If not, you want to know when it happens. When DEBUG = 'YES', you also see the trace. In a more advanced version, you would log the error and trace and show the user a more neutral message (like "sorry, try again"). The H class also contains some functions to manipulate strings, using fatalError() to detect wrong parameter values.

Further in this class, the encrypt() and decrypt() functions, then some string functions to make string handling easier and finally specific application functions. getPhpType() transforms the MySQL data type to a "PHP data type": for the PHP code of this article it makes no difference if the MySQL data type is VARCHAR or TEXT. In this article, not all data types are supported, to limit the amount of code.

getPrimaryWhere(), is used on the browse page. getSelection() is used on the selection page. setSelectionDefault() is used on the query page to initialize the sort and field list and on the selection page to reset to default.

MyList class

The list of fields in a SQL select statement is a comma separated string of field names. In this application, filling the list is done in an array. When saving the list to and retrieving it from Settings, a string is used. When changing the list, an array is used. So this "list" should sometimes behave as a (comma separated) string, sometimes as an array. That is precisely what the MyList class offers.

This class is used whenever there is a list. Sometimes it is an overkill, but the more the class is used, the more chance errors in the code of the class pop up. The add(), remove(), up() and down() functions are used in the selection page.

Select Table Page

The purpose of this page is to select and save a table name and table type for the query page.

The first part of the code is:

require_once 'querymysql_config.php';;
require_once 'querymysql_select_table_Processor.php';

$Settings = new Settings();
$currentTable = $Settings->get('currentTable'); // filled if set previously on this page

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $Processor = new Processor($Settings);
}

// get the list of tables
$Db = new Db($Settings);
$tableArray = $Db->getTables();
$errorMessage = $Db->errorMessage;

The $currentTable is used to show a * on the row with that table value. The $Db->getTables() retrieves the tables and views, with the first use of parameter binding (:database):

public function getTables() : array
{
    $sql = "select TABLE_TYPE, TABLE_NAME, TABLE_ROWS from information_schema.tables
        where table_Schema = :database and table_type in ('BASE TABLE', 'VIEW')
            order by TABLE_TYPE, TABLE_NAME";
    $parameterArray = array(':database' => $this->Settings->get('database'));
    return $this->retrieve($sql, $parameterArray);
}

The code goes on to initialize the page in HTML, then loops through the $tableArray to show one row per table.

There are two tricks in the HTML you may not have seen before. The first is the scroll to location: If you select a table on this page, go to the query page and later you go back to the select table page, this page scrolls to the last selected table. The code to do this is:

echo '<div id="'.$fieldArray['TABLE_NAME'].'"></div>';

in this page AND add the table name (preceded by #) to the url when going back from the query page to this page. So if you come back to this page from the query page, this page scrolls to the last selected table name and shows a * on that row.

The second trick is hiding information in the select buttons:

echo '<input type="submit" class="smallbutton"';
if ($fieldArray['TABLE_TYPE'] === 'VIEW') {
    echo ' name="view'.H::encrypt($fieldArray['TABLE_NAME']);
} else {
    echo ' name="table'.H::encrypt($fieldArray['TABLE_NAME']);
}
echo '" value="Select">';

The table name needs to be encrypted. The reason is explained in the section SQL injection and encryption below.

The processor splits the type and the name and saves them in the Settings class, so the query page know from which table to retrieve:

class Processor
{
	private $Settings;
	
	public function __construct(Settings $aSettings) 
	{
		$this->Settings = $aSettings;

		foreach ($_POST as $key => $dummy) { // $key is the button name
			
			if (mb_substr($key, 0, 4) === 'view') {
				$tableNameEncrtpted = H::removeLeftAfromB('view', $key);
				$this->Settings->set('currentType', 'view');
			} else {
				$tableNameEncrtpted = H::removeLeftAfromB('table', $key);
				$this->Settings->set('currentType', 'table');
			}
			$tableName = H::decrypt($tableNameEncrtpted);
			$this->Settings->set('currentTable', $tableName);
			H::redirect('querymysql_query.php');
		}
	}
}

We don't know which Select button was clicked, so we don't know the full name of the button. That is the reason for the foreach. Only the clicked button is processed here.

Query Page

The purpose of this page is to input and save the data for the WHERE clause of the browse page.

The where clause consists of one or more conditions separated by AND (OR is not supported). A condition consists of a field name, an operator and a value (a condition such as fielda in (b, c, d) is not supported in this version).

So we retrieve the list all fields via $Db->getTableFields($currentTable) and then in the HTML present a row for each field, with name, operator and value.

The function setOperator() sets the supported operators, for this demonstration project just a few possibilities. As mentioned above, not all data types are supported, if not, you see the text "not supported".

The retrieve of data fitting the query parameters is done on the browse page.

In the Processor, the operator and value are saved for each field. Preceded by the table name, to make the query parameters specific per table :

foreach ($this->fieldArray as $fieldNumber => $valueArray) {

    $fieldName = $valueArray['COLUMN_NAME'];

    if (H::getPhpType($valueArray['DATA_TYPE']) == 'not supported') {
        continue; // nothing to save
    }

    if ($_POST[$fieldName.'_value'] != '') {
        $this->Settings->set($currentTable.'_'.$fieldName.'_operator',
                        $_POST[$fieldName.'_operator']);
        $this->Settings->set($currentTable.'_'.$fieldName.'_value',
                        $_POST[$fieldName.'_value']);
        // is value valid?
        $errorMessage = $this->checkType($fieldName, $valueArray['DATA_TYPE'],
                       $_POST[$fieldName.'_value']);
        if ($errorMessage != '') {
            $this->Settings->set('errorMessage', $errorMessage);
            H::redirect('querymysql_query.php');
        }
    }
}

On this page also the Sort and Choose Fields buttons. If you click one of them, you go to the selection page, discussed below. Sort is used to set the ORDER BY ... and Choose Fields to set the list of fields (and their order of appearance) in SELECT ... FROM. If not clicked, the default is saved in Settings, all fields for the list of fields and the primary key for the sort (or empty sort if no primary key). This done in the Processor, before going to the browse page:

// set the fields and sort if not set
if ($this->Settings->get($aTable.'_fields') === '') {
    H::setSelectionDefault($this->Settings, $aFieldArray, $aTable, 'fields');
}
if ($this->Settings->get($aTable.'_sort') === '') {
    H::setSelectionDefault($this->Settings, $aFieldArray, $aTable, 'sort');
}
H::redirect('querymysql_browse.php');

Browse Page

The purpose of this page is to show the rows according to the query parameters. If the table has a primary key, every row has a select button.

The page calls $Db->getTableData(), where the real work is done and the returned array is shown on this page. If there is no primary key, this is the last page in the sequence. If there is a primary key, every line gets a Select button. The HTML looks a lot like the HTML of the select table page. While on the select table page we used the table name for the anchor link and in the name of the Select buttons, we now use the where clause for the primary keys. Again, we need encryption of this where clause in the Select button. When you are going from the detail to the browse, you see the where sql for the primary key in the url. There is no risk of sql injection here, this where is only used to scroll to the right line in the browse.

In the presentation of the rows, only MAXWIDTH characters (a setting in the config file) are shown. If you hover the mouse over the field, the full content is shown, using <abbr>.

$Db->getTableData

getTableData() is the method to retrieve the actual data, based on the query parameters input by the user. Here we use a prepared statement, more on that in the section on sql injection below. The retrieve of the data looks like:

select <field list> from <my table> where <conditions> order by <sort list> limit <n>

The <field list> is either the default (all fields) or set on the selection page. <my table> is the $currentTable, set on the select table page. The <sort list> is either the default (primary key fields or empty) or set on the selection page. <conditions> is the interesting part.

We first count the number of rows to be retrieved. We don't want to retrieve 10.000 complete rows. If we use the limit <n> (MAXROWS in the config file), we still want to know how many rows there actually are. The code for the count is

$wherePlus = ($this->getWhere($this->Settings, $aFieldArray) !== '')
	? ' where '.$this->getWhere($this->Settings, $aFieldArray) // where clause including 'where'
	: ''; // no where clause

// first get the number of rows 
$sql = 'select count(*) as rowcount from '.$aTable.$wherePlus;
$tableArray = $this->retrieve($sql, $this->whereParmsArray);
if ($this->errorMessage !== '') {
	return array();
}
$rowCount = $tableArray[0]['rowcount'];
if ($rowCount === 0) {
	$this->errorMessage = 'nothing found';
	return array();
}

and on the first line we find the call to getWhere(), which generates the where clause, excluding the actual "where ":

private function getWhere(Settings $aSettings, array $aFieldArray) : string
{
	$currentTable = $aSettings->get('currentTable');
	$where = ''; // the where for the select
	$this->displayWhere = ''; // presentable where to show on browse page
		
	foreach ($aFieldArray as $fieldNumber => $valueArray) {
		$fieldName = $valueArray['COLUMN_NAME'];
		if ($aSettings->get($currentTable.'_'.$fieldName.'_value') == '') { 
			// value not set, so not relevant for where clause
			continue;
		}
		$operator = $aSettings->get($currentTable.'_'.$fieldName.'_operator');
		$value = $aSettings->get($currentTable.'_'.$fieldName.'_value');

		$phpType = H::getPhpType($valueArray['DATA_TYPE']);

		$operatorsList = new MyList('=, <, >');
			
		if ($phpType === 'string') {

			// mb_strtolower() and lower() in the following code help 
			// if collation of table not utf8mb4_unicode_ci 
			// but probably do not solve every collation issue
				
			if ($operatorsList->isInList($operator)) {
				// :fieldName goes in the where clause, 
				// the Value goes into $this->whereParmsArray
				$this->whereParmsArray[$fieldName] = mb_strtolower($value);
				$where = $where.' AND lower('.$fieldName.') '.$operator.' :'.$fieldName;
				$this->displayWhere = $this->displayWhere
					.' AND '.$fieldName.' '.$operator.' '.$value;
				continue;
			}

			if ($operator === 'starts with') {
				$this->whereParmsArray[$fieldName] = mb_strtolower($value.'%');
				$where = $where.' AND lower('.$fieldName.') like :'.$fieldName;
				$this->displayWhere = $this->displayWhere
					.' AND '.$fieldName.' like '.$value.'%';
				continue;
			}

			if ($operator === 'contains') {
				$this->whereParmsArray[$fieldName] = mb_strtolower('%'.$value.'%');
				$where = $where.' AND lower('.$fieldName.') like :'.$fieldName;
				$this->displayWhere = $this->displayWhere
					.' AND '.$fieldName.' like %'.$value.'%';
				continue;
			}
		}
			
		if ($phpType == 'numeric') {

			if ($operatorsList->isInList($operator)) {
				$this->whereParmsArray[$fieldName] = $value;
				$where = $where.' AND '.$fieldName.' '.$operator.' :'.$fieldName;
				$this->displayWhere = $this->displayWhere
					.' AND '.$fieldName.' '.$operator.' '.$value;
				continue;
			}
		}

		if ($phpType == 'date') {

			if ($operatorsList->isInList($operator)) {
				$this->whereParmsArray[$fieldName] = $value;
				$where = $where.' AND '.$fieldName.' '.$operator.' :'.$fieldName;
				$this->displayWhere = $this->displayWhere
					.' AND '.$fieldName.' '.$operator.' '.$value;
				continue;
			}
		}			
	}
	if ($where == '') {
		return '';
	}
	// remove the first AND
	$where = H::removeLeftAfromB(' AND ', $where);
	$this->displayWhere = H::removeLeftAfromB(' AND ', $this->displayWhere);
	return $where;
}

The function returns the where clause, but also fills $this->whereParmsArray! The where clause string contains elements like:

.... AND thisField = :thisField AND ....

and the whereParmsArray contains:

whereParmsArray['thisField'] = <actual value>

So the :-fields in the where string match the keys in the whereParmsArray. Which makes binding them in the execute work. After retrieving the number of rows, we retrieve the data, limiting to MAXROWS (set in querymysql_config.php, you can change the value there). The second part of getTableData() is:

// now retrieve the data
$sql = 'select '.$this->Settings->get($aTable.'_fields')
    .' from '.$aTable.$wherePlus;
if ($this->Settings->get($aTable.'_sort') !== '') { // no sort if no primary key
    $sql = $sql.' order by '.$this->Settings->get($aTable.'_sort');
}
if ($rowCount > MAXROWS) { // MAXROWS is set in the config file
    $this->rowCountText = 'showing '.MAXROWS.' rows of '.$rowCount.' ';
    $sql = $sql.' limit '.MAXROWS;
} else {
    $this->rowCountText = 'selected '.$rowCount.' rows ';
}
return $this->retrieve($sql, $this->whereParmsArray);

Detail Page

This purpose of this page is to show all fields of the row selected on the browse page.

The browse page saved the where clause for the primary key in Settings, so retrieving the data is just:

// retrieve the primary key SQL
$primaryKeySql = $Settings->get('primaryKeySql');
$PrimaryKeySqlCompressed = str_replace(' ', '', $primaryKeySql); // anchor link
                                                                // going back to browse
// and get the data
$rowArray = $Db->getRowData($currentTable, $primaryKeySql);
$errorMessage = $Db->errorMessage;

This page always shows all fields, even if you limited the fields shown in the browse with the Choose Fields button. Strings longer then 30 are shown using <textarea>.

Selection Page

The purpose of this page is to change and save the sort and the fields to be displayed in the browse page. If the table has a primary key, you cannot remove the fields of the primary key from the field list: they are needed to create the "where" for the primary key set in the Select button.

This page can be reached from the query page and the browse page.

Logout Page

The purpose of this page is to clear all cookies set by this application.

SQL Injection and Encryption

Manipulating SQL in Input Fields

Most examples of SQL injection describe manipulating SQL in fields input by the user. The common opinion on the internet seems to be that binding parameters is adequate to prevent SQL injection (at least for the more common character sets and recent versions of MySQL). Consider getTables() on the select table page:

public function getTables() : array
{
    $sql = "select TABLE_TYPE, TABLE_NAME, TABLE_ROWS from information_schema.tables
        where table_Schema = :database and table_type in ('BASE TABLE', 'VIEW')
            order by TABLE_TYPE, TABLE_NAME";
    $parameterArray = array(':database' => $this->Settings->get('database'));
    return $this->retrieve($sql, $parameterArray);
}

Here, the select uses :database and database name is set in the $parameterArray. The database name was input by the user, so the risk-of-sql-injection-alert should fire. In this case, I cannot think of a way to inject risky SQL, but most hackers are far smarter than I am, so better safe than sorry. So we use parameter binding in all retrieves.

Changing the HTML in the Browser

Let us suppose that you exclude some tables in the SQL to retrieve the tables, for example, the table "users". A malicious user might guess that there is a "users" table and try to get access to that table in two ways. On the select table page, the table name is in the HTML of the Select buttons. So the first way is to switch to the HTML and change one of the table names to "users". This is the reason the table name is encrypted in the Select button.

The same issue occurs on the browse page, where the Select button contains the SQL for the where clause on primary key.

Changing the Cookie

The select table page saves the table name in the cookies. When on the query page, that same user could change the value of the table name cookie (using a simple PHP script) to "users" and then refresh the page. This is the reason such a cookie needs encryption and to be safe, all cookies are encrypted.

Changing a name to something else is only one example I could think of. The table name is especially vulnerable as on the browse and detail page, you cannot bind it, binding only works on values.

Next Steps

For maintainability, adapt the code to your own style; better yet, use elements of this article to create your own code.

If you have control over the database, you can make a lot of improvements. For example:

• Add more query options, like "between" or "is one of" a list of values
• The detail page and default sort are not available for views, because there is no primary key to anchor on. But a view usually has a (combination of) field(s) which is unique. You might add a table specifying those key fields for views.
• Cookies may not be the best way to store what easily becomes quite a lot of data. Maybe better to store parameters per session in the database.
• Add an update option for non-key fields on the detail page (there may be database constraints on the data). Adding insert and delete is not so easy.
• Add support for more SQL data types. If you know that a BLOB field always contains a .jpg image, you could show the image on the detail page.
• Adding support for another DBMS is easy, as long as the DBMS is supported by PDO.

Conclusion

I hope I have convinced you that building such a query tool is not really difficult. The code gives you a complete tool, but has limited use. It makes more sense to incorporate parts of this code into your own applications.

An unexpected benefit from writing this article is that it made me reconsider some of my programming habits: so this process improved my other code. So if you want to improve your code, write an article!

History

• 18^th February, 2022: Initial version