diff options
| -rw-r--r-- | backend.php | 98 | ||||
| -rw-r--r-- | config.php-dist | 1 | ||||
| -rw-r--r-- | db.php | 6 | ||||
| -rw-r--r-- | index.php | 55 | ||||
| -rw-r--r-- | login.php | 9 | ||||
| -rw-r--r-- | sessions.php | 51 | ||||
| -rw-r--r-- | useradm.php | 22 |
7 files changed, 107 insertions, 135 deletions
diff --git a/backend.php b/backend.php index 0ca006b..999feb5 100644 --- a/backend.php +++ b/backend.php @@ -15,7 +15,7 @@ require_once "sessions.php"; require_once "db.php"; - @$owner = SQLite3::escapeString($_SESSION["owner"]); + @$owner = $_SESSION["owner"]; if (!$owner) { header($_SERVER["SERVER_PROTOCOL"]." 401 Unauthorized"); @@ -33,10 +33,11 @@ case "cover": $id = (int) $_REQUEST["id"]; - $db = new SQLite3(CALIBRE_DB, SQLITE3_OPEN_READONLY); - $result = $db->query("SELECT has_cover, path FROM books WHERE id = " . $id); + $db = new PDO('sqlite:' . CALIBRE_DB); + $sth = $db->prepare("SELECT has_cover, path FROM books WHERE id = ?"); + $sth->execute([$id]); - while ($line = $result->fetchArray(SQLITE3_ASSOC)) { + while ($line = $sth->fetch()) { $filename = BOOKS_DIR . "/" . $line["path"] . "/" . "cover.jpg"; if (file_exists($filename)) { @@ -54,23 +55,22 @@ break; case "getowner": - $owner = SQLite3::escapeString($_SESSION["owner"]);; - print json_encode(["owner" => $owner]); break; case "getinfo": $id = (int) $_REQUEST["id"]; - $db = new SQLite3(CALIBRE_DB, SQLITE3_OPEN_READONLY); + $db = new PDO('sqlite:' . CALIBRE_DB); - $result = $db->query("SELECT books.*, s.name AS series_name, + $sth = $db->prepare("SELECT books.*, s.name AS series_name, (SELECT text FROM comments WHERE book = books.id) AS comment, (SELECT id FROM data WHERE book = books.id AND format = 'EPUB' LIMIT 1) AS epub_id FROM books LEFT JOIN books_series_link AS bsl ON (bsl.book = books.id) LEFT JOIN series AS s ON (bsl.series = s.id) - WHERE books.id = " . $id); + WHERE books.id = ?"); + $sth->execute([$id]); - if ($line = $result->fetchArray(SQLITE3_ASSOC)) { + if ($line = $sth->fetch()) { print json_encode($line); } @@ -79,21 +79,26 @@ case "togglefav": $id = (int) $_REQUEST["id"]; - $result = $ldb->query("SELECT id FROM epube_favorites WHERE bookid = '$id' - AND owner = '$owner' LIMIT 1"); + $sth = $ldb->prepare("SELECT id FROM epube_favorites WHERE bookid = ? + AND owner = ? LIMIT 1"); + $sth->execute([$id, $owner]); $found_id = false; $status = -1; - while ($line = $result->fetchArray(SQLITE3_ASSOC)) { + while ($line = $sth->fetch()) { $found_id = $line["id"]; } if ($found_id) { - $ldb->query("DELETE FROM epube_favorites WHERE id = " . $found_id); + $sth = $ldb->prepare("DELETE FROM epube_favorites WHERE id = ?"); + $sth->execute([$found_id]); + $status = 0; } else { - $ldb->query("INSERT INTO epube_favorites (bookid, owner) VALUES ($id, '$owner')"); + $sth = $ldb->prepare("INSERT INTO epube_favorites (bookid, owner) VALUES (?, ?)"); + $sth->execute([$id, $owner]); + $status = 1; } @@ -102,10 +107,11 @@ case "download": $id = (int) $_REQUEST["id"]; - $db = new SQLite3(CALIBRE_DB, SQLITE3_OPEN_READONLY); - $result = $db->query("SELECT path, name, format FROM data LEFT JOIN books ON (data.book = books.id) WHERE data.id = " . $id); + $db = new PDO('sqlite:' . CALIBRE_DB); + $sth = $db->prepare("SELECT path, name, format FROM data LEFT JOIN books ON (data.book = books.id) WHERE data.id = ?"); + $sth->execute([$id]); - while ($line = $result->fetchArray(SQLITE3_ASSOC)) { + while ($line = $sth->fetch()) { $filename = BOOKS_DIR . "/" . $line["path"] . "/" . $line["name"] . "." . strtolower($line["format"]); if (file_exists($filename)) { @@ -127,9 +133,10 @@ $bookid = (int) $_REQUEST["id"]; if ($bookid) { - $result = $ldb->query("SELECT pagination FROM epube_pagination WHERE bookid = '$bookid' LIMIT 1"); + $sth = $ldb->prepare("SELECT pagination FROM epube_pagination WHERE bookid = ? LIMIT 1"); + $sth->execute([$bookid]); - if ($line = $result->fetchArray()) { + if ($line = $sth->fetch()) { print $line["pagination"]; } else { header($_SERVER["SERVER_PROTOCOL"]." 404 Not Found"); @@ -140,28 +147,30 @@ break; case "storepagination": $bookid = (int) $_REQUEST["id"]; - $payload = SQLite3::escapeString($_REQUEST["payload"]); + $payload = $_REQUEST["payload"]; $total_pages = (int) $_REQUEST["total"]; if ($bookid && $payload && $total_pages) { - $ldb->query("BEGIN"); + $ldb->beginTransaction(); - $result = $ldb->query("SELECT id FROM epube_pagination WHERE bookid = '$bookid' LIMIT 1"); + $sth = $ldb->prepare("SELECT id FROM epube_pagination WHERE bookid = ? LIMIT 1"); + $sth->execute([$bookid]); - if ($line = $result->fetchArray()) { + if ($line = $sth->fetch()) { $id = $line["id"]; - $ldb->query("UPDATE epube_pagination SET pagination = '$payload', - total_pages = '$total_pages' WHERE id = '$id'"); + $sth = $ldb->prepare("UPDATE epube_pagination SET pagination = ?, + total_pages = ? WHERE id = ?"); + $sth->execute([$payload, $total_pages, $id]); } else { - $ldb->query("INSERT INTO epube_pagination (bookid, pagination, total_pages) VALUES - ('$bookid', '$payload', '$total_pages')"); - + $sth = $ldb->prepare("INSERT INTO epube_pagination (bookid, pagination, total_pages) VALUES + (?, ?, ?)"); + $sth->execute([$bookid, $payload, $total_pages]); } - $ldb->query("COMMIT"); + $ldb->commit(); } break; @@ -173,10 +182,11 @@ if ($bookid) { - $result = $ldb->query("SELECT b.lastread, b.lastcfi, p.total_pages FROM epube_books AS b, epube_pagination AS p - WHERE b.bookid = p.bookid AND b.bookid = '$bookid' AND b.owner = '$owner' LIMIT 1"); + $sth = $ldb->prepare("SELECT b.lastread, b.lastcfi, p.total_pages FROM epube_books AS b, epube_pagination AS p + WHERE b.bookid = p.bookid AND b.bookid = ? AND b.owner = ? LIMIT 1"); + $sth->execute([$bookid, $owner]); - if ($line = $result->fetchArray()) { + if ($line = $sth->fetch()) { $lastread = (int) $line["lastread"]; $lastcfi = $line["lastcfi"]; $totalpages = (int) $line["total_pages"]; @@ -190,16 +200,17 @@ case "storelastread": $page = (int) $_REQUEST["page"]; $bookid = (int) $_REQUEST["id"]; - $cfi = SQLite3::escapeString($_REQUEST["cfi"]); + $cfi = $_REQUEST["cfi"]; if ($page && $bookid) { - $ldb->query("BEGIN"); + $ldb->beginTransaction(); - $result = $ldb->query("SELECT id, lastread, lastcfi FROM epube_books - WHERE bookid = '$bookid' AND owner = '$owner' LIMIT 1"); + $sth = $ldb->prepare("SELECT id, lastread, lastcfi FROM epube_books + WHERE bookid = ? AND owner = ? LIMIT 1"); + $sth->execute([$bookid, $owner]); - if ($line = $result->fetchArray()) { + if ($line = $sth->fetch()) { $id = $line["id"]; $lastread = (int) $line["lastread"]; @@ -207,15 +218,16 @@ if ($page == -1) $page = 0; - $ldb->query("UPDATE epube_books SET lastread = '$page', lastcfi = '$cfi' WHERE id = '$id'"); + $sth = $ldb->prepare("UPDATE epube_books SET lastread = ?, lastcfi = ? WHERE id = ?"); + $sth->execute([$page, $cfi, $id]); } } else { - $ldb->query("INSERT INTO epube_books (bookid, owner, lastread, lastcfi) VALUES - ('$bookid', '$owner', '$page', '$cfi')"); - + $sth = $ldb->prepare("INSERT INTO epube_books (bookid, owner, lastread, lastcfi) VALUES + (?, ?, ?, ?)"); + $sth->execute([$bookid, $owner, $page, $cfi]); } - $ldb->query("COMMIT"); + $ldb->commit(); } print json_encode(["page" => $page, "cfi" => $cfi]); diff --git a/config.php-dist b/config.php-dist index 66bd89c..3404f34 100644 --- a/config.php-dist +++ b/config.php-dist @@ -9,4 +9,3 @@ define('DICT_CLIENT', '/usr/bin/dict'); define('DICT_SERVER', 'localhost'); - define('SQLITE_SESSION_STORE', false); @@ -4,9 +4,9 @@ class Db { private $dbh; private function __construct() { - $this->dbh = new SQLite3(SCRATCH_DB); - $this->dbh->busyTimeout(30*1000); - $this->dbh->exec('PRAGMA journal_mode = wal;'); + $this->dbh = new PDO('sqlite:' . SCRATCH_DB); + //$this->dbh->busyTimeout(30*1000); + $this->dbh->query('PRAGMA journal_mode = wal;'); } public static function get() { @@ -20,7 +20,7 @@ require_once "sessions.php"; require_once "db.php"; - @$owner = SQLite3::escapeString($_SESSION["owner"]); + @$owner = $_SESSION["owner"]; if (!$owner) { header("Location: login.php"); @@ -181,24 +181,17 @@ require_once "config.php"; - $db = new SQLite3(CALIBRE_DB, SQLITE3_OPEN_READONLY); - - if ($query) { - $query_esc = SQLite3::escapeString($query); - $search_qpart = "(LOWER(books.author_sort) LIKE LOWER('%$query_esc%') OR - LOWER(books.title) LIKE LOWER('%$query_esc%') OR - LOWER(series_name) LIKE LOWER('%$query_esc%'))"; - } else { - $search_qpart = "1"; - } + $db = new PDO('sqlite:' . CALIBRE_DB); $ids_qpart = "1"; if ($mode == "favorites") { - $fav_result = $ldb->query("SELECT bookid FROM epube_favorites WHERE owner = '$owner'"); + $fav_sth = $ldb->prepare("SELECT bookid FROM epube_favorites WHERE owner = ?"); + $fav_sth->execute([$owner]); + $fav_ids = []; - while ($line = $fav_result->fetchArray(SQLITE3_ASSOC)) { + while ($line = $fav_sth->fetch()) { array_push($fav_ids, $line["bookid"]); } @@ -210,17 +203,27 @@ $order_by = $query ? "author_sort, series_name, series_index, title, books.id" : "books.id DESC"; - $result = $db->query("SELECT books.*, s.name AS series_name, + $sth = $db->prepare("SELECT books.*, s.name AS series_name, (SELECT id FROM data WHERE book = books.id AND format = 'EPUB' LIMIT 1) AS epub_id FROM books LEFT JOIN books_series_link AS bsl ON (bsl.book = books.id) LEFT JOIN series AS s ON (bsl.series = s.id) - WHERE $search_qpart AND $ids_qpart ORDER BY $order_by LIMIT $limit OFFSET $offset"); + WHERE + ((:query = '') OR + ( + LOWER(books.author_sort) LIKE LOWER(:query) OR + LOWER(books.title) LIKE LOWER(:query) OR + LOWER(series_name) LIKE LOWER(:query) + )) + AND $ids_qpart + ORDER BY $order_by LIMIT :limit OFFSET :offset"); + + $sth->execute([':limit' => $limit, ':offset' => $offset, ':query' => '%' . $query . '%']); print "<div class='row'>"; $rows = 0; - while ($line = $result->fetchArray(SQLITE3_ASSOC)) { + while ($line = $sth->fetch()) { ++$rows; $cover_link = "backend.php?" . http_build_query(["op" => "cover", "id" => $line["id"]]); @@ -232,17 +235,17 @@ if ($line["epub_id"]) { $read_link = "read.html?" . http_build_query(["id" => $line["epub_id"], "b" => $line["id"]]); - $lastread_result = $ldb->query("SELECT lastread, total_pages FROM epube_books, epube_pagination + $lastread_sth = $ldb->prepare("SELECT lastread, total_pages FROM epube_books, epube_pagination WHERE epube_pagination.bookid = epube_books.bookid AND - epube_books.bookid = " . $line["epub_id"] . " AND owner = '$owner'"); + epube_books.bookid = ? AND owner = ?"); + $lastread_sth->execute([$line['epub_id'], $owner]); - if ($lastread_line = $lastread_result->fetchArray(SQLITE3_ASSOC)) { + if ($lastread_line = $lastread_sth->fetch()) { $lastread = $lastread_line["lastread"]; $total_pages = $lastread_line["total_pages"]; $is_read = $total_pages - $lastread < 5; $in_progress = $lastread > 1; - } } else { @@ -289,7 +292,8 @@ print "<div><a title=\"".htmlspecialchars($line["author_sort"])."\" href=\"$author_link\">" . $line["author_sort"] . "</a></div>"; - $data_result = $db->query("SELECT * FROM data WHERE book = " . $line["id"] . " LIMIT 3"); + $data_sth = $db->prepare("SELECT * FROM data WHERE book = ? LIMIT 3"); + $data_sth->execute([$line['id']]); /*print "<span class=\"label label-default\"> <span class=\"glyphicon glyphicon-download-alt\"> @@ -317,12 +321,13 @@ <?php - $fav_result = $ldb->query("SELECT id FROM epube_favorites WHERE bookid = ". - $line['id'] . " AND owner = '$owner' LIMIT 1"); + $fav_sth = $ldb->prepare("SELECT id FROM epube_favorites + WHERE bookid = ? AND owner = ? LIMIT 1"); + $fav_sth->execute([$line['id'], $owner]); $found_id = false; - while ($fav_line = $fav_result->fetchArray(SQLITE3_ASSOC)) { + while ($fav_line = $fav_sth->fetch()) { $found_id = $fav_line["id"]; } @@ -349,7 +354,7 @@ <li class="divider"></li> <?php } ?> - <?php while ($data_line = $data_result->fetchArray(SQLITE3_ASSOC)) { + <?php while ($data_line = $data_sth->fetch()) { if ($data_line["format"] != "ORIGINAL_EPUB") { $label_class = $data_line["format"] == "EPUB" ? "label-success" : "label-primary"; @@ -4,16 +4,17 @@ @$op = $_REQUEST["op"]; if ($op == "perform-login") { - $user = SQLite3::escapeString(trim(mb_strtolower($_REQUEST["user"]))); - $password = SQLite3::escapeString('SHA256:' . hash('sha256', "$user:" . trim($_REQUEST["password"]))); + $user = trim(mb_strtolower($_REQUEST["user"])); + $password = 'SHA256:' . hash('sha256', "$user:" . trim($_REQUEST["password"])); require_once "db.php"; $dbh = Db::get(); - $res = $dbh->query("SELECT id FROM epube_users WHERE user = '$user' AND pass = '$password'"); + $sth = $dbh->prepare("SELECT id FROM epube_users WHERE user = ? AND pass = ?"); + $sth->execute([$user, $password]); - if ($line = $res->fetchArray(SQLITE3_ASSOC)) { + if ($line = $sth->fetch()) { require_once "sessions.php"; $_SESSION["owner"] = $user; diff --git a/sessions.php b/sessions.php index 82a90c2..3735add 100644 --- a/sessions.php +++ b/sessions.php @@ -15,56 +15,7 @@ session_set_cookie_params(SESSION_LIFETIME); - function s_open ($s, $n) { - return true; - } - - function s_read ($id){ - $res = Db::get()->query("SELECT data FROM epube_sessions WHERE id='$id'"); - - if ($line = $res->fetchArray(SQLITE3_ASSOC)) { - return base64_decode($line["data"]); - } else { - $expire = time() + SESSION_LIFETIME; - - Db::get()->query("INSERT INTO epube_sessions (id, data, expire) - VALUES ('$id', '', '$expire')"); - } - - } - - function s_write ($id, $data) { - $data = base64_encode($data); - $expire = time() + SESSION_LIFETIME; - - Db::get()->query("UPDATE epube_sessions SET data = '$data', expire = '$expire' WHERE id = '$id'"); - - return true; - } - - function s_close () { - return true; - } - - function s_destroy($id) { - Db::get()->query("DELETE FROM epube_sessions WHERE id = '$id'"); - - return true; - } - - function s_gc ($expire) { - Db::get()->query("DELETE FROM epube_sessions WHERE expire < " . time()); - - return true; - } - - if (defined('SQLITE_SESSION_STORE') && SQLITE_SESSION_STORE) { - session_set_save_handler("s_open", - "s_close", "s_read", "s_write", - "s_destroy", "s_gc"); - } else { - session_save_path(__DIR__ . "/sessions"); - } + session_save_path(__DIR__ . "/sessions"); register_shutdown_function('session_write_close'); diff --git a/useradm.php b/useradm.php index ee330cb..738ef59 100644 --- a/useradm.php +++ b/useradm.php @@ -21,16 +21,18 @@ } if (isset($options["del"])) { - $user = SQLite3::escapeString($options["del"]); + $user = $options["del"]; print "Deleting user $user...\n"; - $dbh->query("DELETE FROM epube_users WHERE user = '$user'"); + $sth = $dbh->prepare("DELETE FROM epube_users WHERE user = ?"); + + $sth->execute([$user]); } if (isset($options["list"])) { $res = $dbh->query("SELECT id, user FROM epube_users ORDER BY user"); - while ($line = $res->fetchArray(SQLITE3_ASSOC)) { + while ($line = $res->fetch()) { printf("%d. %s\n", $line["id"], $line["user"]); } @@ -44,18 +46,20 @@ exit; } - $user = SQLite3::escapeString(trim(mb_strtolower($user))); - $pass_hash = SQLite3::escapeString('SHA256:' . hash('sha256', "$user:" . trim($pass))); + $user = trim(mb_strtolower($user)); + $pass_hash = 'SHA256:' . hash('sha256', "$user:" . trim($pass)); print "Adding user $user with password $pass...\n"; - $res = $dbh->query("SELECT user FROM epube_users WHERE user = '$user'"); + $sth = $dbh->prepare("SELECT user FROM epube_users WHERE user = ?"); + $sth->execute([$user]); - if ($line = $res->fetchArray(SQLITE3_ASSOC)) { + if ($line = $sth->fetch()) { print "User already exists.\n"; } else { - $dbh->query("INSERT INTO epube_users (user, pass) - VALUES ('$user', '$pass_hash')"); + $sth = $dbh->prepare("INSERT INTO epube_users (user, pass) + VALUES (?, ?)"); + $sth->execute([$user, $pass_hash]); } } |