Tickets :: Comment on [#12733] SQL Error in listBookmarks () When $sortby empty

* Your Email Address
* Spam protection	Enter the letters below: .___.___ __ .___ __ [__ [__ / `[__ / ` [___\| \__.\| \__.
Comment	> We have a debian install with php 5.5.3 and we just updated to Horde > 5.1.4, trean 1.0.3-2. I am mainly a perl programmer, but I can find > my way around php fairly well (though I spend a LOT of time at > php.net/manual/ ) > > One possible fix for this bug is at the end of this report. > > While testing the new release we ran into the problem below when > going to bookmarks at /trean/ > > Horde_Db_Exception Object > ( > [details] => > [logged] => 1 > [_logLevel:protected] => 0 > [message:protected] => SQLSTATE[42703]: Undefined column: 7 > ERROR: column "bookmark_" does not exist > LINE 4: ORDER BY bookmark_ LIMIT 999 > ^ > [string:Exception:private] => > [code:protected] => 42703 > [file:protected] => /usr/share/php/Horde/Db/Adapter/Base.php > [line:protected] => 555 > [trace:Exception:private] => Array > ( > [0] => Array > ( > [file] => /usr/share/php/Horde/Db/Adapter/Pdo/Base.php > [line] => 83 > [function] => execute > [class] => Horde_Db_Adapter_Base > [type] => -> > [args] => Array > ( > [0] => SELECT bookmark_id, user_id, > bookmark_url, bookmark_title, bookmark_description, bookmark_clicks, > bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = ? > ORDER BY bookmark_ LIMIT 999 > [1] => Array > ( > [0] => 1 > ) > [2] => > ) > ) > [1] => Array > ( > [file] => /usr/share/horde/trean/lib/Bookmarks.php > [line] => 63 > [function] => selectAll > [class] => Horde_Db_Adapter_Pdo_Base > [type] => -> > [args] => Array > ( > [0] => SELECT bookmark_id, user_id, > bookmark_url, bookmark_title, bookmark_description, bookmark_clicks, > bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = ? > ORDER BY bookmark_ LIMIT 999 > [1] => Array > ( > [0] => 1 > ) > ) > ) > [2] => Array > ( > [file] => > /usr/share/horde/trean/lib/View/BookmarkList.php > [line] => 148 > [function] => listBookmarks > [class] => Trean_Bookmarks > [type] => -> > [args] => Array > ( > [0] => > [1] => > [2] => 0 > [3] => 999 > ) > ) > [3] => Array > ( > [file] => > /usr/share/horde/trean/lib/View/BookmarkList.php > [line] => 109 > [function] => _getBookmarks > [class] => Trean_View_BookmarkList > [type] => -> > [args] => Array > ( > ) > ) > [4] => Array > ( > [file] => /usr/share/horde/trean/browse.php > [line] => 17 > [function] => hasBookmarks > [class] => Trean_View_BookmarkList > [type] => -> > [args] => Array > ( > ) > ) > [5] => Array > ( > [file] => /usr/share/horde/trean/index.php > [line] => 11 > [args] => Array > ( > [0] => /usr/share/horde/trean/browse.php > ) > [function] => require > ) > ) > [previous:Exception:private] => PDOException Object > ( > [message:protected] => SQLSTATE[42703]: Undefined column: > 7 ERROR: column "bookmark_" does not exist > LINE 4: ORDER BY bookmark_ LIMIT 999 > ^ > [string:Exception:private] => > [code:protected] => 42703 > [file:protected] => /usr/share/php/Horde/Db/Adapter/Base.php > [line:protected] => 551 > [trace:Exception:private] => Array > ( > [0] => Array > ( > [file] => > /usr/share/php/Horde/Db/Adapter/Base.php > [line] => 551 > [function] => query > [class] => PDO > [type] => -> > [args] => Array > ( > [0] => SELECT bookmark_id, > user_id, bookmark_url, bookmark_title, bookmark_description, > bookmark_clicks, bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = 1 > ORDER BY bookmark_ LIMIT 999 > ) > ) > [1] => Array > ( > [file] => > /usr/share/php/Horde/Db/Adapter/Pdo/Base.php > [line] => 83 > [function] => execute > [class] => Horde_Db_Adapter_Base > [type] => -> > [args] => Array > ( > [0] => SELECT bookmark_id, > user_id, bookmark_url, bookmark_title, bookmark_description, > bookmark_clicks, bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = ? > ORDER BY bookmark_ LIMIT 999 > [1] => Array > ( > [0] => 1 > ) > [2] => > ) > ) > [2] => Array > ( > [file] => > /usr/share/horde/trean/lib/Bookmarks.php > [line] => 63 > [function] => selectAll > [class] => Horde_Db_Adapter_Pdo_Base > [type] => -> > [args] => Array > ( > [0] => SELECT bookmark_id, > user_id, bookmark_url, bookmark_title, bookmark_description, > bookmark_clicks, bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = ? > ORDER BY bookmark_ LIMIT 999 > [1] => Array > ( > [0] => 1 > ) > ) > ) > [3] => Array > ( > [file] => > /usr/share/horde/trean/lib/View/BookmarkList.php > [line] => 148 > [function] => listBookmarks > [class] => Trean_Bookmarks > [type] => -> > [args] => Array > ( > [0] => > [1] => > [2] => 0 > [3] => 999 > ) > ) > [4] => Array > ( > [file] => > /usr/share/horde/trean/lib/View/BookmarkList.php > [line] => 109 > [function] => _getBookmarks > [class] => Trean_View_BookmarkList > [type] => -> > [args] => Array > ( > ) > ) > [5] => Array > ( > [file] => /usr/share/horde/trean/browse.php > [line] => 17 > [function] => hasBookmarks > [class] => Trean_View_BookmarkList > [type] => -> > [args] => Array > ( > ) > ) > [6] => Array > ( > [file] => /usr/share/horde/trean/index.php > [line] => 11 > [args] => Array > ( > [0] => /usr/share/horde/trean/browse.php > ) > [function] => require > ) > ) > [previous:Exception:private] => > [errorInfo] => Array > ( > [0] => 42703 > [1] => 7 > [2] => ERROR: column "bookmark_" does not exist > LINE 4: ORDER BY bookmark_ LIMIT 999 > ^ > ) > ) > ) > > I looked at the problem code below and was very surprised that > apparently the default value for $sortby in the function call is not > being applied. > > public function listBookmarks($sortby = 'title', $sortdir = 0, > $from = 0, $count = 0) > { > $values = array($this->_userId); > > $sql = 'SELECT bookmark_id, user_id, bookmark_url, > bookmark_title, bookmark_description, bookmark_clicks, > bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = ? > ORDER BY bookmark_' . $sortby . ($sortdir ? ' DESC' : ''); > > So it appears that if you call listBookmarks with an null value for > sortby as shown in this Array, PHP considers that empty value to be > valid and tries to use it? > >>> [args] => Array >>> ( >>> [0] => >>> [1] => >>> [2] => 0 >>> [3] => 999 >>> ) > > I have tested this fix and it is ugly, but it works fine... > > public function listBookmarks($sortby = 'title', $sortdir = 0, > $from = 0, $count = 0) > { > $values = array($this->_userId); > $sql = 'SELECT bookmark_id, user_id, bookmark_url, > bookmark_title, bookmark_description, bookmark_clicks, > bookmark_http_status, favicon_url, bookmark_dt > FROM trean_bookmarks > WHERE user_id = ? > ORDER BY bookmark_' . ($sortby ? $sortby : 'title') . > ($sortdir ? ' DESC' : ''); > $sql = $GLOBALS['trean_db']->addLimitOffset($sql, > array('limit' => $count, 'offset' => $from)); > > It still seems silly to me that PHP apparently considers the empty > string or whatever is being passed for $sortby to be a valid value > and does not invoke the default for it.
Attachment
Watch this ticket

Saved Queries