Subversion Repositories phpLibraryV2

<?php

   /*

    * Class DBQuery

    * 

    * PHP Class as a wrapper around the mysqli class. Allows you to make

    * the connection and run queries and/or DDL's if needed with the

    * most common parameters (ie, not as flexible as mysqli itself).

    * Enhancements include:

    *

    * Logs all queries with an optional username except select statements

    * ie, anything that will change the system ( insert, delete, update,

    * create, drop, alter, etc... ). Statement are logged with a date/time

    * stamp, the user who performed the function, and the query executed

    * 

    * Errors are simply trapped and the message is stored in the public

    * member $error. Functions which find an error return boolean false

    *

    * Public Functions are:

    *    __construct   -- creates connection

    *    doSQL         -- executes an SQL query or DDL

    *    errors        -- returns all errors not yet cleared

    *                     can also directly access via public member errors

    *

    *    public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {

    * Author: R. W. Rodolico (rodo@dailydata.net)

    * Date: 2018-04-30

    * 

    */

   class DBQuery extends mysqli {

      /* everything is in the one $parameters array, which can then

       * be set/saved/loaded as needed

       */

      protected $parameters = array(

             // query(s) to be run

             'query' => '',

             // additional clause to be added to where on a statement

             'whereClause' => '',

             // additional order by clause to be added

             'orderBy' => '',

             // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select

             'rowsAffected' => '',

             // the value of the last row inserted

             'lastInsertKey' => '',

             // an array that contains the data retrieved by a query (select only)

             'returnData' => array(),

             // an array that contains the meta data from the query for each column

             'columnMetaData' => array(),

             // number of columns (fields) returned by query (select only)

             'numfields' => 0,

             // human readable form of fetchType

             'returnType' => 'both',

             // array of any errors which occurred

             'error' => array(),

             // if set to a table name, all modifying queries are written to it

             // must contain, at a minimum, columns whenrun timestamp, whoran varchar and query text

             // can be created with $this->buildAuditTable

             'auditTable' => '_activity_log'

           );

      public function __construct( $server, $username, $password, $database ) {

         parent::__construct(  $server, $username, $password, $database );

      }

      /*

       * static function which simply parses out an error and returns

       * a string suitable for printing. NOTE: it includes line returns

      */

      public static function error2String( $error ) {

         $return = array();

         for ( $i = 0; $i < count( $error ); $i++ ) {

            $return[] = implode( "\n", array( 'Error ' . $error[$i]['id'] . ' - ' . $error[$i]['message'], 'while executing query', $error[$i]['query'] ) );

         }

         return implode( "\n\n", $return ) . "\n";

      } // error2String

      // simply returns parameters for saving and reloading later

      public function save() {

         return $this->parameters;

      } // function save

      // loads parameters from $this->save(), or hand built

      public function load( $parameters = array() ) {

         $this->parameters = $parameters;

      } // function load

      // sets a key/value pair in $this->parameters

      // returns $value

      public function __set( $name, $value ) {

         $this->parameters[$name] = $value;

         return $value;

      }

      // gets the current value of $this->parameters[$name]

      public function __get( $name ) {

         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;

      }

      // returns true if $parameters[$name] is set

      public function __isset( $name ) {

         return isset( $this->parameters[$name] );

      }

      /*

       * function: buildAuditTable

       * parameters:

       *       $tablename - name of table to be built

       *       $createStatement - SQL DDL to build the table

       *

       * If $tablename is set, will use that, otherwise will use

       * $parameters[auditTable]. In either case, $parameters[auditTable]

       * is set to the value of the table used

       * 

       * if $createStatement is set, will be run WITHOUT MODIFICATION, and

       * $parameters[auditTable] is not set to anything (unless $tablename

       * is set)

       *

       * If $createStatement is not set, will use a default to build

       * a table from $parameters[auditTable].

       *

       * Can definitely blow up if the table name is not set both places

       * or if $createStatement is fubar

       */

      public function buildAuditTable( $tablename = '', $createStatement = '' ) {

         if ( $tablename ) // they sent us one, so set it

            $this->parameters[ 'auditTable' ] = $tablename;

         if ( ! $createStatement ) { // they did not set createStatement, so use our default

            $auditTable = $this->parameters['auditTable'];

            $createStatement = "

               create table if not exists $auditTable (

                  _activity_log_id int unsigned not null auto_increment,

                  timestamp  timestamp,

                  user       varchar(64),

                  query      text,

                  primary key(_activity_log_id)

               ) comment 'tracks queries which modify data'";

         } // if

         if ( parent::query( $createStatement ) === false ) {

            // on error, die

            print "Can not create audit table with query<br />\n$createStatement<br />\n";

            die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );

         } // if error, die

      } // buildAuditTable

      /*

       * log queries to a table, file, or nothing

       * log contains date/time, username and query

       * to turn off logginging:

       * unset( $parameters['auditTable'] )

       */

      private function logIt( $username, $query, $recursion = false ) {

         if ( ! isset( $this->parameters['auditTable'] ) )

            return; 

         $username = $this->real_escape_string( $username );

         $query = $this->real_escape_string( $query );

         $logEntry = "insert into " . $this->parameters['auditTable'] . " (user, query) values ( '$username', '$query')";

         //print "Loggging\n$logEntry\n";

         if ( parent::query( $logEntry ) !== false ) { // good

            return;

         } else { // we had an error

            if ( ! $recursion && $this->errno == 1146 ) { // table doesn't exist, so let's create it

               $result = parent::query( "show tables like '" . $this->parameters['auditTable'] . "'" );

               if ( $result->num_rows == 0 ) {

                  $this->buildAuditTable( );

                  return $this->logIt( $username, $query, true );

               }

            } else {

               print "Trying to log transaction with query<br />\n$logEntry<br />\n";

               die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );

            } // if..else

         } // if

      } // function logIt

      /*

       * doSQL

       * Parameters: $query - string or array of strings to be executed

       *             $parameters - hash used to pass additional parameters, to include

       *                           $parameters['username'] = 'fred'; // username for logging

       *                           $parameters['returnType'] = 'hash'; or array or both

       * 

       * executes one or more queries

       *

       * If the query is one of select, show, describe or explain, it must

       * be a single string. It will return the data results in a hash

       * containing

       *    'returnData'  - an array of array/hash/both depending on what you asked for

       *    'count' - number of results (ie, count(data)

       *    'meta'  - metadata for each column returned

       *    'numfields' - number of columns in result (ie, count(meta))

       *    'errors' - normally empty array of errors

       * 

       * if the query modifies data (ie, NOT above), query may be an array

       * which will be surrounded by a transaction and rolled back if

       * anything causes an error.

       *

       * These will return a hash containing

       *    'count' l- number of rows affected by last statement

       *    'last_insert_id' - last insert id created by BLOCK of queries

       *    'errors' - normally empty array of errors which occurred (caused a rollback)

       * 

       */

      public function doSQL( $query = null, $parameters = array() ) {

         $errors = array();

         if ( isset( $query ) ) {

            $this->parameters['query'] = $query;

         }

         if ( isset( $parameters['returnType'] ) ) {

            $this->parameters['returnType'] = $parameters['returnType'];

         }

         // if it is a "selectstatement" it doesn't modify data

         // if query is an array, assume it modifies something

         // if it is a single statement, look for the regex

         $selectStatement = is_array( $this->parameters['query'] ) ?

            false :

            ( preg_match( '/^\s*(select)|(show)|(describe)|(explain)/xsi', $this->parameters['query'] ) === 1 );

         // different actions based on whether it modifies data or not

         if ( $selectStatement ) { // if a select, simply return the rows

            // dataset is small enough, we just read it into memory all at one time.

            // set MYSQLI_USE_RESULT for unbuffered results

            if ( $sth = parent::query( $this->parameters['query'], MYSQLI_USE_RESULT  ) ) {

               if ( $sth === false ) {

                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error );

               } else {

                  // query can return either an array of array, array of hash, or both. Default is both

                  $returnType = MYSQLI_BOTH;

                  $this->parameters['columnMetaData'] = $sth->fetch_fields(); // get metadata

                  $this->parameters['returnData'] = array(); // we'll put all the results in an array

                  // $fetchtype returns either an array of array, array of hash, or both. Default is both

                  switch ( $this->parameters['returnType'] ) {

                     case 'array': 

                        $returnType = MYSQLI_NUM;

                        break;

                     case 'associative':

                        $returnType = MYSQLI_ASSOC;

                        break;

                     default: $returnType = MYSQLI_BOTH;

                  }

                  // slurp all the stuff in

                  while ( $values =  $sth->fetch_array( $returnType ) ) {

                     $this->parameters['returnData'][] = $values;

                  }

                  $sth->free(); // cleanup memory, don't need two copies

               } // if we had no errors

            }

            $this->parameters[ 'rowsAffected' ] = count( $this->parameters[ 'returnData' ] );

            $this->parameters[ 'numfields' ] = count( $this->parameters['columnMetaData'] );

            $this->parameters[ 'lastInsertKey' ] = 0;

            $this->parameters[ 'error' ] = $errors;

         } else {

            if ( ! is_array( $this->parameters['query'] ) ) { // not an array, so make it one

               $temp = $this->parameters['query'];

               $this->parameters['query'] = array( $temp );

            }

            // do it in a transaction so we can back out on failure

            $this->autocommit(false);

            $allOk = true;

            for ( $i = 0; $i < count( $this->parameters['query'] ); $i++ ) {

               // debugging

               //print "$i\t" . $this->parameters['query'][$i] ."\n"; continue;

               // debugging

               $this->logIt( isset( $parameters['username'] ) ? $parameters['username'] : 'unknown', $this->parameters['query'][$i] );

               if ( parent::query( $this->parameters['query'][$i] ) === false ) { // we had an erorr

                  // record it

                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error, 'query' => $this->parameters['query'][$i] );

                  $allOk = false;

                  // and bail

                  break;

               }

            }

            // if we made it through ok, commit, otherwise rollback

            $allOk ? $this->commit() : $this->rollback();

            // reset autocommit to true

            $this->autocommit(true);

            $this->parameters['query'] = $query;

            $this->parameters[ 'rowsAffected' ] = $this->affected_rows;

            $this->parameters[ 'lastInsertKey' ] = $this->insert_id;

            $this->parameters[ 'error' ] = $errors;

         } // if select .. else

         return $this->parameters;

      }  // function doSQL

      public function run () {

         return $this->doSQL( );

      }

      /**

       * Simple function returns last inserted id on success, false on 

       * failure

       * 

       * @params string $query An insert query to execute

       * @return integer The insert id from this insert, if applicable

       */

      public function insert( $query ) {

         return $this->query( $query ) ? $this->insert_id : false;

      }

       /**

        * A query with at least two columns in the result is converted to a 

        * key => value array. The first column is the key, the second is the value

        * All other columns are ignored

        */

       public function queryToKeyedArray ( $query ) {

          $return = array();

         $result = $this->doSQL( $query, array( 'returnType' => 'array' ) );

         foreach ( $result['returnData'] as $row ) {

            $return[$row[0]] = $row[1];

         }

         return $return;

      }

      /**

       * returns an array of the first column for each row returned from query

       * 

       * The query is run, then for each row returns, the first column

       * is added to $return (an array). $return is then returned.

       * 

       * Used to do things like get an array of keyfields, or something

       * else.

       * 

       * @param string $query Query to run

       * @returns string[] Array of values

       */

      public function columnToArray( $query ) {

         $return = array();

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

         foreach ( $result['returnData'] as $row ) {

            $return[] = $row['id'];

         }

         return $return;

      }

      /*

       * function will return one and only one row, NOT as an array of array

       * but as a single row array

       * if more than one row is returned by query, error is set and function

       * returns false.

       * Otherwise, function returns true

       */

      public function getOneRow( $sql = null ) {

         if ( isset( $sql ) )

            $this->parameters[ 'query' ] = $sql;

         $save = $this->parameters[ 'returnType' ];

         //print "<pre>" . print_r( $this->parameters['query'], true) . '</pre>';

         $this->parameters['returnType'] = 'associative';

         $this->run();

         $this->parameters[ 'returnType' ] = $save;

         if ( $this->parameters[ 'rowsAffected' ] == 0 ) {

            return array();

         } elseif ( $this->parameters[ 'rowsAffected' ] == 1 ) {

            $this->parameters[ 'returnData' ] = $this->parameters[ 'returnData' ][0];

            return $this->parameters[ 'returnData' ];

         } else {

            $this->parameters['error'] = "$this->parameters[query] did not return a unique row";

            print "<pre>In Class DBQuery->getOneRow, no unique row in\n" . print_r($this->parameters[query], true) . "\n</pre>"; die;

         }

         return false;

      } // getOneRow

      // function returns the first column of the first row of data returned from query

      // or null no value returned

      public function getOneDBValue( $sql = null ) {

         //print '<pre>' . $sql . '</pre>';

         if ( isset( $sql ) )

            $this->parameters[ 'query' ] = $sql;

         $save = $this->parameters['returnType'];

         $this->parameters['returnType'] = 'array';

         $this->run();

         $this->parameters['returnType'] = $save;

         //print "<pre>" . print_r($this->parameters,true ) . "</pre>";

         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;

      }

      /*

       * function will attempt to make a constant ($value) safe for SQL depending on the type.

       * 

       * if $value is empty, $default is returned, as will happen if any of the

       * conversions (date, datetime, etc...) fail.

       * 

       * First, it will pass it through get_magic_quotes_gpc, 

       * then will run through mysql_real_escape_string

       * 

       * For strings, will encapsulate in quotes

       * Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes

       * DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS

       * Integer and Floats are passed through builtins intval and floatval

       * Boolean only checks the first character, a '0', 'f' and 'n' denoting false

       *    all else denoting true. The result is converted based on the variable

       *    $falsetrue, with the first char denoting false and the second denoting true

       */

      public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {

         if (strlen($value) == 0) // simply set any empty values to null

            return $default;

         // print "Processing $value as $type with default $default<br>\n";

         switch ( strtolower( $type ) ) {

            case 'string' :

            case 's' : 

                     if ( get_magic_quotes_gpc() ) 

                        $value = stripslashes($value);

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

                     $value = strlen( $value ) > 0 ? "'$value'" : $default;

                     break;

            case 'date' :

            case 'd' :

                     if ( $value != 'null' ) {

                        $result = strtotime( $value );

                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";

                     }

                     break;

            case 'datetime':

            case 'timestamp':

            case 'dt': 

                     if ( $value != 'null' ) {

                        $result = strtotime( $value );

                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";

                     }

                     break;

            case 'integer':

            case 'i' :  

                     $value = intval( $value );

                     break;

            case 'float':

            case 'f' :  

                     $value = floatval( $value );

                     break;

            case 'bool':

            case 'boolean':

            case 'b' :  // note, because of the way strpos works, you can not

                        // simply set $value based on the output; you MUST do

                        // as below; specifically check for false, then set the result

                        $value =  strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;

                        $value = substr( $falsetrue, $value, 0, 1 );

                        break;

         } // switch

         return $value;

      }

      /*

         '[^']*(?!\\)'(*SKIP)(*F)       # Make sure we're not matching inside of quotes

         |(?m-s:\s*(?:\-{2}|\#)[^\n]*$) # Single line comment

         |(?:

           \/\*.*?\*\/                  # Multi-line comment

           (?(?=(?m-s:\h+$))         # Get trailing whitespace if any exists and only if it's the rest of the line

             \h+

           )

         )

      */

      /**

       * Functions strips SQL queries from a file. Above is a commented copy of the regex used

       * 

       * @param string $query An arbitrary sized SQL script

       * @returns string $query, with all SQL comments removed

       */

      public function strip_sqlcomment ($query = '') {

         $regex = '\'[^\']*(?!\\\)\'(*SKIP)(*F)|(?m-s:\\s*(?:\\-{2}|\\#)[^\\n]*$)|(?:\\/\\*.*?\\*\\/(?(?=(?m-s:\h+$))\\h+))';

         //print "<pre>$regex</pre>" ; die;

          return (($query == '') ?  '' : preg_replace( "/$regex/xs", '', $query ));

      }

      /**

       * runs an SQL script with multiple statements in it

       * 

       * If $sql is an array, it is assumed each row is a separate query

       * 

       * If $sql is a string, will separate it into different lines and

       * execute them

       * 

       * NOTE: it does this by breaking it based on the semicolon, so

       * in some weird situations, it will break at the wrong place.

       * 

       */

      public function runSQLScript( $sql ) {

         if ( is_array( $sql ) ) {

            $queries = $sql;

         } else {

            $sql = $this->strip_sqlcomment( $sql );

            $queries = explode( ";", $sql );

         }

         //print "<pre>" . print_r( $queries, true ) . '</pre>'; die;

         foreach ( $queries as $query ) {

            $this->doSQL( trim( implode( ' ', explode("\n",$query ) ) ) );

         }

      } // runSQLScript

      /**

       * Overrides real_escape_string to change behaviour slightly

       * 

       * Will check if string is pure numeric and, if it is, will return

       * as it is. Otherwise, will call real_escape_string, then wrap

       * result in single quotes

       */

       public function my_escape_string( $string, $additionalEscapes = array() ) {

          if ( is_numeric( $string ) )

             return $string;

          $string = $this->real_escape_string( $string );

          if ( count( $additionalEscapes ) ) 

             $string = addcslashes( $string, implode( '', $additionalEscapes ) );

          return "'$string'";

       }

      /**

       * Builds a query of form update $tablename set $fields where $where

       * 

       * Creates a query that will update table $tablename. It assumes

       * $fields is an array where the indexes are fieldnames and the values

       * are the new values for the field. Will escape the values.

       * 

       * Appends $where, again where index is a field name

       */

      public function updateQuery( $tablename, $where, $fields ) {

         /*

         print "<pre>updateQuery\n\ntable\n$tablename\n</pre>";

         print "<pre>\nwhere\n" . print_r( $where, true ) . "\n</pre>";

         print "<pre>fields\n" . print_r( $fields, true ) . "\n</pre>"; die;

         */

         $sql = '';

         $updateFields = array();

         foreach ( $fields as $fieldname => $value ) {

            $updateFields[] = sprintf( "%s = %s", $fieldname, $this->my_escape_string( $value ) );

         }

         $sql = "update $tablename set " . implode( ", ", $updateFields );

         $updateFields = array();

         foreach ( $where as $fieldname => $value ) {

            $updateFields[] = sprintf( "%s = %s", $fieldname, $this->my_escape_string( $value ) );

         }

         if ( count( $updateFields ) ) {

            $sql .= ' where ' . implode( ' and ', $updateFields );

         }

         return $sql;

      }

      /**

       * Creates an insert query from $fields

       * 

       */

       public function insertQuery( $tablename, $fields ) {

          //print "<pre>insertQuery\n\ntable\n$tablename\n</pre>";

          //print "<pre>fields\n" . print_r( $fields, true ) . "\n</pre>"; die;

          $query = "insert into $tablename (" . implode( ',',array_keys($fields) );

          $query .= ") values (" . implode( ',', array_map( array($this, 'my_escape_string'), array_values( $fields ) ) );

          return "$query)";

       } // insertQuery

   } // class DBQuery

/*

 *    $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );

   if ($db->connect_error) {

       die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);

   }

   $result = $db->doSQL(

               array(

                     'drop table if exists temp',

                     'create table temp ( col1 int unsigned )',

                     "insert into temp values ('mike')"

                     )

         );

   if ( $result['error'] ) {

      print_r ( $result );

      die ( DBQuery::error2String( $result['error'] ) );

   } else {

      print "running select\n";

      $result = $db->doSQL( 'select * from temp' );

      print_r( $result );

   }

//   $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'" );

//   print_r( $return );

//   print_r( $db );

*/

?>