archinsa/bdd.php

500 lines
14 KiB
PHP
Raw Permalink Normal View History

2023-10-22 19:24:59 +02:00
<?php
2023-11-03 22:22:27 +01:00
include("test_creds.php");
include_once("utils/token.php");
$conn = new mysqli($servername, $db_username, $db_password,$dbname);
2023-11-03 22:22:27 +01:00
2024-01-16 18:07:44 +01:00
$uploadDir = 'archives/';
2023-11-03 22:22:27 +01:00
// le type de document est classifié entre 0 et n dans l'ensemble des entiers naturels
$max_val_type = 3;
/*
1 : texte
2 : image
3 : pdf
0 : non supporté
-1 : erreur
*/
// Liste des extensions autorisées pour les images
$image_extensions = [
'jpg',
'jpeg',
'png',
'gif',
'bmp',
'tiff',
'tif',
'webp',
'svg',
'ico',
'raw'];
// Liste des extensions autorisées pour les fichiers PDF
$pdf_extensions = ['pdf'];
// Liste des extensions autorisées pour les fichiers de présentation (par exemple, PowerPoint)
$presentation_extensions = ['ppt', 'pptx','odp','pptm','ppsx'];
// pour les fonctions speciales comme les quiz html...
// ATTENTION ! NE PAS INCLURE PHP GRAND FOU QUE VOUS ETES
2024-09-26 21:24:09 +02:00
$ext_speciales = ["html","sh","txt","adb","ads","py","ipynb","c","cpp","rs","go","asm","js","java","md"];
// Fusionner les listes en une seule liste
$ext_autorisees = array_merge($image_extensions, $pdf_extensions, $presentation_extensions,$ext_speciales);
function check_ext($filename) {
2024-09-09 17:12:47 +02:00
global $ext_autorisees;
$extension = pathinfo($filename, PATHINFO_EXTENSION);
2024-09-09 17:12:47 +02:00
return in_array(strtolower($extension), $ext_autorisees);
}
function ajouter_doc($request){
2023-10-22 19:24:59 +02:00
2023-11-03 22:22:27 +01:00
global $conn;
2023-10-22 19:24:59 +02:00
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
2023-10-22 19:24:59 +02:00
$sql = "INSERT INTO ensembles (commentaire_auteur,corrige_inclu,date_conception,id_auteur) VALUES(?,?,?,?)";
try{
2024-01-16 19:02:42 +01:00
$stm = $conn->prepare($sql);
2024-09-09 17:12:47 +02:00
$request['commentaire_auteur'] = htmlentities($request["commentaire_auteur"]);
2024-01-16 19:02:42 +01:00
$request["corrige_inclu"] = boolval($request["corrige_inclu"]);
2024-09-09 17:12:47 +02:00
$request["date_conception"] = htmlentities($request["date_conception"]);
$stm->bind_param("sisi",$request['commentaire_auteur'],$request["corrige_inclu"],$request["date_conception"],$_SESSION["user_id"]);
2024-01-16 19:02:42 +01:00
$stm->execute();
//$conn->execute_query($sql,array(htmlspecialchars($request['commentaire_auteur']),boolval($request["corrige_inclu"])));
2023-11-03 22:22:27 +01:00
saveFilesFromPost($request,mysqli_insert_id($conn));
}catch(Exception $e){
2023-11-03 22:22:27 +01:00
echo(json_encode(["status"=>"0","msg"=>$e->getMessage()]));
2023-10-22 19:24:59 +02:00
}
}
2023-11-03 22:22:27 +01:00
function saveFilesFromPost($postData,$id_ensemble) {
global $conn;
2023-11-03 22:22:27 +01:00
2023-10-22 19:24:59 +02:00
// Check if the $_POST variable is set and contains files
2023-11-10 20:03:46 +01:00
//echo(print_r($_FILES,true));
2023-11-05 16:49:48 +01:00
2023-11-03 22:22:27 +01:00
if (isset($_FILES) && is_array($_FILES)) {
// Iterate through each file in the $_FILES array
2023-11-05 16:49:48 +01:00
$i = 0;
2024-04-06 11:37:53 +02:00
//var_dump($_FILES);
2024-01-18 15:38:55 +01:00
foreach ($_FILES as $file) {
$safe_type = checkFileTypeSecure($file['tmp_name']);
2024-09-09 17:12:47 +02:00
2023-10-22 19:24:59 +02:00
// Extract file information
if (isset($file['name'])){
2023-11-05 16:49:48 +01:00
$fileName = htmlspecialchars($file['name']);
2024-09-26 21:12:06 +02:00
// Create a unique filename to avoid overwriting existing files
$uniqueFileName = uniqid() . '_' . $fileName;
2024-09-09 20:40:24 +02:00
// le dernier check est pour autoriser l'upload de fichiers html aux admins
2024-09-09 20:40:59 +02:00
if(!check_ext($fileName) || $safe_type == 0 || ($safe_type == 5 && !$_SESSION["admin"])){
echo(json_encode(["status"=>"0","msg"=>"le fichier '$fileName' n'a pas passé les filtres de contenu. ( dommaaaaggee :c )"]));
exit;
}
}else{
echo("WTFFF");
print_r($file);
}
2023-10-22 19:24:59 +02:00
2024-09-09 17:12:47 +02:00
2023-10-22 19:24:59 +02:00
// Define the path to save the file
2023-11-03 22:22:27 +01:00
$filePath = $GLOBALS['uploadDir'] . $uniqueFileName;
2023-10-22 19:24:59 +02:00
//echo($filePath."\n");
2024-01-18 15:38:55 +01:00
2023-10-22 19:24:59 +02:00
// Save the file
2024-01-18 15:38:55 +01:00
$f = fopen($file['tmp_name'],"r");
2024-04-06 11:37:53 +02:00
//echo fread($f,filesize($file['tmp_name']));
2024-01-18 15:38:55 +01:00
fclose($f);
if (!move_uploaded_file($file['tmp_name'], $filePath)) {
2024-09-26 21:14:52 +02:00
echo(json_encode(["status"=>"0","msg"=>"Error saving file to '$filePath'"]));
exit;
2023-10-22 19:24:59 +02:00
}
try{
//update the database
$safe_titre = htmlspecialchars($postData['titre']);
2023-11-03 22:22:27 +01:00
global $max_val_type;
if ($safe_type < 1 || $safe_type > $max_val_type) {
2023-11-03 22:22:27 +01:00
echo(json_encode(['status'=> '2','msg'=>"Le type de document spécifié n'existe pas."]));
// supprime donc le fichier
unlink($filePath);
2023-11-03 22:22:27 +01:00
exit;
}
// pour tester, pas implémenté les commentaires globaux ni les themes
$sql="INSERT INTO documents (titre,type,upload_path,commentaire_auteur,ensemble_id) VALUES(?,?,?,?,?)";
$conn->execute_query($sql,array($safe_titre,$safe_type,"archives/".$uniqueFileName,$postData['commentaire_doc_'.$i],$id_ensemble));
}catch(Exception $e){
2023-11-03 22:22:27 +01:00
echo(json_encode(['status'=> '0','msg'=>$e->getMessage()]));
//exit;
}
2023-11-05 16:49:48 +01:00
$i ++;
}
// enregistrement des exercices dans le cas d'une annale
if($safe_type == 1){
2023-11-05 17:03:58 +01:00
$exercices = json_decode($postData['exercices'],true);
2023-12-23 21:42:28 +01:00
$document_id = mysqli_insert_id($conn);
2023-11-05 16:49:48 +01:00
foreach ($exercices as $key => $ex) {
// premièrement, on enregistre l'exercice
2023-12-23 21:42:28 +01:00
$sql= 'INSERT INTO exercices (commentaire_auteur,ensemble_id,document_id,duree) VALUES(?,?,?,?)';
$conn->execute_query($sql,array($ex["commentaire_exo"],$id_ensemble,$document_id,intval($ex["duree"])));
2023-11-05 16:49:48 +01:00
$id_exo = mysqli_insert_id($conn);
// on recherche pour chaque thème s'il n'existe pas déjà,
// si non, on en créer un nouveau
foreach($ex["themes"] as $theme){
// pour l'instant un match complet mais on va essayer d'ameliorer ça avec
// des regex
$sql= "SELECT id FROM themes WHERE name=\"".htmlspecialchars($theme)."\"";
$result = $conn->execute_query($sql);
if ($result){
if (mysqli_num_rows($result) > 0) {
$row = mysqli_fetch_assoc($result);
$id_theme = $row["id"];
}else{
//echo("creation d'un theme");
2023-11-05 16:49:48 +01:00
$sql = "INSERT INTO themes (name) VALUES(?)";
$conn->execute_query($sql,array($theme));
$id_theme = mysqli_insert_id($conn);
}
// ensuite, on enregistre les qui lui sont associés
$sql= 'INSERT INTO exercices_themes (exercice_id,ensemble_id,theme_id) VALUES(?,?,?)';
$result = $conn->execute_query($sql,array($id_exo,$id_ensemble,$id_theme));
//echo("enregistrement d'un exercice");
2023-11-05 16:49:48 +01:00
}
}
}
2023-10-22 19:24:59 +02:00
}
echo(json_encode(["status"=>"1","msg" =>"Files has/have been saved successfully."]));
2023-11-05 16:49:48 +01:00
2023-10-22 19:24:59 +02:00
} else {
echo(json_encode(["status"=>"2","msg"=>"No files in the POST data."]));
exit;
}
}
2024-08-10 16:36:26 +02:00
function RechercheExercices($query, $length, $tags, $tout_les_insa)
{
2023-11-10 20:03:46 +01:00
global $conn;
2024-08-10 16:36:26 +02:00
// Start with the base SQL query
$sql = "SELECT * FROM documents AS d INNER JOIN ensembles AS e ON d.ensemble_id = e.id JOIN users as u ON u.id=e.id_auteur WHERE e.valide=TRUE";
2024-08-10 16:36:26 +02:00
// Array to hold the parameters
$params = [];
$types = ""; // Types for the bind_param function
2024-08-10 16:36:26 +02:00
// Handle the INSA restriction
if (!$tout_les_insa) {
$sql .= " AND u.nom_insa = ?";
$params[] = $_SESSION["nom_insa"];
$types .= "s"; // Assuming nom_insa is a string
}
2024-08-10 16:36:26 +02:00
// Handle the search query
if (!empty($query)) {
2024-08-10 16:36:26 +02:00
$query_words = preg_split("/\s+/", htmlspecialchars($query));
2024-01-16 20:43:18 +01:00
foreach ($query_words as $word) {
2024-08-10 16:36:26 +02:00
$sql .= " AND titre LIKE ?";
$params[] = "%$word%";
$types .= "s";
2024-01-16 20:43:18 +01:00
}
}
2024-08-10 16:36:26 +02:00
// Handle the length filter
if (!empty($length)) {
2024-08-10 16:36:26 +02:00
$sql .= " AND duree = ?";
$params[] = $length;
$types .= "i"; // Assuming duree is an integer
}
2024-08-10 16:36:26 +02:00
// Handle the tags filter
if (!empty($tags)) {
2024-08-10 16:36:26 +02:00
foreach ($tags as $tag) {
2023-11-10 20:03:46 +01:00
$tag = htmlspecialchars($tag);
2024-08-10 16:36:26 +02:00
$sql .= " AND EXISTS (SELECT * FROM exercices_themes AS et INNER JOIN themes AS t ON et.exercice_id = t.id WHERE et.theme_id = t.id AND t.name = ?)";
$params[] = $tag;
$types .= "s";
}
}
2024-08-10 16:36:26 +02:00
// Prepare the SQL statement
$stmt = $conn->prepare($sql);
2024-08-10 16:36:26 +02:00
if ($stmt === false) {
throw new Exception("Error preparing the query: " . $conn->error);
}
2024-08-10 16:36:26 +02:00
// Bind the parameters dynamically
if (!empty($params)) {
$stmt->bind_param($types, ...$params);
}
2024-08-10 16:36:26 +02:00
// Execute the query
if (!$stmt->execute()) {
throw new Exception("Error executing the search query: " . $stmt->error);
}
2024-08-10 16:36:26 +02:00
// Fetch the results
$result = $stmt->get_result();
$exercises = [];
while ($row = $result->fetch_assoc()) {
$exercises[] = $row;
}
2024-08-10 16:36:26 +02:00
// Clean up
$stmt->close();
$conn->close();
return $exercises;
}
2024-08-10 16:36:26 +02:00
function valider_ensemble($ensembleId) {
$sql = "UPDATE ensembles SET valide = 1 WHERE id = $ensembleId";
global $conn;
$conn->execute_query($sql);
}
2024-01-16 20:43:18 +01:00
function supprimer_ensemble($ensemble_id){
global $conn;
// premièrement, enlever tout les documents téléversés appartenant à l'ensemble
$sql = "SELECT upload_path FROM documents WHERE ensemble_id=?";
$res = $conn->execute_query($sql,array($ensemble_id));
while($tmp=$res->fetch_assoc()){
unlink($tmp["upload_path"]);
}
// deuxièmement, supprimer toutes les traces de l'ensemble dans la bdd
$sql = "DELETE FROM exercices_themes WHERE ensemble_id=$ensemble_id";
$conn->execute_query($sql);
$sql = "DELETE FROM exercices WHERE ensemble_id=$ensemble_id";
$conn->execute_query($sql);
$sql = "DELETE FROM documents WHERE ensemble_id=$ensemble_id";
$conn->execute_query($sql);
$sql = "DELETE FROM ensembles WHERE id=$ensemble_id";
$conn->execute_query($sql);
2023-10-22 19:24:59 +02:00
}
function generer_chronologie(){
global $conn;
// on va choper les 10 derniers trucs televerses par les gens
2024-09-13 14:13:42 +02:00
$sql = "SELECT * FROM ensembles WHERE valide=1 ORDER BY date_televersement DESC LIMIT 10";
$res = $conn->execute_query($sql);
$ensembles = array();
2024-09-13 14:13:42 +02:00
while (($ens = $res->fetch_assoc())){
array_push($ensembles,$ens);
$i++;
}
// on rajoute le chemin vers chaque document présent dans l'ensemble
$resultat_complet = array();
foreach($ensembles as $ens){
$sql = "SELECT titre,upload_path,ensemble_id,type FROM documents WHERE ensemble_id=?";
$res = $conn->execute_query($sql,array($ens["id"]));
$ens["documents"] = array();
while($doc = $res->fetch_assoc()){
array_push($ens["documents"],$doc);
}
array_push($resultat_complet,$ens);
}
return $resultat_complet;
}
function connecter_utilisateur($username,$password){
global $conn;
2024-09-09 17:40:18 +02:00
$ret = false;
$stmt = $conn->prepare("SELECT id,password_hash,admin,nom_insa FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
$stmt->bind_result($id,$password_hash,$admin,$nom_insa);
$ret = $stmt->fetch();
if (password_verify($password, $password_hash)) {
$_SESSION["utilisateur_authentifie"] = true;
$_SESSION["username"] = $username;
$_SESSION["admin"] = $admin;
$_SESSION["nom_insa"] = $nom_insa;
$_SESSION["user_id"] = $id;
2024-09-09 17:40:18 +02:00
$ret = true;
}
}
$stmt->close();
if($ret){
2024-09-09 17:40:18 +02:00
$ret=utilisateur_est_verifie($id);
}
return $ret;
}
function inscription_utilisateur($username,$password_hash,$nom_insa){
global $conn;
2024-08-10 16:36:26 +02:00
if(!in_array($nom_insa,["insa_toulouse","insa_lyon","insa_rennes","insa_cvl","insa_hdf","insa_rouen","insa_strasbourg","insa_hdf"])){
$ret = 0;
return $ret;
}
2024-09-09 17:20:33 +02:00
$stmt = $conn->prepare("INSERT INTO users (username, password_hash,nom_insa) VALUES (?, ?,?)");
$stmt->bind_param("sss", $username, $password_hash,$nom_insa);
$ret = $stmt->execute();
2024-07-22 17:55:09 +02:00
$stmt->close();
$tok = new Token();
$user_id = mysqli_insert_id($conn);
$tok->Add($user_id);
/*
2024-07-22 17:55:09 +02:00
if($ret){
// met le statut de l'utilisateur à connecté pour lui eviter de se connecter just après l'inscription
$_SESSION["utilisateur_authentifie"] = true;
$_SESSION["username"] = $username;
$_SESSION["admin"] = 0;
$_SESSION["nom_insa"] = $nom_insa;
$_SESSION["user_id"] = $conn->insert_id;
}*/
if($ret){
return $tok->getToken($user_id);
}else{
return "[ERREUR]";
2024-07-22 17:55:09 +02:00
}
}
function verifier_utilisateur($token){
global $conn;
2024-09-09 17:40:18 +02:00
$ret = false;
$t_instance = new Token();
$user_id = $t_instance->getUserID($token);
if($t_instance->isValid($user_id, $token) && $user_id != -1) {
$t_instance->delete($user_id, $token);
$stmt = $conn->prepare("UPDATE users SET verifie=? WHERE id = ?");
$val=1;
$stmt->bind_param("ss",$val,$user_id);
$ret = $stmt->execute();
$stmt->close();
}
return $ret;
}
function utilisateur_est_verifie($user_id){
global $conn;
$stmt = $conn->prepare("SELECT verifie FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$stmt->store_result();
$ret = $stmt->num_rows > 0;
$verif = 0;
if($ret){
$stmt->bind_result($verif);
$ret = $stmt->fetch();
$stmt->close();
}
return $ret && ($verif == 1);
}
?>