if (is_null($value)) {
$where[] = "$key IS NULL";
} else {
+ $isstring = (is_string($value) && !is_number($value));
if ($allowed_types & SQL_PARAMS_NAMED) {
- $normkey = trim(preg_replace('/[^a-zA-Z0-9-_]/', '_', $key), '-_'); // Need to normalize key names
- $where[] = "$key = :$normkey"; // because they can contain, originally,
- $params[$normkey] = $value; // spaces and other forbidden chars when
- } else { // using sql_xxx() functions and friends.
- $where[] = "$key = ?";
+ // Need to verify key names because they can contain, originally,
+ // spaces and other forbidden chars when using sql_xxx() functions and friends.
+ $normkey = trim(preg_replace('/[^a-zA-Z0-9-_]/', '_', $key), '-_');
+ if ($normkey !== $key) {
+ debugging('Invalid key found in the conditions array.');
+ }
+ if ($isstring) {
+ $where[] = $this->sql_binary_equal($key, ':'.$normkey);
+ } else {
+ $where[] = "$key = :$normkey";
+ }
+ $params[$normkey] = $value;
+ } else {
+ if ($isstring) {
+ $where[] = $this->sql_binary_equal($key, '?');
+ } else {
+ $where[] = "$key = ?";
+ }
$params[] = $value;
}
}
return $this->sql_order_by_text($fieldname, $numchars);
}
+ /**
+ * Case and collation sensitive string 'string = string'
+ * @param string $string1
+ * @param string $string2
+ * @return string SQL fragment
+ */
+ public function sql_binary_equal($string1, $string2) {
+ return "$string1 = $string2";
+ }
+
+ /**
+ * Returns 'LIKE' part of a query.
+ *
+ * @param string $fieldname usually name of the table column
+ * @param string $param usually bound query parameter (?, :named)
+ * @param bool $casesensitive use case sensitive search
+ * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+ * @param string $escapechar escape char for '%' and '_'
+ * @return string SQL code fragment
+ */
+ public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+ if (strpos($param, '%') !== false) {
+ debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+ }
+ // by default ignore any sensitiveness - each database does it in a different way
+ return "$fieldname LIKE $param ESCAPE '$escapechar'";
+ }
+
+ /**
+ * Escape sql LIKE special characters.
+ * @param string $text
+ * @param string $escapechar
+ * @return string
+ */
+ public function sql_like_escape($text, $escapechar = '\\') {
+ $text = str_replace('_', $escapechar.'_', $text);
+ $text = str_replace('%', $escapechar.'%', $text);
+ return $text;
+ }
+
/**
* Returns the proper SQL to do LIKE in a case-insensitive way.
*
* the case insensitive search using regexp_like() or NLS_COMP=LINGUISTIC :-(
* See http://docs.moodle.org/en/XMLDB_Problems#Case-insensitive_searches
*
+ * @deprecated
* @return string
*/
public function sql_ilike() {
+ //TODO: debugging('sql_ilike() is deprecated, please use sql_like() instead');
return 'LIKE';
}
class mssql_native_moodle_database extends moodle_database {
protected $mssql = null;
-
protected $last_error_reporting; // To handle mssql driver default verbosity
+ protected $collation; // current DB collation cache
/**
* Detects if all needed PHP stuff installed.
return ' CEILING(' . $fieldname . ')';
}
+
+ protected function get_collation() {
+ if (isset($this->collation)) {
+ return $this->collation;
+ }
+ if (!empty($this->dboptions['dbcollation'])) {
+ // perf speedup
+ $this->collation = $this->dboptions['dbcollation'];
+ return $this->collation;
+ }
+
+ // make some default
+ $this->collation = 'Latin1_General_CI_AI';
+
+ $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
+ $this->query_start($sql, null, SQL_QUERY_AUX);
+ $result = sqlsrv_query($this->sqlsrv, $sql);
+ $this->query_end($result);
+
+ if ($result) {
+ if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
+ $this->collation = reset($rawcolumn);
+ }
+ $this->free_result($result);
+ }
+
+ return $this->collation;
+ }
+
+ /**
+ * Case and collation sensitive string 'string = string'
+ * @param string $string1
+ * @param string $string2
+ * @return string SQL fragment
+ */
+ public function sql_binary_equal($string1, $string2) {
+ return "$string1 COLLATE Latin1_General_CS_AS = $string2";
+ }
+
+ /**
+ * Returns 'LIKE' part of a query.
+ *
+ * @param string $fieldname usually name of the table column
+ * @param string $param usually bound query parameter (?, :named)
+ * @param bool $casesensitive use case sensitive search
+ * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+ * @param string $escapechar escape char for '%' and '_'
+ * @return string SQL code fragment
+ */
+ public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+ if (strpos($param, '%') !== false) {
+ debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+ }
+
+ $collation = $this->get_collation();
+
+ if ($casesensitive) {
+ $collation = str_replace('_CI', '_CS', $collation);
+ } else {
+ $collation = str_replace('_CS', '_CI', $collation);
+ }
+ if ($accentsensitive) {
+ $collation = str_replace('_AI', '_AS', $collation);
+ } else {
+ $collation = str_replace('_AS', '_AI', $collation);
+ }
+
+ return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'";
+ }
+
public function sql_concat() {
$arr = func_get_args();
foreach ($arr as $key => $ele) {
return ' CAST(' . $fieldname . ' AS SIGNED) ';
}
+
+ /**
+ * Case and collation sensitive string 'string = string'
+ * @param string $string1
+ * @param string $string2
+ * @return string SQL fragment
+ */
+ public function sql_binary_equal($string1, $string2) {
+ return "BINARY $string1 = $string2";
+ }
+
+ /**
+ * Returns 'LIKE' part of a query.
+ *
+ * @param string $fieldname usually name of the table column
+ * @param string $param usually bound query parameter (?, :named)
+ * @param bool $casesensitive use case sensitive search
+ * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+ * @param string $escapechar escape char for '%' and '_'
+ * @return string SQL code fragment
+ */
+ public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+ if (strpos($param, '%') !== false) {
+ debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+ }
+ $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\'
+
+ if ($casesensitive) {
+ return "$fieldname LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'";
+ } else {
+ if ($accentsensitive) {
+ return "LOWER($fieldname) LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'";
+ } else {
+ return "$fieldname LIKE $param ESCAPE '$escapechar'";
+ }
+ }
+ }
+
public function sql_concat() {
$arr = func_get_args();
$s = implode(', ', $arr);
}
}
- // TODO: Change this function and uses to support 2 parameters: fieldname and value
- // that way we can use REGEXP_LIKE(x, y, 'i') to provide case-insensitive like searches
- // to lower() comparison or whatever
- public function sql_ilike() {
- // TODO: add some ilike workaround
- return 'LIKE';
+ /**
+ * Returns 'LIKE' part of a query.
+ *
+ * @param string $fieldname usually name of the table column
+ * @param string $param usually bound query parameter (?, :named)
+ * @param bool $casesensitive use case sensitive search
+ * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+ * @param string $escapechar escape char for '%' and '_'
+ * @return string SQL code fragment
+ */
+ public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+ if (strpos($param, '%') !== false) {
+ debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+ }
+
+ // no accent sensitiveness here for now, sorry
+
+ if ($casesensitive) {
+ return "$fieldname LIKE $param ESCAPE '$escapechar'";
+ } else {
+ return "LOWER($fieldname) LIKE LOWER($param) ESCAPE '$escapechar'";
+ }
}
// NOTE: Oracle concat implementation isn't ANSI compliant when using NULLs (the result of
return true;
}
+ /**
+ * Returns 'LIKE' part of a query.
+ *
+ * @param string $fieldname usually name of the table column
+ * @param string $param usually bound query parameter (?, :named)
+ * @param bool $casesensitive use case sensitive search
+ * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+ * @param string $escapechar escape char for '%' and '_'
+ * @return string SQL code fragment
+ */
+ public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+ if (strpos($param, '%') !== false) {
+ debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+ }
+ $escapechar = pg_escape_string($this->pgsql, $escapechar); // prevents problems with C-style escapes of enclosing '\'
+
+ // postgresql does not support accent insensitive text comparisons, sorry
+ if ($casesensitive) {
+ return "$fieldname LIKE $param ESCAPE '$escapechar'";
+ } else {
+ return "$fieldname ILIKE $param ESCAPE '$escapechar'";
+ }
+ }
+
public function sql_ilike() {
+ //TODO: debugging('sql_ilike() is deprecated, please use sql_like() instead');
return 'ILIKE';
}
$this->assertEqual(count($records), 2);
}
- function test_ilike() {
+
+ function test_sql_binary_equal() {
+ $DB = $this->tdb;
+ $dbman = $DB->get_manager();
+
+ $table = $this->get_test_table();
+ $tablename = $table->getName();
+
+ $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+ $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+ $table->add_field('descr', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+ $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+ $dbman->create_table($table);
+ $this->tables[$tablename] = $table;
+
+ $DB->insert_record($tablename, array('name'=>'skodak', 'descr'=>'Skodak'));
+ $DB->insert_record($tablename, array('name'=>'skodák', 'descr'=>'skodák'));
+ $DB->insert_record($tablename, array('name'=>'Skodak', 'descr'=>'Skodák'));
+ $DB->insert_record($tablename, array('name'=>'?ko?ák', 'descr'=>'?ko?ák'));
+ $DB->insert_record($tablename, array('name'=>'skodäk', 'descr'=>'skodak'));
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', '?');
+ $records = $DB->get_records_sql($sql, array("skodak"));
+ $this->assertEqual(count($records), 1);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', '?');
+ $records = $DB->get_records_sql($sql, array("?ko?ák"));
+ $this->assertEqual(count($records), 1);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_binary_equal('name', 'descr');
+ $records = $DB->get_records_sql($sql, array());
+ $this->assertEqual(count($records), 1);
+
+ // get_records() is supposed to use binary comparison too
+ $records = $DB->get_records($tablename, array('name'=>"skodak"));
+ $this->assertEqual(count($records), 1);
+ $records = $DB->get_records($tablename, array('name'=>"?ko?ák"));
+ $this->assertEqual(count($records), 1);
+
+ $bool = $DB->record_exists($tablename, array('name'=>"skodak"));
+ $this->assertTrue($bool);
+ $bool = $DB->record_exists($tablename, array('name'=>"skodAk"));
+ $this->assertFalse($bool);
+ }
+
+ function test_sql_like() {
+ $DB = $this->tdb;
+ $dbman = $DB->get_manager();
+
+ $table = $this->get_test_table();
+ $tablename = $table->getName();
+
+ $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
+ $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
+ $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
+ $dbman->create_table($table);
+ $this->tables[$tablename] = $table;
+
+ $DB->insert_record($tablename, array('name'=>'SuperDuperRecord'));
+ $DB->insert_record($tablename, array('name'=>'Nodupor'));
+ $DB->insert_record($tablename, array('name'=>'ouch'));
+ $DB->insert_record($tablename, array('name'=>'ouc_'));
+ $DB->insert_record($tablename, array('name'=>'ouc%'));
+ $DB->insert_record($tablename, array('name'=>'aui'));
+ $DB->insert_record($tablename, array('name'=>'aüi'));
+ $DB->insert_record($tablename, array('name'=>'aÜi'));
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false);
+ $records = $DB->get_records_sql($sql, array("%dup_r%"));
+ $this->assertEqual(count($records), 2);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true);
+ $records = $DB->get_records_sql($sql, array("%dup%"));
+ $this->assertEqual(count($records), 1);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?'); // defaults
+ $records = $DB->get_records_sql($sql, array("%dup%"));
+ $this->assertEqual(count($records), 1);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true);
+ $records = $DB->get_records_sql($sql, array("ouc\\_"));
+ $this->assertEqual(count($records), 1);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true, '|');
+ $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|')));
+ $this->assertEqual(count($records), 1);
+
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true);
+ $records = $DB->get_records_sql($sql, array('aui'));
+ $this->assertEqual(count($records), 1);
+
+ // we do not require accent insensitivness yet, just make sure it does not throw errors
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, false);
+ $records = $DB->get_records_sql($sql, array('aui'));
+ $this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all database backends, ignore this problem for now.');
+ $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false, false);
+ $records = $DB->get_records_sql($sql, array('aui'));
+ $this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all database backends, ignore this problem for now.');
+ }
+
+ function test_sql_ilike() {
$DB = $this->tdb;
$dbman = $DB->get_manager();
$sql = "SELECT * FROM {".$tablename."} WHERE name ".$DB->sql_ilike()." ?";
$params = array("%dup_r%");
$records = $DB->get_records_sql($sql, $params);
- $this->assertEqual(count($records), 2);
+ $this->assertEqual(count($records), 2, 'DB->sql_ilike() is deprecated, ignore this problem.');
}
function test_sql_concat() {
protected $sqlsrv = null;
protected $last_error_reporting; // To handle SQL*Server-Native driver default verbosity
protected $temptables; // Control existing temptables (sqlsrv_moodle_temptables object)
+ protected $collation; // current DB collation cache
/**
* Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
} else if (is_null($param)) {
$return .= 'NULL';
- } else if ($this->is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
+ } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
$return .= $param;
} else if (is_float($param)) {
$return .= $param;
return $return;
}
- /**
- * Return true if given value is integer or string with integer value
- *
- * @param mixed $value String or Int
- * @return bool true if number, false if not
- */
- private static function is_number($value) {
- /* this function exists in moodlelib, so i would need to include that which is not recommended ... */
- if (is_int($value)) {
- return true;
- } else if (is_string($value)) {
- return ((string)(int)$value) === $value;
- } else {
- return false;
- }
- }
-
/**
* Execute general sql query. Should be used only when no other method suitable.
* Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
return ' CEILING('.$fieldname.')';
}
+ protected function get_collation() {
+ if (isset($this->collation)) {
+ return $this->collation;
+ }
+ if (!empty($this->dboptions['dbcollation'])) {
+ // perf speedup
+ $this->collation = $this->dboptions['dbcollation'];
+ return $this->collation;
+ }
+
+ // make some default
+ $this->collation = 'Latin1_General_CI_AI';
+
+ $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
+ $this->query_start($sql, null, SQL_QUERY_AUX);
+ $result = sqlsrv_query($this->sqlsrv, $sql);
+ $this->query_end($result);
+
+ if ($result) {
+ if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
+ $this->collation = reset($rawcolumn);
+ }
+ $this->free_result($result);
+ }
+
+ return $this->collation;
+ }
+
+ /**
+ * Case and collation sensitive string 'string = string'
+ * @param string $string1
+ * @param string $string2
+ * @return string SQL fragment
+ */
+ public function sql_binary_equal($string1, $string2) {
+ return "$string1 COLLATE Latin1_General_CS_AS = $string2";
+ }
+
+ /**
+ * Returns 'LIKE' part of a query.
+ *
+ * @param string $fieldname usually name of the table column
+ * @param string $param usually bound query parameter (?, :named)
+ * @param bool $casesensitive use case sensitive search
+ * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
+ * @param string $escapechar escape char for '%' and '_'
+ * @return string SQL code fragment
+ */
+ public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
+ if (strpos($param, '%') !== false) {
+ debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
+ }
+
+ $collation = $this->get_collation();
+
+ if ($casesensitive) {
+ $collation = str_replace('_CI', '_CS', $collation);
+ } else {
+ $collation = str_replace('_CS', '_CI', $collation);
+ }
+ if ($accentsensitive) {
+ $collation = str_replace('_AI', '_AS', $collation);
+ } else {
+ $collation = str_replace('_AS', '_AI', $collation);
+ }
+
+ return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'";
+ }
+
public function sql_concat() {
$arr = func_get_args();
