Mercurial > hg > vamp-website

annotate forum/Sources/Subs-Db-postgresql.php @ 76:e3e11437ecea website

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add forum code
author Chris Cannam
date Sun, 07 Jul 2013 11:25:48 +0200
parents
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 ?>