Rev 7 | Rev 16 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
Copyright (c) 2021, Daily Data, Inc. Redistribution and use in
source and binary forms, with or without modification, are permitted
provided that the following conditions are met:
* Redistributions of source code must retain the above copyright
notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright
notice, this list of conditions and the following disclaimer in the
documentation and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
"AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/*
* UsersDataSourceMySQLi.class.php
*
* Authors: R. W. Rodolico
*
*/
/**
* usersDataSource class
*
* usersDataSource provides the data access capabilities for the Users
* class.
*
* To build a data access class for Users, the following 5 methods must
* exist.
* getPassword(username)
* getRecord(username)
* getAllUsers()
* getARecord
* update
*
* Additionally, where appropriate, the following function is useful
* buildTable()
*
* This particular instance provides an interface to MySQL using
* the mysqli libraries.
*
* Create an instance of this, then pass the variable to several Users
* calls.
*
* @author R. W. Rodolico <rodo@unixservertech.com>
*
* @version 0.9.0 (beta)
* @copyright 2021 Daily Data, Inc.
*
*/
class usersDataSource {
/**
* @var string[] $dbDefinition Contains the configuration for the class
*
* May be modified by the calling program. Must be replicated in userDataSource class
*/
private $dbDefinition = array(
'tables' => array(
'users' => array(
'table' => '_users', // table name for user records
'id' => '_user_id', // ID column name
'display' => array( // fields which are displayed to select
'login'
),
'password' => array( // These fields are stored encrypted
'pass'
),
'fields' => array(
'login' => array(
'dbColumn' => 'login', // login name column name
'type' => 'varchar',
'size' => 64,
'required' => true
),
'pass' => array(
'dbColumn' => 'password', // password column name
'type' => 'varchar',
'size' => 128,
'required' => true
),
'admin' => array(
'dbColumn' => 'isAdmin',
'type' => 'boolean',
'required' => true,
'default' => '0'
),
'enabled' => array(
'dbColumn' => 'enabled',
'type' => 'boolean',
'required' => true,
'default' => '1'
)
)
)
)
);
/** @var mysqli $dbConnection Holds the mysqli database connection */
private $dbConnection = false;
/**
* constructor for an instance of the class
*
* If $dbConnection is not null, will be used for database access
* If $dbLoginInfo is not null, will override $dbConnection, make
* a new connection and use that.
*
* If $dbDef is set, will be merged with $dbDefinition
*
* @param mysqli $dbConnection Existing mysqli database connection
* @param string[] $dbDef Array to be merged with $dbDefinition
* @param string[] $dbLoginInfo Array containing username, hostname, etc.. to make mysqli connection_aborted
*
* @return null
*
*/
public function __construct( $dbConnection = null, $dbDef = array(), $dbLoginInfo = array() ) {
$this->dbConnection = $dbConnection;
if ( $dbDef ) {
$this->dbDefinition = array_merge_recursive( $this->dbDefinition, $dbDef );
}
if ( $dbLoginInfo ) {
$this->setDBConnection( $dbLoginInfo );
}
}
/**
* Make string safe for MySQL
*
* If the string is completely numeric, returns it, otherwise
* puts single quotes around it
*
* @param string $string The string to be fixed
* @return string A copy of the string, ready for SQL
*/
private function escapeString ( $string ) {
$string = $this->dbConnection->real_escape_string( $string );
if ( ! is_numeric( $string ) )
$string = "'$string'";
return $string;
}
/**
* Create a query to retrieve info from database
*
* Builds a query to retrieve records from the database. With all
* parameters set to null, will retrieve all columns and records
* Setting $field and $toFind create a where clause, and setting
* $fieldList as a has (ie, 'fieldname' => 1) will limit the
* fields returned
*
* @param string $field A valid field definition from $dbDefinition
* @param string $toFind The string to find, ie where $field = $username
* @param string[] $fieldList a hash where the keys make a list of columns to return. If empty, returns all columns
*
* @return string A cleaned and formatted SQL Query
*
*/
private function buildQuery( $whereFields, $fieldList = null ) {
// always get the ID field
$fields = array( $this->dbDefinition['tables']['users']['id'] . ' id');
// Get the rest of the available fields
foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $value ) {
// do not use this one if $fieldList doesn't have it
if ( ! isset( $fieldList ) || isset( $fieldList[$key] ) )
$fields[] = $value['dbColumn'] . ' ' . $key;
}
// Change it into something SQL can handle
$query = implode( ',', $fields );
// now, build the rest of the query
$query = "select $query from " . $this->dbDefinition['tables']['users']['table'];
if ( isset( $whereFields ) ) {
$temp = array();
foreach ( $whereFields as $key => $value ) {
$temp[] = (
$key == 'id' ?
$this->dbDefinition['tables']['users']['id'] :
$this->dbDefinition['tables']['users']['fields'][$key]['dbColumn']
) . '= ' . $this->escapeString( $value );
}
$query .= ' where ' . implode( ' and ', $temp );
}
//print "<p>$query</p>";
return $query;
}
/**
* Get a record from the database
*
* Gets a single record from the database which matches $field containing
* $username. If more than one record is returned, will return the first
* one
*
* @param string[] $whereFields column=>value pairs for where clause
* @param string[] $fieldList a list of columns to return. If empty, returns all columns
*
* @return string[] a hash containing fieldname=>value pairs from fetch_assoc
*
*/
public function getARecord( $whereFields, $fieldList = null ) {
// run the query, placing value in $result
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$result = $this->dbConnection->query( $this->buildQuery( $whereFields, $fieldList ) );
if ( $result ) { // got one, so return it
return $result->fetch_assoc();
}
// WTFO? nothing, so return empty array
return array();
}
/**
* Retrieves the password field from table
*
* Note that the password is stored as a hash in the table
*
* @param string $username username used to find record
* @return string[] an array of values key/value pairs
*/
public function getPassword( $username ) {
return $this->getARecord( array('login' => $username,'enabled' => 1), array('pass' => 1 ) );
}
/**
* Gets the entire record for a user
*
* NOTE: this does not actually get all columns. getARecord only gets
* the columns defined in $dbDefinition
*
* @param string $username the value of the login field to find
*
* @return string[] fieldname=>value array of found record
*/
public function getRecord ( $username ) {
return $this->getARecord( array( 'login' => $username ) );
}
/**
* Make the database connection
*
* @param string[] $parameters Parameters for makeing the connection
* @return mysqli|false
*/
private function setDBConnection ( $parameters ) {
if ( !isset($parameters['username'], $parameters['password'],$parameters['database'] )) {
return false;
}
if ( !isset( $parameters['host'] ) ) {
$parameters['host'] = 'localhost';
}
mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$this->dbConnection = new mysqli( $parameters['host'], $parameters['username'], $parameters['password'],$parameters['database'] );
}
/**
* Convenience function to create the table
*
* Using $dbDefinition, build the table (replacing the current one)
* then add $username with $password, setting as admin
*
* @param string $username The username to create
* @param string $password The password for this record
* @param bool $admin Whether the user is an admin or not
*
* @return bool true if table was created
*
*/
public function buildTable( $username, $password, $admin = true ) {
if ( $this->dbConnection ) {
$password = password_hash( $password, PASSWORD_DEFAULT );
$fields = array( $this->dbDefinition['tables']['users']['id'] . ' int unsigned not null auto_increment' );
foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
$fieldDef = $record['dbColumn'];
$fieldDef .= ' ' . $record['type'];
if ( isset( $record['size'] ) ) {
$fieldDef .= '(' . $record['size'] . ')';
}
if ( isset( $record['required'] ) ) {
$fieldDef .= $record['required'] ? ' not null ' : '';
}
if ( isset( $record['default'] ) ) {
$fieldDef .= sprintf( " default '%s'", $record['default'] );
}
if ( isset( $record['comment'] ) ) {
$fieldDef .= "comment '" . $record['comment'] . "'";
}
$fields[] = $fieldDef;
}
$fields[] = 'primary key (' . $this->dbDefinition['tables']['users']['id'] . ')';
$query = implode( ',', $fields );
$query = 'create or replace table ' . $this->dbDefinition['tables']['users']['table'] .
"($query)";
$this->dbConnection->query( $query );
$query = "insert into _users( login, password, isAdmin ) values ( '$username', '$password', '$admin' )";
$this->dbConnection->query( $query );
}
return false;
} // buildTable
/**
* Tests that the database connection works and the table is built
*
* @return boolean True if table exists (does not verify columns)
*/
public function test() {
$result = $this->dbConnection->query( sprintf( "show tables like '%s'", $this->dbDefinition['tables']['users']['table'] ) );
return $result !== false && $result->num_rows;
} // test
/**
* updates row in database with $newData
*
* @param string[] $newData fieldname/value pairs to be updated in table
*
* @return mysqli_result|bool The mysqli result from a query
*/
public function update ( $newData ) {
$query = '';
foreach ( $newData as $key => $value ) {
$newData[$key] = $this->escapeString( $value );
}
if ( $newData ) { // make sure they sent us something
if ( $newData['id'] > 0 ) { // we are doing an update
$fields = array();
foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
if ( isset( $newData[$key] ) ) {
$fields[] = $record['dbColumn'] . " = $newData[$key]";
} // if
}
$query = 'update ' . $this->dbDefinition['tables']['users']['table'] . ' set ' .
implode( ',', $fields ) .
' where ' . $this->dbDefinition['tables']['users']['id'] . ' = ' .
$this->dbConnection->real_escape_string( $newData['id'] );
} else { // we are doing an insert
$columns = array();
$values = array();
foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
if ( isset( $newData[$key] ) ) {
$columns[] = $record['dbColumn'];
$values[] = $newData[$key];
} // if
}
$query = 'insert into ' . $this->dbDefinition['tables']['users']['table'] .
'(' . implode( ',', $columns ) . ') values (' .
implode( ',', $values ) . ')';
}
//print "<p>$query</p>";
return $this->dbConnection->query( $query );
}
} // update
/**
* retrieves all users from the database
*
* Retrieves all data for all users from table
*
* @return string[] array of array of rows/columns
*/
public function getAllUsers() {
$query = $this->buildQuery( null, null, array('login' => 1) );
//print "<p>$query</p>\n";
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$result = $this->dbConnection->query( $query );
if ( $result ) {
return $result->fetch_all(MYSQLI_ASSOC);
}
return array();
}
}
?>