public static function buildFetchSql(&$identHash) { //echo "build sql start\n"; // print_r($identHash); $all_fields = static::$fields; $use_fields = array(); foreach ($all_fields as $field_name) { array_push($use_fields, Db_Linked::sanitizeFieldName($field_name)); } $fetchSql = 'SELECT ' . implode(',', $use_fields) . ' FROM ' . static::$dbTable . ' WHERE 1=1'; $keys_to_remove = []; $key_vals_to_add = []; $param_keys_counters = []; foreach ($identHash as $k => $v) { if (is_array($v)) { if (count($v) <= 0) { trigger_error(Db_Linked::$ERR_MSG_BAD_SEARCH_PARAM, E_USER_ERROR); return; } array_push($keys_to_remove, $k); $fetchSql .= ' AND ' . Db_Linked::sanitizeFieldName($k) . ' IN ('; for ($i = 0, $numElts = count($v); $i < $numElts; $i++) { $newKey = "__{$k}{$i}"; $key_use_counter = 1; if (array_key_exists($newKey, $param_keys_counters)) { $key_use_counter = $param_keys_counters[$newKey] + 1; $newKey = $newKey . '__' . $key_use_counter; } $param_keys_counters["__{$k}{$i}"] = $key_use_counter; $key_vals_to_add[$newKey] = $v[$i]; if ($i > 0) { $fetchSql .= ','; } $fetchSql .= ":{$newKey}"; } $fetchSql .= ')'; } else { $k_parts = preg_split('/\\s+/', $k); $num_k_parts = count($k_parts); if ($num_k_parts == 1) { # handle repeated use of same field in the query $newKey = $k; $key_use_counter = 1; if (array_key_exists($newKey, $param_keys_counters)) { $key_use_counter = $param_keys_counters[$newKey] + 1; $newKey = $newKey . '__' . $key_use_counter; $key_vals_to_add[$newKey] = $v; array_push($keys_to_remove, $k); } $param_keys_counters[$k] = $key_use_counter; $fetchSql .= ' AND ' . Db_Linked::sanitizeFieldName($k) . ' = :' . $newKey; } else { $k_comp = strtoupper(implode(' ', array_slice($k_parts, 1, $num_k_parts - 1))); $valid_comps = ['<', '<=', '>', '>=', '!=', 'LIKE', 'NOT LIKE', 'IS NULL', 'IS NOT NULL']; if (in_array($k_comp, $valid_comps)) { array_push($keys_to_remove, $k); if ($k_comp == 'IS NULL' || $k_comp == 'IS NOT NULL') { $fetchSql .= ' AND ' . $k_parts[0] . ' ' . $k_comp; } else { $k_field = $k_parts[0]; $newKey = $k_field; # handle repeated use of same field in the query $key_use_counter = 1; if (array_key_exists($newKey, $param_keys_counters)) { $key_use_counter = $param_keys_counters[$newKey] + 1; $newKey = $newKey . '__' . $key_use_counter; $key_vals_to_add[$newKey] = $v; array_push($keys_to_remove, $k); } $param_keys_counters[$k_field] = $key_use_counter; $key_vals_to_add[$newKey] = $v; $fetchSql .= ' AND ' . Db_Linked::sanitizeFieldName($k_field) . ' ' . $k_comp . ' :' . $newKey; } } } } } foreach ($keys_to_remove as $k) { unset($identHash[$k]); } foreach ($key_vals_to_add as $k => $v) { $identHash[$k] = $v; } $newIdent = []; foreach ($identHash as $k => $v) { $newIdent[":{$k}"] = $v; } $identHash = $newIdent; //echo "build sql end\n"; // print_r($identHash); return $fetchSql; }
function testSanitizeFieldName() { $f = 'order'; $this->assertEqual('`order`', Db_Linked::sanitizeFieldName($f)); }