12 files changed, 175 insertions, 3 deletions
diff --git a/phpBB/phpbb/db/driver/driver.php b/phpBB/phpbb/db/driver/driver.php
index 3e9110d8bc..9fc04d47a1 100644
--- a/phpBB/phpbb/db/driver/driver.php
+++ b/phpBB/phpbb/db/driver/driver.php
@@ -372,6 +372,17 @@ abstract class driver implements driver_interface
 	/**
 	* {@inheritDoc}
 	*/
+	function sql_not_like_expression($expression)
+	{
+		$expression = utf8_str_replace(array('_', '%'), array("\_", "\%"), $expression);
+		$expression = utf8_str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
+
+		return $this->_sql_not_like_expression('NOT LIKE \'' . $this->sql_escape($expression) . '\'');
+	}
+
+	/**
+	* {@inheritDoc}
+	*/
 	public function sql_case($condition, $action_true, $action_false = false)
 	{
 		$sql_case = 'CASE WHEN ' . $condition;
diff --git a/phpBB/phpbb/db/driver/driver_interface.php b/phpBB/phpbb/db/driver/driver_interface.php
index 6722d059a5..8b487c5d42 100644
--- a/phpBB/phpbb/db/driver/driver_interface.php
+++ b/phpBB/phpbb/db/driver/driver_interface.php
@@ -419,6 +419,16 @@ interface driver_interface
 	public function sql_like_expression($expression);
 
 	/**
+	* Correctly adjust NOT LIKE expression for special characters
+	* 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	A SQL statement like: "NOT LIKE 'bertie_%'"
+	*/
+	public function sql_not_like_expression($expression);
+
+	/**
 	* Explain queries
 	*
 	* @param	string	$mode		Available modes: display, start, stop,
diff --git a/phpBB/phpbb/db/driver/factory.php b/phpBB/phpbb/db/driver/factory.php
index f0fa18051b..fb3a826254 100644
--- a/phpBB/phpbb/db/driver/factory.php
+++ b/phpBB/phpbb/db/driver/factory.php
@@ -420,6 +420,14 @@ class factory implements driver_interface
 	/**
 	* {@inheritdoc}
 	*/
+	public function sql_not_like_expression($expression)
+	{
+		return $this->get_driver()->sql_not_like_expression($expression);
+	}
+
+	/**
+	* {@inheritdoc}
+	*/
 	public function sql_report($mode, $query = '')
 	{
 		return $this->get_driver()->sql_report($mode, $query);
diff --git a/phpBB/phpbb/db/driver/mssql.php b/phpBB/phpbb/db/driver/mssql.php
index 268463a151..f9ea884ce2 100644
--- a/phpBB/phpbb/db/driver/mssql.php
+++ b/phpBB/phpbb/db/driver/mssql.php
@@ -351,6 +351,15 @@ class mssql extends \phpbb\db\driver\driver
 	}
 
 	/**
+	* Build NOT LIKE expression
+	* @access private
+	*/
+	function _sql_not_like_expression($expression)
+	{
+		return $expression . " ESCAPE '\\'";
+	}
+
+	/**
 	* return sql error array
 	* @access private
 	*/
diff --git a/phpBB/phpbb/db/driver/mssql_base.php b/phpBB/phpbb/db/driver/mssql_base.php
index e7101903b8..514df9eaca 100644
--- a/phpBB/phpbb/db/driver/mssql_base.php
+++ b/phpBB/phpbb/db/driver/mssql_base.php
@@ -52,6 +52,15 @@ abstract class mssql_base extends \phpbb\db\driver\driver
 	}
 
 	/**
+	* Build NOT LIKE expression
+	* @access private
+	*/
+	function _sql_not_like_expression($expression)
+	{
+		return $expression . " ESCAPE '\\'";
+	}
+
+	/**
 	* Build db-specific query data
 	* @access private
 	*/
diff --git a/phpBB/phpbb/db/driver/mysql_base.php b/phpBB/phpbb/db/driver/mysql_base.php
index e7c9b63f20..5e0b359134 100644
--- a/phpBB/phpbb/db/driver/mysql_base.php
+++ b/phpBB/phpbb/db/driver/mysql_base.php
@@ -112,6 +112,15 @@ abstract class mysql_base extends \phpbb\db\driver\driver
 	}
 
 	/**
+	* Build NOT LIKE expression
+	* @access private
+	*/
+	function _sql_not_like_expression($expression)
+	{
+		return $expression;
+	}
+
+	/**
 	* Build db-specific query data
 	* @access private
 	*/
diff --git a/phpBB/phpbb/db/driver/oracle.php b/phpBB/phpbb/db/driver/oracle.php
index d1a186f1ba..6dcab5dd7d 100644
--- a/phpBB/phpbb/db/driver/oracle.php
+++ b/phpBB/phpbb/db/driver/oracle.php
@@ -645,6 +645,15 @@ class oracle extends \phpbb\db\driver\driver
 		return $expression . " ESCAPE '\\'";
 	}
 
+	/**
+	* Build NOT LIKE expression
+	* @access private
+	*/
+	function _sql_not_like_expression($expression)
+	{
+		return $expression . " ESCAPE '\\'";
+	}
+
 	function _sql_custom_build($stage, $data)
 	{
 		return $data;
diff --git a/phpBB/phpbb/db/driver/postgres.php b/phpBB/phpbb/db/driver/postgres.php
index 83e9fa51f6..a3b9aa4c6b 100644
--- a/phpBB/phpbb/db/driver/postgres.php
+++ b/phpBB/phpbb/db/driver/postgres.php
@@ -371,6 +371,15 @@ class postgres extends \phpbb\db\driver\driver
 	}
 
 	/**
+	* Build NOT LIKE expression
+	* @access private
+	*/
+	function _sql_not_like_expression($expression)
+	{
+		return $expression;
+	}
+
+	/**
 	* {@inheritDoc}
 	*/
 	function cast_expr_to_bigint($expression)
diff --git a/phpBB/phpbb/db/driver/sqlite.php b/phpBB/phpbb/db/driver/sqlite.php
index 2112e5ba2f..d5da0e2438 100644
--- a/phpBB/phpbb/db/driver/sqlite.php
+++ b/phpBB/phpbb/db/driver/sqlite.php
@@ -277,7 +277,7 @@ class sqlite extends \phpbb\db\driver\driver
 	*/
 	function sql_like_expression($expression)
 	{
-		// Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it!
+		// Unlike LIKE, GLOB is unfortunately case sensitive.
 		// 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);
 
@@ -288,6 +288,23 @@ class sqlite extends \phpbb\db\driver\driver
 	}
 
 	/**
+	* {@inheritDoc}
+	*
+	* For SQLite an underscore is a not-known character...
+	*/
+	function sql_not_like_expression($expression)
+	{
+		// Unlike NOT LIKE, NOT GLOB is unfortunately case sensitive.
+		// 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 'NOT GLOB \'' . $this->sql_escape($expression) . '\'';
+	}
+
+	/**
 	* return sql error array
 	* @access private
 	*/
diff --git a/phpBB/phpbb/db/driver/sqlite3.php b/phpBB/phpbb/db/driver/sqlite3.php
index 0922229e0a..4e3e0d3329 100644
--- a/phpBB/phpbb/db/driver/sqlite3.php
+++ b/phpBB/phpbb/db/driver/sqlite3.php
@@ -260,11 +260,11 @@ class sqlite3 extends \phpbb\db\driver\driver
 	/**
 	* {@inheritDoc}
 	*
-	* For SQLite an underscore is a not-known character...
+	* For SQLite an underscore is an unknown character.
 	*/
 	public function sql_like_expression($expression)
 	{
-		// Unlike LIKE, GLOB is case sensitive (unfortunatly). SQLite users need to live with it!
+		// Unlike LIKE, GLOB is unfortunately case sensitive.
 		// 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);
 
@@ -275,6 +275,23 @@ class sqlite3 extends \phpbb\db\driver\driver
 	}
 
 	/**
+	* {@inheritDoc}
+	*
+	* For SQLite an underscore is an unknown character.
+	*/
+	public function sql_not_like_expression($expression)
+	{
+		// Unlike NOT LIKE, NOT GLOB is unfortunately case sensitive
+		// 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 'NOT GLOB \'' . $this->sql_escape($expression) . '\'';
+	}
+
+	/**
 	* return sql error array
 	*
 	* @return array
diff --git a/tests/dbal/select_test.php b/tests/dbal/select_test.php
index e480716a49..b7074552ba 100644
--- a/tests/dbal/select_test.php
+++ b/tests/dbal/select_test.php
@@ -233,6 +233,66 @@ class phpbb_dbal_select_test extends phpbb_database_test_case
 		$db->sql_freeresult($result);
 	}
 
+	public function not_like_expression_data()
+	{
+		// * = any_char; # = one_char
+		return array(
+			array('barfoo', array(
+				array('username_clean' => 'foobar'),
+				array('username_clean' => 'bertie')
+			)),
+			array('bar', array(
+				array('username_clean' => 'barfoo'),
+				array('username_clean' => 'foobar'),
+				array('username_clean' => 'bertie'),
+			)),
+			array('bar*', array(
+				array('username_clean' => 'foobar'),
+				array('username_clean' => 'bertie'))
+			),
+			array('*bar*', array(array('username_clean' => 'bertie'))),
+			array('b*r', array(
+				array('username_clean' => 'barfoo'),
+				array('username_clean' => 'foobar'),
+				array('username_clean' => 'bertie')
+			)),
+			array('b*e', array(
+				array('username_clean' => 'barfoo'),
+				array('username_clean' => 'foobar')
+			)),
+			array('#b*e', array(
+				array('username_clean' => 'barfoo'),
+				array('username_clean' => 'foobar'),
+				array('username_clean' => 'bertie')
+			)),
+			array('b####e', array(
+				array('username_clean' => 'barfoo'),
+				array('username_clean' => 'foobar')
+			)),
+		);
+	}
+
+	/**
+	* @dataProvider not_like_expression_data
+	*/
+	public function test_not_like_expression($like_expression, $expected)
+	{
+		$db = $this->new_dbal();
+
+		$like_expression = str_replace('*', $db->get_any_char(), $like_expression);
+		$like_expression = str_replace('#', $db->get_one_char(), $like_expression);
+		$where = ($like_expression) ? 'username_clean ' . $db->sql_not_like_expression($like_expression) : '';
+
+		$result = $db->sql_query('SELECT username_clean
+			FROM phpbb_users
+			' . (($where) ? ' WHERE ' . $where : '') . '
+			ORDER BY user_id ASC');
+
+		$this->assertEquals($expected, $db->sql_fetchrowset($result));
+
+		$db->sql_freeresult($result);
+	}
+
 	public function in_set_data()
 	{
 		return array(
diff --git a/tests/di/create_container_test.php b/tests/di/create_container_test.php
index 559c0b122c..4ae6017989 100644
--- a/tests/di/create_container_test.php
+++ b/tests/di/create_container_test.php
@@ -191,6 +191,10 @@ namespace phpbb\db\driver
 		{
 		}
 
+		function sql_not_like_expression($expression)
+		{
+		}
+
 		function sql_fetchrowset($query_id = false)
 		{
 			return array(