feat: moved from SQLite to MySQL

4 files changed, 36 insertions, 46 deletions

diff --git a/public/account/delete.php b/public/account/delete.php
index af8a093..ecfcc80 100644
--- a/public/account/delete.php
+++ b/public/account/delete.php
@@ -1,5 +1,6 @@
 <?php
 include "../../src/utils.php";
+include_once "../../src/config.php";
 
 session_start();
 
@@ -10,26 +11,23 @@ if (!isset($_SESSION["user_id"])) {
 
 $id = $_SESSION["user_id"];
 
-$db = new SQLite3("../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
-$stmt = $db->prepare("UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = :id");
-$stmt->bindValue(":id", $id);
-$stmt->execute();
+$stmt = $db->prepare("UPDATE emotes SET uploaded_by = NULL WHERE uploaded_by = ?");
+$stmt->execute([$id]);
 
-$stmt = $db->prepare("DELETE FROM connections WHERE user_id = :id");
-$stmt->bindValue(":id", $id);
-$stmt->execute();
+$stmt = $db->prepare("DELETE FROM connections WHERE user_id = ?");
+$stmt->execute([$id]);
 
-$stmt = $db->prepare("DELETE FROM users WHERE id = :id");
-$stmt->bindValue(":id", $id);
-$stmt->execute();
+$stmt = $db->prepare("DELETE FROM users WHERE id = ?");
+$stmt->execute([$id]);
 
 session_unset();
 session_destroy();
 
 setcookie("secret_key", "", time() - 1000);
 
-$db->close();
+$db = null;
 
 $path = "../static/userdata/avatars/$id";
 if (is_file($path)) {
diff --git a/public/account/login/index.php b/public/account/login/index.php
index 146fde9..e104a88 100644
--- a/public/account/login/index.php
+++ b/public/account/login/index.php
@@ -1,7 +1,6 @@
 <?php
 include "../../../src/accounts.php";
-// FIXME
-//authorize_user();
+authorize_user();
 
 include "../../../src/partials.php";
 ?>
diff --git a/public/account/login/twitch.php b/public/account/login/twitch.php
index ff2fe51..7866eb5 100644
--- a/public/account/login/twitch.php
+++ b/public/account/login/twitch.php
@@ -1,8 +1,9 @@
 <?php
 include "../../../src/utils.php";
+include_once "../../../src/config.php";
 
-$client_id = "";
-$client_secret = "";
+$client_id = "472prq7kqn0a21l5um2lz7374471pp";
+$client_secret = "koho369mw8p51di4fx34jm2ogdmbj2";
 $redirect_uri = "http://localhost:8000/account/login/twitch.php";
 
 if (isset($_GET["error"])) {
@@ -66,32 +67,29 @@ $_SESSION["twitch_access_token"] = $response["access_token"];
 $_SESSION["twitch_refresh_token"] = $response["refresh_token"];
 $_SESSION["twitch_expires_on"] = time() + intval($response["expires_in"]);
 
-$db = new SQLite3("../../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
 // creating user if not exists
-$stmt = $db->prepare("SELECT id, user_id FROM connections WHERE alias_id = :alias_id AND platform = 'twitch'");
-$stmt->bindValue("alias_id", $twitch_user["id"]);
-
-$results = $stmt->execute();
+$stmt = $db->prepare("SELECT id, user_id FROM connections WHERE alias_id = ? AND platform = 'twitch'");
+$stmt->execute([$twitch_user["id"]]);
 
 $user_id = "";
 $user_secret_key = "";
 $user_name = "";
 
-if ($row = $results->fetchArray()) {
+if ($row = $stmt->fetch()) {
     $id = $row["id"];
     $user_id = $row["user_id"];
 
-    $stmt = $db->prepare("SELECT * FROM users WHERE id = :id");
-    $stmt->bindValue(":id", $id);
-    $results = $stmt->execute();
+    $stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
+    $stmt->execute([$user_id]);
 
-    if ($row = $results->fetchArray()) {
+    if ($row = $stmt->fetch()) {
         $user_name = $row["username"];
         $user_secret_key = $row["secret_key"];
         $user_id = $row["id"];
     } else {
-        $db->close();
+        $db = null;
         echo "Connection found, but not user?";
         exit;
     }
@@ -99,32 +97,28 @@ if ($row = $results->fetchArray()) {
     $user_secret_key = generate_random_string(32);
     $user_name = $twitch_user["login"];
 
-    $stmt = $db->prepare("INSERT INTO users(username, secret_key) VALUES (:username, :secret_key)");
-    $stmt->bindValue(":username", $user_name);
-    $stmt->bindValue(":secret_key", $user_secret_key);
-    if (!$stmt->execute()) {
-        $db->close();
+    $stmt = $db->prepare("INSERT INTO users(username, secret_key) VALUES (?, ?)");
+    if (!$stmt->execute([$user_name, $user_secret_key])) {
+        $db = null;
         echo "Failed to create a user";
         exit;
     }
 
-    $user_id = $db->lastInsertRowID();
+    $user_id = $db->lastInsertId();
 
-    $stmt = $db->prepare("INSERT INTO connections(user_id, alias_id, platform, data) VALUES (:user_id, :alias_id, 'twitch', :data)");
-    $stmt->bindValue(":user_id", $user_id);
-    $stmt->bindValue(":alias_id", $twitch_user["id"]);
-    $stmt->bindValue(
-        ":data",
+    $stmt = $db->prepare("INSERT INTO connections(user_id, alias_id, platform, data) VALUES (?, ?, 'twitch', ?)");
+    $stmt->execute([
+        $user_id,
+        $twitch_user["id"],
         $_SESSION["twitch_access_token"] . ":" . $_SESSION["twitch_refresh_token"] . ":" . $_SESSION["twitch_expires_on"]
-    );
-    $stmt->execute();
+    ]);
 }
 
 $_SESSION["user_id"] = $user_id;
 $_SESSION["user_name"] = $user_name;
 setcookie("secret_key", $user_secret_key, time() + 86400 * 30, "/");
 
-$db->close();
+$db = null;
 
 // downloading profile picture
 $path = "../../static/userdata/avatars";
diff --git a/public/account/signout.php b/public/account/signout.php
index dd1d0f9..66a0cac 100644
--- a/public/account/signout.php
+++ b/public/account/signout.php
@@ -1,5 +1,6 @@
 <?php
 include "../../src/utils.php";
+include_once "../../src/config.php";
 
 session_start();
 
@@ -8,18 +9,16 @@ if (!isset($_SESSION["user_id"])) {
     exit;
 }
 
-$db = new SQLite3("../../database.db");
+$db = new PDO(DB_URL, DB_USER, DB_PASS);
 
-$stmt = $db->prepare("UPDATE users SET secret_key = :secret_key WHERE id = :id");
-$stmt->bindValue(":id", $_SESSION["user_id"]);
-$stmt->bindValue(":secret_key", generate_random_string(32));
-$stmt->execute();
+$stmt = $db->prepare("UPDATE users SET secret_key = ? WHERE id = ?");
+$stmt->execute([generate_random_string(32), $_SESSION["user_id"]]);
 
 session_unset();
 session_destroy();
 
 setcookie("secret_key", "", time() - 1000);
 
-$db->close();
+$db = null;
 
 header("Location: /account");
\ No newline at end of file