Subversion Repositories phpLibraryV2

Rev

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

Rev Author Line No. Line
1 rodolico 1
<?php
2
 
13 rodolico 3
   /*
4
    * Class DBQuery
5
    * 
16 rodolico 6
    * PHP Class as a wrapper around the mysqli class. Allows you to make
13 rodolico 7
    * the connection and run queries and/or DDL's if needed with the
16 rodolico 8
    * most common parameters (ie, not as flexible as mysqli itself).
13 rodolico 9
    * Enhancements include:
10
    *
11
    * Logs all queries with an optional username except select statements
12
    * ie, anything that will change the system ( insert, delete, update,
13
    * create, drop, alter, etc... ). Statement are logged with a date/time
14
    * stamp, the user who performed the function, and the query executed
15
    * 
16
    * Errors are simply trapped and the message is stored in the public
17
    * member $error. Functions which find an error return boolean false
18
    *
19
    * Public Functions are:
20
    *    __construct   -- creates connection
21
    *    doSQL         -- executes an SQL query or DDL
22
    *    errors        -- returns all errors not yet cleared
23
    *                     can also directly access via public member errors
24
    *
25
    *    public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
1 rodolico 26
 
4 rodolico 27
 
13 rodolico 28
    * Author: R. W. Rodolico (rodo@dailydata.net)
29
    * Date: 2018-04-30
30
    * 
31
    */
32
 
33
   class DBQuery extends mysqli {
34
 
35
      /* everything is in the one $parameters array, which can then
36
       * be set/saved/loaded as needed
37
       */
6 rodolico 38
      protected $parameters = array(
13 rodolico 39
             // query(s) to be run
40
             'query' => '',
41
             // additional clause to be added to where on a statement
42
             'whereClause' => '',
43
             // additional order by clause to be added
44
             'orderBy' => '',
45
             // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select
46
             'rowsAffected' => '',
47
             // the value of the last row inserted
48
             'lastInsertKey' => '',
49
             // an array that contains the data retrieved by a query (select only)
29 rodolico 50
             'returnData' => array(),
13 rodolico 51
             // an array that contains the meta data from the query for each column
29 rodolico 52
             'columnMetaData' => array(),
13 rodolico 53
             // number of columns (fields) returned by query (select only)
54
             'numfields' => 0,
55
             // type of data returned, array of array, array of hash, or both
56
             'fetchType' => MYSQLI_ASSOC,
57
             // human readable form of fetchType
58
             'returntype' => 'associative',
59
             // array of any errors which occurred
60
             'error' => array(),
61
             // if set to a table name, all modifying queries are written to it
62
             // must contain, at a minimum, columns whenrun timestamp, whoran varchar and query text
63
             // can be created with $this->buildAuditTable
64
             'auditTable' => '_activity_log'
65
           );
66
 
6 rodolico 67
 
4 rodolico 68
 
13 rodolico 69
      /*
70
       * static function which simply parses out an error and returns
71
       * a string suitable for printing. NOTE: it includes line returns
72
      */
73
      public static function error2String( $error ) {
74
         $return = array();
75
         for ( $i = 0; $i < count( $error ); $i++ ) {
76
            $return[] = implode( "\n", array( 'Error ' . $error[$i]['id'] . ' - ' . $error[$i]['message'], 'while executing query', $error[$i]['query'] ) );
6 rodolico 77
         }
13 rodolico 78
         return implode( "\n\n", $return ) . "\n";
79
      } // error2String
6 rodolico 80
 
13 rodolico 81
      // simply returns parameters for saving and reloading later
6 rodolico 82
      public function save() {
83
         return $this->parameters;
84
      } // function save
13 rodolico 85
 
86
      // loads parameters from $this->save(), or hand built
6 rodolico 87
      public function load( $parameters = array() ) {
88
         $this->parameters = $parameters;
89
      } // function load
90
 
13 rodolico 91
      // sets a key/value pair in $this->parameters
92
      // returns $value
1 rodolico 93
      public function __set( $name, $value ) {
6 rodolico 94
         $this->parameters[$name] = $value;
4 rodolico 95
         return $value;
1 rodolico 96
      }
13 rodolico 97
 
98
      // gets the current value of $this->parameters[$name]
1 rodolico 99
      public function __get( $name ) {
6 rodolico 100
         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;
1 rodolico 101
      }
13 rodolico 102
 
103
      // returns true if $parameters[$name] is set
1 rodolico 104
      public function __isset( $name ) {
6 rodolico 105
         return isset( $this->parameters[$name] );
1 rodolico 106
      }
13 rodolico 107
 
108
 
109
      /*
110
       * function: buildAuditTable
111
       * parameters:
112
       *       $tablename - name of table to be built
113
       *       $createStatement - SQL DDL to build the table
114
       *
115
       * If $tablename is set, will use that, otherwise will use
116
       * $parameters[auditTable]. In either case, $parameters[auditTable]
117
       * is set to the value of the table used
118
       * 
119
       * if $createStatement is set, will be run WITHOUT MODIFICATION, and
120
       * $parameters[auditTable] is not set to anything (unless $tablename
121
       * is set)
122
       *
123
       * If $createStatement is not set, will use a default to build
124
       * a table from $parameters[auditTable].
125
       *
126
       * Can definitely blow up if the table name is not set both places
127
       * or if $createStatement is fubar
128
       */
129
 
130
      public function buildAuditTable( $tablename = '', $createStatement = '' ) {
131
         if ( $tablename ) // they sent us one, so set it
132
            $this->parameters[ 'auditTable' ] = $tablename;
133
         if ( ! $createStatement ) { // they did not set createStatement, so use our default
15 rodolico 134
            $auditTable = $this->parameters['auditTable'];
13 rodolico 135
            $createStatement = "
29 rodolico 136
               create table if not exists $auditTable (
13 rodolico 137
                  _activity_log_id int unsigned not null auto_increment,
138
                  timestamp  timestamp,
139
                  user       varchar(64),
140
                  query      text,
141
                  primary key(_activity_log_id)
142
               ) comment 'tracks queries which modify data'";
143
         } // if
144
         if ( parent::query( $createStatement ) === false ) {
145
            // on error, die
146
            print "Can not create audit table with query<br />\n$createStatement<br />\n";
147
            die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
148
         } // if error, die
149
      } // buildAuditTable
150
 
151
      /*
152
       * log queries to a table, file, or nothing
153
       * log contains date/time, username and query
154
       * to turn off logginging:
155
       * unset( $parameters['auditTable'] )
156
       */
157
      private function logIt( $username, $query, $recursion = false ) {
158
         if ( ! isset( $this->parameters['auditTable'] ) )
159
            return; 
160
         $username = $this->real_escape_string( $username );
161
         $query = $this->real_escape_string( $query );
15 rodolico 162
         $logEntry = "insert into " . $this->parameters['auditTable'] . " (user, query) values ( '$username', '$query')";
13 rodolico 163
         //print "Loggging\n$logEntry\n";
164
         if ( parent::query( $logEntry ) !== false ) { // good
165
            return;
166
         } else { // we had an error
167
            if ( ! $recursion && $this->errno == 1146 ) { // table doesn't exist, so let's create it
15 rodolico 168
               $result = parent::query( "show tables like '" . $this->parameters['auditTable'] . "'" );
13 rodolico 169
               if ( $result->num_rows == 0 ) {
170
                  $this->buildAuditTable( );
171
                  return $this->logIt( $username, $query, true );
172
               }
6 rodolico 173
            } else {
13 rodolico 174
               print "Trying to log transaction with query<br />\n$logEntry<br />\n";
175
               die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
176
            } // if..else
1 rodolico 177
         } // if
13 rodolico 178
      } // function logIt
179
 
180
 
181
      /*
182
       * doSQL
183
       * Parameters: $query - string or array of strings to be executed
184
       *             $parameters - hash used to pass additional parameters, to include
185
       *                           $parameters['username'] = 'fred'; // username for logging
186
       *                           $parameters['returntype'] = 'hash'; or array or both
187
       * 
188
       * executes one or more queries
189
       *
190
       * If the query is one of select, show, describe or explain, it must
191
       * be a single string. It will return the data results in a hash
192
       * containing
193
       *    'data'  - an array of array/hash/both depending on what you asked for
194
       *    'count' - number of results (ie, count(data)
195
       *    'meta'  - metadata for each column returned
196
       *    'numfields' - number of columns in result (ie, count(meta))
197
       *    'errors' - normally empty array of errors
198
       * 
199
       * if the query modifies data (ie, NOT above), query may be an array
200
       * which will be surrounded by a transaction and rolled back if
201
       * anything causes an error.
202
       *
203
       * These will return a hash containing
204
       *    'count' l- number of rows affected by last statement
205
       *    'last_insert_id' - last insert id created by BLOCK of queries
206
       *    'errors' - normally empty array of errors which occurred (caused a rollback)
207
       * 
208
       */
15 rodolico 209
      public function doSQL( $query = null, $parameters = array() ) {
13 rodolico 210
         $errors = array();
15 rodolico 211
         if ( isset( $query ) ) {
212
            $this->parameters['query'] = $query;
213
         }
13 rodolico 214
         // if it is a "selectstatement" it doesn't modify data
215
         // if query is an array, assume it modifies something
216
         // if it is a single statement, look for the regex
15 rodolico 217
         $selectStatement = is_array( $this->parameters['query'] ) ?
13 rodolico 218
            false :
29 rodolico 219
            ( preg_match( '/^\s*(select)|(show)|(describe)|(explain)/xsi', $this->parameters['query'] ) === 1 );
13 rodolico 220
 
221
         // different actions based on whether it modifies data or not
222
         if ( $selectStatement ) { // if a select, simply return the rows
223
            // dataset is small enough, we just read it into memory all at one time.
224
            // NOTE: fetch_all is nice, but tied to mysqlnd, which has reports of problems, so we do it the old way
15 rodolico 225
            if ( $sth = parent::query( $this->parameters['query'], MYSQLI_USE_RESULT  ) ) {
13 rodolico 226
               if ( $sth === false ) {
227
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error );
228
               } else {
229
                  $this->parameters['columnMetaData'] = $sth->fetch_fields(); // get metadata
230
                  $this->parameters['returnData'] = array(); // we'll put all the results in an array
231
                  // $fetchtype returns either an array of array, array of hash, or both. Default is array of hash
15 rodolico 232
                  if ( isset( $this->parameters['returntype'] ) ) {
13 rodolico 233
                     $this->parameters[ 'fetchType' ] = $this->parameters['returntype'] == 'array' ? MYSQLI_NUM : (
15 rodolico 234
                           ( $this->parameters['returntype'] == 'both' ) ? MYSQLI_BOTH : MYSQLI_ASSOC
13 rodolico 235
                           );
236
                  } else { // default is associative array (hash)
237
                     $this->parameters[ 'fetchType' ] =  MYSQLI_ASSOC;
238
                     $this->parameters['returntype'] = 'associative';
239
                  }
240
                  // slurp all the stuff in
241
                  while ( $values =  $sth->fetch_array( $this->parameters[ 'fetchType' ] ) ) {
242
                     $this->parameters[ 'returnData' ][] = $values;
243
                  }
244
                  $sth->free(); // cleanup memory, don't need two copies
245
               } // if we had no errors
1 rodolico 246
            }
13 rodolico 247
            $this->parameters[ 'rowsAffected' ] = count( $this->parameters[ 'returnData' ] );
248
            $this->parameters[ 'numfields' ] = count( $this->parameters['columnMetaData'] );
249
            $this->parameters[ 'lastInsertKey' ] = 0;
250
            $this->parameters[ 'error' ] = $errors;
251
         } else {
252
            if ( ! is_array( $this->parameters['query'] ) ) { // not an array, so make it one
253
               $temp = $this->parameters['query'];
254
               $this->parameters['query'] = array( $temp );
1 rodolico 255
            }
13 rodolico 256
            // do it in a transaction so we can back out on failure
257
            $this->autocommit(false);
258
            $allOk = true;
259
            for ( $i = 0; $i < count( $this->parameters['query'] ); $i++ ) {
15 rodolico 260
               // debugging
261
               //print "$i\t" . $this->parameters['query'][$i] ."\n"; continue;
262
               // debugging
13 rodolico 263
               $this->logIt( isset( $parameters['username'] ) ? $parameters['username'] : 'unknown', $this->parameters['query'][$i] );
264
               if ( parent::query( $this->parameters['query'][$i] ) === false ) { // we had an erorr
265
                  // record it
266
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error, 'query' => $this->parameters['query'][$i] );
267
                  $allOk = false;
268
                  // and bail
269
                  break;
270
               }
271
            }
272
            // if we made it through ok, commit, otherwise rollback
273
            $allOk ? $this->commit() : $this->rollback();
274
            // reset autocommit to true
275
            $this->autocommit(true);
276
            $this->parameters['query'] = $query;
277
            $this->parameters[ 'rowsAffected' ] = $this->affected_rows;
278
            $this->parameters[ 'lastInsertKey' ] = $this->insert_id;
279
            $this->parameters[ 'error' ] = $errors;
280
         } // if select .. else
281
         return $this->parameters;
282
      }  // function doSQL
17 rodolico 283
 
284
      public function run () {
285
         return $this->doSQL( );
286
      }
34 rodolico 287
 
288
      /**
289
       * returns an array of the first column for each row returned from query
290
       * 
291
       * The query is run, then for each row returns, the first column
292
       * is added to $return (an array). $return is then returned.
293
       * 
294
       * Used to do things like get an array of keyfields, or something
295
       * else.
296
       * 
297
       * @param string $query Query to run
298
       * @returns string[] Array of values
299
       */
300
      public function columnToArray( $query ) {
301
         $return = array();
302
         $result = $this->doSQL( $query );
303
         foreach ( $result['returnData'] as $row ) {
304
            $return[] = $row['id'];
305
         }
306
         return $return;
307
      }
1 rodolico 308
 
309
      /*
310
       * function will return one and only one row, NOT as an array of array
311
       * but as a single row array
312
       * if more than one row is returned by query, error is set and function
313
       * returns false.
314
       * Otherwise, function returns true
315
       */
316
      public function getOneRow( $sql = null ) {
317
         if ( isset( $sql ) )
6 rodolico 318
            $this->parameters[ 'query' ] = $sql;
319
         $save = $this->parameters[ 'useAssociativeArray' ];
1 rodolico 320
         $useAssociativeArray = true;
321
         $this->run();
322
         $useAssociativeArray = $save;
6 rodolico 323
         if ( $this->parameters[ 'rowsAffected' ] == 1 ) {
324
            $this->parameters[ 'returnData' ] = $this->parameters[ 'returnData' ][0];
1 rodolico 325
            return true;
326
         } else
6 rodolico 327
            $this->parameters['error'] = "$this->parameters[query] did not return a unique row in getOneRow";
1 rodolico 328
         return false;
329
      } // getOneRow
330
 
331
 
332
      // function returns the first column of the first row of data returned from query
333
      // or null no value returned
6 rodolico 334
      public function getOneDBValue( $sql = null ) {
34 rodolico 335
         //print '<pre>' . $sql . '</pre>';
1 rodolico 336
         if ( isset( $sql ) )
6 rodolico 337
            $this->parameters[ 'query' ] = $sql;
17 rodolico 338
         $save = $this->parameters['returntype'];
339
         $this->parameters['returntype'] = 'array';
1 rodolico 340
         $this->run();
17 rodolico 341
         $this->parameters['returntype'] = $save;
342
//         print "<pre>" . print_r($this->parameters,true ) . "</pre>";
6 rodolico 343
         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;
1 rodolico 344
      }
6 rodolico 345
 
4 rodolico 346
      /*
347
       * function will attempt to make a constant ($value) safe for SQL depending on the type.
348
       * 
349
       * if $value is empty, $default is returned, as will happen if any of the
350
       * conversions (date, datetime, etc...) fail.
351
       * 
352
       * First, it will pass it through get_magic_quotes_gpc, 
353
       * then will run through mysql_real_escape_string
354
       * 
355
       * For strings, will encapsulate in quotes
356
       * Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes
357
       * DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS
358
       * Integer and Floats are passed through builtins intval and floatval
359
       * Boolean only checks the first character, a '0', 'f' and 'n' denoting false
360
       *    all else denoting true. The result is converted based on the variable
361
       *    $falsetrue, with the first char denoting false and the second denoting true
362
       */
363
      public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
364
         if (strlen($value) == 0) // simply set any empty values to null
365
            return $default;
366
         // print "Processing $value as $type with default $default<br>\n";
367
         switch ( strtolower( $type ) ) {
368
            case 'string' :
369
            case 's' : 
370
                     if ( get_magic_quotes_gpc() ) 
371
                        $value = stripslashes($value);
13 rodolico 372
                     $value = $this->real_escape_string( $value );
4 rodolico 373
                     $value = strlen( $value ) > 0 ? "'$value'" : $default;
374
                     break;
375
            case 'date' :
376
            case 'd' :
377
                     if ( $value != 'null' ) {
378
                        $result = strtotime( $value );
379
                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";
380
                     }
381
                     break;
382
            case 'datetime':
383
            case 'timestamp':
384
            case 'dt': 
385
                     if ( $value != 'null' ) {
386
                        $result = strtotime( $value );
387
                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";
388
                     }
389
                     break;
390
            case 'integer':
391
            case 'i' :  
392
                     $value = intval( $value );
393
                     break;
394
            case 'float':
395
            case 'f' :  
396
                     $value = floatval( $value );
397
                     break;
398
            case 'bool':
399
            case 'boolean':
400
            case 'b' :  // note, because of the way strpos works, you can not
401
                        // simply set $value based on the output; you MUST do
402
                        // as below; specifically check for false, then set the result
403
                        $value =  strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;
404
                        $value = substr( $falsetrue, $value, 0, 1 );
405
                        break;
406
         } // switch
407
         return $value;
408
      }
1 rodolico 409
 
29 rodolico 410
      /*
411
         '[^']*(?!\\)'(*SKIP)(*F)       # Make sure we're not matching inside of quotes
412
         |(?m-s:\s*(?:\-{2}|\#)[^\n]*$) # Single line comment
413
         |(?:
414
           \/\*.*?\*\/                  # Multi-line comment
415
           (?(?=(?m-s:\h+$))         # Get trailing whitespace if any exists and only if it's the rest of the line
416
             \h+
417
           )
418
         )
419
      */
420
      /**
421
       * Functions strips SQL queries from a file. Above is a commented copy of the regex used
422
       * 
423
       * @param string $query An arbitrary sized SQL script
424
       * @returns string $query, with all SQL comments removed
425
       */
4 rodolico 426
 
29 rodolico 427
      public function strip_sqlcomment ($query = '') {
428
         $regex = '\'[^\']*(?!\\\)\'(*SKIP)(*F)|(?m-s:\\s*(?:\\-{2}|\\#)[^\\n]*$)|(?:\\/\\*.*?\\*\\/(?(?=(?m-s:\h+$))\\h+))';
429
         //print "<pre>$regex</pre>" ; die;
430
          return (($query == '') ?  '' : preg_replace( "/$regex/xs", '', $query ));
431
      }
432
 
433
      /**
434
       * runs an SQL script with multiple statements in it
435
       * 
436
       * NOTE: it does this by breaking it based on the semicolon, so
437
       * in some weird situations, it will break at the wrong place.
438
       * 
439
       */
440
      public function runSQLScript( $sql ) {
441
         $sql = $this->strip_sqlcomment( $sql );
442
         $queries = explode( ";", $sql );
443
         //print "<pre>" . print_r( $queries, true ) . '</pre>'; die;
444
         foreach ( $queries as $query ) {
445
            $this->doSQL( trim( implode( ' ', explode("\n",$query ) ) ) );
446
         }
447
      }      
448
 
1 rodolico 449
   } // class DBQuery
450
 
17 rodolico 451
/*
452
 *    $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );
13 rodolico 453
 
454
   if ($db->connect_error) {
455
       die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);
456
   }
457
   $result = $db->doSQL(
458
               array(
459
                     'drop table if exists temp',
460
                     'create table temp ( col1 int unsigned )',
461
                     "insert into temp values ('mike')"
462
                     )
463
         );
15 rodolico 464
   if ( $result['error'] ) {
13 rodolico 465
      print_r ( $result );
15 rodolico 466
      die ( DBQuery::error2String( $result['error'] ) );
13 rodolico 467
   } else {
468
      print "running select\n";
469
      $result = $db->doSQL( 'select * from temp' );
470
      print_r( $result );
471
   }
472
//   $return = $db->doSQL( "select device.device_id 'id',device.name 'name', device_type.name 'type' from device join device_type using (device_type_id) where device.removed_date is null and device_type.show_as_system = 'Y'" );
473
//   print_r( $return );
474
//   print_r( $db );
17 rodolico 475
*/
1 rodolico 476
?>
13 rodolico 477
 
478
 
479
 
480
 
481
 
482
 
483
 
484
 
485
 
486