From d0e73ed8ae0bf79697115c257743f8ff36e1f64e Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Fri, 1 Dec 2017 17:33:59 +0300 Subject: [PATCH] article: switch to PDO --- classes/article.php | 224 ++++++++++++++++++++++++-------------------- 1 file changed, 122 insertions(+), 102 deletions(-) diff --git a/classes/article.php b/classes/article.php index 8f59e2b8..ff6157f6 100644 --- a/classes/article.php +++ b/classes/article.php @@ -123,35 +123,37 @@ class Article extends Handler_Protected { if (filter_var($url, FILTER_VALIDATE_URL) === FALSE) return false; - $this->pdo->beginTransaction(); + $pdo = Db::pdo(); + + $pdo->beginTransaction(); // only check for our user data here, others might have shared this with different content etc - $sth = $this->pdo->prepare("SELECT id FROM ttrss_entries, ttrss_user_entries WHERE + $sth = $pdo->prepare("SELECT id FROM ttrss_entries, ttrss_user_entries WHERE guid = ? AND ref_id = id AND owner_uid = ? LIMIT 1"); $sth->execute([$guid, $owner_uid]); if ($row = $sth->fetch()) { $ref_id = $row['id']; - $sth = $this->pdo->prepare("SELECT int_id FROM ttrss_user_entries WHERE + $sth = $pdo->prepare("SELECT int_id FROM ttrss_user_entries WHERE ref_id = ? AND owner_uid = ? LIMIT 1"); $sth->execute([$ref_id, $owner_uid]); if ($row = $sth->fetch()) { $int_id = $row['int_id']; - $sth = $this->pdo->prepare("UPDATE ttrss_entries SET + $sth = $pdo->prepare("UPDATE ttrss_entries SET content = ?, content_hash = ? WHERE id = ?"); $sth->execute([$content, $content_hash, $ref_id]); - $sth = $this->pdo->prepare("UPDATE ttrss_user_entries SET published = true, + $sth = $pdo->prepare("UPDATE ttrss_user_entries SET published = true, last_published = NOW() WHERE int_id = ? AND owner_uid = ?"); $sth->execute([$int_id, $owner_uid]); } else { - $sth = $this->pdo->prepare("INSERT INTO ttrss_user_entries + $sth = $pdo->prepare("INSERT INTO ttrss_user_entries (ref_id, uuid, feed_id, orig_feed_id, owner_uid, published, tag_cache, label_cache, last_read, note, unread, last_published) VALUES @@ -168,19 +170,19 @@ class Article extends Handler_Protected { $rc = true; } else { - $sth = $this->pdo->prepare("INSERT INTO ttrss_entries + $sth = $pdo->prepare("INSERT INTO ttrss_entries (title, guid, link, updated, content, content_hash, date_entered, date_updated) VALUES (?, ?, ?, NOW(), ?, ?, NOW(), NOW())"); $sth->execute([$title, $guid, $url, $content, $content_hash]); - $sth = $this->pdo->prepare("SELECT id FROM ttrss_entries WHERE guid = ?"); + $sth = $pdo->prepare("SELECT id FROM ttrss_entries WHERE guid = ?"); $sth->execute([$guid]); if ($row = $sth->fetch()) { $ref_id = $row["id"]; - $sth = $this->pdo->prepare("INSERT INTO ttrss_user_entries + $sth = $pdo->prepare("INSERT INTO ttrss_user_entries (ref_id, uuid, feed_id, orig_feed_id, owner_uid, published, tag_cache, label_cache, last_read, note, unread, last_published) VALUES @@ -197,7 +199,7 @@ class Article extends Handler_Protected { } } - $this->pdo->commit(); + $pdo->commit(); return $rc; } @@ -276,17 +278,19 @@ class Article extends Handler_Protected { $this->pdo->beginTransaction(); - $result = db_query("SELECT int_id FROM ttrss_user_entries WHERE - ref_id = '$id' AND owner_uid = '".$_SESSION["uid"]."' LIMIT 1"); + $sth = $this->pdo->prepare("SELECT int_id FROM ttrss_user_entries WHERE + ref_id = ? AND owner_uid = ? LIMIT 1"); + $sth->execute([$id, $_SESSION['uid']]); - if (db_num_rows($result) == 1) { + if ($row = $sth->fetch()) { $tags_to_cache = array(); - $int_id = db_fetch_result($result, 0, "int_id"); + $int_id = $row['int_id']; - db_query("DELETE FROM ttrss_tags WHERE - post_int_id = $int_id AND owner_uid = '".$_SESSION["uid"]."'"); + $sth = $this->pdo->prepare("DELETE FROM ttrss_tags WHERE + post_int_id = ? AND owner_uid = ?"); + $sth->execute([$int_id, $_SESSION['uid']]); foreach ($tags as $tag) { $tag = sanitize_tag($tag); @@ -302,8 +306,11 @@ class Article extends Handler_Protected { // print ""; if ($tag != '') { - db_query("INSERT INTO ttrss_tags - (post_int_id, owner_uid, tag_name) VALUES ('$int_id', '".$_SESSION["uid"]."', '$tag')"); + $sth = $this->pdo->prepare("INSERT INTO ttrss_tags + (post_int_id, owner_uid, tag_name) + VALUES (?, ?, ?)"); + + $sth->execute([$int_id, $_SESSION['uid'], $tag]); } array_push($tags_to_cache, $tag); @@ -314,9 +321,9 @@ class Article extends Handler_Protected { sort($tags_to_cache); $tags_str = join(",", $tags_to_cache); - db_query("UPDATE ttrss_user_entries - SET tag_cache = '$tags_str' WHERE ref_id = '$id' - AND owner_uid = " . $_SESSION["uid"]); + $sth = $this->pdo->prepare("UPDATE ttrss_user_entries + SET tag_cache = ? WHERE ref_id = ? AND owner_uid = ?"); + $sth->execute([$tags_str, $id, $_SESSION['uid']]); } $this->pdo->commit(); @@ -333,15 +340,17 @@ class Article extends Handler_Protected { function completeTags() { - $search = db_escape_string($_REQUEST["search"]); + $search = $_REQUEST["search"]; - $result = db_query("SELECT DISTINCT tag_name FROM ttrss_tags - WHERE owner_uid = '".$_SESSION["uid"]."' AND - tag_name LIKE '$search%' ORDER BY tag_name + $sth = $this->pdo->prepare("SELECT DISTINCT tag_name FROM ttrss_tags + WHERE owner_uid = ? AND + tag_name LIKE ? ORDER BY tag_name LIMIT 10"); + $sth->execute([$_SESSION['uid'], "$search%"]); + print ""; @@ -389,11 +398,12 @@ class Article extends Handler_Protected { } function getArticleFeed($id) { - $result = db_query("SELECT feed_id FROM ttrss_user_entries - WHERE ref_id = '$id' AND owner_uid = " . $_SESSION["uid"]); + $sth = $this->pdo->prepare("SELECT feed_id FROM ttrss_user_entries + WHERE ref_id = ? AND owner_uid = ?"); + $sth->execute([$id, $_SESSION['uid']]); - if (db_num_rows($result) != 0) { - return db_fetch_result($result, 0, "feed_id"); + if ($row = $sth->fetch()) { + return $row["feed_id"]; } else { return 0; } @@ -547,24 +557,29 @@ class Article extends Handler_Protected { /* we can figure out feed_id from article id anyway, why do we * pass feed_id here? let's ignore the argument :(*/ - $result = db_query("SELECT feed_id FROM ttrss_user_entries - WHERE ref_id = '$id'"); + $pdo = Db::pdo(); + + $sth = $pdo->prepare("SELECT feed_id FROM ttrss_user_entries + WHERE ref_id = ?"); + $sth->execute([$id]); + $row = $sth->fetch(); - $feed_id = (int) db_fetch_result($result, 0, "feed_id"); + $feed_id = (int) $row["feed_id"]; $rv['feed_id'] = $feed_id; //if (!$zoom_mode) { print "
prepare("UPDATE ttrss_user_entries SET unread = false,last_read = NOW() - WHERE ref_id = '$id' AND owner_uid = $owner_uid"); + WHERE ref_id = ? AND owner_uid = ?"); + $sth->execute([$id, $owner_uid]); CCache::update($feed_id, $owner_uid); } - $result = db_query("SELECT id,title,link,content,feed_id,comments,int_id,lang, + $sth = $pdo->prepare("SELECT id,title,link,content,feed_id,comments,int_id,lang, ".SUBSTRING_FOR_DATE."(updated,1,16) as updated, (SELECT site_url FROM ttrss_feeds WHERE id = feed_id) as site_url, (SELECT title FROM ttrss_feeds WHERE id = feed_id) as feed_title, @@ -577,11 +592,10 @@ class Article extends Handler_Protected { orig_feed_id, note FROM ttrss_entries,ttrss_user_entries - WHERE id = '$id' AND ref_id = id AND owner_uid = $owner_uid"); + WHERE id = ? AND ref_id = id AND owner_uid = ?"); + $sth->execute([$id, $owner_uid]); - if ($result) { - - $line = db_fetch_assoc($result); + if ($line = $sth->fetch()) { $line["tags"] = Article::get_article_tags($id, $owner_uid, $line["tag_cache"]); unset($line["tag_cache"]); @@ -700,18 +714,17 @@ class Article extends Handler_Protected { if ($line["orig_feed_id"]) { - $tmp_result = db_query("SELECT * FROM ttrss_archived_feeds - WHERE id = ".$line["orig_feed_id"] . " AND owner_uid = " . $_SESSION["uid"]); + $of_sth = $pdo->prepare("SELECT * FROM ttrss_archived_feeds + WHERE id = ? AND owner_uid = ?"); + $of_sth->execute([$line["orig_feed_id"], $owner_uid]); - if (db_num_rows($tmp_result) != 0) { + if ($tmp_line = $of_sth->fetch()) { $rv['content'] .= "
"; $rv['content'] .= __("Originally from:"); $rv['content'] .= " "; - $tmp_line = db_fetch_assoc($tmp_result); - $rv['content'] .= "" . $tmp_line['title'] . ""; @@ -774,21 +787,23 @@ class Article extends Handler_Protected { if (!$owner_uid) $owner_uid = $_SESSION["uid"]; - $query = "SELECT DISTINCT tag_name, - owner_uid as owner FROM - ttrss_tags WHERE post_int_id = (SELECT int_id FROM ttrss_user_entries WHERE - ref_id = '$a_id' AND owner_uid = '$owner_uid' LIMIT 1) ORDER BY tag_name"; + $pdo = Db::pdo(); + + $sth = $pdo->prepare("SELECT DISTINCT tag_name, + owner_uid as owner FROM ttrss_tags + WHERE post_int_id = (SELECT int_id FROM ttrss_user_entries WHERE + ref_id = ? AND owner_uid = ? LIMIT 1) ORDER BY tag_name"); $tags = array(); /* check cache first */ if ($tag_cache === false) { - $result = db_query("SELECT tag_cache FROM ttrss_user_entries - WHERE ref_id = '$id' AND owner_uid = $owner_uid"); + $csth = $pdo->prepare("SELECT tag_cache FROM ttrss_user_entries + WHERE ref_id = ? AND owner_uid = ?"); + $csth->execute([$id, $owner_uid]); - if (db_num_rows($result) != 0) - $tag_cache = db_fetch_result($result, 0, "tag_cache"); + if ($row = $csth->fetch()) $tag_cache = $row["tag_cache"]; } if ($tag_cache) { @@ -797,9 +812,9 @@ class Article extends Handler_Protected { /* do it the hard way */ - $tmp_result = db_query($query); + $sth->execute([$a_id, $owner_uid]); - while ($tmp_line = db_fetch_assoc($tmp_result)) { + while ($tmp_line = $sth->fetch()) { array_push($tags, $tmp_line["tag_name"]); } @@ -807,9 +822,10 @@ class Article extends Handler_Protected { $tags_str = db_escape_string(join(",", $tags)); - db_query("UPDATE ttrss_user_entries - SET tag_cache = '$tags_str' WHERE ref_id = '$id' - AND owner_uid = $owner_uid"); + $sth = $pdo->prepare("UPDATE ttrss_user_entries + SET tag_cache = ? WHERE ref_id = ? + AND owner_uid = ?"); + $sth->execute([$tags_str, $id, $owner_uid]); } return $tags; @@ -862,22 +878,21 @@ class Article extends Handler_Protected { static function get_article_enclosures($id) { - $query = "SELECT * FROM ttrss_enclosures - WHERE post_id = '$id' AND content_url != ''"; + $pdo = Db::pdo(); - $rv = array(); - - $result = db_query($query); + $sth = $pdo->prepare("SELECT * FROM ttrss_enclosures + WHERE post_id = ? AND content_url != ''"); + $sth->execute([$id]); - if (db_num_rows($result) > 0) { - while ($line = db_fetch_assoc($result)) { + $rv = array(); - if (file_exists(CACHE_DIR . '/images/' . sha1($line["content_url"]))) { - $line["content_url"] = get_self_url_prefix() . '/public.php?op=cached_url&hash=' . sha1($line["content_url"]); - } + while ($line = $sth->fetch()) { - array_push($rv, $line); + if (file_exists(CACHE_DIR . '/images/' . sha1($line["content_url"]))) { + $line["content_url"] = get_self_url_prefix() . '/public.php?op=cached_url&hash=' . sha1($line["content_url"]); } + + array_push($rv, $line); } return $rv; @@ -886,11 +901,13 @@ class Article extends Handler_Protected { static function purge_orphans($do_output = false) { // purge orphaned posts in main content table - $result = db_query("DELETE FROM ttrss_entries WHERE + + $pdo = Db::pdo(); + $res = $pdo->query("DELETE FROM ttrss_entries WHERE NOT EXISTS (SELECT ref_id FROM ttrss_user_entries WHERE ref_id = id)"); if ($do_output) { - $rows = db_affected_rows($result); + $rows = $res->rowCount(); _debug("Purged $rows orphaned posts."); } } @@ -898,46 +915,47 @@ class Article extends Handler_Protected { static function catchupArticlesById($ids, $cmode, $owner_uid = false) { if (!$owner_uid) $owner_uid = $_SESSION["uid"]; - if (count($ids) == 0) return; - $tmp_ids = array(); - - foreach ($ids as $id) { - array_push($tmp_ids, "ref_id = '$id'"); - } + $pdo = Db::pdo(); - $ids_qpart = join(" OR ", $tmp_ids); + $ids_qmarks = arr_qmarks($ids); if ($cmode == 0) { - db_query("UPDATE ttrss_user_entries SET + $sth = $pdo->prepare("UPDATE ttrss_user_entries SET unread = false,last_read = NOW() - WHERE ($ids_qpart) AND owner_uid = $owner_uid"); + WHERE ref_id IN ($ids_qmarks) AND owner_uid = ?"); } else if ($cmode == 1) { - db_query("UPDATE ttrss_user_entries SET + $sth = $pdo->prepare("UPDATE ttrss_user_entries SET unread = true - WHERE ($ids_qpart) AND owner_uid = $owner_uid"); + WHERE ref_id IN ($ids_qmarks) AND owner_uid = ?"); } else { - db_query("UPDATE ttrss_user_entries SET - unread = NOT unread,last_read = NOW() - WHERE ($ids_qpart) AND owner_uid = $owner_uid"); + $sth = $pdo->prepare("UPDATE ttrss_user_entries SET + unread = NOT unread,last_read = NOW() + WHERE ref_id IN ($ids_qmarks) AND owner_uid = ?"); } + $sth->execute(array_merge($ids, [$owner_uid])); + /* update ccache */ - $result = db_query("SELECT DISTINCT feed_id FROM ttrss_user_entries - WHERE ($ids_qpart) AND owner_uid = $owner_uid"); + $sth = $pdo->prepare("SELECT DISTINCT feed_id FROM ttrss_user_entries + WHERE ref_id IN ($ids_qmarks) AND owner_uid = ?"); + $sth->execute(array_merge($ids, [$owner_uid])); - while ($line = db_fetch_assoc($result)) { + while ($line = $sth->fetch()) { CCache::update($line["feed_id"], $owner_uid); } } static function getLastArticleId() { - $result = db_query("SELECT ref_id AS id FROM ttrss_user_entries - WHERE owner_uid = " . $_SESSION["uid"] . " ORDER BY ref_id DESC LIMIT 1"); + $pdo = DB::pdo(); + + $sth = $pdo->prepare("SELECT ref_id AS id FROM ttrss_user_entries + WHERE owner_uid = ? ORDER BY ref_id DESC LIMIT 1"); + $sth->execute([$_SESSION['uid']]); - if (db_num_rows($result) == 1) { - return db_fetch_result($result, 0, "id"); + if ($row = $sth->fetch()) { + return $row['id']; } else { return -1; } @@ -948,12 +966,14 @@ class Article extends Handler_Protected { if (!$owner_uid) $owner_uid = $_SESSION["uid"]; - $result = db_query("SELECT label_cache FROM - ttrss_user_entries WHERE ref_id = '$id' AND owner_uid = " . - $owner_uid); + $pdo = Db::pdo(); + + $sth = $pdo->prepare("SELECT label_cache FROM + ttrss_user_entries WHERE ref_id = ? AND owner_uid = ?"); + $sth->execute([$id, $owner_uid]); - if (db_num_rows($result) > 0) { - $label_cache = db_fetch_result($result, 0, "label_cache"); + if ($row = $sth->fetch()) { + $label_cache = $row["label_cache"]; if ($label_cache) { $label_cache = json_decode($label_cache, true); @@ -965,15 +985,15 @@ class Article extends Handler_Protected { } } - $result = db_query( - "SELECT DISTINCT label_id,caption,fg_color,bg_color + $sth = $pdo->prepare("SELECT DISTINCT label_id,caption,fg_color,bg_color FROM ttrss_labels2, ttrss_user_labels2 WHERE id = label_id - AND article_id = '$id' - AND owner_uid = ". $owner_uid . " + AND article_id = ? + AND owner_uid = ? ORDER BY caption"); + $sth->execute([$id, $owner_uid]); - while ($line = db_fetch_assoc($result)) { + while ($line = $sth->fetch()) { $rk = array(Labels::label_to_feed_id($line["label_id"]), $line["caption"], $line["fg_color"], $line["bg_color"]); -- 2.39.5