-
Bug
-
Resolution: Fixed
-
Major
-
2.1.7, 2.2, 2.3, 2.7.4, 2.8, 2.9
-
MOODLE_21_STABLE, MOODLE_22_STABLE, MOODLE_23_STABLE, MOODLE_27_STABLE, MOODLE_28_STABLE, MOODLE_29_STABLE
-
MOODLE_27_STABLE, MOODLE_28_STABLE
-
MDL-35155-master -
We've run into an interesting if slightly obscure issue with the sql_substr() function on MSSQL when used in a very specific way.
The following code will work in postgresql/mysql but fails in MSSQL:
$length_sql = $DB->sql_length(':myparam');
|
$substr_sql = $DB->sql_substr('myfield', "$length_sql + 1");
|
|
|
$sql = "UPDATE {table} SET myfield = $substr_sql WHERE ...";
|
$params = array('myparam' => $myparam);
|
|
|
$DB->execute($sql, $params);
|
The problem is that in the code above sql_substr() in postgres/mysql returns something like this:
SUBSTR(myfield, LENGTH(:myparam) )
|
whereas MSSQL returns:
SUBSTRING(myfield, :myparam, (LEN(myfield) - :myparam + 1 )) |
MSSQL is repeating the $start argument so when that contains a parameter it repeats it - making the parameter array too short for the query.
Based on the MSSQL documentation:
If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned
http://msdn.microsoft.com/en-us/library/ms187748.aspx
It should be possible to change:
return "SUBSTRING($expr, $start, (LEN($expr) - $start + 1))";
|
to:
return "SUBSTRING($expr, $start, LEN($expr))";
|
in sql_substr() in mssql_native_moodle_database.php which will fix this issue while maintain the same behaviour.
