Subversion Repositories php_users

<?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)

 * 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.

 * 

 */

require_once( 'UsersDataSource.class.php' );

class usersDataSourceMySQLi extends usersDataSource {

   /**

    * @var string[] $configuration Contains the configuration for the class

    * 

    * May be modified by the calling program. Must be replicated in userDataSource class

    */

   protected $configuration = 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,

                     'unique'    => 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 */

      protected $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 $configuration

       * 

       * @param mysqli $dbConnection Existing mysqli database connection or array with login information

       * @param string[] $dbDef Array to be merged with $configuration

       * 

       * @return null

       * 

       */

      public function __construct( $connection, $customFields = array() ) {

         parent::__construct( $customFields );

         if ( is_array( $connection ) ) { // they sent us some login values

            $this->setDBConnection( $connection );

         } elseif ( $connection instanceof mysqli ) { // mysqli, or some extension

            $this->dbConnection = $connection;

         } else {

            throw new Exception( 'Can not open database using; must give open mysqli class or array of login information' );

         }

      }

      /**

       * 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

       */

      protected function escapeString ( $string ) {

         if ( strlen( $string ) == 0 ) {

            $string = 'null';

         } elseif ( ! is_numeric( $string ) ) {

            $string = $this->dbConnection->real_escape_string( $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 $configuration

       * @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

       * 

      */

      protected function buildQuery( $whereFields, $fieldList = null ) {

         // always get the ID field

         $fields = array( $this->configuration['tables']['users']['id'] . ' id');

         // Get the rest of the available fields

         foreach ( $this->configuration['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->configuration['tables']['users']['table'];

         if ( isset( $whereFields ) ) {

            $temp = array();

            foreach ( $whereFields as $key => $value ) {

               $temp[] = ( 

                  $key == 'id' ? 

                  $this->configuration['tables']['users']['id'] : 

                  $this->configuration['tables']['users']['fields'][$key]['dbColumn']

                  ) . '= ' . $this->escapeString( $value );

            }

            $query .= ' where ' . implode( ' and ', $temp );

         }

         $query .= ' order by isnull(removed) desc, login';

         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->doSQL( $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 ) );

      }

      /**

       * retrieves the userID from the table

       * 

       * @param string $username

       * @return integer user id

       */

      public function getUserID( $username ) {

         $record = $this->getARecord( array( 'login' => $username ), array('id' => 1 ) );

         return $record['id'];

      }

      /**

       * Make the database connection

       * 

       * @param string[] $parameters Parameters for makeing the connection

       * @return mysqli|false

       */

      protected 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 tables defined in $configuration

       * 

       * Using $configuration, build the table (replacing the current one)

       * then add $username with $password, setting as admin

       * 

      */

      public function buildTable( ) {

         if ( $this->dbConnection ) {

            foreach ( $this->configuration['tables'] as $table => $tableRecord ) {

               $fields = array( $tableRecord['id'] . ' int unsigned not null auto_increment' );

               foreach ( $tableRecord['fields'] as $key => $record ) {

                  $fieldDef = $record['dbColumn'];

                  $fieldDef .= ' ' . $record['type'];

                  if ( isset( $record['size'] ) ) {

                     $fieldDef .= '(' . $record['size'] . ')';

                  }

                  if ( isset( $record['unique'] ) ) {

                     $fieldDef .= ' unique ';

                  }

                  if ( isset( $record['required'] ) ) {

                     $fieldDef .= $record['required'] ? ' not null ' : '';

                  }

                  if ( isset( $record['default'] ) ) {

                     $fieldDef .= sprintf( " default '%s'", $record['default'] );

                  } elseif ( ! empty( $record['required'] ) && strtolower($record['type']) == 'date' ) {

                     $fieldDef .= ' default CURRENT_TIMESTAMP';

                  }

                  if ( isset( $record['comment'] ) ) {

                     $fieldDef .= "comment '" . $record['comment'] . "'";

                  }

                  $fields[] = $fieldDef;

               }

               $fields[] = 'primary key (' . $tableRecord['id'] . ')';

               $query = implode( ',', $fields );

               $query = 'create or replace table ' . $tableRecord['table'] .

                     "($query)";

               $this->doSQL( $query );

            }

         } // foreach table

      } // buildTable

      /**

       * Convenience function to initialize tables to values

       * 

       * @param string[] $initValues Array of tablenames, column names and values

       * 

      */

      public function initTables ( $initValues ) {

         foreach ( $initValues as $table => $fieldDef ) {

            $columns = array();

            $values = array();

            foreach ( $fieldDef as $columnName => $columnValue ) {

               $columns[] = $this->tableColumnName( $table, $columnName );

               $values[] = $this->escapeString( $columnValue );

            }

            $query = sprintf( "insert into %s (%s) values (%s)", 

                  $this->configuration['tables'][$table]['table'],

                  implode( ",", $columns ), 

                  implode( ',', $values ) 

                  );

            $this->doSQL( $query );

         }

      }

      /**

       * Gets the actual database column name from the configuration file

       * 

       * Since we use a lot of indirection, this is a handy function which

       * allows us to replace something like

       * $this->configuration['tables']['users']['fields']['name']['dbColumn']

       * with

       * $this->tableColumnName( 'users', 'name' )

       * 

       * If called with only one parameter (the table), will return the

       * actual database table name

       * 

       * @param string $table Name of Table

       * @param string $field Name of field in $table

       * @param boolean $fullTableColumn If set to true, will return table.column format

       * 

       * @return string The actual name of the dbColumn in the table

       */

      protected function tableColumnName ( $table, $field = '', $fullTableColumn = false ) {

         if ( ! $field ) { // just return the table name

            $return = $this->configuration['tables'][$table]['table'];

         } elseif ( $field == 'id' ) { // looking for the index

            $return = $this->configuration['tables'][$table]['id'];

         } else { // return the column name

            $return = $this->configuration['tables'][$table]['fields'][$field]['dbColumn'];

         }

         if ( $fullTableColumn && $field ) {

            $return = $this->configuration['tables'][$table]['table'] . '.' . $return;

         }

         return $return;

      }

      /**

       * Tests that the database connection works and the table is built

       *

       * @return boolean True if table exists (does not verify columns)

       */

      public function test() {

         $query = sprintf( "show tables like '%s'", $this->tableColumnName ( 'users' ) );

         $result = $this->doSQL( $query );

         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->configuration['tables']['users']['fields'] as $key => $record ) {

                  if ( isset( $newData[$key] ) ) {

                     $fields[] = $record['dbColumn'] . " = $newData[$key]";

                  } // if

               }

               $query = 'update ' . $this->tableColumnName ( 'users' ) . ' set ' .

                  implode( ',', $fields ) .

                  ' where ' . $this->tableColumnName ( 'users', 'id' ) . ' = ' . 

                  $this->escapeString( $newData['id'] );

            } else { // we are doing an insert

               $columns = array();

               $values = array();

               foreach ( $this->configuration['tables']['users']['fields'] as $key => $record ) {

                  if ( isset( $newData[$key] ) ) {

                     $columns[] = $record['dbColumn'];

                     $values[] = $newData[$key];

                  } // if

               }

               $query = 'insert into ' . $this->tableColumnName ( 'users' ) . 

                  '(' . implode( ',', $columns ) . ') values (' .

                  implode( ',', $values ) . ')';

            }

            return $this->doSQL( $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->doSQL( $query );

         if ( $result ) {

            return $result->fetch_all(MYSQLI_ASSOC);

         }

         return array();

      }

      /**

       * Executes an SQL statement, returning the result

       * 

       * This simply runs mysqli::query, and returns the value of that

       * 

       * Created for testing and debugging, if the second parameter is 

       * true, will NOT execute the query, but will instead display the 

       * query passed.

       * 

       * @parameter string $query SQL Query to execute

       * @parameter string $comment if not empty, writes comment and query to a file

       * 

       * @returns mysqli_result

       */

      protected function doSQL( $query, $comment = '' ) {

         if ( $comment ) {

            $handle = fopen( '/tmp/log.sql', 'a' );

            fwrite( $handle, "$comment\n$query\n" );

            fclose( $handle );

         }

         mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );

         $result = $this->dbConnection->query( $query );

         return $result;

      }

      /**

       * Gets a single field from a table

       * 

       * Builds a query similar to

       * select $returnColumn from $tableName where $fieldName = $value

       * executes it, and returns the first column of the first

       * row returned, or null if it does not exist.

       * 

       * @parameter string $tableName Name of database table

       * @parameter string $returnColumn Column to return

       * @parameter string $fieldName Name of column to search for value

       * @parameter string $value The value to match

       * 

       * @returns string $returnColumn of first row, or null if none

       */

      protected function getAField( $tableName, $returnColumn, $fieldName, $value ) {

         $value = $this->escapeString( $value );

         $result = $this->doSQL( "select $returnColumn from $tableName where $fieldName = $value" );

         $field = $result->fetch_array(MYSQLI_NUM);

         return $field ? $field[0] : null;

      }

}

?>