6 files changed, 388 insertions, 14 deletions
diff --git a/phpBB/develop/create_schema_files.php b/phpBB/develop/create_schema_files.php
index db0752e4d5..bc7ef30962 100644
--- a/phpBB/develop/create_schema_files.php
+++ b/phpBB/develop/create_schema_files.php
@@ -329,6 +329,15 @@ foreach ($supported_dbms as $dbms)
 		// Write columns one by one...
 		foreach ($table_data['COLUMNS'] as $column_name => $column_data)
 		{
+			if (strlen($column_name) > 30)
+			{
+				trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
+			}
+			if (isset($column_data[2]) && $column_data[2] == 'auto_increment' && strlen($column_name) > 26) // "${column_name}_gen"
+			{
+				trigger_error("Index name '${column_name}_gen' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
+			}
+
 			// Get type
 			if (strpos($column_data[0], ':') !== false)
 			{
@@ -632,6 +641,11 @@ foreach ($supported_dbms as $dbms)
 					$key_data[1] = array($key_data[1]);
 				}
 
+				if (strlen($table_name . $key_name) > 30)
+				{
+					trigger_error("Index name '$key_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
+				}
+
 				switch ($dbms)
 				{
 					case 'mysql_40':
diff --git a/phpBB/includes/auth/auth_db.php b/phpBB/includes/auth/auth_db.php
index a43598cadd..f715a11971 100644
--- a/phpBB/includes/auth/auth_db.php
+++ b/phpBB/includes/auth/auth_db.php
@@ -73,7 +73,7 @@ function login_db($username, $password, $ip = '', $browser = '', $forwarded_for
 	if (($ip && !$config['ip_login_limit_use_forwarded']) ||
 		($forwarded_for && $config['ip_login_limit_use_forwarded']))
 	{
-		$sql = 'SELECT COUNT(attempt_id) AS count
+		$sql = 'SELECT COUNT(attempt_id) AS attempts
 			FROM ' . LOGIN_ATTEMPT_TABLE . '
 			WHERE attempt_time > ' . (time() - (int) $config['ip_login_limit_time']);
 		if ($config['ip_login_limit_use_forwarded'])
@@ -86,7 +86,7 @@ function login_db($username, $password, $ip = '', $browser = '', $forwarded_for
 		}
 
 		$result = $db->sql_query($sql);
-		$attempts = (int) $db->sql_fetchfield('count');
+		$attempts = (int) $db->sql_fetchfield('attempts');
 		$db->sql_freeresult($result);
 
 		$attempt_data = array(
diff --git a/phpBB/includes/captcha/plugins/phpbb_captcha_qa_plugin.php b/phpBB/includes/captcha/plugins/phpbb_captcha_qa_plugin.php
index 75fef25a9f..3bc727da41 100644
--- a/phpBB/includes/captcha/plugins/phpbb_captcha_qa_plugin.php
+++ b/phpBB/includes/captcha/plugins/phpbb_captcha_qa_plugin.php
@@ -319,7 +319,7 @@ class phpbb_captcha_qa
 					),
 					'PRIMARY_KEY'		=> 'question_id',
 					'KEYS'				=> array(
-						'lang_iso'			=> array('INDEX', 'lang_iso'),
+						'lang'			=> array('INDEX', 'lang_iso'),
 					),
 				),
 				CAPTCHA_ANSWERS_TABLE		=> array (
@@ -328,7 +328,7 @@ class phpbb_captcha_qa
 						'answer_text'	=> array('STEXT_UNI', ''),
 					),
 					'KEYS'				=> array(
-						'question_id'			=> array('INDEX', 'question_id'),
+						'qid'			=> array('INDEX', 'question_id'),
 					),
 				),
 				CAPTCHA_QA_CONFIRM_TABLE		=> array (
diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php
index b74be221e2..cdf8ce2040 100644
--- a/phpBB/includes/db/db_tools.php
+++ b/phpBB/includes/db/db_tools.php
@@ -417,6 +417,11 @@ class phpbb_db_tools
 			// here lies an array, filled with information compiled on the column's data
 			$prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 
+			if (isset($prepared_column['auto_increment']) && strlen($column_name) > 26) // "${column_name}_gen"
+			{
+				trigger_error("Index name '${column_name}_gen' on table '$table_name' is too long. The maximum auto increment column length is 26 characters.", E_USER_ERROR);
+			}
+
 			// here we add the definition of the new column to the list of columns
 			switch ($this->sql_layer)
 			{
@@ -566,7 +571,13 @@ class phpbb_db_tools
 			case 'firebird':
 				if ($create_sequence)
 				{
-					$statements[] = "CREATE SEQUENCE {$table_name}_seq;";
+					$statements[] = "CREATE GENERATOR {$table_name}_gen;";
+					$statements[] = "SET GENERATOR {$table_name}_gen TO 0;";
+
+					$trigger = "CREATE TRIGGER t_$table_name FOR $table_name\n";
+					$trigger .= "BEFORE INSERT\nAS\nBEGIN\n";
+					$trigger .= "\tNEW.{$create_sequence} = GEN_ID({$table_name}_gen, 1);\nEND;";
+					$statements[] = $trigger;
 				}
 			break;
 		}
@@ -1400,6 +1411,11 @@ class phpbb_db_tools
 	*/
 	function sql_prepare_column_data($table_name, $column_name, $column_data)
 	{
+		if (strlen($column_name) > 30)
+		{
+			trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
+		}
+
 		// Get type
 		if (strpos($column_data[0], ':') !== false)
 		{
@@ -2040,6 +2056,13 @@ class phpbb_db_tools
 	{
 		$statements = array();
 
+		$table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
+		if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
+		{
+			$max_length = $table_prefix + 24;
+			trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
+		}
+
 		switch ($this->sql_layer)
 		{
 			case 'firebird':
@@ -2070,6 +2093,13 @@ class phpbb_db_tools
 	{
 		$statements = array();
 
+		$table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
+		if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
+		{
+			$max_length = $table_prefix + 24;
+			trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
+		}
+
 		// remove index length unless MySQL4
 		if ('mysql_40' != $this->sql_layer)
 		{
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index 58e090ba17..d181c6320b 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -601,12 +601,23 @@ function _sql($sql, &$errored, &$error_ary, $echo_dot = true)
 
 	$db->sql_return_on_error(true);
 
-	$result = $db->sql_query($sql);
-	if ($db->sql_error_triggered)
+	if ($sql === 'begin')
+	{
+		$db->sql_transaction('begin');
+	}
+	else if ($sql === 'commit')
 	{
-		$errored = true;
-		$error_ary['sql'][] = $db->sql_error_sql;
-		$error_ary['error_code'][] = $db->sql_error_returned;
+		$db->sql_transaction('commit');
+	}
+	else
+	{
+		$result = $db->sql_query($sql);
+		if ($db->sql_error_triggered)
+		{
+			$errored = true;
+			$error_ary['sql'][] = $db->sql_error_sql;
+			$error_ary['error_code'][] = $db->sql_error_returned;
+		}
 	}
 
 	$db->sql_return_on_error(false);
@@ -999,9 +1010,9 @@ function database_update_info()
 					),
 					'PRIMARY_KEY'		=> 'attempt_id',
 					'KEYS'				=> array(
-						'attempt_ip'			=> array('INDEX', array('attempt_ip', 'attempt_time')),
-						'attempt_forwarded_for'	=> array('INDEX', array('attempt_forwarded_for', 'attempt_time')),
-						'attempt_time'			=> array('INDEX', array('attempt_time')),
+						'att_ip'			=> array('INDEX', array('attempt_ip', 'attempt_time')),
+						'att_for'	=> array('INDEX', array('attempt_forwarded_for', 'attempt_time')),
+						'att_time'			=> array('INDEX', array('attempt_time')),
 						'user_id'				=> array('INDEX', 'user_id'),
 					),
 				),
@@ -1997,6 +2008,27 @@ function change_database_data(&$no_updates, $version)
 			}
 			$db->sql_freeresult($result);
 
+			global $db_tools, $table_prefix;
+
+			// Recover from potentially broken Q&A CAPTCHA table on firebird
+			// Q&A CAPTCHA was uninstallable, so it's safe to remove these
+			// without data loss
+			if ($db_tools->sql_layer == 'firebird')
+			{
+				$tables = array(
+					$table_prefix . 'captcha_questions',
+					$table_prefix . 'captcha_answers',
+					$table_prefix . 'qa_confirm',
+				);
+				foreach ($tables as $table)
+				{
+					if ($db_tools->sql_table_exists($table))
+					{
+						$db_tools->sql_table_drop($table);
+					}
+				}
+			}
+
 			$no_updates = false;
 		break;
 
@@ -2545,6 +2577,11 @@ class updater_db_tools
 			// here lies an array, filled with information compiled on the column's data
 			$prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 
+			if (isset($prepared_column['auto_increment']) && strlen($column_name) > 26) // "${column_name}_gen"
+			{
+				trigger_error("Index name '${column_name}_gen' on table '$table_name' is too long. The maximum auto increment column length is 26 characters.", E_USER_ERROR);
+			}
+
 			// here we add the definition of the new column to the list of columns
 			switch ($this->sql_layer)
 			{
@@ -2694,7 +2731,13 @@ class updater_db_tools
 			case 'firebird':
 				if ($create_sequence)
 				{
-					$statements[] = "CREATE SEQUENCE {$table_name}_seq;";
+					$statements[] = "CREATE GENERATOR {$table_name}_gen;";
+					$statements[] = "SET GENERATOR {$table_name}_gen TO 0;";
+
+					$trigger = "CREATE TRIGGER t_$table_name FOR $table_name\n";
+					$trigger .= "BEFORE INSERT\nAS\nBEGIN\n";
+					$trigger .= "\tNEW.{$create_sequence} = GEN_ID({$table_name}_gen, 1);\nEND;";
+					$statements[] = $trigger;
 				}
 			break;
 		}
@@ -3528,6 +3571,11 @@ class updater_db_tools
 	*/
 	function sql_prepare_column_data($table_name, $column_name, $column_data)
 	{
+		if (strlen($column_name) > 30)
+		{
+			trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
+		}
+
 		// Get type
 		if (strpos($column_data[0], ':') !== false)
 		{
@@ -4101,6 +4149,13 @@ class updater_db_tools
 	{
 		$statements = array();
 
+		$table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
+		if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
+		{
+			$max_length = $table_prefix + 24;
+			trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
+		}
+
 		switch ($this->sql_layer)
 		{
 			case 'firebird':
@@ -4131,6 +4186,13 @@ class updater_db_tools
 	{
 		$statements = array();
 
+		$table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
+		if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
+		{
+			$max_length = $table_prefix + 24;
+			trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
+		}
+
 		// remove index length unless MySQL4
 		if ('mysql_40' != $this->sql_layer)
 		{
diff --git a/tests/dbal/db_tools_test.php b/tests/dbal/db_tools_test.php
new file mode 100644
index 0000000000..eb2af4c4cc
--- /dev/null
+++ b/tests/dbal/db_tools_test.php
@@ -0,0 +1,268 @@
+<?php
+/**
+*
+* @package testing
+* @copyright (c) 2011 phpBB Group
+* @license http://opensource.org/licenses/gpl-license.php GNU Public License
+*
+*/
+
+require_once dirname(__FILE__) . '/../../phpBB/includes/functions.php';
+require_once dirname(__FILE__) . '/../../phpBB/includes/db/db_tools.php';
+
+class phpbb_dbal_db_tools_test extends phpbb_database_test_case
+{
+	protected $db;
+	protected $tools;
+	protected $table_exists;
+	protected $table_data;
+
+	public function getDataSet()
+	{
+		return $this->createXMLDataSet(dirname(__FILE__).'/fixtures/config.xml');
+	}
+
+	protected function setUp()
+	{
+		parent::setUp();
+
+		$this->db = $this->new_dbal();
+		$this->tools = new phpbb_db_tools($this->db);
+
+		$this->table_data = array(
+			'COLUMNS'		=> array(
+				'c_id'				=> array('UINT', NULL, 'auto_increment'),
+				'c_int_size'			=> array('INT:4', 4),
+				'c_bint'				=> array('BINT', 4),
+				'c_uint'				=> array('UINT', 4),
+				'c_uint_size'			=> array('UINT:4', 4),
+				'c_tint_size'			=> array('TINT:2', 4),
+				'c_usint'				=> array('USINT', 4),
+				'c_bool'				=> array('BOOL', 1),
+				'c_vchar'				=> array('VCHAR', 'foo'),
+				'c_vchar_size'		=> array('VCHAR:4', 'foo'),
+				'c_char_size'			=> array('CHAR:4', 'foo'),
+				'c_xstext'			=> array('XSTEXT', 'foo'),
+				'c_stext'				=> array('STEXT', 'foo'),
+				'c_text'				=> array('TEXT', 'foo'),
+				'c_mtext'				=> array('MTEXT', 'foo'),
+				'c_xstext_uni'		=> array('XSTEXT_UNI', 'foo'),
+				'c_stext_uni'			=> array('STEXT_UNI', 'foo'),
+				'c_text_uni'			=> array('TEXT_UNI', 'foo'),
+				'c_mtext_uni'			=> array('MTEXT_UNI', 'foo'),
+				'c_timestamp'			=> array('TIMESTAMP', 4),
+				'c_decimal'			=> array('DECIMAL', 4.2),
+				'c_decimal_size'		=> array('DECIMAL:6', 4.2),
+				'c_pdecimal'			=> array('PDECIMAL', 4.2),
+				'c_pdecimal_size'		=> array('PDECIMAL:7', 4.2),
+				'c_vchar_uni'			=> array('VCHAR_UNI', 'foo'),
+				'c_vchar_uni_size'	=> array('VCHAR_UNI:4', 'foo'),
+				'c_vchar_ci'			=> array('VCHAR_CI', 'foo'),
+				'c_varbinary'			=> array('VARBINARY', 'foo'),
+			),
+			'PRIMARY_KEY'	=> 'c_id',
+			'KEYS'			=> array(
+				'i_simple'	=> array('INDEX', 'c_uint'),
+				'i_uniq'	=> array('UNIQUE', 'c_vchar'),
+				'i_comp'	=> array('INDEX', array('c_vchar_uni', 'c_bool')),
+				'i_comp_uniq'	=> array('UNIQUE', array('c_vchar_size', 'c_usint')),
+			),
+		);
+		$this->tools->sql_create_table('prefix_table_name', $this->table_data);
+		$this->table_exists = true;
+	}
+
+	protected function tearDown()
+	{
+		if ($this->table_exists)
+		{
+			$this->tools->sql_table_drop('prefix_table_name');
+		}
+
+		parent::tearDown();
+	}
+
+	public function test_created_and_drop_table()
+	{
+		// table is empty after creation and queryable
+		$sql = 'SELECT * FROM prefix_table_name';
+		$result = $this->db->sql_query($sql);
+		$this->assertTrue(! $this->db->sql_fetchrow($result));
+		$this->db->sql_freeresult($result);
+
+		$this->table_exists = false;
+		$this->tools->sql_table_drop('prefix_table_name');
+	}
+
+	static protected function get_default_values()
+	{
+		return array(
+			'c_int_size' => 0,
+			'c_bint' => 0,
+			'c_uint' => 0,
+			'c_uint_size' => 0,
+			'c_tint_size' => 0,
+			'c_usint' => 0,
+			'c_bool' => 0,
+			'c_vchar' => '',
+			'c_vchar_size' => '',
+			'c_char_size' => '',
+			'c_xstext' => '',
+			'c_stext' => '',
+			'c_text' => '',
+			'c_mtext' => '',
+			'c_xstext_uni' => '',
+			'c_stext_uni' => '',
+			'c_text_uni' => '',
+			'c_mtext_uni' => '',
+			'c_timestamp' => 0,
+			'c_decimal' => 0,
+			'c_decimal_size' => 0,
+			'c_pdecimal' => 0,
+			'c_pdecimal_size' => 0,
+			'c_vchar_uni' => '',
+			'c_vchar_uni_size' => '',
+			'c_vchar_ci' => '',
+			'c_varbinary' => '',
+		);
+	}
+
+	static public function column_values()
+	{
+		return array(
+			array('c_int_size', -9999),
+			array('c_bint', '99999999999999999'),
+			array('c_uint', 16777215),
+			array('c_uint_size', 9999),
+			array('c_tint_size', -99),
+			array('c_usint', 99),
+			array('c_bool', 0),
+			array('c_vchar', str_repeat('a', 255)),
+			array('c_vchar_size', str_repeat('a', 4)),
+			array('c_char_size', str_repeat('a', 4)),
+			array('c_xstext', str_repeat('a', 1000)),
+			array('c_stext', str_repeat('a', 3000)),
+			array('c_text', str_repeat('a', 8000)),
+			array('c_mtext', str_repeat('a', 10000)),
+			array('c_xstext_uni', str_repeat("\xC3\x84", 100)),
+			array('c_stext_uni', str_repeat("\xC3\x84", 255)),
+			array('c_text_uni', str_repeat("\xC3\x84", 4000)),
+			array('c_mtext_uni', str_repeat("\xC3\x84", 10000)),
+			array('c_timestamp', 2147483647),
+			array('c_decimal', 999.99),
+			array('c_decimal_size', 9999.99),
+			array('c_pdecimal', 999.999),
+			array('c_pdecimal_size', 9999.999),
+			array('c_vchar_uni', str_repeat("\xC3\x84", 255)),
+			array('c_vchar_uni_size', str_repeat("\xC3\x84", 4)),
+			array('c_vchar_ci', str_repeat("\xC3\x84", 255)),
+			array('c_varbinary', str_repeat("\x00\xFF", 127)),
+		);
+	}
+
+	/**
+	* @dataProvider column_values
+	*/
+	public function test_created_column($column_name, $column_value)
+	{
+		$row_insert = self::get_default_values();
+		$row_insert[$column_name] = $column_value;
+
+		// empty table
+		$sql = 'DELETE FROM prefix_table_name';
+		$result = $this->db->sql_query($sql);
+
+		$sql = 'INSERT INTO prefix_table_name ' . $this->db->sql_build_array('INSERT', $row_insert);
+		$result = $this->db->sql_query($sql);
+
+		$sql = "SELECT *
+			FROM prefix_table_name";
+		$result = $this->db->sql_query($sql);
+		$row_actual = $this->db->sql_fetchrow($result);
+		$this->db->sql_freeresult($result);
+
+		$row_expect = $row_insert;
+
+		unset($row_actual['id']); // auto increment id changes, so ignore
+
+		$type = $this->table_data['COLUMNS'][$column_name][0];
+		$this->assertEquals($row_expect[$column_name], $row_actual[$column_name], "Column $column_name of type $type should have equal return and input value.");
+	}
+
+	public function test_auto_increment()
+	{
+		$sql = 'DELETE FROM prefix_table_name';
+		$result = $this->db->sql_query($sql);
+
+		$row1 = array_merge(self::get_default_values(), array(
+			'c_uint' => 1,
+			'c_vchar' => '1', // these values are necessary to avoid unique index issues
+			'c_vchar_size' => '1',
+		));
+		$row2 = array_merge(self::get_default_values(), array(
+			'c_uint' => 2,
+			'c_vchar' => '2',
+			'c_vchar_size' => '2',
+		));
+
+		$sql = 'INSERT INTO prefix_table_name ' . $this->db->sql_build_array('INSERT', $row1);
+		$result = $this->db->sql_query($sql);
+		$id1 = $this->db->sql_nextid();
+
+		$sql = 'INSERT INTO prefix_table_name ' . $this->db->sql_build_array('INSERT', $row2);
+		$result = $this->db->sql_query($sql);
+		$id2 = $this->db->sql_nextid();
+
+		$this->assertGreaterThan($id1, $id2, 'Auto increment should increase the id value');
+
+		$sql = "SELECT *
+			FROM prefix_table_name WHERE c_id = $id1";
+		$result = $this->db->sql_query($sql);
+		$row_actual = $this->db->sql_fetchrow($result);
+		$this->db->sql_freeresult($result);
+
+		$row1['c_id'] = $id1;
+		$this->assertEquals($row1, $row_actual);
+
+		$sql = "SELECT *
+			FROM prefix_table_name WHERE c_id = $id2";
+		$result = $this->db->sql_query($sql);
+		$row_actual = $this->db->sql_fetchrow($result);
+		$this->db->sql_freeresult($result);
+
+		$row2['c_id'] = $id2;
+		$this->assertEquals($row2, $row_actual);
+	}
+
+	public function test_column_exists()
+	{
+		$this->assertTrue($this->tools->sql_column_exists('prefix_table_name', 'c_id'));
+		$this->assertFalse($this->tools->sql_column_exists('prefix_table_name', 'column_does_not_exist'));
+	}
+
+	public function test_column_remove()
+	{
+		$this->assertTrue($this->tools->sql_column_exists('prefix_table_name', 'c_id'));
+
+		$this->assertTrue($this->tools->sql_column_remove('prefix_table_name', 'c_id'));
+
+		$this->assertFalse($this->tools->sql_column_exists('prefix_table_name', 'c_id'));
+	}
+
+	public function test_table_exists()
+	{
+		$this->assertTrue($this->tools->sql_table_exists('prefix_table_name'));
+		$this->assertFalse($this->tools->sql_table_exists('prefix_does_not_exist'));
+	}
+
+	public function test_table_drop()
+	{
+		$this->tools->sql_create_table('prefix_test_table',
+			array('COLUMNS' => array(
+				'foo' => array('UINT', 42)))
+		);
+
+		$this->tools->sql_table_drop('prefix_test_table');
+	}
+
+}