tweak the sql_like_expression feature a little bit to allow correct escaping

git-svn-id: file:///svn/phpbb/trunk@7789 89ea8834-ac86-4346-8a33-228a782c2dd0
author: Meik Sievertsen <acydburn@phpbb.com> 2007-06-24 12:49:13 +0000
committer: Meik Sievertsen <acydburn@phpbb.com> 2007-06-24 12:49:13 +0000
commit: 5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274 (patch)
tree: 63f64d470a44ff71aa4ef83775e3e0727dd76e46 /phpBB/includes
parent: 318418b0f266998895f88e9fcbcd3873a518c4b5 (diff)
download: forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar
forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar.gz
forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar.bz2
forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar.xz
forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.zip
20 files changed, 93 insertions, 50 deletions
diff --git a/phpBB/includes/acp/acp_permission_roles.php b/phpBB/includes/acp/acp_permission_roles.php
index d3df765e6e..674468a862 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 " . $db->sql_like_expression($permission_type . '%') . "
+						WHERE auth_option " . $db->sql_like_expression($permission_type . $db->any_char) . "
 							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 " . $db->sql_like_expression($permission_type . '%') . "
+					WHERE auth_option " . $db->sql_like_expression($permission_type . $db->any_char) . "
 						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 " . $db->sql_like_expression($permission_type . '%');
+			WHERE auth_option " . $db->sql_like_expression($permission_type . $db->any_char);
 		$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 8189db23d3..3a7318231b 100644
--- a/phpBB/includes/acp/acp_permissions.php
+++ b/phpBB/includes/acp/acp_permissions.php
@@ -1069,7 +1069,7 @@ 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 ' . $db->sql_like_expression($permission_type . '%');
+		$sql_permission_option = ' AND o.auth_option ' . $db->sql_like_expression($permission_type . $db->any_char);
 		
 		$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 95ad9eee31..14885394e6 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 ' . $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 .= ($username) ? ' AND username_clean ' . $db->sql_like_expression(str_replace('*', $db->any_char, utf8_clean_string($username))) : '';
+			$where_sql .= ($email) ? ' AND user_email ' . $db->sql_like_expression(str_replace('*', $db->any_char, $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 0f7190d952..6015471b9d 100644
--- a/phpBB/includes/acp/acp_users.php
+++ b/phpBB/includes/acp/acp_users.php
@@ -1831,7 +1831,7 @@ class acp_users
 					// Select auth options
 					$sql = 'SELECT auth_option, is_local, is_global
 						FROM ' . ACL_OPTIONS_TABLE . '
-						WHERE auth_option ' . $db->sql_like_expression('%_') . '
+						WHERE auth_option ' . $db->sql_like_expression($db->any_char . '_') . '
 							AND is_global = 1
 						ORDER BY auth_option';
 					$result = $db->sql_query($sql);
@@ -1851,7 +1851,7 @@ class acp_users
 				{
 					$sql = 'SELECT auth_option, is_local, is_global
 						FROM ' . ACL_OPTIONS_TABLE . "
-						WHERE auth_option " . $db->sql_like_expression('%_') . "
+						WHERE auth_option " . $db->sql_like_expression($db->any_char . '_') . "
 							AND is_local = 1
 						ORDER BY is_global DESC, auth_option";
 					$result = $db->sql_query($sql);
diff --git a/phpBB/includes/acp/auth.php b/phpBB/includes/acp/auth.php
index 0fb64b60e6..7d0b00cc25 100644
--- a/phpBB/includes/acp/auth.php
+++ b/phpBB/includes/acp/auth.php
@@ -968,7 +968,7 @@ class auth_admin extends auth
 			// Get permission type
 			$sql = 'SELECT auth_option, auth_option_id
 				FROM ' . ACL_OPTIONS_TABLE . "
-				WHERE auth_option " . $db->sql_like_expression($permission_type . '%');
+				WHERE auth_option " . $db->sql_like_expression($permission_type . $db->any_char);
 			$result = $db->sql_query($sql);
 
 			$auth_id_ary = array();
diff --git a/phpBB/includes/auth.php b/phpBB/includes/auth.php
index 6601527026..76d6036c77 100644
--- a/phpBB/includes/auth.php
+++ b/phpBB/includes/auth.php
@@ -842,7 +842,7 @@ class auth
 		{
 			if (strpos($auth_options, '%') !== false)
 			{
-				$sql_opts = "AND $key " . $db->sql_like_expression($auth_options);
+				$sql_opts = "AND $key " . $db->sql_like_expression(str_replace('%', $db->any_char, $auth_options));
 			}
 			else
 			{
@@ -873,7 +873,7 @@ class auth
 				{
 					if (strpos($option, '%') !== false)
 					{
-						$sql[] = $key . ' ' . $db->sql_like_expression($option);
+						$sql[] = $key . ' ' . $db->sql_like_expression(str_replace('%', $db->any_char, $option));
 					}
 					else
 					{
diff --git a/phpBB/includes/db/dbal.php b/phpBB/includes/db/dbal.php
index 141a7cb71e..aa8adda86c 100644
--- a/phpBB/includes/db/dbal.php
+++ b/phpBB/includes/db/dbal.php
@@ -50,6 +50,12 @@ class dbal
 	var $sql_layer = '';
 
 	/**
+	* Wildcards for matching any (%) or exactly one (_) character within LIKE expressions
+	*/
+	var $any_char;
+	var $one_char;
+
+	/**
 	* Constructor
 	*/
 	function dbal()
@@ -63,6 +69,10 @@ class dbal
 		// Fill default sql layer based on the class being called.
 		// This can be changed by the specified layer itself later if needed.
 		$this->sql_layer = substr(get_class($this), 5);
+
+		// Do not change this please! This variable is used to easy the use of it - and is hardcoded.
+		$this->any_char = chr(0) . '%';
+		$this->one_char = chr(0) . '_';
 	}
 
 	/**
@@ -193,17 +203,17 @@ class dbal
 
 	/**
 	* Correctly adjust LIKE expression for special characters
-	* Some DBMS are handling them in a different way we need to take into account
+	* Some DBMS are handling them in a different way
+	*
+	* @param string $expression The expression to use. Every wildcard is escaped, except $this->any_char and $this->one_char
+	* @return string LIKE expression including the keyword!
 	*/
 	function sql_like_expression($expression)
 	{
-		// Standard for most DBMS
-		if (strpos($expression, '_') === false)
-		{
-			return 'LIKE \'' . $this->sql_escape($expression) . '\'';
-		}
+		$expression = str_replace(array('_', '%'), array("\_", "\%"), $expression);
+		$expression = str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
 
-		return 'LIKE \'' . $this->sql_escape(str_replace('_', "\_", $expression)) . '\'';
+		return $this->_sql_like_expression('LIKE \'' . $this->sql_escape($expression) . '\'');
 	}
 
 	/**
diff --git a/phpBB/includes/db/firebird.php b/phpBB/includes/db/firebird.php
index 6139d8608b..8aa6c43512 100644
--- a/phpBB/includes/db/firebird.php
+++ b/phpBB/includes/db/firebird.php
@@ -409,6 +409,15 @@ class dbal_firebird extends dbal
 	}
 
 	/**
+	* Build LIKE expression
+	* @access private
+	*/
+	function _sql_like_expression($expression)
+	{
+		return $expression;
+	}
+
+	/**
 	* Build db-specific query data
 	* @access private
 	*/
diff --git a/phpBB/includes/db/mssql.php b/phpBB/includes/db/mssql.php
index ba8e8681ec..422a5d44a4 100644
--- a/phpBB/includes/db/mssql.php
+++ b/phpBB/includes/db/mssql.php
@@ -309,19 +309,12 @@ class dbal_mssql extends dbal
 	}
 
 	/**
-	* Correctly adjust LIKE expression for special characters
-	* MSSQL needs an escape character being defined
+	* Build LIKE expression
+	* @access private
 	*/
-	function sql_like_expression($expression)
+	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 $expression . " ESCAPE '\\'";
 	}
 
 	/**
diff --git a/phpBB/includes/db/mssql_odbc.php b/phpBB/includes/db/mssql_odbc.php
index 9133f5d0de..290142103f 100644
--- a/phpBB/includes/db/mssql_odbc.php
+++ b/phpBB/includes/db/mssql_odbc.php
@@ -320,19 +320,12 @@ class dbal_mssql_odbc extends dbal
 	}
 
 	/**
-	* Correctly adjust LIKE expression for special characters
-	* MSSQL needs an escape character being defined
+	* Build LIKE expression
+	* @access private
 	*/
-	function sql_like_expression($expression)
+	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 $expression . " ESCAPE '\\'";
 	}
 
 	/**
diff --git a/phpBB/includes/db/mysql.php b/phpBB/includes/db/mysql.php
index f83019f58b..71a2002eb4 100644
--- a/phpBB/includes/db/mysql.php
+++ b/phpBB/includes/db/mysql.php
@@ -277,6 +277,15 @@ class dbal_mysql extends dbal
 	}
 
 	/**
+	* Build LIKE expression
+	* @access private
+	*/
+	function _sql_like_expression($expression)
+	{
+		return $expression;
+	}
+
+	/**
 	* Build db-specific query data
 	* @access private
 	*/
diff --git a/phpBB/includes/db/mysqli.php b/phpBB/includes/db/mysqli.php
index 42a1057970..660188d1c7 100644
--- a/phpBB/includes/db/mysqli.php
+++ b/phpBB/includes/db/mysqli.php
@@ -246,6 +246,15 @@ class dbal_mysqli extends dbal
 	}
 
 	/**
+	* Build LIKE expression
+	* @access private
+	*/
+	function _sql_like_expression($expression)
+	{
+		return $expression;
+	}
+
+	/**
 	* Build db-specific query data
 	* @access private
 	*/
diff --git a/phpBB/includes/db/oracle.php b/phpBB/includes/db/oracle.php
index 709cb653e9..76a920d4b8 100644
--- a/phpBB/includes/db/oracle.php
+++ b/phpBB/includes/db/oracle.php
@@ -533,6 +533,15 @@ class dbal_oracle extends dbal
 		return str_replace("'", "''", $msg);
 	}
 
+	/**
+	* Build LIKE expression
+	* @access private
+	*/
+	function _sql_like_expression($expression)
+	{
+		return $expression . " ESCAPE '\\'";
+	}
+
 	function _sql_custom_build($stage, $data)
 	{
 		return $data;
diff --git a/phpBB/includes/db/postgres.php b/phpBB/includes/db/postgres.php
index e85a8d8f3f..340c32b37a 100644
--- a/phpBB/includes/db/postgres.php
+++ b/phpBB/includes/db/postgres.php
@@ -346,6 +346,15 @@ class dbal_postgres extends dbal
 	}
 
 	/**
+	* Build LIKE expression
+	* @access private
+	*/
+	function _sql_like_expression($expression)
+	{
+		return $expression;
+	}
+
+	/**
 	* return sql error array
 	* @access private
 	*/
diff --git a/phpBB/includes/db/sqlite.php b/phpBB/includes/db/sqlite.php
index 88a0d612b4..3248b439c6 100644
--- a/phpBB/includes/db/sqlite.php
+++ b/phpBB/includes/db/sqlite.php
@@ -247,12 +247,14 @@ class dbal_sqlite extends dbal
 	*/
 	function sql_like_expression($expression)
 	{
-		if (strpos($expression, '_') === false)
-		{
-			return "LIKE '" . $this->sql_escape($expression) . "'";
-		}
+		// Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it!
+		// We only catch * and ? here, not the character map possible on file globbing.
+		$expression = str_replace(array(chr(0) . '_', chr(0) . '%'), array(chr(0) . '?', chr(0) . '*'), $expression);
+
+		$expression = str_replace(array('?', '*'), array("\?", "\*"), $expression);
+		$expression = str_replace(array(chr(0) . "\?", chr(0) . "\*"), array('?', '*'), $expression);
 
-		return "GLOB '" . $this->sql_escape(str_replace('%', '*', $expression)) . "'";
+		return 'GLOB \'' . $this->sql_escape($expression) . '\'';
 	}
 
 	/**
diff --git a/phpBB/includes/functions.php b/phpBB/includes/functions.php
index 61c49c978b..a91bddd9b8 100644
--- a/phpBB/includes/functions.php
+++ b/phpBB/includes/functions.php
@@ -3877,7 +3877,7 @@ function page_header($page_title = '', $display_online_list = true)
 		{
 			$f = request_var('f', 0);
 
-			$reading_sql = ' AND s.session_page ' . $db->sql_like_expression("%_f_={$f}x%");
+			$reading_sql = ' AND s.session_page ' . $db->sql_like_expression("{$db->any_char}_f_={$f}x{$db->any_char}");
 		}
 
 		// Get number of online guests
diff --git a/phpBB/includes/functions_admin.php b/phpBB/includes/functions_admin.php
index 2017904038..5e94b0d9e0 100644
--- a/phpBB/includes/functions_admin.php
+++ b/phpBB/includes/functions_admin.php
@@ -2206,7 +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 " . $db->sql_like_expression('m_%'),
+				AND o.auth_option " . $db->sql_like_expression('m_' . $db->any_char),
 		));
 		$result = $db->sql_query($sql);
 
diff --git a/phpBB/includes/search/search.php b/phpBB/includes/search/search.php
index 545e7fe0eb..f4a1ddac27 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 " . $db->sql_like_expression('%' . $word . '%');
+				$sql_where .= " OR search_keywords " . $db->sql_like_expression($db->any_char . $word . $db->any_char);
 			}
 
 			$sql = 'SELECT search_key
diff --git a/phpBB/includes/template.php b/phpBB/includes/template.php
index 9a4d259df2..9095a31dba 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 " . $db->sql_like_expression('%' . $this->filename[$handle] . ':%') . ')';
+						OR template_included " . $db->sql_like_expression($db->any_char . $this->filename[$handle] . ':' . $db->any_char) . ')';
 			$result = $db->sql_query($sql);
 			$row = $db->sql_fetchrow($result);
 
diff --git a/phpBB/includes/ucp/ucp_register.php b/phpBB/includes/ucp/ucp_register.php
index 513e27f503..69328e2ef3 100644
--- a/phpBB/includes/ucp/ucp_register.php
+++ b/phpBB/includes/ucp/ucp_register.php
@@ -148,7 +148,7 @@ class ucp_register
 			'email'				=> strtolower(request_var('email', '')),
 			'email_confirm'		=> strtolower(request_var('email_confirm', '')),
 			'confirm_code'		=> request_var('confirm_code', ''),
-			'lang'				=> request_var('lang', $user->lang_name),
+			'lang'				=> basename(request_var('lang', $user->lang_name)),
 			'tz'				=> request_var('tz', (float) $timezone),
 		);
author	Meik Sievertsen <acydburn@phpbb.com>	2007-06-24 12:49:13 +0000
committer	Meik Sievertsen <acydburn@phpbb.com>	2007-06-24 12:49:13 +0000
commit	5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274 (patch)
tree	63f64d470a44ff71aa4ef83775e3e0727dd76e46 /phpBB/includes
parent	318418b0f266998895f88e9fcbcd3873a518c4b5 (diff)
download	forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar.gz forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar.bz2 forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.tar.xz forums-5aa220bcd21c6e3decd8f2b9833dc90a8ee6a274.zip