Rev 100 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
/*
* 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
* if default is the constant now(), will pass that through to MySQL
* 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
*/
function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
// return $default on undefined or empty values
if ( ! isset( $value ) ) return $default;
if (strlen($value) == 0) 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 = mysql_real_escape_string( $value );
$value = strlen( $value ) > 0 ? "'$value'" : $default;
break;
case 'date' :
case 'd' :
if ( $value != 'null' && $value != 'now()' ) {
$result = strtotime( $value );
$value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";
}
break;
case 'datetime':
case 'timestamp':
case 'dt':
if ( $value != 'null' && $value != 'now()' ) {
$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;
}
/****************************************************************************************
* Functions to process an array the device_attrib table
***************************************************************************************/
/*
* takes a tab delimited array of lines and turns it into an array of array
* Assumes first line contains the headers
* turns the file
* header1\theader2\theader3
* value1\tvalue2\tvalue3
*
* into
* [0]
* header1=>value1
* header2=>value2
* header3=>value3
*
* $contents is an array of lines, with each line having multiple
* fields delimited by $delimiter
*
*/
function tabDelimToArray($contents, $delimiter = "\t" ) {
$rows = array();
if ( gettype($contents) != 'array' )
$contents = explode( "\n", $contents );
$headers = explode( $delimiter, array_shift( $contents ) );
for ( $line = 0; $line < count( $contents ); $line++ ) {
if ( $contents[$line] ) {
$thisLine = explode( $delimiter, $contents[$line] );
$thisLine = preg_replace( '/^\s+/', '', $thisLine );
$thisLine = preg_replace( '/\s+$/', '', $thisLine );
$columns = array();
for ( $i = 0; $i < count( $headers ); $i++ ) {
$columns[$headers[$i]] = $thisLine[$i];
} // for
$rows[] = $columns;
} // if
} // while
return $rows;
} // function
/*
* getDeviceID
*/
function getDeviceID( $device, $createIfNotFound = false, $site = '', $client = '', $site_id = 0, $client_id = 0 ) {
$device = makeSafeSQLConstant( $device, 's', '' );
if ( ! $device ) return 0;
$device_id = getOneDBValue( "select device_id from device where name = $device" );
if ( $device_id ) return $device_id;
if ( $createIfNotFound ) {
if ( ! $site_id ) {
$site_id = findSite( $site, $client, $site_id, $client_id );
}
if ( $site_id ) {
$site_id = makeSafeSQLConstant( $site_id,'i','' );
$result = queryDatabaseExtended( "insert into device (site_id,device_type_id,name,added_date,removed_date) values( $site_id,1,$device,now(),null )" );
if ( $result['insert_id'] )
return $result[insert_id];
}
}
return 0;
} // getDeviceID
function getAttributeID( $attribute, $createIfNotFound = false ) {
$attribute = makeSafeSQLConstant( $attribute, 's', '' );
if ( ! $attribute ) return 0;
$return = getOneDBValue( "select attrib_id from attrib where name = $attribute" );
if ( ! $return && $createIfNotFound ) {
$return = queryDatabaseExtended( "insert into attrib( name, added_date, removed_date ) values ( $attribute, now(), null )" );
return $return['insert_id'] ? $return['insert_id'] : 0;
}
return $return;
}
/*
* returns true if the value is empty or null
*/
function nullOrEmpty( $str ) {
return ( !isset( $str) || trim($str) === '');
}
/*
* Takes a two dimensional array and adds/updates the values in device_attrib
*
* $contents is an array of array, where they keys for each sub-array is the field name of the table to insert/update
* $unique if true will replace any existing key for the device in question
* $createDeviceIfNotFound, if true, will create any device name found if it doesn't exist
* $device_id is used for the insert/update unless $device_id is a key in any row in the array
* $site_id is used to A) uniquely identify a device or B) create the device if $createDeviceIfNotFound is true and device not foudn
* $client_id is used the same way as $site_id
*
* $contents is assumed to be
* [0] => {
* [value] => string to be set/added to device_attrib table
* [attrib_id] => key from attrib table
* [attribute] => string matching name from attrib table
* [device_id] => key from device table
* [device] => string matching name from device table
* [site_id] => key from site table
* [site] => string matching name from site table
* [client_id] => key from client table
* [client] => string matching name from client table
* }
* [1] => { another set of values the same as above }
*
* The only required values are attrib_id (or attribute) and value. If this is the case, it will be added to the device from
* parameter $device_id
*
* If [something_id] is found, that is used. If it is null (or doesn't exist), an attempt is made to determine the proper
* id from the database by looking for the string. If that is null, the parameter passed to this function is used. If all of
* that fails, the row is returned to the caller.
*
*/
function parseTabDelimFile ( $contents, $createAttributeIfNotFound = false, $createDeviceIfNotFound = false, $default_device_id='', $default_site_id='', $default_client_id='' ) {
$data = tabDelimToArray( $contents );
// we'll put our SQL into an array, then dump it.
$sql = array();
// and track any errors here
$errors = array();
/*
print "<pre>";
var_dump( $createAttributeIfNotFound, $createDeviceIfNotFound, $default_device_id, $default_site_id, $default_client_id );
print "</pre>";
return $sql;
*/
// the following two arrays will store attributes and devices as we find them
// we can then FIRST look them up here, in memory, and go to database only when we don't know them
$attributesFromDatabase = array(); // uniquely store our attributes here
$deviceFromDatabase = array();
for ( $i = 0; $i < count( $data ); $i++ ) { // go through each line and grab fields we need
// get device_id
if ( ! $data[$i]['device_id'] ) {
if ( $data[$i]['device'] ) {
if ( isset( $deviceFromDatabase[$data[$i]['device']] ) ) {
$data[$i]['device_id'] = $deviceFromDatabase[$data[$i]['device']];
} else {
$data[$i]['device_id'] = getDeviceID( $data[$i]['device'], $createDeviceIfNotFound, $default_site_id, $default_client_id );
}
if ( $data[$i]['device_id'] )
$deviceFromDatabase[$data[$i]['device']] = $data[$i]['device_id'];
}
if ( ! $data[$i]['device_id'] ) {
if ( $default_device_id ) {
$data[$i]['device_id'] = $default_device_id;
} else {
$errors[] = "Can not locate device [" . $data[$i]['device'] . "] in line $i";
continue;
}
}
}
// get attribute_id
if ( ! $data[$i]['attrib_id'] ) {
if ( $data[$i]['attribute'] ) {
if ( isset( $attributesFromDatabase[$data[$i]['attribute']] ) ) {
$data[$i]['attrib_id'] = $attributesFromDatabase[$data[$i]['attribute']];
} else {
$data[$i]['attrib_id'] = getAttributeID( $data[$i]['attribute'], $createAttributeIfNotFound );
}
if ( $data[$i]['attrib_id'] )
$attributesFromDatabase[$data[$i]['attribute']] = $data[$i]['attrib_id'];
}
if ( ! $data[$i]['attrib_id'] ) {
$errors[] = 'Can not locate attribute [' . $data[$i]['attribute'] . "] in line $i";
continue;
}
}
if ( ! $data[$i]['value'] ) {
$errors[] = "No Value given for line $i, skipped";
continue;
}
$sql = array_merge( $sql, makeSQL( $data[$i] ) );
}
return array( 'errors' => $errors, 'sql' => $sql );
}
/*
* makeSQL
* creates one or more SQL statements to upload attribute/value into the database.
* if unique is true, will not do an insert. Instead, it will only update an existing
* row if device_id and attrib_id pair already match.
* if unique is false, will only do an insert of a new (possibly duplicated) value
* WARNING: If you have multiple rows with device_id/attrib_id pair, this could delete all of them.
*/
function makeSQL ( $data, $unique = false ) {
$value = makeSafeSQLConstant( $data['value'] );
$attrib_id = makeSafeSQLConstant( $data['attrib_id'], 'i' );
$device_id = makeSafeSQLConstant( $data['device_id'],'i' );
$sql = array();
// standard SQL that does an insert if the value doesn't already exist.
// remove any existing records
$sql[] =
"update attrib_device
set removed_date = now()
where
device_id = $device_id
and attrib_id = $attrib_id
and removed_date is null
and attrib_id in (select attrib_id from attrib where multiples is null);";
$sql[] =
"update attrib_device
set value = $value
where
device_id = $device_id
and attrib_id = $attrib_id
and attrib_id in (select attrib_id from attrib where multiples is null)
and not exists (
select * from (select * from attrib_device ) as m2 where device_id = $device_id and attrib_id = $attrib_id and value = $value
);";
$sql[] =
"insert into attrib_device ( device_id, attrib_id,value, added_date )
select $device_id,$attrib_id, $value, now()
from dual
where
not exists (
select *
from
attrib_device
where
device_id = $device_id
and attrib_id = $attrib_id
and value = $value
and removed_date is null
);";
return $sql;
} // getSQL
function updateDatabase ( $queries ) {
$count = 0;
for( $i = 0; $i < count( $queries ); $i++ ) {
$result = doSQL( $queries[$i] );
$count += $result['affected_rows'];
}
return "$count entries added or updated";
}
/*
* following block of code is duplicated from the files module. It should instead be placed in root/include/library.php or something
*/
/*
* function designed to handle input from a form. If the input is
* unset, will retrun the $default value.
* Otherwise, will filter the value based on $filter
* Some common filters are:
* FILTER_SANITIZE_SPECIAL_CHARS - clean up text so no HTML
* FILTER_SANITIZE_EMAIL - email addresses
* FILTER_SANITIZE_NUMBER_FLOAT - floating point numbers
* FILTER_SANITIZE_NUMBER_INT - integers
* FILTER_SANITIZE_URL - A URL
* http://php.net/manual/en/filter.filters.sanitize.php
*/
function cleanInput ( $value, $default = '', $filter = FILTER_DEFAULT ) {
// unset or empty values just get the default
if ( ! isset( $value ) || strlen( trim( $value ) ) == 0 ) return $default;
return filter_var( trim( $value ), $filter );
}
function return_bytes($val) {
$val = trim($val);
$last = strtolower($val[strlen($val)-1]);
switch($last)
{
case 'g':
$val *= 1024;
case 'm':
$val *= 1024;
case 'k':
$val *= 1024;
}
return $val;
} // return_bytes
function prettyPrintBytes( $value ) {
$sizes = array( '', 'kilo', 'mega', 'giga', 'tera' );
while ( $value > 1024 ) {
$value /= 1024;
$index++;
}
return intval( $value ) . ' ' . $sizes[$index] . 'bytes';
}
function maxUploadFileSize () {
//select maximum upload size
$max_upload = return_bytes(ini_get('upload_max_filesize'));
//select post limit
$max_post = return_bytes(ini_get('post_max_size'));
//select memory limit
$memory_limit = return_bytes(ini_get('memory_limit'));
// return the smallest of them, this defines the real limit
return prettyPrintBytes( min($max_upload, $max_post, $memory_limit) );
} // maxUploadFileSize
function getFileUploadError( $error ) {
$message = '';
switch ( $error ) {
case 0 : $message = 'There is no error, the file uploaded with success';
break;
case 1 : $message = 'The uploaded file exceeds the upload_max_filesize directive in php.ini';
break;
case 2 : $message = 'The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form';
break;
case 3 : $message = 'The uploaded file was only partially uploaded';
break;
case 4 : $message = 'No file was uploaded';
break;
case 6 : $message = 'Missing a temporary folder';
break;
case 7 : $message = 'Failed to write file to disk.';
break;
case 8 : $message = 'A PHP extension stopped the file upload.';
}
return array( 'valid' => $error == 0, 'message' => $message );
} // getFileUploadError
function uploadFile ( $source, $nameOnDisk ) {
$saveTo = getAbsolutePath( $nameOnDisk );
if ( makePath( $saveTo ) ) {
logIt( "Path Made - $saveTo" );
logIt( "moving $source to $saveTo" );
$result['valid'] = move_uploaded_file( $source, $saveTo );
} else {
$result = array( 'valid'=>false, 'message' => print_r(error_get_last(), true) );
} // if move_uploaded_file .. else
return $result;
} // uploadFile
?>
?>