18 files changed, 97 insertions, 82 deletions
diff --git a/phpBB/docs/CHANGELOG.html b/phpBB/docs/CHANGELOG.html
index 32bc7d797e..656624b569 100644
--- a/phpBB/docs/CHANGELOG.html
+++ b/phpBB/docs/CHANGELOG.html
@@ -295,6 +295,7 @@ p a {
 		<li>[Fix] Correctly check permissions on the UCP subscription/bookmark pages (Bug #12595)</li>
 		<li>[Fix] Only convert non-orphaned PMs</li>
 		<li>[Fix] Fixed a few Postgres related errors (Bug #12587)</li>
+		<li>[Feature] New DBAL wrapper for LIKE expressions / sql_like_expression()</li>
 
 	</ul>
 
diff --git a/phpBB/includes/acp/acp_permission_roles.php b/phpBB/includes/acp/acp_permission_roles.php
index 4728168a82..d3df765e6e 100644
--- a/phpBB/includes/acp/acp_permission_roles.php
+++ b/phpBB/includes/acp/acp_permission_roles.php
@@ -239,7 +239,7 @@ class acp_permission_roles
 				{
 					$sql = 'SELECT auth_option_id, auth_option
 						FROM ' . ACL_OPTIONS_TABLE . "
-						WHERE auth_option LIKE '{$permission_type}%'
+						WHERE auth_option " . $db->sql_like_expression($permission_type . '%') . "
 							AND auth_option <> '{$permission_type}'
 						ORDER BY auth_option_id";
 					$result = $db->sql_query($sql);
@@ -305,7 +305,7 @@ class acp_permission_roles
 				// We need to fill the auth options array with ACL_NO options ;)
 				$sql = 'SELECT auth_option_id, auth_option
 					FROM ' . ACL_OPTIONS_TABLE . "
-					WHERE auth_option LIKE '{$permission_type}%'
+					WHERE auth_option " . $db->sql_like_expression($permission_type . '%') . "
 						AND auth_option <> '{$permission_type}'
 					ORDER BY auth_option_id";
 				$result = $db->sql_query($sql);
@@ -490,7 +490,7 @@ class acp_permission_roles
 		// Get complete auth array
 		$sql = 'SELECT auth_option, auth_option_id
 			FROM ' . ACL_OPTIONS_TABLE . "
-			WHERE auth_option LIKE '" . $db->sql_escape($permission_type) . "%'";
+			WHERE auth_option " . $db->sql_like_expression($permission_type . '%');
 		$result = $db->sql_query($sql);
 
 		$auth_settings = array();
diff --git a/phpBB/includes/acp/acp_permissions.php b/phpBB/includes/acp/acp_permissions.php
index dfc1355f5c..8189db23d3 100644
--- a/phpBB/includes/acp/acp_permissions.php
+++ b/phpBB/includes/acp/acp_permissions.php
@@ -1069,8 +1069,8 @@ class acp_permissions
 		global $db, $user;
 
 		$sql_forum_id = ($permission_scope == 'global') ? 'AND a.forum_id = 0' : ((sizeof($forum_id)) ? 'AND ' . $db->sql_in_set('a.forum_id', $forum_id) : 'AND a.forum_id <> 0');
-		$sql_permission_option = "AND o.auth_option LIKE '" . $db->sql_escape($permission_type) . "%'";
-
+		$sql_permission_option = ' AND o.auth_option ' . $db->sql_like_expression($permission_type . '%');
+		
 		$sql = $db->sql_build_query('SELECT_DISTINCT', array(
 			'SELECT'	=> 'u.username, u.username_clean, u.user_regdate, u.user_id',
 
diff --git a/phpBB/includes/acp/acp_prune.php b/phpBB/includes/acp/acp_prune.php
index 7405a271ed..95ad9eee31 100644
--- a/phpBB/includes/acp/acp_prune.php
+++ b/phpBB/includes/acp/acp_prune.php
@@ -393,8 +393,8 @@ class acp_prune
 			$sort_by_types = array('username', 'user_email', 'user_posts', 'user_regdate', 'user_lastvisit');
 
 			$where_sql = '';
-			$where_sql .= ($username) ? " AND username_clean LIKE '" . $db->sql_escape(str_replace('*', '%', utf8_clean_string($username))) . "'" : '';
-			$where_sql .= ($email) ? " AND user_email LIKE '" . $db->sql_escape(str_replace('*', '%', $email)) . "' " : '';
+			$where_sql .= ($username) ? ' AND username_clean ' . $db->sql_like_expression(str_replace('*', '%', utf8_clean_string($username))) : '';
+			$where_sql .= ($email) ? ' AND user_email ' . $db->sql_like_expression(str_replace('*', '%', $email)) . ' ' : '';
 			$where_sql .= (sizeof($joined)) ? " AND user_regdate " . $key_match[$joined_select] . ' ' . gmmktime(0, 0, 0, (int) $joined[1], (int) $joined[2], (int) $joined[0]) : '';
 			$where_sql .= ($count !== '') ? " AND user_posts " . $key_match[$count_select] . ' ' . (int) $count . ' ' : '';
 			$where_sql .= (sizeof($active)) ? " AND user_lastvisit " . $key_match[$active_select] . " " . gmmktime(0, 0, 0, (int) $active[1], (int) $active[2], (int) $active[0]) : '';
diff --git a/phpBB/includes/acp/acp_users.php b/phpBB/includes/acp/acp_users.php
index 122d1d9e35..0f7190d952 100644
--- a/phpBB/includes/acp/acp_users.php
+++ b/phpBB/includes/acp/acp_users.php
@@ -1830,15 +1830,9 @@ class acp_users
 				{
 					// Select auth options
 					$sql = 'SELECT auth_option, is_local, is_global
-						FROM ' . ACL_OPTIONS_TABLE . "
-						WHERE auth_option LIKE '%" . $db->sql_escape('\_') . "'";
-
-					if ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc')
-					{
-						$sql .= " ESCAPE '\\' ";
-					}
-
-					$sql .= 'AND is_global = 1
+						FROM ' . ACL_OPTIONS_TABLE . '
+						WHERE auth_option ' . $db->sql_like_expression('%_') . '
+							AND is_global = 1
 						ORDER BY auth_option';
 					$result = $db->sql_query($sql);
 
@@ -1857,15 +1851,9 @@ class acp_users
 				{
 					$sql = 'SELECT auth_option, is_local, is_global
 						FROM ' . ACL_OPTIONS_TABLE . "
-						WHERE auth_option LIKE '%" . $db->sql_escape('\_') . "'";
-
-					if ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc')
-					{
-						$sql .= " ESCAPE '\\' ";
-					}
-
-					$sql .= 'AND is_local = 1
-						ORDER BY is_global DESC, auth_option';
+						WHERE auth_option " . $db->sql_like_expression('%_') . "
+							AND is_local = 1
+						ORDER BY is_global DESC, auth_option";
 					$result = $db->sql_query($sql);
 
 					while ($row = $db->sql_fetchrow($result))
diff --git a/phpBB/includes/acp/auth.php b/phpBB/includes/acp/auth.php
index 77b199b8a5..0fb64b60e6 100644
--- a/phpBB/includes/acp/auth.php
+++ b/phpBB/includes/acp/auth.php
@@ -966,20 +966,9 @@ class auth_admin extends auth
 		if ($permission_type !== false)
 		{
 			// Get permission type
-			if ($db->sql_layer == 'sqlite')
-			{
-				$sql = 'SELECT auth_option, auth_option_id
-					FROM ' . ACL_OPTIONS_TABLE . "
-					WHERE auth_option LIKE '" . $db->sql_escape($permission_type) . "%'";
-			}
-			else
-			{
-				$sql = 'SELECT auth_option, auth_option_id
-					FROM ' . ACL_OPTIONS_TABLE . "
-					WHERE auth_option LIKE '" . $db->sql_escape(str_replace('_', "\_", $permission_type)) . "%'";
-				$sql .= ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc') ? " ESCAPE '\\'" : '';
-			}
-
+			$sql = 'SELECT auth_option, auth_option_id
+				FROM ' . ACL_OPTIONS_TABLE . "
+				WHERE auth_option " . $db->sql_like_expression($permission_type . '%');
 			$result = $db->sql_query($sql);
 
 			$auth_id_ary = array();
diff --git a/phpBB/includes/auth.php b/phpBB/includes/auth.php
index c7b53b4a9a..6601527026 100644
--- a/phpBB/includes/auth.php
+++ b/phpBB/includes/auth.php
@@ -842,15 +842,7 @@ class auth
 		{
 			if (strpos($auth_options, '%') !== false)
 			{
-				if (strpos($auth_options, '_') !== false && $db->sql_layer !== 'sqlite')
-				{
-					$sql_opts = "AND $key LIKE '" . $db->sql_escape(str_replace('_', "\_", $auth_options)) . "'";
-					$sql_opts .= ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc') ? " ESCAPE '\\' " : '';
-				}
-				else
-				{
-					$sql_opts = "AND $key LIKE '" . $db->sql_escape($auth_options) . "'";
-				}
+				$sql_opts = "AND $key " . $db->sql_like_expression($auth_options);
 			}
 			else
 			{
@@ -881,16 +873,7 @@ class auth
 				{
 					if (strpos($option, '%') !== false)
 					{
-						if (strpos($option, '_') !== false && $db->sql_layer !== 'sqlite')
-						{
-							$_sql = $key . " LIKE '" . $db->sql_escape(str_replace('_', "\_", $option)) . "'";
-							$_sql .= ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc') ? " ESCAPE '\\'" : '';
-							$sql[] = $_sql;
-						}
-						else
-						{
-							$sql[] = $key . " LIKE '" . $db->sql_escape($option) . "'";
-						}
+						$sql[] = $key . ' ' . $db->sql_like_expression($option);
 					}
 					else
 					{
diff --git a/phpBB/includes/db/dbal.php b/phpBB/includes/db/dbal.php
index 8ecb7627e5..141a7cb71e 100644
--- a/phpBB/includes/db/dbal.php
+++ b/phpBB/includes/db/dbal.php
@@ -192,6 +192,21 @@ class dbal
 	}
 
 	/**
+	* Correctly adjust LIKE expression for special characters
+	* Some DBMS are handling them in a different way we need to take into account
+	*/
+	function sql_like_expression($expression)
+	{
+		// Standard for most DBMS
+		if (strpos($expression, '_') === false)
+		{
+			return 'LIKE \'' . $this->sql_escape($expression) . '\'';
+		}
+
+		return 'LIKE \'' . $this->sql_escape(str_replace('_', "\_", $expression)) . '\'';
+	}
+
+	/**
 	* SQL Transaction
 	* @access private
 	*/
diff --git a/phpBB/includes/db/mssql.php b/phpBB/includes/db/mssql.php
index 44ea132a8f..ba8e8681ec 100644
--- a/phpBB/includes/db/mssql.php
+++ b/phpBB/includes/db/mssql.php
@@ -309,6 +309,22 @@ class dbal_mssql extends dbal
 	}
 
 	/**
+	* Correctly adjust LIKE expression for special characters
+	* MSSQL needs an escape character being defined
+	*/
+	function sql_like_expression($expression)
+	{
+		// Standard for most DBMS
+		if (strpos($expression, '_') === false)
+		{
+			return 'LIKE \'' . $this->sql_escape($expression) . '\'';
+		}
+
+		// sql_like_expression is only allowed directly within single quotes (to ease the use of it), therefore the special writing of ESCAPE below
+		return 'LIKE \'' . $this->sql_escape(str_replace('_', "\_", $expression)) . "' ESCAPE '\\'";
+	}
+
+	/**
 	* return sql error array
 	* @access private
 	*/
diff --git a/phpBB/includes/db/mssql_odbc.php b/phpBB/includes/db/mssql_odbc.php
index 6803228e13..9133f5d0de 100644
--- a/phpBB/includes/db/mssql_odbc.php
+++ b/phpBB/includes/db/mssql_odbc.php
@@ -320,6 +320,22 @@ class dbal_mssql_odbc extends dbal
 	}
 
 	/**
+	* Correctly adjust LIKE expression for special characters
+	* MSSQL needs an escape character being defined
+	*/
+	function sql_like_expression($expression)
+	{
+		// Standard for most DBMS
+		if (strpos($expression, '_') === false)
+		{
+			return 'LIKE \'' . $this->sql_escape($expression) . '\'';
+		}
+
+		// sql_like_expression is only allowed directly within single quotes (to ease the use of it), therefore the special writing of ESCAPE below
+		return 'LIKE \'' . $this->sql_escape(str_replace('_', "\_", $expression)) . "' ESCAPE '\\'";
+	}
+
+	/**
 	* Build db-specific query data
 	* @access private
 	*/
diff --git a/phpBB/includes/db/sqlite.php b/phpBB/includes/db/sqlite.php
index 398d044672..88a0d612b4 100644
--- a/phpBB/includes/db/sqlite.php
+++ b/phpBB/includes/db/sqlite.php
@@ -242,6 +242,20 @@ class dbal_sqlite extends dbal
 	}
 
 	/**
+	* Correctly adjust LIKE expression for special characters
+	* For SQLite an underscore is a not-known character... this may change with SQLite3
+	*/
+	function sql_like_expression($expression)
+	{
+		if (strpos($expression, '_') === false)
+		{
+			return "LIKE '" . $this->sql_escape($expression) . "'";
+		}
+
+		return "GLOB '" . $this->sql_escape(str_replace('%', '*', $expression)) . "'";
+	}
+
+	/**
 	* return sql error array
 	* @access private
 	*/
diff --git a/phpBB/includes/functions.php b/phpBB/includes/functions.php
index ba9ec8dad2..61c49c978b 100644
--- a/phpBB/includes/functions.php
+++ b/phpBB/includes/functions.php
@@ -3877,14 +3877,7 @@ function page_header($page_title = '', $display_online_list = true)
 		{
 			$f = request_var('f', 0);
 
-			// Do not change this (it is defined as _f_={forum_id}x within session.php)
-			$reading_sql = " AND s.session_page LIKE '%" . $db->sql_escape("\_f\_={$f}x") . "%'";
-
-			// Specify escape character for MSSQL
-			if ($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc')
-			{
-				$reading_sql .= " ESCAPE '\\' ";
-			}
+			$reading_sql = ' AND s.session_page ' . $db->sql_like_expression("%_f_={$f}x%");
 		}
 
 		// Get number of online guests
diff --git a/phpBB/includes/functions_admin.php b/phpBB/includes/functions_admin.php
index d734b67706..2017904038 100644
--- a/phpBB/includes/functions_admin.php
+++ b/phpBB/includes/functions_admin.php
@@ -2206,8 +2206,7 @@ function cache_moderators()
 				AND a.group_id = ug.group_id
 				AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "
 				AND ug.user_pending = 0
-				AND o.auth_option LIKE '" . $db->sql_escape('m\_') . "%'" . 
-				(($db->sql_layer == 'mssql' || $db->sql_layer == 'mssql_odbc') ? " ESCAPE '\\'" : ''),
+				AND o.auth_option " . $db->sql_like_expression('m_%'),
 		));
 		$result = $db->sql_query($sql);
 
diff --git a/phpBB/includes/search/search.php b/phpBB/includes/search/search.php
index ee9fa0ea98..545e7fe0eb 100755
--- a/phpBB/includes/search/search.php
+++ b/phpBB/includes/search/search.php
@@ -273,7 +273,7 @@ class search_backend
 			$sql_where = '';
 			foreach ($words as $word)
 			{
-				$sql_where .= ' OR search_keywords LIKE \'%' . $db->sql_escape($word) . '%\'';
+				$sql_where .= " OR search_keywords " . $db->sql_like_expression('%' . $word . '%');
 			}
 
 			$sql = 'SELECT search_key
diff --git a/phpBB/includes/template.php b/phpBB/includes/template.php
index 3326852ee7..9a4d259df2 100644
--- a/phpBB/includes/template.php
+++ b/phpBB/includes/template.php
@@ -226,7 +226,7 @@ class template
 				FROM ' . STYLES_TEMPLATE_DATA_TABLE . '
 				WHERE template_id = ' . $user->theme['template_id'] . "
 					AND (template_filename = '" . $db->sql_escape($this->filename[$handle]) . "'
-						OR template_included LIKE '%" . $db->sql_escape($this->filename[$handle]) . ":%')";
+						OR template_included " . $db->sql_like_expression('%' . $this->filename[$handle] . ':%') . ')';
 			$result = $db->sql_query($sql);
 			$row = $db->sql_fetchrow($result);
 
diff --git a/phpBB/install/convertors/convert_phpbb20.php b/phpBB/install/convertors/convert_phpbb20.php
index 604df58e9e..796c2e19d4 100644
--- a/phpBB/install/convertors/convert_phpbb20.php
+++ b/phpBB/install/convertors/convert_phpbb20.php
@@ -31,7 +31,7 @@ unset($dbpasswd);
 */
 $convertor_data = array(
 	'forum_name'	=> 'phpBB 2.0.x',
-	'version'		=> '1.0.RC2-dev',
+	'version'		=> '1.0.RC2',
 	'phpbb_version'	=> '3.0.0',
 	'author'		=> '<a href="http://www.phpbb.com/">phpBB Group</a>',
 	'dbms'			=> $dbms,
diff --git a/phpBB/memberlist.php b/phpBB/memberlist.php
index 3458d45fec..7b6345470f 100644
--- a/phpBB/memberlist.php
+++ b/phpBB/memberlist.php
@@ -934,13 +934,13 @@ switch ($mode)
 				$s_find_active_time .= '<option value="' . $key . '"' . $selected . '>' . $value . '</option>';
 			}
 
-			$sql_where .= ($username) ? " AND u.username_clean LIKE '" . str_replace('*', '%', $db->sql_escape(utf8_clean_string($username))) . "'" : '';
-			$sql_where .= ($email) ? " AND u.user_email LIKE '" . str_replace('*', '%', $db->sql_escape($email)) . "' " : '';
-			$sql_where .= ($icq) ? " AND u.user_icq LIKE '" . str_replace('*', '%', $db->sql_escape($icq)) . "' " : '';
-			$sql_where .= ($aim) ? " AND u.user_aim LIKE '" . str_replace('*', '%', $db->sql_escape($aim)) . "' " : '';
-			$sql_where .= ($yahoo) ? " AND u.user_yim LIKE '" . str_replace('*', '%', $db->sql_escape($yahoo)) . "' " : '';
-			$sql_where .= ($msn) ? " AND u.user_msnm LIKE '" . str_replace('*', '%', $db->sql_escape($msn)) . "' " : '';
-			$sql_where .= ($jabber) ? " AND u.user_jabber LIKE '" . str_replace('*', '%', $db->sql_escape($jabber)) . "' " : '';
+			$sql_where .= ($username) ? ' AND u.username_clean ' . $db->sql_like_expression(str_replace('*', '%', utf8_clean_string($username))) : '';
+			$sql_where .= ($email) ? ' AND u.user_email ' . $db->sql_like_expression(str_replace('*', '%', $email)) . ' ' : '';
+			$sql_where .= ($icq) ? ' AND u.user_icq ' . $db->sql_like_expression(str_replace('*', '%', $icq)) . ' ' : '';
+			$sql_where .= ($aim) ? ' AND u.user_aim ' . $db->sql_like_expression(str_replace('*', '%', $aim)) . ' ' : '';
+			$sql_where .= ($yahoo) ? ' AND u.user_yim ' . $db->sql_like_expression(str_replace('*', '%', $yahoo)) . ' ' : '';
+			$sql_where .= ($msn) ? ' AND u.user_msnm ' . $db->sql_like_expression(str_replace('*', '%', $msn)) . ' ' : '';
+			$sql_where .= ($jabber) ? ' AND u.user_jabber ' . $db->sql_like_expression(str_replace('*', '%', $jabber)) . ' ' : '';
 			$sql_where .= (is_numeric($count)) ? ' AND u.user_posts ' . $find_key_match[$count_select] . ' ' . (int) $count . ' ' : '';
 			$sql_where .= (sizeof($joined) > 1) ? " AND u.user_regdate " . $find_key_match[$joined_select] . ' ' . gmmktime(0, 0, 0, intval($joined[1]), intval($joined[2]), intval($joined[0])) : '';
 			$sql_where .= (sizeof($active) > 1) ? " AND u.user_lastvisit " . $find_key_match[$active_select] . ' ' . gmmktime(0, 0, 0, $active[1], intval($active[2]), intval($active[0])) : '';
@@ -1015,12 +1015,12 @@ switch ($mode)
 		{
 			for ($i = 97; $i < 123; $i++)
 			{
-				$sql_where .= " AND u.username_clean NOT LIKE '" . chr($i) . "%'";
+				$sql_where .= ' AND u.username_clean NOT ' . $db->sql_like_expression(chr($i) . '%');
 			}
 		}
 		else if ($first_char)
 		{
-			$sql_where .= " AND u.username_clean LIKE '" . $db->sql_escape(substr($first_char, 0, 1)) . "%'";
+			$sql_where .= ' AND u.username_clean ' . $db->sql_like_expression(substr($first_char, 0, 1) . '%');
 		}
 
 		// Are we looking at a usergroup? If so, fetch additional info
diff --git a/phpBB/search.php b/phpBB/search.php
index 487411d613..5b8bdf5f20 100644
--- a/phpBB/search.php
+++ b/phpBB/search.php
@@ -100,10 +100,11 @@ if ($keywords || $author || $author_id || $search_id || $submit)
 			trigger_error(sprintf($user->lang['TOO_FEW_AUTHOR_CHARS'], $config['min_search_author_chars']));
 		}
 
-		$sql_where = (strpos($author, '*') !== false) ? ' LIKE ' : ' = ';
+		$sql_where = (strpos($author, '*') !== false) ? ' username_clean ' . $db->sql_like_expression(str_replace('*', '%', utf8_clean_string($author))) : " username_clean = '" . $db->sql_escape(utf8_clean_string($author)) . "'";
+
 		$sql = 'SELECT user_id
 			FROM ' . USERS_TABLE . "
-			WHERE username_clean $sql_where '" . $db->sql_escape(preg_replace('#\*+#', '%', utf8_clean_string($author))) . "'
+			WHERE $sql_where
 				AND user_type IN (" . USER_NORMAL . ', ' . USER_FOUNDER . ')';
 		$result = $db->sql_query_limit($sql, 100);