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 |
// human readable form of fetchType
|
36 |
rodolico |
56 |
'returnType' => 'both',
|
13 |
rodolico |
57 |
// array of any errors which occurred
|
|
|
58 |
'error' => array(),
|
|
|
59 |
// if set to a table name, all modifying queries are written to it
|
|
|
60 |
// must contain, at a minimum, columns whenrun timestamp, whoran varchar and query text
|
|
|
61 |
// can be created with $this->buildAuditTable
|
|
|
62 |
'auditTable' => '_activity_log'
|
|
|
63 |
);
|
|
|
64 |
|
37 |
rodolico |
65 |
public function __construct( $server, $username, $password, $database ) {
|
|
|
66 |
parent::__construct( $server, $username, $password, $database );
|
|
|
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
|
36 |
rodolico |
186 |
* $parameters['returnType'] = 'hash'; or array or both
|
13 |
rodolico |
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
|
39 |
rodolico |
193 |
* 'returnData' - an array of array/hash/both depending on what you asked for
|
13 |
rodolico |
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 |
}
|
42 |
rodolico |
214 |
if ( isset( $parameters['returnType'] ) ) {
|
|
|
215 |
$this->parameters['returnType'] = $parameters['returnType'];
|
|
|
216 |
}
|
13 |
rodolico |
217 |
// if it is a "selectstatement" it doesn't modify data
|
|
|
218 |
// if query is an array, assume it modifies something
|
|
|
219 |
// if it is a single statement, look for the regex
|
15 |
rodolico |
220 |
$selectStatement = is_array( $this->parameters['query'] ) ?
|
13 |
rodolico |
221 |
false :
|
29 |
rodolico |
222 |
( preg_match( '/^\s*(select)|(show)|(describe)|(explain)/xsi', $this->parameters['query'] ) === 1 );
|
13 |
rodolico |
223 |
|
|
|
224 |
// different actions based on whether it modifies data or not
|
|
|
225 |
if ( $selectStatement ) { // if a select, simply return the rows
|
|
|
226 |
// dataset is small enough, we just read it into memory all at one time.
|
42 |
rodolico |
227 |
// set MYSQLI_USE_RESULT for unbuffered results
|
15 |
rodolico |
228 |
if ( $sth = parent::query( $this->parameters['query'], MYSQLI_USE_RESULT ) ) {
|
13 |
rodolico |
229 |
if ( $sth === false ) {
|
|
|
230 |
$errors[] = array( 'id' => $this->errno, 'message' => $this->error );
|
|
|
231 |
} else {
|
42 |
rodolico |
232 |
// query can return either an array of array, array of hash, or both. Default is both
|
47 |
rodolico |
233 |
//$returnType = MYSQLI_BOTH;
|
13 |
rodolico |
234 |
$this->parameters['columnMetaData'] = $sth->fetch_fields(); // get metadata
|
47 |
rodolico |
235 |
// in newer PHP, this is an array of objects, so we turn it into an array of array
|
|
|
236 |
foreach ( $this->parameters['columnMetaData'] as $index => $obj ) {
|
|
|
237 |
if ( is_object($obj) ) {
|
|
|
238 |
$this->parameters['columnMetaData'][$index] = (array) $obj;
|
|
|
239 |
}
|
|
|
240 |
}
|
13 |
rodolico |
241 |
$this->parameters['returnData'] = array(); // we'll put all the results in an array
|
42 |
rodolico |
242 |
// $fetchtype returns either an array of array, array of hash, or both. Default is both
|
|
|
243 |
switch ( $this->parameters['returnType'] ) {
|
|
|
244 |
case 'array':
|
|
|
245 |
$returnType = MYSQLI_NUM;
|
|
|
246 |
break;
|
|
|
247 |
case 'associative':
|
|
|
248 |
$returnType = MYSQLI_ASSOC;
|
|
|
249 |
break;
|
|
|
250 |
default: $returnType = MYSQLI_BOTH;
|
13 |
rodolico |
251 |
}
|
|
|
252 |
// slurp all the stuff in
|
42 |
rodolico |
253 |
while ( $values = $sth->fetch_array( $returnType ) ) {
|
38 |
rodolico |
254 |
$this->parameters['returnData'][] = $values;
|
13 |
rodolico |
255 |
}
|
|
|
256 |
$sth->free(); // cleanup memory, don't need two copies
|
|
|
257 |
} // if we had no errors
|
1 |
rodolico |
258 |
}
|
13 |
rodolico |
259 |
$this->parameters[ 'rowsAffected' ] = count( $this->parameters[ 'returnData' ] );
|
|
|
260 |
$this->parameters[ 'numfields' ] = count( $this->parameters['columnMetaData'] );
|
|
|
261 |
$this->parameters[ 'lastInsertKey' ] = 0;
|
|
|
262 |
$this->parameters[ 'error' ] = $errors;
|
|
|
263 |
} else {
|
|
|
264 |
if ( ! is_array( $this->parameters['query'] ) ) { // not an array, so make it one
|
|
|
265 |
$temp = $this->parameters['query'];
|
|
|
266 |
$this->parameters['query'] = array( $temp );
|
1 |
rodolico |
267 |
}
|
13 |
rodolico |
268 |
// do it in a transaction so we can back out on failure
|
|
|
269 |
$this->autocommit(false);
|
|
|
270 |
$allOk = true;
|
|
|
271 |
for ( $i = 0; $i < count( $this->parameters['query'] ); $i++ ) {
|
15 |
rodolico |
272 |
// debugging
|
|
|
273 |
//print "$i\t" . $this->parameters['query'][$i] ."\n"; continue;
|
|
|
274 |
// debugging
|
13 |
rodolico |
275 |
$this->logIt( isset( $parameters['username'] ) ? $parameters['username'] : 'unknown', $this->parameters['query'][$i] );
|
|
|
276 |
if ( parent::query( $this->parameters['query'][$i] ) === false ) { // we had an erorr
|
|
|
277 |
// record it
|
|
|
278 |
$errors[] = array( 'id' => $this->errno, 'message' => $this->error, 'query' => $this->parameters['query'][$i] );
|
|
|
279 |
$allOk = false;
|
|
|
280 |
// and bail
|
|
|
281 |
break;
|
|
|
282 |
}
|
|
|
283 |
}
|
|
|
284 |
// if we made it through ok, commit, otherwise rollback
|
|
|
285 |
$allOk ? $this->commit() : $this->rollback();
|
|
|
286 |
// reset autocommit to true
|
|
|
287 |
$this->autocommit(true);
|
|
|
288 |
$this->parameters['query'] = $query;
|
|
|
289 |
$this->parameters[ 'rowsAffected' ] = $this->affected_rows;
|
|
|
290 |
$this->parameters[ 'lastInsertKey' ] = $this->insert_id;
|
|
|
291 |
$this->parameters[ 'error' ] = $errors;
|
|
|
292 |
} // if select .. else
|
|
|
293 |
return $this->parameters;
|
|
|
294 |
} // function doSQL
|
17 |
rodolico |
295 |
|
|
|
296 |
public function run () {
|
|
|
297 |
return $this->doSQL( );
|
|
|
298 |
}
|
34 |
rodolico |
299 |
|
|
|
300 |
/**
|
44 |
rodolico |
301 |
* Simple function returns last inserted id on success, false on
|
40 |
rodolico |
302 |
* failure
|
|
|
303 |
*
|
|
|
304 |
* @params string $query An insert query to execute
|
|
|
305 |
* @return integer The insert id from this insert, if applicable
|
|
|
306 |
*/
|
|
|
307 |
|
|
|
308 |
public function insert( $query ) {
|
|
|
309 |
return $this->query( $query ) ? $this->insert_id : false;
|
|
|
310 |
}
|
|
|
311 |
|
44 |
rodolico |
312 |
/**
|
|
|
313 |
* A query with at least two columns in the result is converted to a
|
|
|
314 |
* key => value array. The first column is the key, the second is the value
|
|
|
315 |
* All other columns are ignored
|
|
|
316 |
*/
|
|
|
317 |
|
|
|
318 |
public function queryToKeyedArray ( $query ) {
|
|
|
319 |
$return = array();
|
|
|
320 |
$result = $this->doSQL( $query, array( 'returnType' => 'array' ) );
|
|
|
321 |
foreach ( $result['returnData'] as $row ) {
|
|
|
322 |
$return[$row[0]] = $row[1];
|
|
|
323 |
}
|
|
|
324 |
return $return;
|
|
|
325 |
}
|
|
|
326 |
|
40 |
rodolico |
327 |
/**
|
34 |
rodolico |
328 |
* returns an array of the first column for each row returned from query
|
|
|
329 |
*
|
|
|
330 |
* The query is run, then for each row returns, the first column
|
|
|
331 |
* is added to $return (an array). $return is then returned.
|
|
|
332 |
*
|
|
|
333 |
* Used to do things like get an array of keyfields, or something
|
|
|
334 |
* else.
|
|
|
335 |
*
|
|
|
336 |
* @param string $query Query to run
|
|
|
337 |
* @returns string[] Array of values
|
|
|
338 |
*/
|
|
|
339 |
public function columnToArray( $query ) {
|
|
|
340 |
$return = array();
|
|
|
341 |
$result = $this->doSQL( $query );
|
|
|
342 |
foreach ( $result['returnData'] as $row ) {
|
|
|
343 |
$return[] = $row['id'];
|
|
|
344 |
}
|
|
|
345 |
return $return;
|
|
|
346 |
}
|
1 |
rodolico |
347 |
|
|
|
348 |
/*
|
|
|
349 |
* function will return one and only one row, NOT as an array of array
|
|
|
350 |
* but as a single row array
|
|
|
351 |
* if more than one row is returned by query, error is set and function
|
|
|
352 |
* returns false.
|
|
|
353 |
* Otherwise, function returns true
|
|
|
354 |
*/
|
|
|
355 |
public function getOneRow( $sql = null ) {
|
|
|
356 |
if ( isset( $sql ) )
|
6 |
rodolico |
357 |
$this->parameters[ 'query' ] = $sql;
|
36 |
rodolico |
358 |
$save = $this->parameters[ 'returnType' ];
|
|
|
359 |
//print "<pre>" . print_r( $this->parameters['query'], true) . '</pre>';
|
43 |
rodolico |
360 |
$this->parameters['returnType'] = 'associative';
|
1 |
rodolico |
361 |
$this->run();
|
43 |
rodolico |
362 |
$this->parameters[ 'returnType' ] = $save;
|
|
|
363 |
if ( $this->parameters[ 'rowsAffected' ] == 0 ) {
|
|
|
364 |
return array();
|
|
|
365 |
} elseif ( $this->parameters[ 'rowsAffected' ] == 1 ) {
|
6 |
rodolico |
366 |
$this->parameters[ 'returnData' ] = $this->parameters[ 'returnData' ][0];
|
35 |
rodolico |
367 |
return $this->parameters[ 'returnData' ];
|
37 |
rodolico |
368 |
} else {
|
43 |
rodolico |
369 |
$this->parameters['error'] = "$this->parameters[query] did not return a unique row";
|
|
|
370 |
print "<pre>In Class DBQuery->getOneRow, no unique row in\n" . print_r($this->parameters[query], true) . "\n</pre>"; die;
|
37 |
rodolico |
371 |
}
|
1 |
rodolico |
372 |
return false;
|
|
|
373 |
} // getOneRow
|
|
|
374 |
|
|
|
375 |
|
|
|
376 |
// function returns the first column of the first row of data returned from query
|
|
|
377 |
// or null no value returned
|
6 |
rodolico |
378 |
public function getOneDBValue( $sql = null ) {
|
34 |
rodolico |
379 |
//print '<pre>' . $sql . '</pre>';
|
1 |
rodolico |
380 |
if ( isset( $sql ) )
|
6 |
rodolico |
381 |
$this->parameters[ 'query' ] = $sql;
|
36 |
rodolico |
382 |
$save = $this->parameters['returnType'];
|
|
|
383 |
$this->parameters['returnType'] = 'array';
|
1 |
rodolico |
384 |
$this->run();
|
36 |
rodolico |
385 |
$this->parameters['returnType'] = $save;
|
|
|
386 |
//print "<pre>" . print_r($this->parameters,true ) . "</pre>";
|
6 |
rodolico |
387 |
return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;
|
1 |
rodolico |
388 |
}
|
6 |
rodolico |
389 |
|
4 |
rodolico |
390 |
/*
|
|
|
391 |
* function will attempt to make a constant ($value) safe for SQL depending on the type.
|
|
|
392 |
*
|
|
|
393 |
* if $value is empty, $default is returned, as will happen if any of the
|
|
|
394 |
* conversions (date, datetime, etc...) fail.
|
|
|
395 |
*
|
|
|
396 |
* First, it will pass it through get_magic_quotes_gpc,
|
|
|
397 |
* then will run through mysql_real_escape_string
|
|
|
398 |
*
|
|
|
399 |
* For strings, will encapsulate in quotes
|
|
|
400 |
* Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes
|
|
|
401 |
* DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS
|
|
|
402 |
* Integer and Floats are passed through builtins intval and floatval
|
|
|
403 |
* Boolean only checks the first character, a '0', 'f' and 'n' denoting false
|
|
|
404 |
* all else denoting true. The result is converted based on the variable
|
|
|
405 |
* $falsetrue, with the first char denoting false and the second denoting true
|
|
|
406 |
*/
|
|
|
407 |
public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
|
|
|
408 |
if (strlen($value) == 0) // simply set any empty values to null
|
|
|
409 |
return $default;
|
|
|
410 |
// print "Processing $value as $type with default $default<br>\n";
|
|
|
411 |
switch ( strtolower( $type ) ) {
|
|
|
412 |
case 'string' :
|
|
|
413 |
case 's' :
|
|
|
414 |
if ( get_magic_quotes_gpc() )
|
|
|
415 |
$value = stripslashes($value);
|
13 |
rodolico |
416 |
$value = $this->real_escape_string( $value );
|
4 |
rodolico |
417 |
$value = strlen( $value ) > 0 ? "'$value'" : $default;
|
|
|
418 |
break;
|
|
|
419 |
case 'date' :
|
|
|
420 |
case 'd' :
|
|
|
421 |
if ( $value != 'null' ) {
|
|
|
422 |
$result = strtotime( $value );
|
|
|
423 |
$value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";
|
|
|
424 |
}
|
|
|
425 |
break;
|
|
|
426 |
case 'datetime':
|
|
|
427 |
case 'timestamp':
|
|
|
428 |
case 'dt':
|
|
|
429 |
if ( $value != 'null' ) {
|
|
|
430 |
$result = strtotime( $value );
|
|
|
431 |
$value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";
|
|
|
432 |
}
|
|
|
433 |
break;
|
|
|
434 |
case 'integer':
|
|
|
435 |
case 'i' :
|
|
|
436 |
$value = intval( $value );
|
|
|
437 |
break;
|
|
|
438 |
case 'float':
|
|
|
439 |
case 'f' :
|
|
|
440 |
$value = floatval( $value );
|
|
|
441 |
break;
|
|
|
442 |
case 'bool':
|
|
|
443 |
case 'boolean':
|
|
|
444 |
case 'b' : // note, because of the way strpos works, you can not
|
|
|
445 |
// simply set $value based on the output; you MUST do
|
|
|
446 |
// as below; specifically check for false, then set the result
|
|
|
447 |
$value = strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;
|
|
|
448 |
$value = substr( $falsetrue, $value, 0, 1 );
|
|
|
449 |
break;
|
|
|
450 |
} // switch
|
|
|
451 |
return $value;
|
|
|
452 |
}
|
1 |
rodolico |
453 |
|
29 |
rodolico |
454 |
/*
|
|
|
455 |
'[^']*(?!\\)'(*SKIP)(*F) # Make sure we're not matching inside of quotes
|
|
|
456 |
|(?m-s:\s*(?:\-{2}|\#)[^\n]*$) # Single line comment
|
|
|
457 |
|(?:
|
|
|
458 |
\/\*.*?\*\/ # Multi-line comment
|
|
|
459 |
(?(?=(?m-s:\h+$)) # Get trailing whitespace if any exists and only if it's the rest of the line
|
|
|
460 |
\h+
|
|
|
461 |
)
|
|
|
462 |
)
|
|
|
463 |
*/
|
|
|
464 |
/**
|
|
|
465 |
* Functions strips SQL queries from a file. Above is a commented copy of the regex used
|
|
|
466 |
*
|
|
|
467 |
* @param string $query An arbitrary sized SQL script
|
|
|
468 |
* @returns string $query, with all SQL comments removed
|
|
|
469 |
*/
|
4 |
rodolico |
470 |
|
29 |
rodolico |
471 |
public function strip_sqlcomment ($query = '') {
|
|
|
472 |
$regex = '\'[^\']*(?!\\\)\'(*SKIP)(*F)|(?m-s:\\s*(?:\\-{2}|\\#)[^\\n]*$)|(?:\\/\\*.*?\\*\\/(?(?=(?m-s:\h+$))\\h+))';
|
|
|
473 |
//print "<pre>$regex</pre>" ; die;
|
|
|
474 |
return (($query == '') ? '' : preg_replace( "/$regex/xs", '', $query ));
|
|
|
475 |
}
|
|
|
476 |
|
|
|
477 |
/**
|
|
|
478 |
* runs an SQL script with multiple statements in it
|
|
|
479 |
*
|
37 |
rodolico |
480 |
* If $sql is an array, it is assumed each row is a separate query
|
|
|
481 |
*
|
|
|
482 |
* If $sql is a string, will separate it into different lines and
|
|
|
483 |
* execute them
|
|
|
484 |
*
|
29 |
rodolico |
485 |
* NOTE: it does this by breaking it based on the semicolon, so
|
|
|
486 |
* in some weird situations, it will break at the wrong place.
|
|
|
487 |
*
|
|
|
488 |
*/
|
|
|
489 |
public function runSQLScript( $sql ) {
|
37 |
rodolico |
490 |
if ( is_array( $sql ) ) {
|
|
|
491 |
$queries = $sql;
|
|
|
492 |
} else {
|
|
|
493 |
$sql = $this->strip_sqlcomment( $sql );
|
|
|
494 |
$queries = explode( ";", $sql );
|
|
|
495 |
}
|
29 |
rodolico |
496 |
//print "<pre>" . print_r( $queries, true ) . '</pre>'; die;
|
|
|
497 |
foreach ( $queries as $query ) {
|
|
|
498 |
$this->doSQL( trim( implode( ' ', explode("\n",$query ) ) ) );
|
|
|
499 |
}
|
37 |
rodolico |
500 |
} // runSQLScript
|
|
|
501 |
|
|
|
502 |
/**
|
|
|
503 |
* Overrides real_escape_string to change behaviour slightly
|
|
|
504 |
*
|
|
|
505 |
* Will check if string is pure numeric and, if it is, will return
|
|
|
506 |
* as it is. Otherwise, will call real_escape_string, then wrap
|
|
|
507 |
* result in single quotes
|
|
|
508 |
*/
|
|
|
509 |
public function my_escape_string( $string, $additionalEscapes = array() ) {
|
|
|
510 |
if ( is_numeric( $string ) )
|
|
|
511 |
return $string;
|
|
|
512 |
$string = $this->real_escape_string( $string );
|
|
|
513 |
if ( count( $additionalEscapes ) )
|
|
|
514 |
$string = addcslashes( $string, implode( '', $additionalEscapes ) );
|
|
|
515 |
return "'$string'";
|
|
|
516 |
}
|
|
|
517 |
|
|
|
518 |
/**
|
|
|
519 |
* Builds a query of form update $tablename set $fields where $where
|
|
|
520 |
*
|
|
|
521 |
* Creates a query that will update table $tablename. It assumes
|
|
|
522 |
* $fields is an array where the indexes are fieldnames and the values
|
|
|
523 |
* are the new values for the field. Will escape the values.
|
|
|
524 |
*
|
|
|
525 |
* Appends $where, again where index is a field name
|
|
|
526 |
*/
|
|
|
527 |
public function updateQuery( $tablename, $where, $fields ) {
|
|
|
528 |
/*
|
|
|
529 |
print "<pre>updateQuery\n\ntable\n$tablename\n</pre>";
|
|
|
530 |
print "<pre>\nwhere\n" . print_r( $where, true ) . "\n</pre>";
|
|
|
531 |
print "<pre>fields\n" . print_r( $fields, true ) . "\n</pre>"; die;
|
|
|
532 |
*/
|
|
|
533 |
$sql = '';
|
|
|
534 |
$updateFields = array();
|
|
|
535 |
foreach ( $fields as $fieldname => $value ) {
|
|
|
536 |
$updateFields[] = sprintf( "%s = %s", $fieldname, $this->my_escape_string( $value ) );
|
|
|
537 |
}
|
|
|
538 |
$sql = "update $tablename set " . implode( ", ", $updateFields );
|
|
|
539 |
$updateFields = array();
|
|
|
540 |
foreach ( $where as $fieldname => $value ) {
|
|
|
541 |
$updateFields[] = sprintf( "%s = %s", $fieldname, $this->my_escape_string( $value ) );
|
|
|
542 |
}
|
|
|
543 |
if ( count( $updateFields ) ) {
|
|
|
544 |
$sql .= ' where ' . implode( ' and ', $updateFields );
|
|
|
545 |
}
|
|
|
546 |
return $sql;
|
|
|
547 |
}
|
|
|
548 |
|
|
|
549 |
/**
|
|
|
550 |
* Creates an insert query from $fields
|
|
|
551 |
*
|
|
|
552 |
*/
|
|
|
553 |
public function insertQuery( $tablename, $fields ) {
|
40 |
rodolico |
554 |
//print "<pre>insertQuery\n\ntable\n$tablename\n</pre>";
|
|
|
555 |
//print "<pre>fields\n" . print_r( $fields, true ) . "\n</pre>"; die;
|
37 |
rodolico |
556 |
$query = "insert into $tablename (" . implode( ',',array_keys($fields) );
|
40 |
rodolico |
557 |
$query .= ") values (" . implode( ',', array_map( array($this, 'my_escape_string'), array_values( $fields ) ) );
|
37 |
rodolico |
558 |
return "$query)";
|
|
|
559 |
} // insertQuery
|
44 |
rodolico |
560 |
|
46 |
rodolico |
561 |
function insertValuesIntoQuery( $query, $values ) {
|
|
|
562 |
foreach ( $values as $name => $value ) {
|
|
|
563 |
$query = $this->search_replace_string($query, "<$name>", $value );
|
|
|
564 |
}
|
|
|
565 |
return $query;
|
|
|
566 |
}
|
44 |
rodolico |
567 |
|
46 |
rodolico |
568 |
function search_replace_string($string, $searchFor, $replaceWith ) {
|
|
|
569 |
$string = str_replace ( $searchFor, $replaceWith, $string );
|
|
|
570 |
return $string;
|
|
|
571 |
}
|
|
|
572 |
|
|
|
573 |
|
37 |
rodolico |
574 |
|
29 |
rodolico |
575 |
|
1 |
rodolico |
576 |
} // class DBQuery
|
|
|
577 |
|
17 |
rodolico |
578 |
/*
|
|
|
579 |
* $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );
|
13 |
rodolico |
580 |
|
|
|
581 |
if ($db->connect_error) {
|
|
|
582 |
die('Connect Error (' . $db->connect_errno . ') ' . $db->connect_error);
|
|
|
583 |
}
|
|
|
584 |
$result = $db->doSQL(
|
|
|
585 |
array(
|
|
|
586 |
'drop table if exists temp',
|
|
|
587 |
'create table temp ( col1 int unsigned )',
|
|
|
588 |
"insert into temp values ('mike')"
|
|
|
589 |
)
|
|
|
590 |
);
|
15 |
rodolico |
591 |
if ( $result['error'] ) {
|
13 |
rodolico |
592 |
print_r ( $result );
|
15 |
rodolico |
593 |
die ( DBQuery::error2String( $result['error'] ) );
|
13 |
rodolico |
594 |
} else {
|
|
|
595 |
print "running select\n";
|
|
|
596 |
$result = $db->doSQL( 'select * from temp' );
|
|
|
597 |
print_r( $result );
|
|
|
598 |
}
|
|
|
599 |
// $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'" );
|
|
|
600 |
// print_r( $return );
|
|
|
601 |
// print_r( $db );
|
17 |
rodolico |
602 |
*/
|
1 |
rodolico |
603 |
?>
|
13 |
rodolico |
604 |
|
|
|
605 |
|
|
|
606 |
|
|
|
607 |
|
|
|
608 |
|
|
|
609 |
|
|
|
610 |
|
|
|
611 |
|
|
|
612 |
|
|
|
613 |
|