Subversion Repositories php_users

Rev

Rev 4 | Rev 10 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
4 rodolico 1
<?php
2
 
3
/*
7 rodolico 4
   Copyright (c) 2021, Daily Data, Inc. Redistribution and use in 
5
   source and binary forms, with or without modification, are permitted
6
   provided that the following conditions are met:
7
 
8
   * Redistributions of source code must retain the above copyright 
9
     notice, this list of conditions and the following disclaimer.
10
   * Redistributions in binary form must reproduce the above copyright 
11
     notice, this list of conditions and the following disclaimer in the 
12
     documentation and/or other materials provided with the distribution.
13
 
14
   THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
15
   "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
16
   LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
17
   A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
18
   OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
19
   SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 
20
   LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
21
   DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
22
   THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
23
   (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
24
   OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
25
 
26
*/
27
 
28
/*
29
 * UsersDataSourceMySQLi.class.php
30
 * 
31
 * Authors: R. W. Rodolico
32
 * 
4 rodolico 33
 */
34
 
7 rodolico 35
/**
36
 * usersDataSource class
37
 * 
38
 * usersDataSource provides the data access capabilities for the Users
39
 * class.
40
 * 
41
 * To build a data access class for Users, the following 5 methods must
42
 * exist.
43
 * getPassword(username)
44
 * getRecord(username)
45
 * getAllUsers()
46
 * getARecord
47
 * update
48
 * 
49
 * Additionally, where appropriate, the following function is useful
50
 * buildTable()
51
 * 
52
 * This particular instance provides an interface to MySQL using
53
 * the mysqli libraries.
54
 * 
55
 * Create an instance of this, then pass the variable to several Users
56
 * calls.
57
 * 
58
 * @author R. W. Rodolico <rodo@unixservertech.com>
59
 * 
60
 * @version 0.9.0 (beta)
61
 * @copyright 2021 Daily Data, Inc.
62
 * 
63
 */
64
 
4 rodolico 65
class usersDataSource {
66
 
7 rodolico 67
   /**
68
    * @var string[] $dbDefinition Contains the configuration for the class
69
    * 
70
    * May be modified by the calling program. Must be replicated in userDataSource class
71
    */
4 rodolico 72
   private $dbDefinition = array(
73
      'tables' => array(
74
         'users'  => array(
75
            'table'     => '_users',   // table name for user records
76
            'id'        => '_user_id', // ID column name
77
            'display'   => array(      // fields which are displayed to select
78
               'login'
79
               ),
80
            'password'  => array(      // These fields are stored encrypted
81
               'pass'
82
               ),
83
            'fields' => array(
84
               'login'  => array(
85
                     'dbColumn'  =>  'login',       // login name column name
86
                     'type'      => 'varchar',
87
                     'size'      => 64,
88
                     'required'  => true,
89
                     'editable'  => true
90
                     ),
91
               'pass'   => array( 
92
                     'dbColumn'  => 'password',    // password column name
93
                     'type'   => 'varchar',
94
                     'size'      => 128,
95
                     'required'  => true,
96
                     'editable'  => true
97
                     ),
98
               'admin'  => array(
99
                     'dbColumn'  => 'isAdmin',
100
                     'type'      => 'boolean',
101
                     'required'  => true,
102
                     'default'   => '0'
103
                     ),
104
               'enabled'   => array(
105
                     'dbColumn'  => 'enabled',
106
                     'type'      => 'boolean',
107
                     'required'  => true,
108
                     'default'   => '1'
109
                     )
110
               )
111
            )
112
         )
113
      );
7 rodolico 114
      /** @var mysqli $dbConnection Holds the mysqli database connection */
4 rodolico 115
      private $dbConnection = false;
116
 
7 rodolico 117
      /**
118
       * constructor for an instance of the class
119
       * 
120
       * If $dbConnection is not null, will be used for database access
121
       * If $dbLoginInfo is not null, will override $dbConnection, make
122
       * a new connection and use that.
123
       * 
124
       * If $dbDef is set, will be merged with $dbDefinition
125
       * 
126
       * @param mysqli $dbConnection Existing mysqli database connection
127
       * @param string[] $dbDef Array to be merged with $dbDefinition
128
       * @param string[] $dbLoginInfo Array containing username, hostname, etc.. to make mysqli connection_aborted
129
       * 
130
       * @return null
131
       * 
132
       */
4 rodolico 133
      public function __construct( $dbConnection = null, $dbDef = array(), $dbLoginInfo = array() ) {
134
         $this->dbConnection = $dbConnection;
135
         if ( $dbDef ) {
136
            $this->dbDefinition = array_merge_recursive( $this->dbDefinition, $dbDef );
137
         }
138
         if ( $dbLoginInfo ) {
139
            $this->setDBConnection( $dbLoginInfo );
140
         }
141
      }
142
 
7 rodolico 143
      /**
144
       * Make string safe for MySQL
145
       * 
146
       * If the string is completely numeric, returns it, otherwise 
147
       * puts single quotes around it
148
       * 
149
       * @param string $string The string to be fixed
150
       * @return string A copy of the string, ready for SQL
151
       */
4 rodolico 152
      private function escapeString ( $string ) {
153
         $string = $this->dbConnection->real_escape_string( $string );
154
         if ( ! is_numeric( $string ) )
155
            $string = "'$string'";
156
         return $string;
157
      }
158
 
159
      /**
7 rodolico 160
       * Create a query to retrieve info from database
4 rodolico 161
       * 
162
       * Builds a query to retrieve records from the database. With all
163
       * parameters set to null, will retrieve all columns and records
164
       * Setting $field and $toFind create a where clause, and setting
165
       * $fieldList as a has (ie, 'fieldname' => 1) will limit the 
166
       * fields returned
167
       * 
7 rodolico 168
       * @param string $field A valid field definition from $dbDefinition
4 rodolico 169
       * @param string $toFind The string to find, ie where $field = $username
170
       * @param string[] $fieldList a hash where the keys make a list of columns to return. If empty, returns all columns
171
       * 
172
       * @return string A cleaned and formatted SQL Query
173
       * 
174
      */
175
      private function buildQuery( $whereFields, $fieldList = null ) {
176
         // always get the ID field
177
         $fields = array( $this->dbDefinition['tables']['users']['id'] . ' id');
178
         // Get the rest of the available fields
179
         foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $value ) {
180
            // do not use this one if $fieldList doesn't have it
181
            if ( ! isset( $fieldList ) || isset( $fieldList[$key] ) )
182
               $fields[] = $value['dbColumn'] . ' ' . $key;
183
         }
184
         // Change it into something SQL can handle
185
         $query = implode( ',', $fields );
186
         // now, build the rest of the query
187
         $query = "select $query from " . $this->dbDefinition['tables']['users']['table'];
188
         if ( isset( $whereFields ) ) {
189
            $temp = array();
190
            foreach ( $whereFields as $key => $value ) {
191
               $temp[] = ( 
192
                  $key == 'id' ? 
193
                  $this->dbDefinition['tables']['users']['id'] : 
194
                  $this->dbDefinition['tables']['users']['fields'][$key]['dbColumn']
195
                  ) . '= ' . $this->escapeString( $value );
196
            }
197
            $query .= ' where ' . implode( ' and ', $temp );
198
         }
199
         //print "<p>$query</p>";
200
         return $query;
201
      }
202
 
203
      /**
204
       * Get a record from the database
205
       * 
206
       * Gets a single record from the database which matches $field containing
207
       * $username. If more than one record is returned, will return the first
208
       * one
209
       * 
210
       * @param string[] $whereFields column=>value pairs for where clause
211
       * @param string[] $fieldList a list of columns to return. If empty, returns all columns
212
       * 
7 rodolico 213
       * @return string[] a hash containing fieldname=>value pairs from fetch_assoc
4 rodolico 214
       * 
215
      */
216
      public function getARecord( $whereFields, $fieldList = null ) {
217
         // run the query, placing value in $result
218
         mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
219
         $result = $this->dbConnection->query( $this->buildQuery( $whereFields, $fieldList ) );
220
         if ( $result ) { // got one, so return it
221
            return $result->fetch_assoc();
222
         }
223
         // WTFO? nothing, so return empty array
224
         return array();
225
      }
226
 
7 rodolico 227
      /**
228
       * Retrieves the password field from table
229
       * 
230
       * Note that the password is stored as a hash in the table
231
       * 
232
       * @param string $username username used to find record
233
       * @return string[] an array of values key/value pairs
234
       */
4 rodolico 235
      public function getPassword( $username ) {
236
         return $this->getARecord( array('login' => $username,'enabled' => 1), array('pass' => 1 ) );
237
      }
238
 
7 rodolico 239
      /**
240
       * Gets the entire record for a user
241
       * 
242
       * NOTE: this does not actually get all columns. getARecord only gets
243
       * the columns defined in $dbDefinition
244
       * 
245
       * @param string $username the value of the login field to find
246
       * 
247
       * @return string[] fieldname=>value array of found record
248
       */
4 rodolico 249
      public function getRecord ( $username ) {
250
         return $this->getARecord( array( 'login' => $username ) );
251
      }
252
 
7 rodolico 253
      /**
254
       * Make the database connection
255
       * 
256
       * @param string[] $parameters Parameters for makeing the connection
257
       * @return mysqli|false
258
       */
4 rodolico 259
      private function setDBConnection ( $parameters ) {
260
         if ( !isset($parameters['username'], $parameters['password'],$parameters['database']  )) {
261
            return false;
262
         }
263
         if ( !isset( $parameters['host'] ) ) {
264
            $parameters['host'] = 'localhost';
265
         }
266
         mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
267
         $this->dbConnection = new mysqli( $parameters['host'], $parameters['username'], $parameters['password'],$parameters['database'] );
268
      }
269
 
270
      /**
271
       * Convenience function to create the table
272
       * 
273
       * Using $dbDefinition, build the table (replacing the current one)
274
       * then add $username with $password, setting as admin
275
       * 
276
       * @param string $username The username to create
277
       * @param string $password The password for this record
278
       * @param bool $admin Whether the user is an admin or not
279
       * 
280
       * @return bool true if table was created
281
       * 
282
      */
283
      public function buildTable( $username, $password, $admin = true ) {
284
         if ( $this->dbConnection ) {
285
            $password = password_hash( $password, PASSWORD_DEFAULT );
286
            $fields = array( $this->dbDefinition['tables']['users']['id'] . ' int unsigned not null auto_increment' );
287
            foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
288
               $fieldDef = $record['dbColumn'];
289
               $fieldDef .= ' ' . $record['type'];
290
               if ( isset( $record['size'] ) ) {
291
                  $fieldDef .= '(' . $record['size'] . ')';
292
               }
293
               if ( isset( $record['required'] ) ) {
294
                  $fieldDef .= $record['required'] ? ' not null ' : '';
295
               }
296
               if ( isset( $record['default'] ) ) {
297
                  $fieldDef .= sprintf( " default '%s'", $record['default'] );
298
               }
299
               if ( isset( $record['comment'] ) ) {
300
                  $fieldDef .= "comment '" . $record['comment'] . "'";
301
               }
302
               $fields[] = $fieldDef;
303
            }
304
            $fields[] = 'primary key (' . $this->dbDefinition['tables']['users']['id'] . ')';
305
            $query = implode( ',', $fields );
306
            $query = 'create or replace table ' . $this->dbDefinition['tables']['users']['table'] .
307
                  "($query)";
308
            $this->dbConnection->query( $query );
309
            $query = "insert into _users( login, password, isAdmin ) values ( '$username', '$password', '$admin' )";
310
            $this->dbConnection->query( $query );
311
         }
312
         return false;
313
      } // buildTable
314
 
315
      /**
316
       * Tests that the database connection works and the table is built
7 rodolico 317
       *
318
       * @return boolean True if table exists (does not verify columns)
4 rodolico 319
       */
320
      public function test() {
321
         $result = $this->dbConnection->query( sprintf( "show tables like '%s'", $this->dbDefinition['tables']['users']['table'] ) );
322
         return $result !== false && $result->num_rows;
323
      } // test
324
 
7 rodolico 325
      /**
326
       * updates row in database with $newData
327
       * 
328
       * @param string[] $newData fieldname/value pairs to be updated in table
329
       * 
330
       * @return mysqli_result|bool The mysqli result from a query
331
       */
4 rodolico 332
      public function update ( $newData ) {
333
         $query = '';
334
         foreach ( $newData as $key => $value ) {
335
            $newData[$key] = $this->escapeString( $value );
336
         }
337
         if ( $newData ) { // make sure they sent us something
338
            if ( $newData['id'] > 0 ) { // we are doing an update
339
               $fields = array();
340
               foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
341
                  if ( isset( $newData[$key] ) ) {
342
                     $fields[] = $record['dbColumn'] . " = $newData[$key]";
343
                  } // if
344
               }
345
               $query = 'update ' . $this->dbDefinition['tables']['users']['table'] . ' set ' .
346
                  implode( ',', $fields ) .
347
                  ' where ' . $this->dbDefinition['tables']['users']['id'] . ' = ' . 
348
                  $this->dbConnection->real_escape_string( $newData['id'] );
349
            } else { // we are doing an insert
350
               $columns = array();
351
               $values = array();
352
               foreach ( $this->dbDefinition['tables']['users']['fields'] as $key => $record ) {
353
                  if ( isset( $newData[$key] ) ) {
354
                     $columns[] = $record['dbColumn'];
355
                     $values[] = $newData[$key];
356
                  } // if
357
               }
358
               $query = 'insert into ' . $this->dbDefinition['tables']['users']['table'] . 
359
                  '(' . implode( ',', $columns ) . ') values (' .
360
                  implode( ',', $values ) . ')';
361
            }
362
            //print "<p>$query</p>";
363
            return $this->dbConnection->query( $query );
364
         }
365
      } // update
366
 
7 rodolico 367
      /**
368
       * retrieves all users from the database
369
       * 
370
       * Retrieves all data for all users from table
371
       * 
372
       * @return string[] array of array of rows/columns
373
       */
4 rodolico 374
      public function getAllUsers() {
375
         $query = $this->buildQuery( null, null, array('login' => 1) );
376
         //print "<p>$query</p>\n";
377
         mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
378
         $result = $this->dbConnection->query( $query );
379
         if ( $result ) {
380
            return $result->fetch_all(MYSQLI_ASSOC);
381
         }
382
         return array();
383
      }
384
 
385
}
386
 
387
?>