This is a bug report for phpScheduleIt 1.2.8, under PHP 5.2.5 running in ISAPI mode on IIS 6.0 on Windows Server 2003. SQL server is Microsoft SQL Server Express Edition 2005.
BackgroundThere is an irritating limitation of Transact-SQL, the Microsoft SQL parser, whereby you cannot use the same column twice in an ORDER BY statement. MySQL does not have this limitation.
KB890771 is the Microsoft KB article addressing this issue.
IssuephpScheduleIt uses an idiom of 'ORDER BY $user-specified-column, static-column-1, static-column-2' in a couple of places, and when $user-specified-column is the same as static-column-1/2 (as it often is by default), the SQL command will fail.
This has been partially addressed in DBEngine by the use of a preg_replace, but results of the regular expression are never included in the SQL query. Furthermore, a similar problem occurs in AdminDB.
ResolutionI've put together a patch which generalises the regular expression matching into a new function (DBEngine->get_order_by) and applied it in the two places where it is useful. I believe this functionality may be required elsewhere in the future.
Index: lib/db/AdminDB.class.php
===================================================================
--- lib/db/AdminDB.class.php (revision 82)
+++ lib/db/AdminDB.class.php (working copy)
@@ -429,13 +429,15 @@
if ($order == 'date' && !isset($_GET['vert'])) // Default the date to DESC
$vert = 'DESC';
+
+ $sqlorder = $this->get_order_by($order . ' '. $vert, array('l.lname', 'l.fname'));
// Set up query to get neccessary records ordered by user request first, then logical order
$query = 'SELECT l.*'
. ' FROM ' . $this->get_table(TBL_LOGIN) . ' as l'
. $inner_join
. $where
- . ' ORDER BY ' . $order . ' ' . $vert . ', l.lname, l.fname';
+ . ' ORDER BY ' . $sqlorder;
$result = $this->db->limitQuery($query, $pager->getOffset(), $pager->getLimit(), $values);
Index: lib/DBEngine.class.php
===================================================================
--- lib/DBEngine.class.php (revision 82)
+++ lib/DBEngine.class.php (working copy)
@@ -208,10 +208,7 @@
$return = array();
// Clean out the duplicated order so that MSSQL is OK
- $orders = trim(preg_replace("/(res|rs).$order,?/", '', 'res.start_date, rs.name, res.starttime'));
- if (strrpos($orders, ',') == strlen($orders)-1) {
- $orders = substr($orders, 0, strlen($orders)-1);
- }
+ $orders = $this->get_order_by($order . ' '. $vert, array('res.start_date', 'rs.name', 'res.starttime'));
$query = 'SELECT res.*, resusers.*, rs.name, rs.rphone, rs.location FROM '
. $this->get_table('reservations') . ' as res INNER JOIN '
@@ -221,7 +218,7 @@
. ' AND (res.start_date>=? OR (res.start_date<=? AND res.end_date>=?))'
. ' AND res.is_blackout <> 1'
. (!$include_participating ? ' AND owner = 1' : ' AND invited = 0')
- . " ORDER BY $order $vert, res.start_date, rs.name, res.starttime";
+ . " ORDER BY $orders";
$values = array($id, mktime(0,0,0), mktime(0,0,0), mktime(0,0,0));
@@ -543,5 +540,29 @@
function get_err() {
return $this->err_msg;
}
+
+ /** Creates an ORDER BY string by combining a user-selected and list of
+ * default columns (+/- sort orders), eliminiating duplicates to avoid a
+ * limitation in MSSQL (Transact-SQL). User-selected column is always given
+ * preference but can be empty.
+ * @param string $user_order user-selected order statement
+ * @param array $default_columns array of default sort order
+ * @return string suitable for inclusion as 'ORDER BY @return'
+ */
+ function get_order_by($user_order, $default_columns) {
+ $retstring = '';
+ if ($user_order == '') {
+ $retstring = join(', ', $default_columns);
+ } else {
+ $retstring = $user_order;
+ // Construct a column matching regex which will match against
+ // columns with or without table names.
+ $user_col_regex = '/^(.+\.)?' . strtok($user_order, ' ') . '($| )/';
+ $sort_columns = preg_grep($user_col_regex, $default_columns, PREG_GREP_INVERT);
+ array_unshift($sort_columns, $user_order);
+ $retstring = join(', ', $sort_columns);
+ }
+ return $retstring;
+ }
}
?>
