public static function prepare_write_statement($db, $arrdata, $mode)
{
// version: 160527
//--
$mode = strtolower((string) $mode);
//--
switch ((string) $mode) {
case 'insert':
case 'new':
$mode = 'insert';
break;
case 'update':
case 'edit':
$mode = 'update';
break;
case 'in-select':
$mode = 'in-select';
break;
default:
self::error($db, 'PREPARE-WRITE-STATEMENT', 'Invalid Mode', '', $mode);
return '';
}
//end switch
//--
//--
$tmp_query = '';
//--
$tmp_query_x = '';
$tmp_query_y = '';
$tmp_query_z = '';
$tmp_query_w = '';
//--
//--
if (is_array($arrdata)) {
//--
foreach ($arrdata as $key => $val) {
//-- check for SQL INJECTION
$key = trim(str_replace(array('`', "'", '"'), array('', '', ''), (string) $key));
//-- except in-select, do not allow invalid keys as they represent the field names ; valid fields must contain only the following chars [A..Z][a..z][0..9][_]
if ((string) $mode == 'in-select') {
// in-select
$key = (int) $key;
// force int keys
} else {
if (!self::validate_table_and_fields_names($key)) {
// no unicode modifier
self::error($db, 'PREPARE-WRITE-STATEMENT', 'Invalid KEY', '', $key);
return '';
}
//end if
}
//end if
//--
$val_x = '';
// reset
//--
if (is_array($val)) {
// array (this is a special case, and always escape data)
//--
$val_x = (string) "'" . self::escape_str($db, Smart::array_to_list($val)) . "'";
// array values will be converted to: <val1>, <val2>, ...
//--
} elseif ($val === null) {
// emulate the SQL: NULL
//--
$val_x = 'NULL';
//--
} elseif ($val === false) {
// emulate the SQL: FALSE
//--
$val_x = 'FALSE';
//--
} elseif ($val === true) {
// emulate the SQL: TRUE
//--
$val_x = 'TRUE';
//--
} elseif (SmartValidator::validate_numeric_integer_or_decimal_values($val) === true) {
// number ; {{{SYNC-DETECT-PURE-NUMERIC-INT-OR-DECIMAL-VALUES}}}
//--
$val_x = (string) trim((string) $val);
// not escaped, it is safe: numeric and can contain just 0-9 - .
//--
} else {
// string or other cases
//--
$val_x = (string) "'" . self::escape_str($db, $val) . "'";
//--
}
//end if else
//--
if ((string) $mode == 'in-select') {
// in-select
$tmp_query_w .= $val_x . ',';
} elseif ((string) $mode == 'update') {
// update
$tmp_query_x .= '"' . $key . '"' . '=' . $val_x . ',';
} else {
// insert
$tmp_query_y .= '"' . $key . '"' . ',';
$tmp_query_z .= $val_x . ',';
}
//end if else
//--
}
//end while
//--
} else {
//--
self::error($db, 'PREPARE-WRITE-STATEMENT', 'The second argument must be array !', '', '');
return '';
//--
}
//end if else
//--
//-- eliminate last comma
if ((string) $mode == 'in-select') {
// in-select
$tmp_query_w = rtrim($tmp_query_w, ' ,');
} elseif ((string) $mode == 'update') {
// update
$tmp_query_x = rtrim($tmp_query_x, ' ,');
} else {
// insert
$tmp_query_y = rtrim($tmp_query_y, ' ,');
$tmp_query_z = rtrim($tmp_query_z, ' ,');
}
//end if else
//--
//--
if ((string) $mode == 'in-select') {
// in-select
$tmp_query = ' IN (' . $tmp_query_w . ') ';
} elseif ((string) $mode == 'update') {
// update
$tmp_query = ' SET ' . $tmp_query_x . ' ';
} else {
// (new) insert
$tmp_query = ' (' . $tmp_query_y . ') VALUES (' . $tmp_query_z . ') ';
}
//end if else
//--
//--
return (string) $tmp_query;
//--
}
/**
* Set the (in-memory) Auth Login Data
* It can be used just once per execution (session) as it stores the data using constants,
* and the data cannot be changed after a successful or failed authentication has set.
*
* @param STRING $y_user_id :: The user (login) ID used to authenticate the user ; Mandatory ; it can be the UserID from DB or if not using a DB must supply a unique ID to identify the user like username
* @param STRING $y_user_alias :: The user (login) Alias, used to display the logged in user ; Mandatory ; can be the same as the login ID or different (Ex: login ID can be 'myUserName' and this 'myUserName' ; or: login ID can be 5017 and this 'myUserName')
* @param STRING $y_user_email :: *OPTIONAL* The user Email ; if email is used as login ID this may be redundant !
* @param STRING $y_user_fullname :: *OPTIONAL* The user Full Name (First Name + Last Name)
* @param ARRAY $y_user_privileges_list :: *OPTIONAL* The user Privileges List as array that list all the current user privileges
* @param STRING $y_user_quota :: *OPTIONAL* The user (storage) Quota
* @param ARRAY $y_user_metadata :: *OPTIONAL* The user metainfo, associative array key => value
* @param STRING $y_realm :: *OPTIONAL* The user Authentication Realm(s)
* @param ENUM $y_method :: *OPTIONAL* The authentication method used: HTTP-BASIC / HTTP-DIGEST / OTHER
* @param STRING $y_pass :: *OPTIONAL* The user login password (will be stored in memory as Blowfish encrypted to avoid exposure)
*
* @return BOOLEAN :: TRUE if all data is OK, FALSE if not or try to reauthenticate under the same execution (which is not allowed ; must be just once per execution)
*/
public static function set_login_data($y_user_id, $y_user_alias, $y_user_email = '', $y_user_fullname = '', $y_user_privileges_list = array('none', 'no-privilege'), $y_user_quota = -1, $y_user_metadata = array(), $y_realm = 'DEFAULT', $y_method = '', $y_pass = '')
{
//--
if (self::$AuthCompleted !== false) {
// avoid to re-auth
Smart::log_warning('Re-Authentication is not allowed ...');
return;
}
//end if
self::$AuthCompleted = true;
//--
self::$AuthData = array();
// reset the auth data
//--
$y_user_id = trim((string) $y_user_id);
// user ID
$y_user_alias = trim((string) $y_user_alias);
// username (user alias ; can be the same as userID or different)
$y_user_email = trim((string) $y_user_email);
$y_user_fullname = trim((string) $y_user_fullname);
//--
if (is_array($y_user_privileges_list)) {
$y_user_privileges_list = (string) strtolower((string) Smart::array_to_list((array) $y_user_privileges_list));
} else {
$y_user_privileges_list = (string) strtolower((string) trim((string) $y_user_privileges_list));
// in this case can be provided a raw list of privileges (Example: '<none>, <no-privilege>')
}
//end if else
//--
$y_user_quota = Smart::format_number_int($y_user_quota);
// can be also negative
//--
switch (strtoupper((string) $y_method)) {
case 'HTTP-BASIC':
$y_method = 'HTTP-BASIC';
break;
case 'HTTP-DIGEST':
$y_method = 'HTTP-DIGEST';
break;
case 'OTHER':
default:
$y_method = 'OTHER';
}
//end switch
//--
$the_key = '#' . Smart::random_number(10000, 99999) . '#';
$the_pass = '';
if ((string) $y_pass != '') {
$the_pass = SmartCipherCrypto::encrypt('hash/sha1', (string) $the_key, (string) $y_pass);
}
//end if
//--
if ((string) $y_user_id != '') {
//--
self::$AuthData['USER_ID'] = (string) $y_user_id;
self::$AuthData['USER_EMAIL'] = (string) $y_user_email;
self::$AuthData['USER_ALIAS'] = (string) $y_user_alias;
self::$AuthData['USER_FULLNAME'] = (string) $y_user_fullname;
self::$AuthData['USER_PRIVILEGES'] = (string) $y_user_privileges_list;
self::$AuthData['USER_QUOTA'] = (int) $y_user_quota;
self::$AuthData['USER_METADATA'] = (array) $y_user_metadata;
self::$AuthData['USER_LOGIN_REALM'] = (string) $y_realm;
self::$AuthData['USER_LOGIN_METHOD'] = (string) $y_method;
self::$AuthData['USER_LOGIN_PASS'] = (string) $the_pass;
self::$AuthData['KEY'] = (string) $the_key;
//--
return true;
//--
} else {
//--
return false;
//--
}
//end if
//--
}
/**
* Create Escaped Write SQL Statements from Data - to be used with PgSQL for: INSERT ; INSERT-SUBSELECT ; UPDATE ; IN-SELECT ; DATA-ARRAY
* To be used with: write_data() or write_igdata() to build an INSERT / INSERT (SELECT) / UPDATE / SELECT IN query from an associative array
*
* @param ARRAY-associative $arrdata :: array of form data as $arr=array(); $arr['field1'] = 'a string'; $arr['field2'] = 100;
* @param ENUM $mode :: mode: 'insert' | 'insert-subselect' | 'update' | 'in-select', 'data-array'
* @param RESOURCE $y_connection :: the connection to pgsql server
* @return STRING :: The SQL partial Statement
*
*/
public static function prepare_write_statement($arrdata, $mode, $y_connection = 'DEFAULT')
{
// version: 161003
//==
$y_connection = self::check_connection($y_connection, 'PREPARE-WRITE-STATEMENT');
//==
//--
$mode = strtolower((string) $mode);
//--
switch ((string) $mode) {
case 'insert':
case 'new':
$mode = 'insert';
break;
case 'insert-subselect':
case 'new-subselect':
$mode = 'insert-subselect';
break;
case 'update':
case 'edit':
$mode = 'update';
break;
case 'in-select':
$mode = 'in-select';
break;
case 'data-array':
$mode = 'data-array';
break;
default:
self::error($y_connection, 'PREPARE-WRITE-STATEMENT', 'Invalid Mode', '', $mode);
return '';
}
//end switch
//--
//--
$tmp_query = '';
//--
$tmp_query_x = '';
$tmp_query_y = '';
$tmp_query_z = '';
$tmp_query_w = '';
//--
//--
if (is_array($arrdata)) {
//--
foreach ($arrdata as $key => $val) {
//-- check for SQL INJECTION
$key = trim(str_replace(array('`', "'", '"'), array('', '', ''), (string) $key));
//-- except [ in-select | 'data-array' ], do not allow invalid keys as they represent the field names ; valid fields must contain only the following chars [A..Z][a..z][0..9][_]
if ((string) $mode == 'in-select' or (string) $mode == 'data-array') {
// in-select, data-array
$key = (int) $key;
// force int keys
} else {
if (!self::validate_table_and_fields_names($key)) {
// no unicode modifier
self::error($y_connection, 'PREPARE-WRITE-STATEMENT', 'Invalid KEY', '', $key);
return '';
}
//end if
}
//end if
//--
$val_x = '';
// reset
//--
if (is_array($val)) {
// array (this is a special case, and always escape data)
//--
$val_x = (string) self::escape_literal((string) Smart::array_to_list($val), 'no', $y_connection);
// array values will be always escaped and converted to: <val1>, <val2>, ...
//--
} elseif ($val === null) {
// emulate the SQL: NULL
//--
$val_x = 'NULL';
//--
} elseif ($val === false) {
// emulate the SQL: FALSE
//--
$val_x = 'FALSE';
//--
} elseif ($val === true) {
// emulate the SQL: TRUE
//--
$val_x = 'TRUE';
//--
} else {
// string, number or other cases
//--
$val_x = (string) self::escape_literal($val, 'no', $y_connection);
//--
}
//end if else
//--
if ((string) $mode == 'in-select' or (string) $mode == 'data-array') {
// in-select, data-array
$tmp_query_w .= $val_x . ',';
} elseif ((string) $mode == 'update') {
// update
$tmp_query_x .= self::escape_identifier($key, $y_connection) . '=' . $val_x . ',';
} else {
// insert, insert-subselect
$tmp_query_y .= self::escape_identifier($key, $y_connection) . ',';
$tmp_query_z .= $val_x . ',';
}
//end if else
//--
}
//end while
//--
} else {
//--
self::error($y_connection, 'PREPARE-WRITE-STATEMENT', 'The first argument must be array !', '', '');
return '';
//--
}
//end if else
//--
//-- eliminate last comma
if ((string) $mode == 'in-select' or (string) $mode == 'data-array') {
// in-select, data-array
$tmp_query_w = rtrim($tmp_query_w, ' ,');
} elseif ((string) $mode == 'update') {
// update
$tmp_query_x = rtrim($tmp_query_x, ' ,');
} else {
// insert, insert-subselect
$tmp_query_y = rtrim($tmp_query_y, ' ,');
$tmp_query_z = rtrim($tmp_query_z, ' ,');
}
//end if else
//--
//--
if ((string) $mode == 'in-select') {
// in-select
$tmp_query = ' IN (' . $tmp_query_w . ') ';
} elseif ((string) $mode == 'data-array') {
// data-array
$tmp_query = ' ARRAY [' . $tmp_query_w . '] ';
} elseif ((string) $mode == 'update') {
// update
$tmp_query = ' SET ' . $tmp_query_x . ' ';
} elseif ((string) $mode == 'insert-subselect') {
// (upsert) insert-subselect
$tmp_query = ' (' . $tmp_query_y . ') SELECT ' . $tmp_query_z . ' ';
} else {
// (new) insert
$tmp_query = ' (' . $tmp_query_y . ') VALUES (' . $tmp_query_z . ') ';
}
//end if else
//--
//--
return (string) $tmp_query;
//--
}
