From cb727d392892ba2b6199a918f320dc6423d74d4c Mon Sep 17 00:00:00 2001 From: Alex Auvolat Date: Wed, 18 Jan 2017 19:12:01 +0100 Subject: Start update to PDO instead of old mysql_ functions --- .gitignore | 13 +++++++++++++ design/style.css | 13 +++++++++---- lib/account/list.php | 2 +- lib/conf/file.php | 2 +- lib/login.php | 14 +++++++------- lib/notes/delete.php | 2 +- lib/notes/edit.php | 11 ++++++----- lib/notes/move.php | 8 ++++---- lib/notes/new.php | 11 ++++++----- lib/notes/source.php | 6 +++--- lib/notes/user.php | 10 +++++----- lib/notes/view.php | 6 +++--- lib/sql.php | 35 ++++++++++++++++++++++------------- lib/static/home.md | 12 +++++++----- tpl/account/login.php | 2 +- tpl/blog/view.php | 8 ++++---- tpl/general/top.php | 10 +++++----- tpl/notes/inc_relativestree.php | 6 +++--- tpl/notes/view.php | 2 +- 19 files changed, 102 insertions(+), 71 deletions(-) diff --git a/.gitignore b/.gitignore index dc0e92a..bad4d63 100644 --- a/.gitignore +++ b/.gitignore @@ -1,4 +1,17 @@ +* + +!lib/* +!tpl/* +!js/* +!design/* + +!index.php +!.gitignore +!README +!schema.sql + lib/conf/sql.php +*~ .htaccess *.swp nohup.out diff --git a/design/style.css b/design/style.css index 94008f2..7799183 100644 --- a/design/style.css +++ b/design/style.css @@ -115,6 +115,7 @@ body { input, button { border:1px solid black; background: white; + margin: 2px; } } @@ -183,15 +184,19 @@ iframe, textarea { } .small_right { - clear: right; - float: right; + clear: right; + float: right; } -.small_right, .tool_link { +.small_align_right { + text-align: right; +} + +.small_right, .small_align_right, .tool_link { font-size: 0.8em; } -.small_right a, .tool_link { +.small_right a, .small_align_right a, .tool_link { font-style: italic; } diff --git a/lib/account/list.php b/lib/account/list.php index 56392c0..c45a8ca 100644 --- a/lib/account/list.php +++ b/lib/account/list.php @@ -27,5 +27,5 @@ $n = sql("SELECT account.id AS id, login AS name, nc.count AS nbNotes, pc.count "GROUP BY blog_posts.owner) pc ON pc.owner = account.id ". get_filter("cat") . " ORDER BY " . get_filter("order") . " " . get_filter("way") . " "); -while ($nn = mysql_fetch_assoc($n)) $users[] = $nn; +while ($nn = $n->fetch()) $users[] = $nn; require("tpl/account/list.php"); diff --git a/lib/conf/file.php b/lib/conf/file.php index 2e8cd18..945bf3e 100644 --- a/lib/conf/file.php +++ b/lib/conf/file.php @@ -1,6 +1,6 @@ "Anonymous", 1 => "Member", 2 => "Administrator"); $user = array('id' => 0, 'name' => 'Anonymous', 'priv' => 0); @@ -15,9 +15,9 @@ if (isset($_GET['logout'])) { } if (isset($_POST['login']) && isset($_POST['pw'])) { - $sql = sql("SELECT id FROM account ". - "WHERE login = '" . esc($_POST['login']) . "' AND password = PASSWORD('" . esc($_POST['pw']) . "')"); - if ($util = mysql_fetch_assoc($sql)) { + $sql = sql("SELECT id FROM account WHERE login = ? AND password = PASSWORD(?)", + esc($_POST['login']), esc($_POST['pw'])); + if ($util = $sql->fetch()) { $_SESSION['user_id'] = intval($util['id']); } else { $error = "Wrong username or password."; @@ -31,9 +31,9 @@ if (isset($_SESSION['user_id'])) { $user = $_SESSION['user']; } else { $sql = sql("SELECT login AS name, id, priv ". - "FROM account ". - "WHERE id = " . $_SESSION['user_id']); - if ($util = mysql_fetch_assoc($sql)) { + "FROM account WHERE id = ?", + $_SESSION['user_id']); + if ($util = $sql->fetch()) { $user['id'] = $_SESSION['user_id']; $user['name'] = $util['name']; $user['priv'] = $util['priv']; diff --git a/lib/notes/delete.php b/lib/notes/delete.php index 43dbf44..179f788 100644 --- a/lib/notes/delete.php +++ b/lib/notes/delete.php @@ -3,7 +3,7 @@ assert_redir(count($args) >= 3, 'notes'); $noteid = intval($args[2]); -$note = mysql_fetch_assoc(sql("SELECT owner FROM notes WHERE id = $noteid")); +$note = sql("SELECT owner FROM notes WHERE id = $noteid")->fetch(); assert_error($note && ($note['owner'] == $user['id'] || $user['priv'] >= $priv_admin), "This note does not exist, or you are not allowed to delete it."); diff --git a/lib/notes/edit.php b/lib/notes/edit.php index ec479b0..cecbb2d 100644 --- a/lib/notes/edit.php +++ b/lib/notes/edit.php @@ -5,12 +5,12 @@ require("lib/markdown.php"); assert_redir(count($args) == 3, 'notes'); $noteid = intval($args[2]); -$note = mysql_fetch_assoc(sql( +$note = sql( "SELECT na.id AS id, na.title AS title, na.text AS text, na.public AS public, na.owner AS owner, ". "nb.title AS parent_title, nb.id AS parent_id, account.login AS ownername FROM notes na ". "LEFT JOIN notes nb ON na.parent = nb.id LEFT JOIN account ON account.id = na.owner ". "WHERE na.id = $noteid" -)); +)->fetch(); assert_error($note && ($note['owner'] == $user['id'] || $user['priv'] >= $priv_admin), "This note does not exist, or you are not allowed to edit it."); @@ -29,9 +29,10 @@ if (isset($_POST['title']) && isset($_POST['text'])) { $preview = $note_html; $message = "Your preview is below the edit form."; } else { - sql("UPDATE notes SET title = '" . escs($note_title) . "', text = '" . escs($note_text) . - "', text_html = '" . escs($note_html) . "', public = " . ($note_public?'1':'0') . - " WHERE id = $noteid"); + sql("UPDATE notes SET title = ?, text = ?, text_html = ?, ". + " public = " . ($note_public?'1':'0') . + " WHERE id = $noteid", + escs($note_title), escs($note_text), escs($note_html)); header("Location: view-notes-" . $noteid); die(); } diff --git a/lib/notes/move.php b/lib/notes/move.php index d51b4ad..2f6375a 100644 --- a/lib/notes/move.php +++ b/lib/notes/move.php @@ -3,12 +3,12 @@ assert_redir(count($args) >= 3, 'notes'); $noteid = intval($args[2]); -$note = mysql_fetch_assoc(sql( +$note = sql( "SELECT na.id AS id, na.title AS title, na.text AS text, na.public AS public, na.owner AS owner, ". "nb.title AS parent_title, nb.id AS parent_id, account.login AS ownername FROM notes na ". "LEFT JOIN notes nb ON na.parent = nb.id LEFT JOIN account ON account.id = na.owner ". "WHERE na.id = $noteid" -)); +)->fetch(); assert_error($note && ($note['owner'] == $user['id'] || $user['priv'] >= $priv_admin), "This note does not exist, or you are not allowed to move it."); @@ -16,7 +16,7 @@ if (count($args) == 4) { $newparent = intval($args[3]); // SHOULD CHECK FOR TREE CONSISTENCY, SKIP FOR NOW. if ($newparent != 0) { - $p = mysql_fetch_assoc(sql("SELECT id, owner FROM notes WHERE id = $newparent")); + $p = sql("SELECT id, owner FROM notes WHERE id = $newparent")->fetch(); } if ($newparent != 0 && !$p) { $error = "Selected parent does not exist."; @@ -32,7 +32,7 @@ if (count($args) == 4) { $notes_tree = array(); $n = sql("SELECT id, parent, title FROM notes ". "WHERE owner = " . $user['id'] . " AND id != $noteid AND parent != $noteid ORDER BY title ASC"); -while ($nn = mysql_fetch_assoc($n)) { +while ($nn = $n->fetch()) { if (isset($notes_tree[$nn['parent']])) { $notes_tree[$nn['parent']][] = $nn; } else { diff --git a/lib/notes/new.php b/lib/notes/new.php index 1213b94..adad015 100644 --- a/lib/notes/new.php +++ b/lib/notes/new.php @@ -6,12 +6,12 @@ assert_redir(count($args) == 3, 'notes'); $parentid = intval($args[2]); if ($parentid != 0) { - $parent = mysql_fetch_assoc(sql( + $parent = sql( "SELECT na.id AS id, na.title AS title, na.text_html AS html, na.public AS public, na.owner AS owner, ". "nb.title AS parent_title, nb.id AS parent_id, account.login AS ownername FROM notes na ". "LEFT JOIN notes nb ON na.parent = nb.id LEFT JOIN account ON account.id = na.owner ". "WHERE na.id = $parentid" - )); + )->fetch(); assert_error($parent && $parent['owner'] == $user['id'], "The selected parent does not exist, or you cannot create children for it."); } @@ -28,9 +28,10 @@ if (isset($_POST['title']) && isset($_POST['text'])) { $error = "You must enter a title for your note"; } else { sql("INSERT INTO notes(owner, parent, title, text, text_html, public) ". - "VALUES(" . $user['id'] . ", $parentid, '" . escs($note_title) . "', '" . - escs($note_text) . "', '" . escs($note_html) . "', ". ($note_public?'1':'0') . ")"); - header("Location: view-notes-" . mysql_insert_id()); + "VALUES(?, ?, ?, ?, ?, ". ($note_public?'1':'0') . ")", + $user['id'], $parentid, escs($note_title), + escs($note_text), escs($note_html)); + header("Location: view-notes-" . $sql_conn->lastInsertId()); die(); } } diff --git a/lib/notes/source.php b/lib/notes/source.php index d032d33..091ab8c 100644 --- a/lib/notes/source.php +++ b/lib/notes/source.php @@ -3,7 +3,7 @@ assert_redir(count($args) == 3, 'notes'); $noteid = intval($args[2]); -$note = mysql_fetch_assoc(sql("SELECT id, title, text, public, owner FROM notes WHERE id = $noteid")); +$note = sql("SELECT id, title, text, public, owner FROM notes WHERE id = $noteid")->fetch(); assert_error($note && ($note['public'] != 0 || $note['owner'] == $user['id']), "This note does not exist, or you are not allowed to see it."); @@ -15,8 +15,8 @@ assert_error($note && ($note['public'] != 0 || $note['owner'] == $user['id']), -
+
-fetch(); assert_error($note_owner, "That user id does not exist.", "no such user"); } @@ -15,14 +15,14 @@ $n = sql("SELECT account.id AS id, login AS name, COUNT(notes.id) AS nbNotes FRO "LEFT JOIN notes ON notes.owner = account.id ". "WHERE notes.public != 0 AND notes.id != 0 ". "GROUP BY account.id ORDER BY nbNotes DESC"); -while ($nn = mysql_fetch_assoc($n)) $users[] = $nn; +while ($nn =$n->fetch()) $users[] = $nn; $notes_tree = array(); $n = sql("SELECT id, parent, title FROM notes ". - "WHERE owner = $userid ". + "WHERE owner = ? ". ($userid == $user['id'] ? "" : "AND public != 0 "). - "ORDER BY title ASC"); -while ($nn = mysql_fetch_assoc($n)) { + "ORDER BY title ASC", $userid); +while ($nn = $n->fetch()) { if (isset($notes_tree[$nn['parent']])) { $notes_tree[$nn['parent']][] = $nn; } else { diff --git a/lib/notes/view.php b/lib/notes/view.php index a6a014c..d29732d 100644 --- a/lib/notes/view.php +++ b/lib/notes/view.php @@ -3,12 +3,12 @@ assert_redir(count($args) == 3, 'notes'); $noteid = intval($args[2]); -$note = mysql_fetch_assoc(sql( +$note = sql( "SELECT na.id AS id, na.title AS title, na.text_html AS html, na.public AS public, na.owner AS owner, ". "nb.title AS parent_title, nb.id AS parent_id, account.login AS ownername FROM notes na ". "LEFT JOIN notes nb ON na.parent = nb.id LEFT JOIN account ON account.id = na.owner ". - "WHERE na.id = $noteid" -)); + "WHERE na.id = ?", $noteid +)->fetch(); assert_error($note && ($note['public'] != 0 || $note['owner'] == $user['id'] || $user['priv'] >= $priv_admin), "This note does not exist, or you are not allowed to see it."); diff --git a/lib/sql.php b/lib/sql.php index 9f65568..839d469 100644 --- a/lib/sql.php +++ b/lib/sql.php @@ -4,30 +4,40 @@ require("conf/sql.php"); $sql_queries = 0; $sql_connected = false; +$sql_conn = null; function sql_connect() { - global $sql_server, $sql_user, $sql_password, $sql_database, $sql_connected; + global $sql_server, $sql_user, $sql_password, $sql_database, $sql_connected, $sql_conn; if ($sql_connected == true) return; - if (!@mysql_connect($sql_server, $sql_user, $sql_password)) { + + try { + $sql_conn = new PDO("mysql:host=$sql_server;dbname=$sql_database;charset=utf8", + $sql_user, $sql_password, [ + PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, + PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, + PDO::ATTR_EMULATE_PREPARES => false, + ]); + } catch(PDOException $e) { $title = "Cannot connect to SQL server"; - $error = "An error has occurred with the SQL server !"; + $error = "An error has occurred with the SQL server ! " . $e->getMessage(); require("tpl/general/empty.php"); } - mysql_select_db($sql_database); - mysql_query("SET NAMES 'utf8'"); $sql_connected = true; } -function sql($r) { - global $sql_queries, $sql_connected; +function sql($r, ...$args) { + global $sql_queries, $sql_connected, $sql_conn; if ($sql_connected != true) sql_connect(); $sql_queries++; - if ($a = mysql_query($r)) { - return $a; - } else { + + $stmt = $sql_conn->prepare($r); + try { + $stmt->execute($args); + return $stmt; + } catch(PDOException $e) { $title = "SQL error."; $request = $r; - $sql_error = mysql_error(); + $sql_error = $e->getMessage(); require("tpl/general/sqlerror.php"); } } @@ -40,8 +50,7 @@ function esca($v) { } } function escs($v) { - sql_connect(); - return mysql_escape_string($v); + return $v; } function esc($v) { return escs(esca($v)); diff --git a/lib/static/home.md b/lib/static/home.md index e291e72..db11071 100644 --- a/lib/static/home.md +++ b/lib/static/home.md @@ -2,21 +2,22 @@ Page destinée à être peuplée (plus ou moins intensément) dans un futur proc ### Fiche d'identité -Patronyme +Identification : Alex AUVOLAT--BERNSTEIN Occupation -: Étudiant au Département d'Informatique de l'ENS +: Étudiant au [Département d'Informatique](http://www.di.ens.fr) de l'[ENS](http://www.ens.fr/) Cursus -: Élève normalien (2013-) +: Élève à l'ENS rue d'Ulm (2013-) : Prépa MPSI/MP au lycée Masséna (2011-2013) : Lycée Albert Calmette (2008-2011) : École Montessori Les Pouces Verts (1997-2008) : Conservatoire (dans ma jeunesse) Contact -: `alex [chez] adnab.me`, ou bien `alex.auvolat [chez] ens.fr` +: `alex.auvolat [chez] ens.fr` +: `alex [chez] adnab.me` Intérêts en informatique : Systèmes d'exploitations originaux @@ -33,6 +34,7 @@ Compétences remarquables : Programmation : Administration Linux : Anglais (bilingue) +: Piano ### Le serveur `adnab.me` @@ -41,7 +43,7 @@ Localisation : Services -: [Dépôts Git](http://adnab.me/cgi-bin/cgit.cgi/) +: [Dépôts Git](http://adnab.me/cgit/) diff --git a/tpl/account/login.php b/tpl/account/login.php index d76f315..9b0d95c 100644 --- a/tpl/account/login.php +++ b/tpl/account/login.php @@ -1,5 +1,5 @@ '; -echo 'Written by ' . $post['owner']; +echo '
'; +echo 'Écrit par ' . $post['owner']; if ($can_edit && $post['owner_id'] == $user['id']) echo ' | modifier'; if ($can_delete && $post['owner_id'] == $user['id']) @@ -15,9 +15,9 @@ if ($can_comment && !$is_draft) if ($is_draft) echo ' | publier'; echo '
'; -echo '
publié le ' . $post['date'] . '
'; +echo '
publié le ' . $post['date'] . '
'; if ($post['tags'] != '') { - echo '
tags: ' . $post['tags'] . '
'; + echo '
tags: ' . $post['tags'] . '
'; } echo $post['text_html']; diff --git a/tpl/general/top.php b/tpl/general/top.php index f060b42..f9ef6d8 100644 --- a/tpl/general/top.php +++ b/tpl/general/top.php @@ -23,17 +23,17 @@ global $user, $apps; //These might be hidden because this page is called from sq
InscriptionConnexion'; + echo 'InscriptionConnexion'; } else { - echo 'Déconnexion (' . $user['name'] . ')'; + echo 'Déconnexion (' . $user['name'] . ')'; } ?>
- Accueil - Blog - Accueil +Blog'; echo 'bloc-notes'; echo 'fichiers'; } diff --git a/tpl/notes/inc_relativestree.php b/tpl/notes/inc_relativestree.php index fd47ab4..295e552 100644 --- a/tpl/notes/inc_relativestree.php +++ b/tpl/notes/inc_relativestree.php @@ -4,10 +4,10 @@ $notes_tree = array(); $notes_parents = array(); $n = sql("SELECT id, parent, title FROM notes ". - "WHERE owner = " . $note['owner'] . + "WHERE owner = ?" . ($note['owner'] == $user['id'] ? " " : " AND public != 0 ") . - "ORDER BY title ASC"); -while ($nn = mysql_fetch_assoc($n)) { + "ORDER BY title ASC", $note['owner']); +while ($nn = $n->fetch()) { $notes_parents[$nn['id']] = $nn['parent']; if (isset($notes_tree[$nn['parent']])) { $notes_tree[$nn['parent']][] = $nn; diff --git a/tpl/notes/view.php b/tpl/notes/view.php index 7615fe3..559c39e 100644 --- a/tpl/notes/view.php +++ b/tpl/notes/view.php @@ -10,7 +10,7 @@ if ($can_edit) $t[] = 'modifier'; $t[] = 'code source'; if ($can_move) $t[] = 'déplacer'; if ($can_delete) $t[] = 'supprimer'; -echo '
' . implode(' | ', $t) . '
'; +echo '
' . implode(' | ', $t) . '
'; echo $note['html']; require("tpl/notes/inc_relativestree.php"); -- cgit v1.2.3