Rev 39 | Rev 43 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?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,
// type of data returned, array of array, array of hash, or both
'fetchType' => MYSQLI_BOTH,
// 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 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.
// NOTE: fetch_all is nice, but tied to mysqlnd, which has reports of problems, so we do it the old way
if ( $sth = parent::query( $this->parameters['query'], MYSQLI_USE_RESULT ) ) {
if ( $sth === false ) {
$errors[] = array( 'id' => $this->errno, 'message' => $this->error );
} else {
$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 array of hash
if ( isset( $this->parameters['returnType'] ) ) {
$this->parameters[ 'fetchType' ] = $this->parameters['returnType'] == 'array' ? MYSQLI_NUM : (
( $this->parameters['returnType'] == 'both' ) ? MYSQLI_BOTH : MYSQLI_ASSOC
);
} else { // default is both (hash and numeric)
$this->parameters[ 'fetchType' ] = MYSQLI_BOTH;
$this->parameters['returnType'] = 'both';
}
// slurp all the stuff in
while ( $values = $sth->fetch_array( $this->parameters[ 'fetchType' ] ) ) {
$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 succes, 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;
}
/**
* 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>';
$useAssociativeArray = true;
$this->run();
$useAssociativeArray = $save;
if ( $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 in getOneRow";
print_r( $this->parameters['query'] ); 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 );
*/
?>