Easy2Drive - SLAM, 2022 session

Mission A1 - Risk assessment from user stories

a) Availability - story 1 (**) vs story 2 (*). Story 1 (browsing the website for information) concerns the public showcase: its unavailability leads to lost prospects and commercial visibility -> high requirement. Story 2 (changing password) is a one-off action that can be postponed without business impact -> moderate requirement.

b) Integrity and confidentiality of story 2 (**). Integrity is high: an altered password (corruption, truncation) would make the account inaccessible or weakened. Confidentiality is high: the password is an authentication secret whose disclosure would enable account takeover.

c) Proof - story 1 (-) vs story 3 (**). Story 1 is anonymous browsing without an account: no actions to prove. Story 3 (posting a review) produces legally binding content: in the event of a dispute (defamatory review, litigation), you must prove who posted what and when -> high requirement.

Mission A2 - Addressing GDPR

The CNIL decision of 17 September 2020 requires explicit consent: users must be clearly informed of the purposes of cookies and be able to choose between accepting, rejecting or configuring them. However, the current banner merely displays "We use cookies to ensure the proper functioning of our site":

• it does not specify the precise purposes;
• it offers neither rejection nor configuration options (implicit consent);
• using the site is deemed acceptance, which is no longer permitted.

The banner is therefore non-compliant and exposes Easy2Drive to a fine (sanctions applicable since 1 April 2021).

Story 1 (browsing without creating an account) automatically collects (see privacy policy): IP address, device type, request content, browser version, screen resolution, operating system and language.

The personal data is the IP address: it allows, directly or indirectly (via the ISP), the identification of a natural person. This is the definition of personal data under the GDPR (Art. 4). Other information (resolution, OS, browser, etc.) does not on its own allow the user to be identified.

Mission A3 - Checking password security

a) Issues with the initial password. It is sent in plain text by email (Doc A4: qamQdVD3 legible): emails are unencrypted and can be intercepted or viewed by a third party. Furthermore, the student can keep it all year without changing it: a secret shared via an insecure channel and never updated remains permanently compromised.

b) Best solution. Provide a one-time password / temporary link requiring the student to set their own password upon first login (the initial password expires after use). This prevents a long-term secret from circulating by email.

a) Current policy (reading verifPassword). Validation requires $points_total == $resultat with $resultat = $points_long * $points_comp, and $points_total = 6. $points_long equals 1 if length ≥ 8, 0 otherwise. To reach 6, $points_long = 1 and $points_comp = 6 are therefore required. Complexity awards +1 (lowercase), +2 (uppercase), +3 (digit): 6 = 1+2+3. The current policy therefore requires: length ≥ 8 and the presence of at least one lowercase letter, one uppercase letter, and one digit. Special characters are not required, and the minimum length (8) is below the 12 characters required by the CNIL.

b) Modification to comply with the CNIL (length ≥ 12; lowercase +1, uppercase +2, digit +3, special character +4; threshold ≥ 10). Parts to modify/add:

$points_total = 10;            // ligne 1 : seuil de validation ≥ 10
...
if ($longueur >= 12) { $points_long = 1; }   // ligne 5 : 12 caractères mini (CNIL)
...
// après le test du chiffre (ligne 8), ajouter le caractère spécial :
if (preg_match("/\W/", $mdp)) { $points_comp = $points_comp + 4; }

$resultat = $points_long * $points_comp;     // ligne 9 inchangée
return ($points_total <= $resultat);         // ligne 10 : ≥ au lieu de ==

A 12-character password containing all 4 character sets gives $points_comp = 1+2+3+4 = 10, so $resultat = 1 × 10 = 10 ≥ 10 → valid.

c) Updated testVerifPassword unit tests (under the new policy, "qamQdVD3" - 8 characters, no special characters - is no longer valid):

public function testVerifPassword()
{
    // trop court (< 12) → false
    $this->assertSame(false, verifPassword("Qam3!"));
    // 12 car. mais pas de caractère spécial : 1*(1+2+3)=6 → false
    $this->assertSame(false, verifPassword("QamQdVD3abcd"));
    // 12 car., 4 familles : 1*(1+2+3+4)=10 → true
    $this->assertSame(true,  verifPassword("QamQdVD3abc!"));
    // longueur 14, 4 familles → true
    $this->assertSame(true,  verifPassword("Qam3!QdVDbcdEf"));
}

a) Adding the dateMajMDP field (mandatory, initialised upon creation):

ALTER TABLE Utilisateur ADD dateMajMDP DATE NOT NULL DEFAULT (CURRENT_DATE());

b) Stored function renouvelleMDP (returns true if the password is more than 90 days old), following the model of Common Doc 2:

CREATE FUNCTION renouvelleMDP(num INT)
RETURNS BOOLEAN
BEGIN
    DECLARE v_date DATE;
    DECLARE v_retour BOOLEAN DEFAULT false;
    SELECT dateMajMDP INTO v_date FROM Utilisateur WHERE id = num;
    IF (DATE_ADD(v_date, INTERVAL 90 DAY) < CURRENT_DATE()) THEN
        SET v_retour = true;   -- mot de passe trop ancien : renouvellement requis
    END IF;
    RETURN v_retour;
END

Mission B1 - Awarding the Garantie Réussite

Easy2Drive wrongly reimburses the fees to retake the theory test to a student who was not entitled to it (false declaration of failure by the driving school). The main consequence is therefore a financial loss for the company (undue reimbursements, multiplied across 6,000 driving schools), alongside a risk of organised fraud.

a) Trigger analysis (Doc B2). Several conditions of the Garantie Réussite (Doc B1) are incorrectly or not implemented:

Expected condition (B1)	State in the trigger	Anomaly
Failure dated less than 6 months ago	line 12: DATE_ADD(NEW.dateEtg, INTERVAL 6 MONTH) >= NOW() then error	Inverted test: it rejects recent failures when it should reject old failures (> 6 months)
≥ 25 quiz sets	lines 16-20: WHERE idEleve = NEW.id	NEW.id instead of NEW.idUtilisateur (key of Eleve)
Average ≥ 34/40 on the 4 best exams	lines 21-25: same as NEW.id	same key error; OK on LIMIT 4
≥ 4 mock exams taken	missing	no check on the minimum number of mock exams
Guarantee awarded only once (1st failure)	missing	nothing prevents a second award
garantieReussite actually set	no assignment	the field is never set to TRUE at the end

b) Corrected code (parts to modify/add):

DECLARE v_nbExam INT;

-- échec trop ANCIEN : on rejette si l'échec date de plus de 6 mois
IF DATE_ADD(NEW.dateEtg, INTERVAL 6 MONTH) < NOW() THEN
    SIGNAL SQLSTATE '10002'
        SET MESSAGE_TEXT = 'Garantie réussite : échec trop ancien';
END IF;

-- garantie déjà attribuée une fois
IF OLD.garantieReussite = TRUE THEN
    SIGNAL SQLSTATE '10006'
        SET MESSAGE_TEXT = 'Garantie réussite : déjà attribuée';
END IF;

-- séries de quiz : corriger la clé (idUtilisateur)
SELECT COUNT(*) INTO v_nbSerie FROM Evaluer WHERE idEleve = NEW.idUtilisateur;

-- au moins 4 examens blancs (condition manquante)
SELECT COUNT(*) INTO v_nbExam FROM Passer WHERE idEleve = NEW.idUtilisateur;
IF v_nbExam < 4 THEN
    SIGNAL SQLSTATE '10004'
        SET MESSAGE_TEXT = 'Garantie réussite : examens blancs insuffisants';
END IF;

-- moyenne des 4 meilleurs : corriger la clé
SELECT AVG(examenScore) INTO v_scoreMoyen FROM
   (SELECT examenScore FROM Passer WHERE idEleve = NEW.idUtilisateur
    ORDER BY examenScore DESC LIMIT 4) AS MeilleureNotes;

-- toutes les conditions OK : on attribue la garantie
SET NEW.garantieReussite = TRUE;

Mission B2 - Event logging on personal data

The interview (Doc B3) requires knowing, for each table containing personal data, which user, under which role, performed which action, when, and on which record. Proposed relational schema:

Log (id, dateHeure, action, nomTable, idEnregistrement,
     idUtilisateur, nomUtilisateur, prenomUtilisateur, role)
Clé primaire : id

• action ∈ { consultation, insertion, modification, suppression };
• nomTable + idEnregistrement: the data concerned (e.g. table Eleve, record no. 2348);
• idUtilisateur, nom, prenom, role: the author of the action (role: director, trainer, student, moderator);
• dateHeure: timestamp.

We deliberately denormalise surname/first name/role in the log table: as the database is on a separate server, it cannot join with BD_EASY, and the log must remain fixed even if the user is subsequently modified or deleted.

The application connects from web server Easy2Drive.fr, so the authorised host is this DNS name:

CREATE USER 'APPLI_RGPD_LOGS'@'Easy2Drive.fr' IDENTIFIED BY 'unMotDePasseFort';

In accordance with least privilege (CNIL: tamper-proof logs), we grant only INSERT, on database BD_RGPD_LOGS:

GRANT INSERT ON BD_RGPD_LOGS.* TO 'APPLI_RGPD_LOGS'@'Easy2Drive.fr';

No SELECT, UPDATE or DELETE: the application can only add logs, never read or modify them - read access is restricted to the DPO via the "GDPR Logs" application.

a) Retention period. The CNIL (Doc CNIL 3) imposes a rolling period not exceeding 6 months for this type of log. We therefore propose 6 months.

b) Document in which to record this period. The record of processing activities required by Article 30 of the GDPR (which specifies, for each processing activity, the expected retention periods).

c) Automatic purge (technical solution without implementation). Set up a MySQL scheduled event (CREATE EVENT) running periodically (e.g. daily) to delete records older than 6 months (DELETE FROM Log WHERE dateHeure < DATE_SUB(NOW(), INTERVAL 6 MONTH)). Alternative: a system scheduled task (cron job) running the same purge script.

Mission C1 - Student review submission

The student can no longer submit reviews once they have posted 3 (comment: 'array of 0 to 3 Reviews'):

public function getNbMaxAvisAtteint(): bool
{
    return count($this->lesAvis) >= 3;
}

The form must only be accessible if the student is authorised to submit a review. This is no longer the case in two scenarios: they have reached the maximum number of rewrites (3 reviews), or their latest review is pending moderation (getModere() == false). Redirection to the home page is therefore triggered when either condition is met:

if ( $user->getNbMaxAvisAtteint()
     || ( count($user->getLesAvis()) > 0
          && $user->getDernierAvis()->getModere() == false ) ) {
    // l'utilisateur est redirigé vers l'accueil
    return $this->redirectToRoute('home');
} else {
    // le formulaire est accessible
    ...
}

a) Result of the injection. Query insertUnAvis (Doc C5) concatenates content without escaping:

$req = "insert into avis(contenu,dateDepot,publie,modere,idEleve) values('"
     . $unAvis->getContenu() . "',now()," . ... . ")";

The injected payload closes the first value and then chains multiple VALUES (...) tuples. Result: several reviews are inserted in a single query, all marked as publie=true, modere=true (3rd and 4th values) - therefore directly published and treated as moderated, with a 5/5 rating. Student 12 thus successfully mass-publishes fake negative reviews that are already approved, bypassing moderation entirely.

b) Bypassing previous measures. The countermeasures in Mission C1 (limit of 3 reviews, blocking while unmoderated) rely on the normal application flow. The SQL injection short-circuits the business logic: it writes directly to the database with publie=true/modere=true, so neither moderation nor the review counter is triggered. The PHP safeguards are ineffective because the vulnerability lies downstream, in the data access layer.

c) Remediation (without implementation). Use parameterised prepared statements (PDO): replace concatenation with placeholders :contenu, :idEleve... bound via bindParam/bindValue, and set publie/modere server-side (always false on insert). The input content is then treated as data, never as SQL:

$req = "insert into avis(contenu,dateDepot,publie,modere,idEleve)
        values(:contenu, now(), 0, 0, :idEleve)";
$res = PdoEasy2Drive::$monPdo->prepare($req);
$res->bindValue(':contenu', $unAvis->getContenu());
$res->bindValue(':idEleve', $unAvis->getLEleve()->getId());
$res->execute();

Mission C2 - Review moderation

Returns true if the address is shared by multiple students (prepared statement):

public function getDoublonMail($unEmail): bool
{
    $req = "select count(*) as nb from utilisateur
            join eleve on utilisateur.id = eleve.idUtilisateur
            where utilisateur.email = :email";
    $res = PdoEasy2Drive::$monPdo->prepare($req);
    $res->bindParam(':email', $unEmail);
    $res->execute();
    $ligne = $res->fetch();
    return $ligne['nb'] > 1;
}

Inside the foreach loop, call getDoublonMail with the current student's address, then add the doublonMail key to the array passed to the view:

foreach ($lesEleves as $unEleve) {
    if ($unEleve->getNeph() == null) {
        $pasDeNeph = true;
    } else {
        $pasDeNeph = false;
    }

    // détection du doublon d'adresse électronique
    $doublonMail = $PdoEasy2Drive->getDoublonMail($unEleve->getEmail());

    $tabDernierAvisParEleve[] = [
        'leEleve'     => $unEleve->getIdentite(),
        'avis'        => $unEleve->getDernierAvis(),
        'nbRefus'     => $unEleve->getNbAvisRefuse(),
        'pasDeNeph'   => $pasDeNeph,
        'doublonMail' => $doublonMail
    ];
}

For each review, the moderator then has the two indicators of a fake student: a duplicate address and a missing NEPH.