/**
* execute query, requires connection to be opened
* An error will be triggered if there is a problem executing the query.
* This will pop the database parameter stack {@see MantisDbParam} after a successful execution
* @global array of previous executed queries for profiling
* @global adodb database connection object
* @global boolean indicating whether queries array is populated
* @param string $p_query Parameterlised Query string to execute.
* @param array $p_arr_parms Array of parameters matching $p_query.
* @param integer $p_limit Number of results to return.
* @param integer $p_offset Offset query results for paging.
* @return IteratorAggregate|boolean adodb result set or false if the query failed.
*/
function db_query($p_query, array $p_arr_parms = null, $p_limit = -1, $p_offset = -1)
{
global $g_queries_array, $g_db, $g_db_log_queries, $g_db_param;
$t_db_type = config_get_global('db_type');
static $s_check_params;
if ($s_check_params === null) {
$s_check_params = db_is_pgsql() || $t_db_type == 'odbc_mssql' || $t_db_type == 'mssqlnative';
}
$t_start = microtime(true);
# This ensures that we don't get an error from ADOdb if $p_arr_parms == null,
# as Execute() expects either an array or false if there are no parameters -
# null actually gets treated as array( 0 => null )
if (is_null($p_arr_parms)) {
$p_arr_parms = array();
}
if (!empty($p_arr_parms) && $s_check_params) {
$t_params = count($p_arr_parms);
for ($i = 0; $i < $t_params; $i++) {
if ($p_arr_parms[$i] === false) {
$p_arr_parms[$i] = 0;
} elseif ($p_arr_parms[$i] === true && $t_db_type == 'mssqlnative') {
$p_arr_parms[$i] = 1;
}
}
}
static $s_prefix;
static $s_suffix;
if ($s_prefix === null) {
# Determine table prefix and suffixes including trailing and leading '_'
$s_prefix = trim(config_get_global('db_table_prefix'));
$s_suffix = trim(config_get_global('db_table_suffix'));
if (!empty($s_prefix) && '_' != substr($s_prefix, -1)) {
$s_prefix .= '_';
}
if (!empty($s_suffix) && '_' != substr($s_suffix, 0, 1)) {
$s_suffix = '_' . $s_suffix;
}
}
$p_query = strtr($p_query, array('{' => $s_prefix, '}' => $s_suffix, '%s' => db_param(), '%d' => db_param(), '%b' => db_param(), '%l' => db_param()));
if (db_is_oracle()) {
$p_query = db_oracle_adapt_query_syntax($p_query, $p_arr_parms);
}
if ($p_limit != -1 || $p_offset != -1) {
$t_result = $g_db->SelectLimit($p_query, $p_limit, $p_offset, $p_arr_parms);
} else {
$t_result = $g_db->Execute($p_query, $p_arr_parms);
}
$t_elapsed = number_format(microtime(true) - $t_start, 4);
if (ON == $g_db_log_queries) {
$t_lastoffset = 0;
$i = 0;
if (!empty($p_arr_parms)) {
while (preg_match('/\\?/', $p_query, $t_matches, PREG_OFFSET_CAPTURE, $t_lastoffset)) {
$t_matches = $t_matches[0];
# Realign the offset returned by preg_match as it is byte-based,
# which causes issues with UTF-8 characters in the query string
# (e.g. from custom fields names)
$t_utf8_offset = utf8_strlen(substr($p_query, 0, $t_matches[1]), mb_internal_encoding());
if ($i <= count($p_arr_parms)) {
if (is_null($p_arr_parms[$i])) {
$t_replace = 'NULL';
} else {
if (is_string($p_arr_parms[$i])) {
$t_replace = "'" . $p_arr_parms[$i] . "'";
} else {
if (is_integer($p_arr_parms[$i]) || is_float($p_arr_parms[$i])) {
$t_replace = (double) $p_arr_parms[$i];
} else {
if (is_bool($p_arr_parms[$i])) {
switch ($t_db_type) {
case 'pgsql':
$t_replace = '\'' . $p_arr_parms[$i] . '\'';
break;
default:
$t_replace = $p_arr_parms[$i];
break;
}
} else {
echo 'Invalid argument type passed to query_bound(): ' . ($i + 1);
exit(1);
}
}
}
}
$p_query = utf8_substr($p_query, 0, $t_utf8_offset) . $t_replace . utf8_substr($p_query, $t_utf8_offset + utf8_strlen($t_matches[0]));
$t_lastoffset = $t_matches[1] + strlen($t_replace) + 1;
} else {
$t_lastoffset = $t_matches[1] + 1;
}
$i++;
}
}
$t_log_msg = array($p_query, $t_elapsed);
log_event(LOG_DATABASE, $t_log_msg);
array_push($g_queries_array, $t_log_msg);
} else {
array_push($g_queries_array, array('', $t_elapsed));
}
if (!$t_result) {
db_error($p_query);
trigger_error(ERROR_DB_QUERY_FAILED, ERROR);
return false;
} else {
$g_db_param->pop();
return $t_result;
}
}
/**
* execute query, requires connection to be opened
* An error will be triggered if there is a problem executing the query.
* @global array of previous executed queries for profiling
* @global adodb database connection object
* @global boolean indicating whether queries array is populated
* @param string $p_query Parameterlised Query string to execute
* @param array $arr_parms array of parameters matching $p_query
* @param int $p_limit Number of results to return
* @param int $p_offset offset query results for paging
* @return ADORecordSet|bool adodb result set or false if the query failed.
*/
function db_query_bound($p_query, $arr_parms = null, $p_limit = -1, $p_offset = -1)
{
global $g_queries_array, $g_db, $g_db_log_queries, $g_db_param_count;
$t_db_type = config_get_global('db_type');
static $s_check_params;
if ($s_check_params === null) {
$s_check_params = db_is_pgsql() || $t_db_type == 'odbc_mssql' || $t_db_type == 'mssqlnative';
}
$t_start = microtime(true);
if ($arr_parms != null && $s_check_params) {
$params = count($arr_parms);
for ($i = 0; $i < $params; $i++) {
if ($arr_parms[$i] === false) {
$arr_parms[$i] = 0;
} elseif ($arr_parms[$i] === true && $t_db_type == 'mssqlnative') {
$arr_parms[$i] = 1;
}
}
}
if (db_is_oracle()) {
$p_query = db_oracle_adapt_query_syntax($p_query, $arr_parms);
}
if ($p_limit != -1 || $p_offset != -1) {
$t_result = $g_db->SelectLimit($p_query, $p_limit, $p_offset, $arr_parms);
} else {
$t_result = $g_db->Execute($p_query, $arr_parms);
}
$t_elapsed = number_format(microtime(true) - $t_start, 4);
if (ON == $g_db_log_queries) {
$lastoffset = 0;
$i = 0;
if (!(is_null($arr_parms) || empty($arr_parms))) {
while (preg_match('/\\?/', $p_query, $matches, PREG_OFFSET_CAPTURE, $lastoffset)) {
$matches = $matches[0];
# Realign the offset returned by preg_match as it is byte-based,
# which causes issues with UTF-8 characters in the query string
# (e.g. from custom fields names)
$t_utf8_offset = utf8_strlen(substr($p_query, 0, $matches[1]), mb_internal_encoding());
if ($i <= count($arr_parms)) {
if (is_null($arr_parms[$i])) {
$replace = 'NULL';
} else {
if (is_string($arr_parms[$i])) {
$replace = "'" . $arr_parms[$i] . "'";
} else {
if (is_integer($arr_parms[$i]) || is_float($arr_parms[$i])) {
$replace = (double) $arr_parms[$i];
} else {
if (is_bool($arr_parms[$i])) {
switch ($t_db_type) {
case 'pgsql':
$replace = "'" . $arr_parms[$i] . "'";
break;
default:
$replace = $arr_parms[$i];
break;
}
} else {
echo "Invalid argument type passed to query_bound(): " . $i + 1;
exit(1);
}
}
}
}
$p_query = utf8_substr($p_query, 0, $t_utf8_offset) . $replace . utf8_substr($p_query, $t_utf8_offset + utf8_strlen($matches[0]));
$lastoffset = $matches[1] + strlen($replace) + 1;
} else {
$lastoffset = $matches[1] + 1;
}
$i++;
}
}
$t_log_msg = array($p_query, $t_elapsed);
log_event(LOG_DATABASE, $t_log_msg, debug_backtrace());
array_push($g_queries_array, $t_log_msg);
} else {
array_push($g_queries_array, array('', $t_elapsed));
}
# We can't reset the counter because we have queries being built
# and executed while building bigger queries in filter_api. -jreese
# $g_db_param_count = 0;
if (!$t_result) {
db_error($p_query);
trigger_error(ERROR_DB_QUERY_FAILED, ERROR);
return false;
} else {
return $t_result;
}
}
