Mercurial > hg > vamp-website

annotate forum/Sources/Subs-Db-postgresql.php @ 87:df86d318892b website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Link to SampleType doc
author Chris Cannam
date Mon, 10 Feb 2014 18:11:48 +0000
parents e3e11437ecea
children
rev   line source
Chris@76 1 <?php
Chris@76 2
Chris@76 3 /**
Chris@76 4 * Simple Machines Forum (SMF)
Chris@76 5 *
Chris@76 6 * @package SMF
Chris@76 7 * @author Simple Machines http://www.simplemachines.org
Chris@76 8 * @copyright 2011 Simple Machines
Chris@76 9 * @license http://www.simplemachines.org/about/smf/license.php BSD
Chris@76 10 *
Chris@76 11 * @version 2.0.4
Chris@76 12 */
Chris@76 13
Chris@76 14 if (!defined('SMF'))
Chris@76 15 die('Hacking attempt...');
Chris@76 16
Chris@76 17 /* This file has all the main functions in it that relate to the database.
Chris@76 18
Chris@76 19 smf_db_initiate() maps the implementations in this file (smf_db_function_name)
Chris@76 20 to the $smcFunc['db_function_name'] variable.
Chris@76 21
Chris@76 22 */
Chris@76 23
Chris@76 24 // Initialize the database settings
Chris@76 25 function smf_db_initiate($db_server, $db_name, $db_user, $db_passwd, &$db_prefix, $db_options = array())
Chris@76 26 {
Chris@76 27 global $smcFunc, $mysql_set_mode;
Chris@76 28
Chris@76 29 // Map some database specific functions, only do this once.
Chris@76 30 if (!isset($smcFunc['db_fetch_assoc']) || $smcFunc['db_fetch_assoc'] != 'postg_fetch_assoc')
Chris@76 31 $smcFunc += array(
Chris@76 32 'db_query' => 'smf_db_query',
Chris@76 33 'db_quote' => 'smf_db_quote',
Chris@76 34 'db_insert' => 'smf_db_insert',
Chris@76 35 'db_insert_id' => 'smf_db_insert_id',
Chris@76 36 'db_fetch_assoc' => 'smf_db_fetch_assoc',
Chris@76 37 'db_fetch_row' => 'smf_db_fetch_row',
Chris@76 38 'db_free_result' => 'pg_free_result',
Chris@76 39 'db_num_rows' => 'pg_num_rows',
Chris@76 40 'db_data_seek' => 'smf_db_data_seek',
Chris@76 41 'db_num_fields' => 'pg_num_fields',
Chris@76 42 'db_escape_string' => 'pg_escape_string',
Chris@76 43 'db_unescape_string' => 'smf_db_unescape_string',
Chris@76 44 'db_server_info' => 'smf_db_version',
Chris@76 45 'db_affected_rows' => 'smf_db_affected_rows',
Chris@76 46 'db_transaction' => 'smf_db_transaction',
Chris@76 47 'db_error' => 'pg_last_error',
Chris@76 48 'db_select_db' => 'smf_db_select_db',
Chris@76 49 'db_title' => 'PostgreSQL',
Chris@76 50 'db_sybase' => true,
Chris@76 51 'db_case_sensitive' => true,
Chris@76 52 'db_escape_wildcard_string' => 'smf_db_escape_wildcard_string',
Chris@76 53 );
Chris@76 54
Chris@76 55 if (!empty($db_options['persist']))
Chris@76 56 $connection = @pg_pconnect('host=' . $db_server . ' dbname=' . $db_name . ' user=\'' . $db_user . '\' password=\'' . $db_passwd . '\'');
Chris@76 57 else
Chris@76 58 $connection = @pg_connect( 'host=' . $db_server . ' dbname=' . $db_name . ' user=\'' . $db_user . '\' password=\'' . $db_passwd . '\'');
Chris@76 59
Chris@76 60 // Something's wrong, show an error if its fatal (which we assume it is)
Chris@76 61 if (!$connection)
Chris@76 62 {
Chris@76 63 if (!empty($db_options['non_fatal']))
Chris@76 64 {
Chris@76 65 return null;
Chris@76 66 }
Chris@76 67 else
Chris@76 68 {
Chris@76 69 db_fatal_error();
Chris@76 70 }
Chris@76 71 }
Chris@76 72
Chris@76 73 return $connection;
Chris@76 74 }
Chris@76 75
Chris@76 76 // Extend the database functionality.
Chris@76 77 function db_extend ($type = 'extra')
Chris@76 78 {
Chris@76 79 global $sourcedir, $db_type;
Chris@76 80
Chris@76 81 require_once($sourcedir . '/Db' . strtoupper($type[0]) . substr($type, 1) . '-' . $db_type . '.php');
Chris@76 82 $initFunc = 'db_' . $type . '_init';
Chris@76 83 $initFunc();
Chris@76 84 }
Chris@76 85
Chris@76 86 // Do nothing on postgreSQL
Chris@76 87 function db_fix_prefix (&$db_prefix, $db_name)
Chris@76 88 {
Chris@76 89 return;
Chris@76 90 }
Chris@76 91
Chris@76 92 function smf_db_replacement__callback($matches)
Chris@76 93 {
Chris@76 94 global $db_callback, $user_info, $db_prefix;
Chris@76 95
Chris@76 96 list ($values, $connection) = $db_callback;
Chris@76 97
Chris@76 98 if (!is_resource($connection))
Chris@76 99 db_fatal_error();
Chris@76 100
Chris@76 101 if ($matches[1] === 'db_prefix')
Chris@76 102 return $db_prefix;
Chris@76 103
Chris@76 104 if ($matches[1] === 'query_see_board')
Chris@76 105 return $user_info['query_see_board'];
Chris@76 106
Chris@76 107 if ($matches[1] === 'query_wanna_see_board')
Chris@76 108 return $user_info['query_wanna_see_board'];
Chris@76 109
Chris@76 110 if (!isset($matches[2]))
Chris@76 111 smf_db_error_backtrace('Invalid value inserted or no type specified.', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 112
Chris@76 113 if (!isset($values[$matches[2]]))
Chris@76 114 smf_db_error_backtrace('The database value you\'re trying to insert does not exist: ' . htmlspecialchars($matches[2]), '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 115
Chris@76 116 $replacement = $values[$matches[2]];
Chris@76 117
Chris@76 118 switch ($matches[1])
Chris@76 119 {
Chris@76 120 case 'int':
Chris@76 121 if (!is_numeric($replacement) || (string) $replacement !== (string) (int) $replacement)
Chris@76 122 smf_db_error_backtrace('Wrong value type sent to the database. Integer expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 123 return (string) (int) $replacement;
Chris@76 124 break;
Chris@76 125
Chris@76 126 case 'string':
Chris@76 127 case 'text':
Chris@76 128 return sprintf('\'%1$s\'', pg_escape_string($replacement));
Chris@76 129 break;
Chris@76 130
Chris@76 131 case 'array_int':
Chris@76 132 if (is_array($replacement))
Chris@76 133 {
Chris@76 134 if (empty($replacement))
Chris@76 135 smf_db_error_backtrace('Database error, given array of integer values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 136
Chris@76 137 foreach ($replacement as $key => $value)
Chris@76 138 {
Chris@76 139 if (!is_numeric($value) || (string) $value !== (string) (int) $value)
Chris@76 140 smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 141
Chris@76 142 $replacement[$key] = (string) (int) $value;
Chris@76 143 }
Chris@76 144
Chris@76 145 return implode(', ', $replacement);
Chris@76 146 }
Chris@76 147 else
Chris@76 148 smf_db_error_backtrace('Wrong value type sent to the database. Array of integers expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 149
Chris@76 150 break;
Chris@76 151
Chris@76 152 case 'array_string':
Chris@76 153 if (is_array($replacement))
Chris@76 154 {
Chris@76 155 if (empty($replacement))
Chris@76 156 smf_db_error_backtrace('Database error, given array of string values is empty. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 157
Chris@76 158 foreach ($replacement as $key => $value)
Chris@76 159 $replacement[$key] = sprintf('\'%1$s\'', pg_escape_string($value));
Chris@76 160
Chris@76 161 return implode(', ', $replacement);
Chris@76 162 }
Chris@76 163 else
Chris@76 164 smf_db_error_backtrace('Wrong value type sent to the database. Array of strings expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 165 break;
Chris@76 166
Chris@76 167 case 'date':
Chris@76 168 if (preg_match('~^(\d{4})-([0-1]?\d)-([0-3]?\d)$~', $replacement, $date_matches) === 1)
Chris@76 169 return sprintf('\'%04d-%02d-%02d\'', $date_matches[1], $date_matches[2], $date_matches[3]);
Chris@76 170 else
Chris@76 171 smf_db_error_backtrace('Wrong value type sent to the database. Date expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 172 break;
Chris@76 173
Chris@76 174 case 'float':
Chris@76 175 if (!is_numeric($replacement))
Chris@76 176 smf_db_error_backtrace('Wrong value type sent to the database. Floating point number expected. (' . $matches[2] . ')', '', E_USER_ERROR, __FILE__, __LINE__);
Chris@76 177 return (string) (float) $replacement;
Chris@76 178 break;
Chris@76 179
Chris@76 180 case 'identifier':
Chris@76 181 return '`' . strtr($replacement, array('`' => '', '.' => '')) . '`';
Chris@76 182 break;
Chris@76 183
Chris@76 184 case 'raw':
Chris@76 185 return $replacement;
Chris@76 186 break;
Chris@76 187
Chris@76 188 default:
Chris@76 189 smf_db_error_backtrace('Undefined type used in the database query. (' . $matches[1] . ':' . $matches[2] . ')', '', false, __FILE__, __LINE__);
Chris@76 190 break;
Chris@76 191 }
Chris@76 192 }
Chris@76 193
Chris@76 194 // Just like the db_query, escape and quote a string, but not executing the query.
Chris@76 195 function smf_db_quote($db_string, $db_values, $connection = null)
Chris@76 196 {
Chris@76 197 global $db_callback, $db_connection;
Chris@76 198
Chris@76 199 // Only bother if there's something to replace.
Chris@76 200 if (strpos($db_string, '{') !== false)
Chris@76 201 {
Chris@76 202 // This is needed by the callback function.
Chris@76 203 $db_callback = array($db_values, $connection == null ? $db_connection : $connection);
Chris@76 204
Chris@76 205 // Do the quoting and escaping
Chris@76 206 $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
Chris@76 207
Chris@76 208 // Clear this global variable.
Chris@76 209 $db_callback = array();
Chris@76 210 }
Chris@76 211
Chris@76 212 return $db_string;
Chris@76 213 }
Chris@76 214
Chris@76 215 // Do a query. Takes care of errors too.
Chris@76 216 function smf_db_query($identifier, $db_string, $db_values = array(), $connection = null)
Chris@76 217 {
Chris@76 218 global $db_cache, $db_count, $db_connection, $db_show_debug, $time_start;
Chris@76 219 global $db_unbuffered, $db_callback, $db_last_result, $db_replace_result, $modSettings;
Chris@76 220
Chris@76 221 // Decide which connection to use.
Chris@76 222 $connection = $connection == null ? $db_connection : $connection;
Chris@76 223
Chris@76 224 // Special queries that need processing.
Chris@76 225 $replacements = array(
Chris@76 226 'alter_table_boards' => array(
Chris@76 227 '~(.+)~' => '',
Chris@76 228 ),
Chris@76 229 'alter_table_icons' => array(
Chris@76 230 '~(.+)~' => '',
Chris@76 231 ),
Chris@76 232 'alter_table_smileys' => array(
Chris@76 233 '~(.+)~' => '',
Chris@76 234 ),
Chris@76 235 'alter_table_spiders' => array(
Chris@76 236 '~(.+)~' => '',
Chris@76 237 ),
Chris@76 238 'ban_suggest_error_ips' => array(
Chris@76 239 '~RLIKE~' => '~',
Chris@76 240 '~\\.~' => '\.',
Chris@76 241 ),
Chris@76 242 'ban_suggest_message_ips' => array(
Chris@76 243 '~RLIKE~' => '~',
Chris@76 244 '~\\.~' => '\.',
Chris@76 245 ),
Chris@76 246 'consolidate_spider_stats' => array(
Chris@76 247 '~MONTH\(log_time\), DAYOFMONTH\(log_time\)~' => 'MONTH(CAST(CAST(log_time AS abstime) AS timestamp)), DAYOFMONTH(CAST(CAST(log_time AS abstime) AS timestamp))',
Chris@76 248 ),
Chris@76 249 'delete_subscription' => array(
Chris@76 250 '~LIMIT 1~' => '',
Chris@76 251 ),
Chris@76 252 'display_get_post_poster' => array(
Chris@76 253 '~GROUP BY id_msg\s+HAVING~' => 'AND',
Chris@76 254 ),
Chris@76 255 'attach_download_increase' => array(
Chris@76 256 '~LOW_PRIORITY~' => '',
Chris@76 257 ),
Chris@76 258 'boardindex_fetch_boards' => array(
Chris@76 259 '~IFNULL\(lb.id_msg, 0\) >= b.id_msg_updated~' => 'CASE WHEN IFNULL(lb.id_msg, 0) >= b.id_msg_updated THEN 1 ELSE 0 END',
Chris@76 260 '~(.)$~' => '$1 ORDER BY b.board_order',
Chris@76 261 ),
Chris@76 262 'get_random_number' => array(
Chris@76 263 '~RAND~' => 'RANDOM',
Chris@76 264 ),
Chris@76 265 'insert_log_search_topics' => array(
Chris@76 266 '~NOT RLIKE~' => '!~',
Chris@76 267 ),
Chris@76 268 'insert_log_search_results_no_index' => array(
Chris@76 269 '~NOT RLIKE~' => '!~',
Chris@76 270 ),
Chris@76 271 'insert_log_search_results_subject' => array(
Chris@76 272 '~NOT RLIKE~' => '!~',
Chris@76 273 ),
Chris@76 274 'messageindex_fetch_boards' => array(
Chris@76 275 '~(.)$~' => '$1 ORDER BY b.board_order',
Chris@76 276 ),
Chris@76 277 'select_message_icons' => array(
Chris@76 278 '~(.)$~' => '$1 ORDER BY icon_order',
Chris@76 279 ),
Chris@76 280 'set_character_set' => array(
Chris@76 281 '~SET\\s+NAMES\\s([a-zA-Z0-9\\-_]+)~' => 'SET NAMES \'$1\'',
Chris@76 282 ),
Chris@76 283 'pm_conversation_list' => array(
Chris@76 284 '~ORDER\\s+BY\\s+\\{raw:sort\\}~' => 'ORDER BY ' . (isset($db_values['sort']) ? ($db_values['sort'] === 'pm.id_pm' ? 'MAX(pm.id_pm)' : $db_values['sort']) : ''),
Chris@76 285 ),
Chris@76 286 'top_topic_starters' => array(
Chris@76 287 '~ORDER BY FIND_IN_SET\(id_member,(.+?)\)~' => 'ORDER BY STRPOS(\',\' || $1 || \',\', \',\' || id_member|| \',\')',
Chris@76 288 ),
Chris@76 289 'order_by_board_order' => array(
Chris@76 290 '~(.)$~' => '$1 ORDER BY b.board_order',
Chris@76 291 ),
Chris@76 292 'spider_check' => array(
Chris@76 293 '~(.)$~' => '$1 ORDER BY LENGTH(user_agent) DESC',
Chris@76 294 ),
Chris@76 295 'unread_replies' => array(
Chris@76 296 '~SELECT\\s+DISTINCT\\s+t.id_topic~' => 'SELECT t.id_topic, {raw:sort}',
Chris@76 297 ),
Chris@76 298 'profile_board_stats' => array(
Chris@76 299 '~COUNT\(\*\) \/ MAX\(b.num_posts\)~' => 'CAST(COUNT(*) AS DECIMAL) / CAST(b.num_posts AS DECIMAL)',
Chris@76 300 ),
Chris@76 301 'set_smiley_order' => array(
Chris@76 302 '~(.+)~' => '',
Chris@76 303 ),
Chris@76 304 );
Chris@76 305
Chris@76 306 if (isset($replacements[$identifier]))
Chris@76 307 $db_string = preg_replace(array_keys($replacements[$identifier]), array_values($replacements[$identifier]), $db_string);
Chris@76 308
Chris@76 309 // Limits need to be a little different.
Chris@76 310 $db_string = preg_replace('~\sLIMIT\s(\d+|{int:.+}),\s*(\d+|{int:.+})\s*$~i', 'LIMIT $2 OFFSET $1', $db_string);
Chris@76 311
Chris@76 312 if (trim($db_string) == '')
Chris@76 313 return false;
Chris@76 314
Chris@76 315 // Comments that are allowed in a query are preg_removed.
Chris@76 316 static $allowed_comments_from = array(
Chris@76 317 '~\s+~s',
Chris@76 318 '~/\*!40001 SQL_NO_CACHE \*/~',
Chris@76 319 '~/\*!40000 USE INDEX \([A-Za-z\_]+?\) \*/~',
Chris@76 320 '~/\*!40100 ON DUPLICATE KEY UPDATE id_msg = \d+ \*/~',
Chris@76 321 );
Chris@76 322 static $allowed_comments_to = array(
Chris@76 323 ' ',
Chris@76 324 '',
Chris@76 325 '',
Chris@76 326 '',
Chris@76 327 );
Chris@76 328
Chris@76 329 // One more query....
Chris@76 330 $db_count = !isset($db_count) ? 1 : $db_count + 1;
Chris@76 331 $db_replace_result = 0;
Chris@76 332
Chris@76 333 if (empty($modSettings['disableQueryCheck']) && strpos($db_string, '\'') !== false && empty($db_values['security_override']))
Chris@76 334 smf_db_error_backtrace('Hacking attempt...', 'Illegal character (\') used in query...', true, __FILE__, __LINE__);
Chris@76 335
Chris@76 336 if (empty($db_values['security_override']) && (!empty($db_values) || strpos($db_string, '{db_prefix}') !== false))
Chris@76 337 {
Chris@76 338 // Pass some values to the global space for use in the callback function.
Chris@76 339 $db_callback = array($db_values, $connection);
Chris@76 340
Chris@76 341 // Inject the values passed to this function.
Chris@76 342 $db_string = preg_replace_callback('~{([a-z_]+)(?::([a-zA-Z0-9_-]+))?}~', 'smf_db_replacement__callback', $db_string);
Chris@76 343
Chris@76 344 // This shouldn't be residing in global space any longer.
Chris@76 345 $db_callback = array();
Chris@76 346 }
Chris@76 347
Chris@76 348 // Debugging.
Chris@76 349 if (isset($db_show_debug) && $db_show_debug === true)
Chris@76 350 {
Chris@76 351 // Get the file and line number this function was called.
Chris@76 352 list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
Chris@76 353
Chris@76 354 // Initialize $db_cache if not already initialized.
Chris@76 355 if (!isset($db_cache))
Chris@76 356 $db_cache = array();
Chris@76 357
Chris@76 358 if (!empty($_SESSION['debug_redirect']))
Chris@76 359 {
Chris@76 360 $db_cache = array_merge($_SESSION['debug_redirect'], $db_cache);
Chris@76 361 $db_count = count($db_cache) + 1;
Chris@76 362 $_SESSION['debug_redirect'] = array();
Chris@76 363 }
Chris@76 364
Chris@76 365 $st = microtime();
Chris@76 366 // Don't overload it.
Chris@76 367 $db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
Chris@76 368 $db_cache[$db_count]['f'] = $file;
Chris@76 369 $db_cache[$db_count]['l'] = $line;
Chris@76 370 $db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
Chris@76 371 }
Chris@76 372
Chris@76 373 // First, we clean strings out of the query, reduce whitespace, lowercase, and trim - so we can check it over.
Chris@76 374 if (empty($modSettings['disableQueryCheck']))
Chris@76 375 {
Chris@76 376 $clean = '';
Chris@76 377 $old_pos = 0;
Chris@76 378 $pos = -1;
Chris@76 379 while (true)
Chris@76 380 {
Chris@76 381 $pos = strpos($db_string, '\'', $pos + 1);
Chris@76 382 if ($pos === false)
Chris@76 383 break;
Chris@76 384 $clean .= substr($db_string, $old_pos, $pos - $old_pos);
Chris@76 385
Chris@76 386 while (true)
Chris@76 387 {
Chris@76 388 $pos1 = strpos($db_string, '\'', $pos + 1);
Chris@76 389 $pos2 = strpos($db_string, '\\', $pos + 1);
Chris@76 390 if ($pos1 === false)
Chris@76 391 break;
Chris@76 392 elseif ($pos2 == false || $pos2 > $pos1)
Chris@76 393 {
Chris@76 394 $pos = $pos1;
Chris@76 395 break;
Chris@76 396 }
Chris@76 397
Chris@76 398 $pos = $pos2 + 1;
Chris@76 399 }
Chris@76 400 $clean .= ' %s ';
Chris@76 401
Chris@76 402 $old_pos = $pos + 1;
Chris@76 403 }
Chris@76 404 $clean .= substr($db_string, $old_pos);
Chris@76 405 $clean = trim(strtolower(preg_replace($allowed_comments_from, $allowed_comments_to, $clean)));
Chris@76 406
Chris@76 407 // We don't use UNION in SMF, at least so far. But it's useful for injections.
Chris@76 408 if (strpos($clean, 'union') !== false && preg_match('~(^|[^a-z])union($|[^[a-z])~s', $clean) != 0)
Chris@76 409 $fail = true;
Chris@76 410 // Comments? We don't use comments in our queries, we leave 'em outside!
Chris@76 411 elseif (strpos($clean, '/*') > 2 || strpos($clean, '--') !== false || strpos($clean, ';') !== false)
Chris@76 412 $fail = true;
Chris@76 413 // Trying to change passwords, slow us down, or something?
Chris@76 414 elseif (strpos($clean, 'sleep') !== false && preg_match('~(^|[^a-z])sleep($|[^[_a-z])~s', $clean) != 0)
Chris@76 415 $fail = true;
Chris@76 416 elseif (strpos($clean, 'benchmark') !== false && preg_match('~(^|[^a-z])benchmark($|[^[a-z])~s', $clean) != 0)
Chris@76 417 $fail = true;
Chris@76 418 // Sub selects? We don't use those either.
Chris@76 419 elseif (preg_match('~\([^)]*?select~s', $clean) != 0)
Chris@76 420 $fail = true;
Chris@76 421
Chris@76 422 if (!empty($fail) && function_exists('log_error'))
Chris@76 423 smf_db_error_backtrace('Hacking attempt...', 'Hacking attempt...' . "\n" . $db_string, E_USER_ERROR, __FILE__, __LINE__);
Chris@76 424 }
Chris@76 425
Chris@76 426 $db_last_result = @pg_query($connection, $db_string);
Chris@76 427
Chris@76 428 if ($db_last_result === false && empty($db_values['db_error_skip']))
Chris@76 429 $db_last_result = smf_db_error($db_string, $connection);
Chris@76 430
Chris@76 431 // Debugging.
Chris@76 432 if (isset($db_show_debug) && $db_show_debug === true)
Chris@76 433 $db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));
Chris@76 434
Chris@76 435 return $db_last_result;
Chris@76 436 }
Chris@76 437
Chris@76 438 function smf_db_affected_rows($result = null)
Chris@76 439 {
Chris@76 440 global $db_last_result, $db_replace_result;
Chris@76 441
Chris@76 442 if ($db_replace_result)
Chris@76 443 return $db_replace_result;
Chris@76 444 elseif ($result == null && !$db_last_result)
Chris@76 445 return 0;
Chris@76 446
Chris@76 447 return pg_affected_rows($result == null ? $db_last_result : $result);
Chris@76 448 }
Chris@76 449
Chris@76 450 function smf_db_insert_id($table, $field = null, $connection = null)
Chris@76 451 {
Chris@76 452 global $db_connection, $smcFunc, $db_prefix;
Chris@76 453
Chris@76 454 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 455
Chris@76 456 if ($connection === false)
Chris@76 457 $connection = $db_connection;
Chris@76 458
Chris@76 459 // Try get the last ID for the auto increment field.
Chris@76 460 $request = $smcFunc['db_query']('', 'SELECT CURRVAL(\'' . $table . '_seq\') AS insertID',
Chris@76 461 array(
Chris@76 462 )
Chris@76 463 );
Chris@76 464 if (!$request)
Chris@76 465 return false;
Chris@76 466 list ($lastID) = $smcFunc['db_fetch_row']($request);
Chris@76 467 $smcFunc['db_free_result']($request);
Chris@76 468
Chris@76 469 return $lastID;
Chris@76 470 }
Chris@76 471
Chris@76 472 // Do a transaction.
Chris@76 473 function smf_db_transaction($type = 'commit', $connection = null)
Chris@76 474 {
Chris@76 475 global $db_connection;
Chris@76 476
Chris@76 477 // Decide which connection to use
Chris@76 478 $connection = $connection == null ? $db_connection : $connection;
Chris@76 479
Chris@76 480 if ($type == 'begin')
Chris@76 481 return @pg_query($connection, 'BEGIN');
Chris@76 482 elseif ($type == 'rollback')
Chris@76 483 return @pg_query($connection, 'ROLLBACK');
Chris@76 484 elseif ($type == 'commit')
Chris@76 485 return @pg_query($connection, 'COMMIT');
Chris@76 486
Chris@76 487 return false;
Chris@76 488 }
Chris@76 489
Chris@76 490 // Database error!
Chris@76 491 function smf_db_error($db_string, $connection = null)
Chris@76 492 {
Chris@76 493 global $txt, $context, $sourcedir, $webmaster_email, $modSettings;
Chris@76 494 global $forum_version, $db_connection, $db_last_error, $db_persist;
Chris@76 495 global $db_server, $db_user, $db_passwd, $db_name, $db_show_debug, $ssi_db_user, $ssi_db_passwd;
Chris@76 496 global $smcFunc;
Chris@76 497
Chris@76 498 // We'll try recovering the file and line number the original db query was called from.
Chris@76 499 list ($file, $line) = smf_db_error_backtrace('', '', 'return', __FILE__, __LINE__);
Chris@76 500
Chris@76 501 // Decide which connection to use
Chris@76 502 $connection = $connection == null ? $db_connection : $connection;
Chris@76 503
Chris@76 504 // This is the error message...
Chris@76 505 $query_error = @pg_last_error($connection);
Chris@76 506
Chris@76 507 // Log the error.
Chris@76 508 if (function_exists('log_error'))
Chris@76 509 log_error($txt['database_error'] . ': ' . $query_error . (!empty($modSettings['enableErrorQueryLogging']) ? "\n\n" .$db_string : ''), 'database', $file, $line);
Chris@76 510
Chris@76 511 // Nothing's defined yet... just die with it.
Chris@76 512 if (empty($context) || empty($txt))
Chris@76 513 die($query_error);
Chris@76 514
Chris@76 515 // Show an error message, if possible.
Chris@76 516 $context['error_title'] = $txt['database_error'];
Chris@76 517 if (allowedTo('admin_forum'))
Chris@76 518 $context['error_message'] = nl2br($query_error) . '<br />' . $txt['file'] . ': ' . $file . '<br />' . $txt['line'] . ': ' . $line;
Chris@76 519 else
Chris@76 520 $context['error_message'] = $txt['try_again'];
Chris@76 521
Chris@76 522 // A database error is often the sign of a database in need of updgrade. Check forum versions, and if not identical suggest an upgrade... (not for Demo/CVS versions!)
Chris@76 523 if (allowedTo('admin_forum') && !empty($forum_version) && $forum_version != 'SMF ' . @$modSettings['smfVersion'] && strpos($forum_version, 'Demo') === false && strpos($forum_version, 'CVS') === false)
Chris@76 524 $context['error_message'] .= '<br /><br />' . sprintf($txt['database_error_versions'], $forum_version, $modSettings['smfVersion']);
Chris@76 525
Chris@76 526 if (allowedTo('admin_forum') && isset($db_show_debug) && $db_show_debug === true)
Chris@76 527 {
Chris@76 528 $context['error_message'] .= '<br /><br />' . nl2br($db_string);
Chris@76 529 }
Chris@76 530
Chris@76 531 // It's already been logged... don't log it again.
Chris@76 532 fatal_error($context['error_message'], false);
Chris@76 533 }
Chris@76 534
Chris@76 535 // A PostgreSQL specific function for tracking the current row...
Chris@76 536 function smf_db_fetch_row($request, $counter = false)
Chris@76 537 {
Chris@76 538 global $db_row_count;
Chris@76 539
Chris@76 540 if ($counter !== false)
Chris@76 541 return pg_fetch_row($request, $counter);
Chris@76 542
Chris@76 543 // Reset the row counter...
Chris@76 544 if (!isset($db_row_count[(int) $request]))
Chris@76 545 $db_row_count[(int) $request] = 0;
Chris@76 546
Chris@76 547 // Return the right row.
Chris@76 548 return @pg_fetch_row($request, $db_row_count[(int) $request]++);
Chris@76 549 }
Chris@76 550
Chris@76 551 // Get an associative array
Chris@76 552 function smf_db_fetch_assoc($request, $counter = false)
Chris@76 553 {
Chris@76 554 global $db_row_count;
Chris@76 555
Chris@76 556 if ($counter !== false)
Chris@76 557 return pg_fetch_assoc($request, $counter);
Chris@76 558
Chris@76 559 // Reset the row counter...
Chris@76 560 if (!isset($db_row_count[(int) $request]))
Chris@76 561 $db_row_count[(int) $request] = 0;
Chris@76 562
Chris@76 563 // Return the right row.
Chris@76 564 return @pg_fetch_assoc($request, $db_row_count[(int) $request]++);
Chris@76 565 }
Chris@76 566
Chris@76 567 // Reset the pointer...
Chris@76 568 function smf_db_data_seek($request, $counter)
Chris@76 569 {
Chris@76 570 global $db_row_count;
Chris@76 571
Chris@76 572 $db_row_count[(int) $request] = $counter;
Chris@76 573
Chris@76 574 return true;
Chris@76 575 }
Chris@76 576
Chris@76 577 // Unescape an escaped string!
Chris@76 578 function smf_db_unescape_string($string)
Chris@76 579 {
Chris@76 580 return strtr($string, array('\'\'' => '\''));
Chris@76 581 }
Chris@76 582
Chris@76 583 // For inserting data in a special way...
Chris@76 584 function smf_db_insert($method = 'replace', $table, $columns, $data, $keys, $disable_trans = false, $connection = null)
Chris@76 585 {
Chris@76 586 global $db_replace_result, $db_in_transact, $smcFunc, $db_connection, $db_prefix;
Chris@76 587
Chris@76 588 $connection = $connection === null ? $db_connection : $connection;
Chris@76 589
Chris@76 590 if (empty($data))
Chris@76 591 return;
Chris@76 592
Chris@76 593 if (!is_array($data[array_rand($data)]))
Chris@76 594 $data = array($data);
Chris@76 595
Chris@76 596 // Replace the prefix holder with the actual prefix.
Chris@76 597 $table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76 598
Chris@76 599 $priv_trans = false;
Chris@76 600 if ((count($data) > 1 || $method == 'replace') && !$db_in_transact && !$disable_trans)
Chris@76 601 {
Chris@76 602 $smcFunc['db_transaction']('begin', $connection);
Chris@76 603 $priv_trans = true;
Chris@76 604 }
Chris@76 605
Chris@76 606 // PostgreSQL doesn't support replace: we implement a MySQL-compatible behavior instead
Chris@76 607 if ($method == 'replace')
Chris@76 608 {
Chris@76 609 $count = 0;
Chris@76 610 $where = '';
Chris@76 611 foreach ($columns as $columnName => $type)
Chris@76 612 {
Chris@76 613 // Are we restricting the length?
Chris@76 614 if (strpos($type, 'string-') !== false)
Chris@76 615 $actualType = sprintf($columnName . ' = SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $count);
Chris@76 616 else
Chris@76 617 $actualType = sprintf($columnName . ' = {%1$s:%2$s}, ', $type, $count);
Chris@76 618
Chris@76 619 // A key? That's what we were looking for.
Chris@76 620 if (in_array($columnName, $keys))
Chris@76 621 $where .= (empty($where) ? '' : ' AND ') . substr($actualType, 0, -2);
Chris@76 622 $count++;
Chris@76 623 }
Chris@76 624
Chris@76 625 // Make it so.
Chris@76 626 if (!empty($where) && !empty($data))
Chris@76 627 {
Chris@76 628 foreach ($data as $k => $entry)
Chris@76 629 {
Chris@76 630 $smcFunc['db_query']('', '
Chris@76 631 DELETE FROM ' . $table .
Chris@76 632 ' WHERE ' . $where,
Chris@76 633 $entry, $connection
Chris@76 634 );
Chris@76 635 }
Chris@76 636 }
Chris@76 637 }
Chris@76 638
Chris@76 639 if (!empty($data))
Chris@76 640 {
Chris@76 641 // Create the mold for a single row insert.
Chris@76 642 $insertData = '(';
Chris@76 643 foreach ($columns as $columnName => $type)
Chris@76 644 {
Chris@76 645 // Are we restricting the length?
Chris@76 646 if (strpos($type, 'string-') !== false)
Chris@76 647 $insertData .= sprintf('SUBSTRING({string:%1$s}, 1, ' . substr($type, 7) . '), ', $columnName);
Chris@76 648 else
Chris@76 649 $insertData .= sprintf('{%1$s:%2$s}, ', $type, $columnName);
Chris@76 650 }
Chris@76 651 $insertData = substr($insertData, 0, -2) . ')';
Chris@76 652
Chris@76 653 // Create an array consisting of only the columns.
Chris@76 654 $indexed_columns = array_keys($columns);
Chris@76 655
Chris@76 656 // Here's where the variables are injected to the query.
Chris@76 657 $insertRows = array();
Chris@76 658 foreach ($data as $dataRow)
Chris@76 659 $insertRows[] = smf_db_quote($insertData, array_combine($indexed_columns, $dataRow), $connection);
Chris@76 660
Chris@76 661 foreach ($insertRows as $entry)
Chris@76 662 // Do the insert.
Chris@76 663 $smcFunc['db_query']('', '
Chris@76 664 INSERT INTO ' . $table . '("' . implode('", "', $indexed_columns) . '")
Chris@76 665 VALUES
Chris@76 666 ' . $entry,
Chris@76 667 array(
Chris@76 668 'security_override' => true,
Chris@76 669 'db_error_skip' => $method == 'ignore' || $table === $db_prefix . 'log_errors',
Chris@76 670 ),
Chris@76 671 $connection
Chris@76 672 );
Chris@76 673 }
Chris@76 674
Chris@76 675 if ($priv_trans)
Chris@76 676 $smcFunc['db_transaction']('commit', $connection);
Chris@76 677 }
Chris@76 678
Chris@76 679 // Dummy function really.
Chris@76 680 function smf_db_select_db($db_name, $db_connection)
Chris@76 681 {
Chris@76 682 return true;
Chris@76 683 }
Chris@76 684
Chris@76 685 // Get the current version.
Chris@76 686 function smf_db_version()
Chris@76 687 {
Chris@76 688 $version = pg_version();
Chris@76 689
Chris@76 690 return $version['client'];
Chris@76 691 }
Chris@76 692
Chris@76 693 // This function tries to work out additional error information from a back trace.
Chris@76 694 function smf_db_error_backtrace($error_message, $log_message = '', $error_type = false, $file = null, $line = null)
Chris@76 695 {
Chris@76 696 if (empty($log_message))
Chris@76 697 $log_message = $error_message;
Chris@76 698
Chris@76 699 if (function_exists('debug_backtrace'))
Chris@76 700 {
Chris@76 701 foreach (debug_backtrace() as $step)
Chris@76 702 {
Chris@76 703 // Found it?
Chris@76 704 if (strpos($step['function'], 'query') === false && !in_array(substr($step['function'], 0, 7), array('smf_db_', 'preg_re', 'db_erro', 'call_us')) && substr($step['function'], 0, 2) != '__')
Chris@76 705 {
Chris@76 706 $log_message .= '<br />Function: ' . $step['function'];
Chris@76 707 break;
Chris@76 708 }
Chris@76 709
Chris@76 710 if (isset($step['line']))
Chris@76 711 {
Chris@76 712 $file = $step['file'];
Chris@76 713 $line = $step['line'];
Chris@76 714 }
Chris@76 715 }
Chris@76 716 }
Chris@76 717
Chris@76 718 // A special case - we want the file and line numbers for debugging.
Chris@76 719 if ($error_type == 'return')
Chris@76 720 return array($file, $line);
Chris@76 721
Chris@76 722 // Is always a critical error.
Chris@76 723 if (function_exists('log_error'))
Chris@76 724 log_error($log_message, 'critical', $file, $line);
Chris@76 725
Chris@76 726 if (function_exists('fatal_error'))
Chris@76 727 {
Chris@76 728 fatal_error($error_message, $error_type);
Chris@76 729
Chris@76 730 // Cannot continue...
Chris@76 731 exit;
Chris@76 732 }
Chris@76 733 elseif ($error_type)
Chris@76 734 trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''), $error_type);
Chris@76 735 else
Chris@76 736 trigger_error($error_message . ($line !== null ? '<em>(' . basename($file) . '-' . $line . ')</em>' : ''));
Chris@76 737 }
Chris@76 738
Chris@76 739 // Escape the LIKE wildcards so that they match the character and not the wildcard.
Chris@76 740 // The optional second parameter turns human readable wildcards into SQL wildcards.
Chris@76 741 function smf_db_escape_wildcard_string($string, $translate_human_wildcards=false)
Chris@76 742 {
Chris@76 743 $replacements = array(
Chris@76 744 '%' => '\%',
Chris@76 745 '_' => '\_',
Chris@76 746 '\\' => '\\\\',
Chris@76 747 );
Chris@76 748
Chris@76 749 if ($translate_human_wildcards)
Chris@76 750 $replacements += array(
Chris@76 751 '*' => '%',
Chris@76 752 );
Chris@76 753
Chris@76 754 return strtr($string, $replacements);
Chris@76 755 }
Chris@76 756
Chris@76 757 ?>