diff options
37 files changed, 653 insertions, 147 deletions
diff --git a/data/config.default.php b/data/config.default.php index bb9eddb..791eb7a 100644 --- a/data/config.default.php +++ b/data/config.default.php @@ -364,6 +364,24 @@ $longdate = 'j F Y'; $nofollow = true; /** + * Include rel="noreferer" attribute on bookmark links to prevent them from + * specifying your site as the one that referred to the page. + */ +$noreferrer = true; + +/** + * Include rel="noopener" attribute on bookmark links to prevent them from + * giving the opening page access to your site through the window.opener + * property. + */ +$noopener = true; + +/** + * Include target="_blank" to open links in a new window or tab. + */ +$openLinksInNewWindow = false; + +/** * Default number of bookmarks per page. * -1 means no limit. * diff --git a/data/tables.sqlite.sql b/data/tables.sqlite.sql new file mode 100644 index 0000000..b7265cc --- /dev/null +++ b/data/tables.sqlite.sql @@ -0,0 +1,207 @@ +-- Semantic Scuttle - Tables creation SQL script +-- ! Dont forget to change table names according to $tableprefix defined in config.php ! + +-- +-- Table structure for table `sc_bookmarks` +-- + +CREATE TABLE IF NOT EXISTS `sc_bookmarks` ( + `bId` integer NOT NULL, + `uId` int(11) NOT NULL default '0', + `bIp` varchar(40) default NULL, + `bStatus` tinyint(1) NOT NULL default '0', + `bDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + `bModified` datetime NOT NULL default '0000-00-00 00:00:00', + `bTitle` varchar(255) NOT NULL default '', + `bAddress` varchar(1500) NOT NULL, + `bDescription` text default NULL, + `bPrivateNote` text default NULL, + `bHash` varchar(32) NOT NULL default '', + `bVotes` int(11) NOT NULL default '0', + `bVoting` int(11) NOT NULL default '0', + `bShort` varchar(16) default NULL, + PRIMARY KEY (`bId`) +); + +CREATE INDEX IF NOT EXISTS `sc_bookmarks_usd` ON `sc_bookmarks` (`uId`, `bStatus`, `bDatetime`); +CREATE INDEX IF NOT EXISTS `sc_bookmarks_hui` ON `sc_bookmarks` (`bHash`, `uId`, `bId`); +CREATE INDEX IF NOT EXISTS `sc_bookmarks_du` ON `sc_bookmarks` (`bDatetime`, `uId`); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tags` +-- + +CREATE TABLE IF NOT EXISTS `sc_tags` ( + `tId` integer NOT NULL, + `tag` varchar(100) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + `tDescription` text default NULL, + PRIMARY KEY (`tId`), + CONSTRAINT `sc_tags_tag_uId` UNIQUE (`tag`, `uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_bookmarks2tags` +-- + +CREATE TABLE IF NOT EXISTS `sc_bookmarks2tags` ( + `id` integer NOT NULL, + `bId` int(11) NOT NULL default '0', + `tag` varchar(100) NOT NULL default '', + PRIMARY KEY (`id`), + CONSTRAINT `sc_bookmarks2tags_tag_bId` UNIQUE (`tag`,`bId`) +); + +CREATE INDEX IF NOT EXISTS `sc_bookmarks2tags_bId` ON `sc_bookmarks2tags` (`bId`); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_users` +-- + +CREATE TABLE IF NOT EXISTS `sc_users` ( + `uId` integer NOT NULL, + `username` varchar(25) NOT NULL default '', + `password` varchar(40) NOT NULL default '', + `uDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + `uModified` datetime NOT NULL default '0000-00-00 00:00:00', + `name` varchar(50) default NULL, + `email` varchar(50) NOT NULL default '', + `homepage` varchar(255) default NULL, + `uContent` text, + `privateKey` varchar(33) default NULL, + PRIMARY KEY (`uId`), + CONSTRAINT `privateKey` UNIQUE (`privateKey`) +); + +-- -------------------------------------------------------- + +CREATE TABLE IF NOT EXISTS `sc_users_sslclientcerts` ( + `id` INTEGER NOT NULL , + `uId` INT NOT NULL , + `sslSerial` VARCHAR( 32 ) NOT NULL , + `sslClientIssuerDn` VARCHAR( 1024 ) NOT NULL , + `sslName` VARCHAR( 64 ) NOT NULL , + `sslEmail` VARCHAR( 64 ) NOT NULL , + PRIMARY KEY ( `id` ) +); + +-- +-- Table structure for table `sc_watched` +-- + +CREATE TABLE IF NOT EXISTS `sc_watched` ( + `wId` integer NOT NULL, + `uId` int(11) NOT NULL default '0', + `watched` int(11) NOT NULL default '0', + PRIMARY KEY (`wId`) +); + +CREATE INDEX IF NOT EXISTS `sc_watched_uId` ON `sc_watched` (`uId`); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tags2tags` +-- + +CREATE TABLE IF NOT EXISTS `sc_tags2tags` ( + `ttId` integer NOT NULL, + `tag1` varchar(100) NOT NULL default '', + `tag2` varchar(100) NOT NULL default '', + `relationType` varchar(32) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + PRIMARY KEY (`ttId`), + CONSTRAINT `sc_tags2tags_tag1_tag2_uId` UNIQUE (`tag1`,`tag2`,`relationType`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tagsstats` +-- + +CREATE TABLE IF NOT EXISTS `sc_tagsstats` ( + `tstId` integer NOT NULL, + `tag1` varchar(100) NOT NULL default '', + `relationType` varchar(32) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + `nb` int(11) NOT NULL default '0', + `depth` int(11) NOT NULL default '0', + `nbupdate` int(11) NOT NULL default '0', + PRIMARY KEY (`tstId`), + CONSTRAINT `sc_tagsstats_tag1_type_uId` UNIQUE (`tag1`,`relationType`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_tagscache` +-- + +CREATE TABLE IF NOT EXISTS `sc_tagscache` ( + `tcId` integer NOT NULL, + `tag1` varchar(100) NOT NULL default '', + `tag2` varchar(100) NOT NULL default '', + `relationType` varchar(32) NOT NULL default '', + `uId` int(11) NOT NULL default '0', + PRIMARY KEY (`tcId`), + CONSTRAINT `sc_tagscache_tag1_tag2_type_uId` UNIQUE (`tag1`,`tag2`,`relationType`,`uId`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_commondescription` +-- + +CREATE TABLE IF NOT EXISTS `sc_commondescription` ( + `cdId` integer NOT NULL, + `uId` int(11) NOT NULL default '0', + `tag` varchar(100) NOT NULL default '', + `bHash` varchar(32) NOT NULL default '', + `cdTitle` varchar(255) NOT NULL default '', + `cdDescription` text default NULL, + `cdDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`cdId`), + CONSTRAINT `sc_commondescription_tag_datetime` UNIQUE (`tag`,`CDDATETIME`), + CONSTRAINT `sc_commondescription_bookmark_datetime` UNIQUE (`bHash`,`cdDatetime`) +); + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sc_searchhistory` +-- + +CREATE TABLE IF NOT EXISTS `sc_searchhistory` ( + `shId` integer NOT NULL, + `shTerms` varchar(255) NOT NULL default '', + `shRange` varchar(32) NOT NULL default '', + `shDatetime` datetime NOT NULL default '0000-00-00 00:00:00', + `shNbResults` int(6) NOT NULL default '0', + `uId` int(11) NOT NULL default '0', + PRIMARY KEY (`shId`) +); + + +CREATE TABLE IF NOT EXISTS `sc_votes` ( + `bId` INT NOT NULL , + `uId` INT NOT NULL , + `vote` INT( 2 ) NOT NULL , + CONSTRAINT `bid_2` UNIQUE (`bId`,`uId`) +); + +CREATE INDEX IF NOT EXISTS `bId` ON `sc_votes` (`bId`); +CREATE INDEX IF NOT EXISTS `uId` ON `sc_votes` (`uId`); + + +CREATE TABLE IF NOT EXISTS `sc_version` ( + `schema_version` int(11) NOT NULL +); +INSERT INTO `sc_version` (`schema_version`) VALUES ('6'); diff --git a/data/templates/default/bookmarkcommondescriptionedit.tpl.php b/data/templates/default/bookmarkcommondescriptionedit.tpl.php index 807c58b..306086e 100644 --- a/data/templates/default/bookmarkcommondescriptionedit.tpl.php +++ b/data/templates/default/bookmarkcommondescriptionedit.tpl.php @@ -30,8 +30,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '<a href="'.createURL('profile', $lastUser['username']).'">' - . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + if ($lastUser) { + echo '<a href="'.createURL('profile', $lastUser['username']).'">' + . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + } + else { + echo 'Unknown user'; + } } ?> </td> diff --git a/data/templates/default/bookmarks.tpl.php b/data/templates/default/bookmarks.tpl.php index 2a3d169..0f88a60 100644 --- a/data/templates/default/bookmarks.tpl.php +++ b/data/templates/default/bookmarks.tpl.php @@ -86,7 +86,7 @@ if ($userservice->isLoggedOn()) { /* personal tag description */ if($currenttag!= '' && $user!='') { $userObject = $userservice->getUserByUsername($user); - if($tagservice->getDescription($currenttag, $userObject['uId'])) { ?> + if($userObject && $tagservice->getDescription($currenttag, $userObject['uId'])) { ?> <p class="commondescription"><?php $pDescription = $tagservice->getDescription($currenttag, $userObject['uId']); diff --git a/data/templates/default/sidebar.block.menu.php b/data/templates/default/sidebar.block.menu.php index 94a9fa2..dfb2c25 100644 --- a/data/templates/default/sidebar.block.menu.php +++ b/data/templates/default/sidebar.block.menu.php @@ -63,7 +63,7 @@ if (sizeof($menuTags) > 0 || ($userid != 0 && $userid === $logged_on_userid)) { </table> <?php $cUser = $userservice->getUser($userid); ?> -<?php if($userid>0): ?> +<?php if($cUser && $userid>0): ?> <?php if($userid==$logged_on_userid): ?> <p style="text-align:right"><a href="<?php echo createURL('alltags', $cUser['username']); ?>" title="<?php echo T_('See all your tags')?>"><?php echo T_('all your tags'); ?></a> →</p> <?php else: ?> diff --git a/data/templates/default/tagcommondescriptionedit.tpl.php b/data/templates/default/tagcommondescriptionedit.tpl.php index 207cfd2..f52d5b0 100644 --- a/data/templates/default/tagcommondescriptionedit.tpl.php +++ b/data/templates/default/tagcommondescriptionedit.tpl.php @@ -20,8 +20,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '<a href="' . createURL('profile', $lastUser['username']) . '">' - . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + if ($lastUser) { + echo '<a href="' . createURL('profile', $lastUser['username']) . '">' + . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + } + else { + echo 'Unknown user'; + } } ?> </td> diff --git a/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php b/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php index b1114d7..4629d72 100644 --- a/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php +++ b/data/templates/minimal/bookmarkcommondescriptionedit.tpl.php @@ -33,8 +33,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '<a href="'.createURL('profile', $lastUser['username']).'">' - . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + if ($lastUser) { + echo '<a href="'.createURL('profile', $lastUser['username']).'">' + . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + } + else { + echo 'Unknown user'; + } } ?> </td> diff --git a/data/templates/minimal/bookmarks.tpl.php b/data/templates/minimal/bookmarks.tpl.php index 1510d44..86749bc 100644 --- a/data/templates/minimal/bookmarks.tpl.php +++ b/data/templates/minimal/bookmarks.tpl.php @@ -87,7 +87,7 @@ if ($userservice->isLoggedOn()) { /* personal tag description */ if($currenttag!= '' && $user!='') { $userObject = $userservice->getUserByUsername($user); - if($tagservice->getDescription($currenttag, $userObject['uId'])) { ?> + if($userObject && $tagservice->getDescription($currenttag, $userObject['uId'])) { ?> <p class="commondescription"><?php $pDescription = $tagservice->getDescription($currenttag, $userObject['uId']); diff --git a/data/templates/minimal/sidebar.block.menu.php b/data/templates/minimal/sidebar.block.menu.php index 94a9fa2..dfb2c25 100644 --- a/data/templates/minimal/sidebar.block.menu.php +++ b/data/templates/minimal/sidebar.block.menu.php @@ -63,7 +63,7 @@ if (sizeof($menuTags) > 0 || ($userid != 0 && $userid === $logged_on_userid)) { </table> <?php $cUser = $userservice->getUser($userid); ?> -<?php if($userid>0): ?> +<?php if($cUser && $userid>0): ?> <?php if($userid==$logged_on_userid): ?> <p style="text-align:right"><a href="<?php echo createURL('alltags', $cUser['username']); ?>" title="<?php echo T_('See all your tags')?>"><?php echo T_('all your tags'); ?></a> →</p> <?php else: ?> diff --git a/data/templates/minimal/tagcommondescriptionedit.tpl.php b/data/templates/minimal/tagcommondescriptionedit.tpl.php index e46c184..bde2ff8 100644 --- a/data/templates/minimal/tagcommondescriptionedit.tpl.php +++ b/data/templates/minimal/tagcommondescriptionedit.tpl.php @@ -24,8 +24,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '<a href="' . createURL('profile', $lastUser['username']) . '">' - . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + if ($lastUser) { + echo '<a href="' . createURL('profile', $lastUser['username']) . '">' + . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + } + else { + echo 'Unknown user'; + } } ?> </td> diff --git a/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php b/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php index b1114d7..4629d72 100644 --- a/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php +++ b/data/templates/sscuttlizr/bookmarkcommondescriptionedit.tpl.php @@ -33,8 +33,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '<a href="'.createURL('profile', $lastUser['username']).'">' - . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + if ($lastUser) { + echo '<a href="'.createURL('profile', $lastUser['username']).'">' + . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + } + else { + echo 'Unknown user'; + } } ?> </td> diff --git a/data/templates/sscuttlizr/bookmarks.tpl.php b/data/templates/sscuttlizr/bookmarks.tpl.php index cb062a4..c87a337 100644 --- a/data/templates/sscuttlizr/bookmarks.tpl.php +++ b/data/templates/sscuttlizr/bookmarks.tpl.php @@ -87,7 +87,7 @@ if ($userservice->isLoggedOn()) { /* personal tag description */ if($currenttag!= '' && $user!='') { $userObject = $userservice->getUserByUsername($user); - if($tagservice->getDescription($currenttag, $userObject['uId'])) { ?> + if($userObject && $tagservice->getDescription($currenttag, $userObject['uId'])) { ?> <p class="commondescription"><?php $pDescription = $tagservice->getDescription($currenttag, $userObject['uId']); @@ -415,8 +415,14 @@ if ($currenttag!= '') { // Nofollow option $rel = ''; if ($GLOBALS['nofollow']) { - $rel = ' rel="nofollow"'; + $rel .= ' nofollow '; } + if ($GLOBALS['noreferrer']) { + $rel .= ' noreferrer '; + } + if ($GLOBALS['noopener']) { + $rel .= ' noopener '; + } $address = $row['bAddress']; $oaddress = $address; @@ -465,7 +471,10 @@ if ($currenttag!= '') { echo ' <div' . $adminBgClass . '>' . "\n"; echo ' <div class="link">' - . '<a href="'. htmlspecialchars($address) .'"'. $rel .' class="taggedlink">' + . ('<a href="' . htmlspecialchars($address) . '"' + . ($rel ? ' rel="' . trim($rel) . '"' : "") + . ($GLOBALS['openLinksInNewWindow'] ? ' target="_blank"' : '') + . ' class="taggedlink">') . filter($row['bTitle']) . '</a>' . $adminStar . "</div>\n"; if ($row['bDescription'] == '') { diff --git a/data/templates/sscuttlizr/sidebar.block.menu.php b/data/templates/sscuttlizr/sidebar.block.menu.php index 94a9fa2..dfb2c25 100644 --- a/data/templates/sscuttlizr/sidebar.block.menu.php +++ b/data/templates/sscuttlizr/sidebar.block.menu.php @@ -63,7 +63,7 @@ if (sizeof($menuTags) > 0 || ($userid != 0 && $userid === $logged_on_userid)) { </table> <?php $cUser = $userservice->getUser($userid); ?> -<?php if($userid>0): ?> +<?php if($cUser && $userid>0): ?> <?php if($userid==$logged_on_userid): ?> <p style="text-align:right"><a href="<?php echo createURL('alltags', $cUser['username']); ?>" title="<?php echo T_('See all your tags')?>"><?php echo T_('all your tags'); ?></a> →</p> <?php else: ?> diff --git a/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php b/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php index e46c184..bde2ff8 100644 --- a/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php +++ b/data/templates/sscuttlizr/tagcommondescriptionedit.tpl.php @@ -24,8 +24,13 @@ window.onload = function() { if(strlen($description['cdDatetime'])>0) { echo T_('Last modification:').' '.$description['cdDatetime'].', '; $lastUser = $userservice->getUser($description['uId']); - echo '<a href="' . createURL('profile', $lastUser['username']) . '">' - . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + if ($lastUser) { + echo '<a href="' . createURL('profile', $lastUser['username']) . '">' + . SemanticScuttle_Model_UserArray::getName($lastUser) . '</a>'; + } + else { + echo 'Unknown user'; + } } ?> </td> diff --git a/src/SemanticScuttle/Config.php b/src/SemanticScuttle/Config.php index 756c303..28ae817 100644 --- a/src/SemanticScuttle/Config.php +++ b/src/SemanticScuttle/Config.php @@ -81,16 +81,25 @@ class SemanticScuttle_Config // then don't look in /etc for config files. // the check is not perfect, but it covers most cases $arFiles = array( - $datadir . 'config.' . $host . '.php', - $datadir . 'config.php', + "{$datadir}config.{$host}.php", + "{$datadir}config.php", + ); + $arDefaultFiles = array( + "{$datadir}config.{$host}.default.php", + "{$datadir}config.default.php", ); } else { $arFiles = array( - $datadir . 'config.' . $host . '.php', - '/etc/semanticscuttle/config.' . $host . '.php', - $datadir . 'config.php', + "{$datadir}config.{$host}.php", + "/etc/semanticscuttle/config.{$host}.php", + "{$datadir}config.php", '/etc/semanticscuttle/config.php', ); + $arDefaultFiles = array( + "{$datadir}config.{$host}.default.php", + "/etc/semanticscuttle/config.{$host}.default.php", + "{$datadir}config.default.php", + ); } $configfile = null; @@ -101,14 +110,6 @@ class SemanticScuttle_Config } } - //find default file - $arDefaultFiles = array_unique( - array( - substr($configfile, 0, -3) . 'default.php', - $datadir . 'config.default.php', - '/etc/semanticscuttle/config.default.php', - ) - ); $defaultfile = null; foreach ($arDefaultFiles as $file) { if (file_exists($this->filePrefix . $file)) { @@ -120,4 +121,4 @@ class SemanticScuttle_Config } } -?>
\ No newline at end of file +?> diff --git a/src/SemanticScuttle/Model/User.php b/src/SemanticScuttle/Model/User.php index 3fbbaa0..96dddbf 100644 --- a/src/SemanticScuttle/Model/User.php +++ b/src/SemanticScuttle/Model/User.php @@ -83,7 +83,9 @@ class SemanticScuttle_Model_User if (!isset($this->privateKey)) { $us = SemanticScuttle_Service_Factory::get('User'); $user = $us->getUser($this->id); - $this->privateKey = $user['privateKey']; + if ($user) { + $this->privateKey = $user['privateKey']; + } } //2023-12-20 add line to avoid 'Deprecated' warning if (is_null($this->privateKey)) return null; @@ -106,7 +108,12 @@ class SemanticScuttle_Model_User if (!isset($this->name)) { $us = SemanticScuttle_Service_Factory::get('User'); $user = $us->getUser($this->id); - $this->name = $user['name']; + if ($user) { + $this->name = $user['name']; + } + else { + $this->name = 'unknown'; + } } return $this->name; } @@ -122,7 +129,9 @@ class SemanticScuttle_Model_User if (!isset($this->email)) { $us = SemanticScuttle_Service_Factory::get('User'); $user = $us->getUser($this->id); - $this->email = $user['email']; + if ($user) { + $this->email = $user['email']; + } } return $this->email; } @@ -138,7 +147,9 @@ class SemanticScuttle_Model_User if(!isset($this->homepage)) { $us = SemanticScuttle_Service_Factory::get('User'); $user = $us->getUser($this->id); - $this->homepage = $user['homepage']; + if ($user) { + $this->homepage = $user['homepage']; + } } return $this->homepage; } @@ -154,7 +165,9 @@ class SemanticScuttle_Model_User if(!isset($this->content)) { $us = SemanticScuttle_Service_Factory::get('User'); $user = $us->getUser($this->id); - $this->content = $user['uContent']; + if ($user) { + $this->content = $user['uContent']; + } } return $this->content; } @@ -171,7 +184,9 @@ class SemanticScuttle_Model_User if(!isset($this->content)) { $us = SemanticScuttle_Service_Factory::get('User'); $user = $us->getUser($this->id); - $this->datetime = $user['uDatetime']; + if ($user) { + $this->datetime = $user['uDatetime']; + } } return $this->datetime; } diff --git a/src/SemanticScuttle/Service/AuthUser.php b/src/SemanticScuttle/Service/AuthUser.php index 9447ee4..f0bc908 100644 --- a/src/SemanticScuttle/Service/AuthUser.php +++ b/src/SemanticScuttle/Service/AuthUser.php @@ -168,10 +168,12 @@ class SemanticScuttle_Service_AuthUser extends SemanticScuttle_Service_User //user must have changed password in external auth. //we need to update the local database. $user = $this->getUserByUsername($username); - $this->_updateuser( - $user['uId'], $this->getFieldName('password'), - $this->sanitisePassword($password) - ); + if ($user) { + $this->_updateuser( + $user['uId'], $this->getFieldName('password'), + $this->sanitisePassword($password) + ); + } return parent::login($username, $password, $remember); } @@ -229,4 +231,4 @@ class SemanticScuttle_Service_AuthUser extends SemanticScuttle_Service_User } } -?>
\ No newline at end of file +?> diff --git a/src/SemanticScuttle/Service/Bookmark.php b/src/SemanticScuttle/Service/Bookmark.php index 17f91b1..7eb1174 100644 --- a/src/SemanticScuttle/Service/Bookmark.php +++ b/src/SemanticScuttle/Service/Bookmark.php @@ -138,7 +138,7 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService if ($GLOBALS['enableVoting'] && $userservice->isLoggedOn()) { $cuid = $userservice->getCurrentUserId(); $vs = SemanticScuttle_Service_Factory::get('Vote'); - $query_1 .= ', !ISNULL(V.bId) as hasVoted, V.vote as vote'; + $query_1 .= ', ' . $this->db->QueryBuilder->isNotNull('V.bId') . ' as hasVoted, V.vote as vote'; $query_2 .= ' LEFT JOIN ' . $vs->getTableName() . ' AS V' . ' ON B.bId = V.bId' . ' AND V.uId = ' . (int)$cuid; @@ -549,7 +549,15 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService '', __LINE__, __FILE__, $sql, $this->db ); } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, + 'Could not commit bookmark', + '', __LINE__, __FILE__, $sql, $this->db + ); + } // Everything worked out, so return the new bookmark's bId. return $bId; @@ -653,7 +661,13 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService ); } - $this->db->sql_transaction('commit'); + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit bookmark update', + '', __LINE__, __FILE__, $sql, $this->db + ); + } // Everything worked out, so return true. return true; } @@ -789,7 +803,7 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService if ($GLOBALS['enableVoting'] && $userservice->isLoggedOn()) { $cuid = $userservice->getCurrentUserId(); $vs = SemanticScuttle_Service_Factory::get('Vote'); - $query_1 .= ', !ISNULL(V.bId) as hasVoted, V.vote as vote'; + $query_1 .= ', ' . $this->db->QueryBuilder->isNotNull('V.bId') . ' as hasVoted, V.vote as vote'; $query_2 .= ' LEFT JOIN ' . $vs->getTableName() . ' AS V' . ' ON B.bId = V.bId' . ' AND V.uId = ' . (int)$cuid; @@ -1003,7 +1017,13 @@ class SemanticScuttle_Service_Bookmark extends SemanticScuttle_DbService ); } - $this->db->sql_transaction('commit'); + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit deleting votes for bookmark', + '', __LINE__, __FILE__, $query, $this->db + ); + } return true; } diff --git a/src/SemanticScuttle/Service/Bookmark2Tag.php b/src/SemanticScuttle/Service/Bookmark2Tag.php index 910da48..232fc80 100644 --- a/src/SemanticScuttle/Service/Bookmark2Tag.php +++ b/src/SemanticScuttle/Service/Bookmark2Tag.php @@ -208,7 +208,16 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService return false; } } - $this->db->sql_transaction('commit'); + + if ($this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit attaching tags', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } + return true; } @@ -287,7 +296,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService $query = 'SELECT tag FROM ' . $this->getTableName() . ' WHERE bId = ' . intval($bookmarkid); if (!$systemTags) { - $query .= ' AND LEFT(tag, 7) <> "system:"'; + $query .= ' AND ' . $this->db->QueryBuilder->left('tag', 7) . ' <> "system:"'; } $query .= ' ORDER BY id ASC'; @@ -329,7 +338,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService $query = 'SELECT tag, bId FROM ' . $this->getTableName() . ' WHERE bId IN (' . implode(',', $bookmarkids) . ')' - . ' AND LEFT(tag, 7) <> "system:"' + . ' AND ' . $this->db->QueryBuilder->left('tag', 7) . ' <> "system:"' . ' ORDER BY id, bId ASC'; if (!($dbresult = $this->db->sql_query($query))) { @@ -367,7 +376,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService $conditions['B.bStatus'] = 0; } - $query .= ' WHERE '. $this->db->sql_build_array('SELECT', $conditions) .' AND LEFT(T.tag, 7) <> "system:" GROUP BY T.tag ORDER BY bCount DESC, tag'; + $query .= ' WHERE '. $this->db->sql_build_array('SELECT', $conditions) .' AND ' . $this->db->QueryBuilder->left('T.tag', 7) . ' <> "system:" GROUP BY T.tag ORDER BY bCount DESC, tag'; if (!($dbresult = $this->db->sql_query($query))) { message_die(GENERAL_ERROR, 'Could not get tags', '', __LINE__, __FILE__, $query, $this->db); @@ -414,7 +423,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService $query_2 .= ', '. $this->getTableName() .' AS T'. $i; $query_4 .= ' AND T'. $i .'.bId = B.bId AND T'. $i .'.tag = "'. $this->db->sql_escape($tags[$i - 1]) .'" AND T0.tag <> "'. $this->db->sql_escape($tags[$i - 1]) .'"'; } - $query_5 = ' AND LEFT(T0.tag, 7) <> "system:" GROUP BY T0.tag ORDER BY bCount DESC, T0.tag'; + $query_5 = ' AND ' . $this->db->QueryBuilder->left('T0.tag', 7) . ' <> "system:" GROUP BY T0.tag ORDER BY bCount DESC, T0.tag'; $query = $query_1 . $query_2 . $query_3 . $query_4 . $query_5; if (! ($dbresult = $this->db->sql_query_limit($query, $limit)) ){ @@ -445,7 +454,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService $privacy = ' AND B.bStatus = 0 '; } - $query = 'SELECT T.tag, COUNT(T.tag) AS bCount FROM '.$GLOBALS['tableprefix'].'bookmarks AS B LEFT JOIN '.$GLOBALS['tableprefix'].'bookmarks2tags AS T ON B.bId = T.bId WHERE B.bHash = \''. $this->db->sql_escape($hash) .'\' '. $privacy .'AND LEFT(T.tag, 7) <> "system:" GROUP BY T.tag ORDER BY bCount DESC'; + $query = 'SELECT T.tag, COUNT(T.tag) AS bCount FROM '.$GLOBALS['tableprefix'].'bookmarks AS B LEFT JOIN '.$GLOBALS['tableprefix'].'bookmarks2tags AS T ON B.bId = T.bId WHERE B.bHash = \''. $this->db->sql_escape($hash) .'\' '. $privacy .'AND ' . $this->db->QueryBuilder->left('T.tag', 7) . ' <> "system:" GROUP BY T.tag ORDER BY bCount DESC'; if (!($dbresult = $this->db->sql_query_limit($query, $limit))) { message_die(GENERAL_ERROR, 'Could not get related tags for this hash', '', __LINE__, __FILE__, $query, $this->db); @@ -599,7 +608,7 @@ class SemanticScuttle_Service_Bookmark2Tag extends SemanticScuttle_DbService . '%\''; } - $query .= ' AND LEFT(T.tag, 7) <> "system:"' + $query .= ' AND ' . $this->db->QueryBuilder->left('T.tag', 7) . ' <> "system:"' . ' GROUP BY T.tag' . ' ORDER BY bCount DESC, tag'; diff --git a/src/SemanticScuttle/Service/Factory.php b/src/SemanticScuttle/Service/Factory.php index b661cdb..4cc2efa 100644 --- a/src/SemanticScuttle/Service/Factory.php +++ b/src/SemanticScuttle/Service/Factory.php @@ -124,7 +124,7 @@ class SemanticScuttle_Service_Factory $db->sql_connect( $dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbpersist ); - if (!$db->db_connect_id) { + if (!$db->db_connection) { message_die( CRITICAL_ERROR, 'Could not connect to the database', diff --git a/src/SemanticScuttle/Service/Tag.php b/src/SemanticScuttle/Service/Tag.php index 17acae7..087a004 100644 --- a/src/SemanticScuttle/Service/Tag.php +++ b/src/SemanticScuttle/Service/Tag.php @@ -119,7 +119,13 @@ class SemanticScuttle_Service_Tag extends SemanticScuttle_DbService message_die(GENERAL_ERROR, 'Could not delete bookmarks', '', __LINE__, __FILE__, $query, $this->db); return false; } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not commit deleting bookmarks', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + return true; } diff --git a/src/SemanticScuttle/Service/Tag2Tag.php b/src/SemanticScuttle/Service/Tag2Tag.php index 9dddc44..d404bb5 100644 --- a/src/SemanticScuttle/Service/Tag2Tag.php +++ b/src/SemanticScuttle/Service/Tag2Tag.php @@ -94,7 +94,15 @@ class SemanticScuttle_Service_Tag2Tag extends SemanticScuttle_DbService ); return false; } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit attaching tag to tag', + '', __LINE__, __FILE__, $query, $this->db + ); + return false; + } // Update stats and cache $this->update($tag1, $tag2, $relationType, $uId); @@ -270,7 +278,7 @@ class SemanticScuttle_Service_Tag2Tag extends SemanticScuttle_DbService $tsts =SemanticScuttle_Service_Factory::get('TagStat'); $query.= ", ".$tsts->getTableName() ." tsts"; } - $query.= " WHERE tts.tag1 <> ALL"; + $query.= " WHERE tts.tag1 not in"; $query.= " (SELECT DISTINCT tag2 FROM `". $this->getTableName() ."`"; $query.= " WHERE relationType = '" . $this->db->sql_escape($relationType) . "'"; if($uId > 0) { diff --git a/src/SemanticScuttle/Service/TagCache.php b/src/SemanticScuttle/Service/TagCache.php index f8a28af..fb3c99a 100644 --- a/src/SemanticScuttle/Service/TagCache.php +++ b/src/SemanticScuttle/Service/TagCache.php @@ -106,7 +106,12 @@ class SemanticScuttle_Service_TagCache extends SemanticScuttle_DbService message_die(GENERAL_ERROR, 'Could not add tag cache inference', '', __LINE__, __FILE__, $query, $this->db); return false; } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not commit adding tag cache inference', '', __LINE__, __FILE__, $query, $this->db); + return false; + } } function removeChild($tag1, $tag2, $uId) { @@ -220,7 +225,13 @@ class SemanticScuttle_Service_TagCache extends SemanticScuttle_DbService message_die(GENERAL_ERROR, 'Could not add tag cache synonymy', '', __LINE__, __FILE__, $query, $this->db); return false; } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not commit adding tag cache synonymy', '', __LINE__, __FILE__, $query, $this->db); + return false; + } + break; } } diff --git a/src/SemanticScuttle/Service/User.php b/src/SemanticScuttle/Service/User.php index 68e8c11..dbbb202 100644 --- a/src/SemanticScuttle/Service/User.php +++ b/src/SemanticScuttle/Service/User.php @@ -103,15 +103,7 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService GENERAL_ERROR, 'Could not get user', '', __LINE__, __FILE__, $query, $this->db ); - return [ 'uId' => 0, - 'username' => '', - 'name' => '', - 'email' => '', - 'homepage' => '', - 'content' => '', - 'datetime' => NULL, - 'isAdmin' => false, - 'privateKeey' => '' ]; + return false; } $row = $this->db->sql_fetchrow($dbresult); @@ -119,15 +111,7 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService if ($row) { return $row; } else { - return [ 'uId' => 0, - 'username' => '', - 'name' => '', - 'email' => '', - 'homepage' => '', - 'content' => '', - 'datetime' => NULL, - 'isAdmin' => false, - 'privateKeey' => '' ]; + return false; } } @@ -218,7 +202,15 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService ); return false; } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit user update', '', + __LINE__, __FILE__, $sql, $this->db + ); + return false; + } // Everything worked out, so return true. return true; @@ -411,7 +403,12 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService { if (is_numeric($user)) { $user = $this->getUser($user); - $user = $user['username']; + if ($user) { + $user = $user['username']; + } + else { + $user = 'unknown'; + } } else if (is_array($user)) { $user = $user['username']; } @@ -620,10 +617,6 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService } $arrWatch = array(); - if ($this->db->sql_numrows($dbresult) == 0) { - $this->db->sql_freeresult($dbresult); - return $arrWatch; - } while ($row = $this->db->sql_fetchrow($dbresult)) { $arrWatch[] = $row['watched']; } @@ -670,10 +663,6 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService } $arrWatch = array(); - if ($this->db->sql_numrows($dbresult) == 0) { - $this->db->sql_freeresult($dbresult); - return $arrWatch; - } while ($row = $this->db->sql_fetchrow($dbresult)) { $arrWatch[] = $row[$this->getFieldName('username')]; } @@ -726,7 +715,12 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService } } - $this->db->sql_transaction('commit'); + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die(GENERAL_ERROR, 'Could not commit adding user to watch list', '', __LINE__, __FILE__, $sql, $this->db); + return false; + } + return true; } @@ -770,7 +764,15 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService return false; } $uId = $this->db->sql_nextid($dbresult); - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit user', + '', __LINE__, __FILE__, $sql, $this->db + ); + return false; + } return $uId; } @@ -852,7 +854,15 @@ class SemanticScuttle_Service_User extends SemanticScuttle_DbService ); return false; } - $this->db->sql_transaction('commit'); + + if (!$this->db->sql_transaction('commit')) { + $this->db->sql_transaction('rollback'); + message_die( + GENERAL_ERROR, 'Could not commit user update', '', + __LINE__, __FILE__, $sql, $this->db + ); + return false; + } // Everything worked out, so return true. return true; diff --git a/src/SemanticScuttle/db/QueryBuilder.php b/src/SemanticScuttle/db/QueryBuilder.php new file mode 100644 index 0000000..7fe612f --- /dev/null +++ b/src/SemanticScuttle/db/QueryBuilder.php @@ -0,0 +1,8 @@ +<?php +class QueryBuilder +{ + public function isNotNull($query) + { + return " !ISNULL({$query}) "; + } +} diff --git a/src/SemanticScuttle/db/db2.php b/src/SemanticScuttle/db/db2.php index 63c5bdd..0c75961 100644 --- a/src/SemanticScuttle/db/db2.php +++ b/src/SemanticScuttle/db/db2.php @@ -16,6 +16,8 @@ if(!defined("SQL_LAYER")) define("SQL_LAYER","db2"); +require_once 'QueryBuilder.php'; + /** * @package dbal_db2 * DB2 Database Abstraction Layer @@ -37,6 +39,14 @@ class sql_db var $persistency; var $dbname; var $server; + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + /*2023-12-20 end */ // // Constructor diff --git a/src/SemanticScuttle/db/firebird.php b/src/SemanticScuttle/db/firebird.php index ab9198a..97a727b 100644 --- a/src/SemanticScuttle/db/firebird.php +++ b/src/SemanticScuttle/db/firebird.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'firebird'); +require_once 'QueryBuilder.php'; + /** * @package dbal_firebird * Firebird/Interbase Database Abstraction Layer @@ -38,6 +40,14 @@ class sql_db var $persistency; var $dbname; var $server; + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + /*2023-12-20 end */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { diff --git a/src/SemanticScuttle/db/mssql-odbc.php b/src/SemanticScuttle/db/mssql-odbc.php index eb9d45b..967314b 100644 --- a/src/SemanticScuttle/db/mssql-odbc.php +++ b/src/SemanticScuttle/db/mssql-odbc.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'mssql-odbc'); +require_once 'QueryBuilder.php'; + /** * @package dbal_odbc_mssql * MSSQL ODBC Database Abstraction Layer for MSSQL @@ -42,6 +44,14 @@ class sql_db var $persistency; var $dbname; var $server; + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + /*2023-12-20 end */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { diff --git a/src/SemanticScuttle/db/mssql.php b/src/SemanticScuttle/db/mssql.php index dbcfcae..3df0343 100644 --- a/src/SemanticScuttle/db/mssql.php +++ b/src/SemanticScuttle/db/mssql.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'mssql'); +require_once 'QueryBuilder.php'; + /** * @package dbal_mssql * MSSQL Database Abstraction Layer @@ -36,6 +38,14 @@ class sql_db var $persistency; var $dbname; var $server; + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + /*2023-12-20 end */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { diff --git a/src/SemanticScuttle/db/mysql.php b/src/SemanticScuttle/db/mysql.php index f5f6cc2..649ad3b 100644 --- a/src/SemanticScuttle/db/mysql.php +++ b/src/SemanticScuttle/db/mysql.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'mysql'); +require_once 'QueryBuilder.php'; + /** * @package dbal_mysql * MySQL Database Abstraction Layer @@ -37,6 +39,14 @@ class sql_db var $dbname; var $server; /*2023-12-20 end */ + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; diff --git a/src/SemanticScuttle/db/mysql4.php b/src/SemanticScuttle/db/mysql4.php index 7244e51..51d757b 100644 --- a/src/SemanticScuttle/db/mysql4.php +++ b/src/SemanticScuttle/db/mysql4.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'mysql4'); +require_once 'QueryBuilder.php'; + /** * @package dbal_mysql4 * MySQL4 Database Abstraction Layer @@ -36,6 +38,14 @@ class sql_db var $persistency; var $dbname; var $server; + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + /*2023-12-20 end */ function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { diff --git a/src/SemanticScuttle/db/mysqli.php b/src/SemanticScuttle/db/mysqli.php index caaa893..5401d37 100644 --- a/src/SemanticScuttle/db/mysqli.php +++ b/src/SemanticScuttle/db/mysqli.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'mysqli'); +require_once 'QueryBuilder.php'; + /** * @package dbal_mysqli * MySQLi Database Abstraction Layer @@ -43,6 +45,13 @@ class sql_db var $cur_index; /*2023-12-20 end block (see more below)*/ + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->persistency = $persistency; diff --git a/src/SemanticScuttle/db/oracle.php b/src/SemanticScuttle/db/oracle.php index f1a45a6..3e017d4 100644 --- a/src/SemanticScuttle/db/oracle.php +++ b/src/SemanticScuttle/db/oracle.php @@ -16,6 +16,8 @@ if(!defined("SQL_LAYER")) define("SQL_LAYER","oracle"); +require_once 'QueryBuilder.php'; + /** * @package dbal_oracle * Oracle Database Abstraction Layer @@ -36,6 +38,14 @@ class sql_db var $dbname; var $server; /*2023-12-20 end */ + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + // // Constructor // diff --git a/src/SemanticScuttle/db/postgres.php b/src/SemanticScuttle/db/postgres.php index 4acd6a2..c9ed35c 100644 --- a/src/SemanticScuttle/db/postgres.php +++ b/src/SemanticScuttle/db/postgres.php @@ -16,6 +16,8 @@ if (!defined('SQL_LAYER')) define('SQL_LAYER', 'postgresql'); +require_once 'QueryBuilder.php'; + /** * @package dbal_postgres * PostgreSQL Database Abstraction Layer @@ -37,6 +39,14 @@ class sql_db var $dbname; var $server; /*2023-12-20 end */ + + public $QueryBuilder; + + public function __construct() + { + $this->QueryBuilder = new QueryBuilder(); + } + function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false) { $this->connect_string = ''; diff --git a/src/SemanticScuttle/db/sqlite.php b/src/SemanticScuttle/db/sqlite.php index 940e194..f34be28 100644 --- a/src/SemanticScuttle/db/sqlite.php +++ b/src/SemanticScuttle/db/sqlite.php @@ -1,10 +1,10 @@ <?php -/** +/** * * @package dbal_sqlite * @version $Id: sqlite.php,v 1.2 2005/06/10 08:52:03 devalley Exp $ -* @copyright (c) 2005 phpBB Group -* @license http://opensource.org/licenses/gpl-license.php GNU Public License +* @copyright (c) 2005 phpBB Group +* @license http://opensource.org/licenses/gpl-license.php GNU Public License * */ @@ -16,16 +16,17 @@ if (!defined("SQL_LAYER")) define("SQL_LAYER","sqlite"); +require_once 'sqlite.querybuilder.php'; + /** * @package dbal_sqlite * Sqlite Database Abstraction Layer */ class sql_db { - var $db_connect_id; + var $db_connection; var $query_result; var $return_on_error = false; - var $transaction = false; var $sql_report = ''; var $sql_time = 0; var $num_queries = 0; @@ -38,6 +39,15 @@ class sql_db var $server; /*2023-12-20 end */ + public $QueryBuilder; + + private int $transaction_level = 0; + + public function __construct() + { + $this->QueryBuilder = new SQLiteQueryBuilder(); + } + function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port, $persistency = false) { $this->persistency = $persistency; @@ -45,20 +55,20 @@ class sql_db $this->server = $sqlserver . (($port) ? ':' . $port : ''); $this->dbname = $database; - $this->db_connect_id = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : @sqlite_open($this->server, 0, $error); + $this->db_connection = ($this->persistency) ? @sqlite_popen($this->server, 0, $error) : new SQLite3($this->server); - return ($this->db_connect_id) ? true : $error; + return ($this->db_connection) ? true : $error; } // Other base methods function sql_close() { - if (!$this->db_connect_id) + if (!$this->db_connection) { return false; } - return @sqlite_close($this->db_connect_id); + return $this->db_connection->close(); } function sql_return_on_error($fail = false) @@ -76,19 +86,34 @@ class sql_db switch ($status) { case 'begin': - $this->transaction = true; - $result = @sqlite_query('BEGIN', $this->db_connect_id); - break; + $this->transaction_level++; + if ($this->transaction_level == 1) { + $result = $this->db_connection->exec('BEGIN'); + break; + } + else { + $result = true; + } case 'commit': - $this->transaction = false; - $result = @sqlite_query('COMMIT', $this->db_connect_id); - break; + $this->transaction_level--; + if ($this->transaction_level == 0) { + $result = $this->db_connection->exec('COMMIT'); + break; + } + else { + $result = true; + } case 'rollback': - $this->transaction = false; - $result = @sqlite_query('ROLLBACK', $this->db_connect_id); - break; + $this->transaction_level--; + if ($this->transaction_level == 0) { + $result = $this->db_connection->exec('ROLLBACK'); + break; + } + else { + $result = true; + } default: $result = true; @@ -98,7 +123,7 @@ class sql_db } // Base query method - function sql_query($query = '', $expire_time = 0) + function sql_query($query = '', $cache_ttl = 0) { if ($query != '') { @@ -124,7 +149,7 @@ class sql_db $curtime = $curtime[0] + $curtime[1] - $starttime; } - if (!($this->query_result = @sqlite_query($query, $this->db_connect_id))) + if (!($this->query_result = $this->db_connection->query($query))) { $this->sql_error($query); } @@ -151,9 +176,9 @@ class sql_db if (preg_match('#^SELECT#', $query)) { $html_table = FALSE; - if ($result = @sqlite_query("EXPLAIN $query", $this->db_connect_id)) + if ($result = $this->db_connection->query("EXPLAIN $query")) { - while ($row = @sqlite_fetch_array($result, @sqlite_ASSOC)) + while ($row = $result->fetchArray(SQLITE_ASSOC)) { if (!$html_table && sizeof($row)) { @@ -261,6 +286,25 @@ class sql_db } $query = implode(', ', $values); } + else if ($query == 'SELECT') + { + foreach ($assoc_ary as $key => $var) + { + if (is_null($var)) + { + $values[] = "$key = NULL"; + } + elseif (is_string($var)) + { + $values[] = "$key = '" . $this->sql_escape($var) . "'"; + } + else + { + $values[] = (is_bool($var)) ? "$key = " . intval($var) : "$key = $var"; + } + } + $query = implode(' AND ', $values); + } return $query; } @@ -271,50 +315,46 @@ class sql_db // don't want this here by a middle Milestone function sql_numrows($query_id = false) { - if (!$query_id) - { - $query_id = $this->query_result; - } - - return ($query_id) ? @sqlite_num_rows($query_id) : false; + // The new SQLite3 module in PHP doesn't provide a way of determining + // the number of rows returned before iterating through them. + throw new Exception('Not Implemented'); } function sql_affectedrows() { - return ($this->db_connect_id) ? @sqlite_changes($this->db_connect_id) : false; + return ($this->db_connection) ? $this->db_connection->changes() : false; } - function sql_fetchrow($query_id = 0) + function sql_fetchrow($query_result = NULL) { global $cache; - if (!$query_id) + if (!$query_result) { - $query_id = $this->query_result; + $query_result = $this->query_result; } - if ($cache->sql_exists($query_id)) + if (isset($cache->sql_rowset[$query_result])) { - return $cache->sql_fetchrow($query_id); + return $cache->sql_fetchrow($query_result); } - return ($query_id) ? @sqlite_fetch_array($query_id, @sqlite_ASSOC) : false; + return ($query_result) ? $query_result->fetchArray(SQLITE3_ASSOC) : false; } - function sql_fetchrowset($query_id = 0) + function sql_fetchrowset($query_result = false) { - if (!$query_id) + if (!$query_result) { - $query_id = $this->query_result; + $query_result = $this->query_result; } - if ($query_id) + if ($query_result) { - unset($this->rowset[$query_id]); - unset($this->row[$query_id]); - while ($this->rowset[$query_id] = @sqlite_fetch_array($query_id, @sqlite_ASSOC)) + $result = array(); + while ($row = $query_result->fetchArray(SQLITE3_ASSOC)) { - $result[] = $this->rowset[$query_id]; + $result[] = $row; } return $result; } @@ -331,9 +371,15 @@ class sql_db $query_id = $this->query_result; } - if ($query_id) + $row = []; + + while ($rownum > 0) { + $row = $query_id->fetchArray(SQLITE3_NUM); + } + + if ($row) { - return ($rownum > -1) ? ((@sqlite_seek($query_id, $rownum)) ? @sqlite_column($query_id, $field) : false) : @sqlite_column($query_id, $field); + return $row[$field]; } } @@ -349,7 +395,7 @@ class sql_db function sql_nextid() { - return ($this->db_connect_id) ? @sqlite_last_insert_rowid($this->db_connect_id) : false; + return ($this->db_connection) ? $this->db_connection->lastInsertRowID() : false; } function sql_freeresult($query_id = false) @@ -359,7 +405,7 @@ class sql_db function sql_escape($msg) { - return @sqlite_escape_string(stripslashes($msg)); + return SQLite3::escapeString(stripslashes($msg)); } function sql_error($sql = '') @@ -369,9 +415,9 @@ class sql_db $this_page = (!empty($_SERVER['PHP_SELF'])) ? $_SERVER['PHP_SELF'] : $_ENV['PHP_SELF']; $this_page .= '&' . ((!empty($_SERVER['QUERY_STRING'])) ? $_SERVER['QUERY_STRING'] : $_ENV['QUERY_STRING']); - $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . @sqlite_error_string(@sqlite_last_error($this->db_connect_id)) . '<br /><br /><u>CALLING PAGE</u><br /><br />' . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />'; + $message = '<u>SQL ERROR</u> [ ' . SQL_LAYER . ' ]<br /><br />' . $this->db_connection->lastErrorMsg() . '<br /><br /><u>CALLING PAGE</u><br /><br />' . htmlspecialchars($this_page) . (($sql != '') ? '<br /><br /><u>SQL</u><br /><br />' . $sql : '') . '<br />'; - if ($this->transaction) + while ($this->transaction_level > 0) { $this->sql_transaction('rollback'); } @@ -380,8 +426,8 @@ class sql_db } $result = array( - 'message' => @sqlite_error_string(@sqlite_last_error($this->db_connect_id)), - 'code' => @sqlite_last_error($this->db_connect_id) + 'message' => $this->db_connection->lastErrorMsg(), + 'code' => $this->db_connection->lastErrorCode(), ); return $result; diff --git a/src/SemanticScuttle/db/sqlite.querybuilder.php b/src/SemanticScuttle/db/sqlite.querybuilder.php new file mode 100644 index 0000000..91cabcf --- /dev/null +++ b/src/SemanticScuttle/db/sqlite.querybuilder.php @@ -0,0 +1,16 @@ +<?php +require_once 'QueryBuilder.php'; + +class SQLiteQueryBuilder extends QueryBuilder +{ + public function isNotNull($query) + { + return " IIF({$query}, true, false) "; + } + + public function left($query, $length) + { + $endIndex = $length + 1; + return " SUBSTRING({$query}, 0, {$endIndex}) "; + } +} diff --git a/www/profile.php b/www/profile.php index 6a4222e..88e58e5 100644 --- a/www/profile.php +++ b/www/profile.php @@ -130,10 +130,11 @@ if (POST_SUBMITTED!='' && $currentUser->getId() == $userid) { } } $userinfo = $userservice->getObjectUserByUsername($user); - $tplVars['privateKey'] = $userinfo->getPrivateKey(true); - if ($userservice->isPrivateKeyValid($userinfo->getPrivateKey())) { + if ($userinfo && $userservice->isPrivateKeyValid($userinfo->getPrivateKey())) { + $tplVars['privateKey'] = $userinfo->getPrivateKey(true); $tplVars['privateKeyIsEnabled'] = 'checked="checked"'; } else { + $tplVars['privateKey'] = null; $tplVars['privateKeyIsEnabled'] = ''; } } |