new wrapper for LIKE expressions to streamline the fixes. We actually need to adjust them for different DBMS as well as SQLite2 not supporting escaping characters in LIKE statements (which is a reason why we think about dropping sqlite support completely).

git-svn-id: file:///svn/phpbb/trunk@7788 89ea8834-ac86-4346-8a33-228a782c2dd0
author: Meik Sievertsen <acydburn@phpbb.com> 2007-06-23 12:16:20 +0000
committer: Meik Sievertsen <acydburn@phpbb.com> 2007-06-23 12:16:20 +0000
commit: 318418b0f266998895f88e9fcbcd3873a518c4b5 (patch)
tree: 7fb1714171d4744d4df35138da1a3e799f28db90 /phpBB/includes
parent: 1e2db705ca061228ddedd312e00d99cfce2a2ae6 (diff)
download: forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar
forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar.gz
forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar.bz2
forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar.xz
forums-318418b0f266998895f88e9fcbcd3873a518c4b5.zip
14 files changed, 83 insertions, 70 deletions
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);
author	Meik Sievertsen <acydburn@phpbb.com>	2007-06-23 12:16:20 +0000
committer	Meik Sievertsen <acydburn@phpbb.com>	2007-06-23 12:16:20 +0000
commit	318418b0f266998895f88e9fcbcd3873a518c4b5 (patch)
tree	7fb1714171d4744d4df35138da1a3e799f28db90 /phpBB/includes
parent	1e2db705ca061228ddedd312e00d99cfce2a2ae6 (diff)
download	forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar.gz forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar.bz2 forums-318418b0f266998895f88e9fcbcd3873a518c4b5.tar.xz forums-318418b0f266998895f88e9fcbcd3873a518c4b5.zip