NDV - SLAM, 2026 session

Mission A1 - DBMS activity logs

The form sends type_de_logs (CONNEXION, REQUETE...). We add a condition on log_nature, based on the date filter already in place - and we keep a prepared statement:

// après le bloc qui récupère $date (vers la ligne 16)
$nature = null;
if (isset($_POST['type_de_logs']) && $_POST['type_de_logs'] != '') {
    $nature = $_POST['type_de_logs'];
}

// requête modifiée (ligne 17)
$query = "SELECT * FROM t_logs
          WHERE (:date   IS NULL OR DATE(log_date) = :date)
            AND (:nature IS NULL OR log_nature     = :nature)
          ORDER BY log_date ASC";

// après le bindParam(':date', …) (vers la ligne 20)
$stmt->bindParam(':nature', $nature, PDO::PARAM_STR);

The (:param IS NULL OR colonne = :param) pattern makes it possible to filter only if the value is provided - exactly the logic already used for the date.

a) In the logs (doc A1a) - several anomalies point to an over-privileged and compromised account:

• line 8: login of ndv_64784 from 172.17.12.45 (and not localhost), which should not be permitted;
• lines 9-10: CREATE USER 'test'@'%' then GRANT ALL ON *.* TO 'test'@'%' - creating an account and granting all privileges: ndv_64784 can therefore administer the server;
• line 14: DROP TABLE Contrat - table deletion, well beyond expected CRUD operations.

b) In the permissions (doc A3) - ndv_64784 is declared on host % (any IP), with ALL PRIVILEGES and Grant = Oui (it can delegate its privileges). This is the opposite of least privilege.

c) Correction queries (least privilege + local connection only):

-- 1. Retirer les droits excessifs et la délégation
REVOKE ALL PRIVILEGES, GRANT OPTION ON ndv.* FROM 'ndv_64784'@'%';

-- 2. Restreindre la connexion au serveur local
RENAME USER 'ndv_64784'@'%' TO 'ndv_64784'@'localhost';

-- 3. N'accorder que les opérations nécessaires au pilote applicatif
GRANT SELECT, INSERT, UPDATE, DELETE ON ndv.* TO 'ndv_64784'@'localhost';

Mission A2 - Protect personal data

a) Personal data (CNIL/GDPR) is any information relating to an identified or identifiable natural person (name, email address, telephone number, IP address...). Sensitive data falls under the special categories of Article 9 of the GDPR (racial origin, political opinions, health, sexual orientation, biometric/genetic data...), subject to heightened protection.

b) In the intranet tables: contact names and email addresses are personal data; no strictly defined sensitive data appears in the provided schema.

Encrypting personal data is a recommendation / best practice: Article 32 of the GDPR requires 'appropriate technical measures' and cites encryption as an example, without making it mandatory in all cases. The risk assessment is what makes it necessary here.

Scenario involving an internal actor: a database administrator (or a developer) with legitimate access to the database could read or exfiltrate plaintext email addresses. As the encryption key is stored outside the database (environment variable), encryption protects even against this direct DBMS access.

Adapt the column, then encrypt existing data with the provided key:

-- Adapter le type pour stocker le binaire chiffré
ALTER TABLE Contact MODIFY courriel VARBINARY(200);

-- Chiffrer les adresses des contacts externes déjà stockées
UPDATE Contact SET courriel = AES_ENCRYPT(courriel, 'lhh4&abt2!');

In getContact(), decrypt on display using the key passed via the environment (and still using a prepared statement):

SELECT id, nom, AES_DECRYPT(courriel, :cle) AS courriel
FROM Contact
WHERE id = :id;

$stmt->bindParam(':cle', $_ENV['AES_key'], PDO::PARAM_STR);

Mission A3 - 'Mirror' organisation (schema)

For each contract, a role (project manager, DPO, API security lead...) must be linked to two people: one on the NDV side, one on the client side. We therefore add:

• a table Role (id_role, libelle) listing generic roles;
• a junction table Affectation linking Contrat, Role, and two people (id_personne_ndv, id_personne_client) - as the same person can hold multiple roles.

This way, an exchange is linked to a (role, contract) pair and remains confidential between the two mirror contacts.

Mission B1 - SQL injection

a) Queries actually executed:

• payload1 = sbegard' OR 1=1 # → … WHERE username='sbegard' OR 1=1 #'…: condition OR 1=1 is always true and # comments out the rest (the password check).
• payload2 = sbegard' AND 1=0 UNION SELECT null, username, password, null, null FROM T_Users # → AND 1=0 cancels the first query and the UNION returns the username/password from T_Users.

b) Display: payload1 logs the attacker in (first user found) without knowing a password; payload2 displays the usernames and passwords from T_Users.

c) User sbegard does not need to exist: OR 1=1 (payload1) and AND 1=0 (payload2) render the condition on username ineffective.

a) To empty the table, a stacked query payload such as sbegard'; DELETE FROM T_Documents; # (or a DROP/TRUNCATE) if query stacking is enabled.

b) The table name could have been discovered via an overly verbose SQL error message returned to the browser (it exposes the database structure).

a) The injection is possible because the query is constructed using direct concatenation of user input.

b) This is fixed using a PDO prepared statement, which separates SQL code from data:

$query = "SELECT * FROM T_Users WHERE username = :user AND password = :mdp";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':user', $username, PDO::PARAM_STR);
$stmt->bindParam(':mdp',  $hash,     PDO::PARAM_STR);
$stmt->execute();

Mission B2 - Modifying another user's document (IDOR)

a) Vulnerability OWASP A01:2021 - Broken Access Control (IDOR variant: Insecure Direct Object Reference): modifying doc_id in the URL allows editing another client's document, as the code does not verify resource ownership.

b) Fix in espace_client.php: check that the document belongs to the logged-in user before making any changes, for instance by verifying that doc_id is present in $lesDocuments (the user's documents), or on the SQL side:

UPDATE T_Documents
SET nom = :nom, description = :desc
WHERE doc_id = :doc_id AND client_id = :idClientConnecte;

Mission B3 - Password hashing

a) The algorithm used is a fast, unsalted hash (MD5/SHA-1 type): it is vulnerable to rainbow tables and GPU-accelerated brute force, which allowed the attacker to retrieve/modify passwords.

b) Vulnerability OWASP A02:2021 - Cryptographic Failures.

a) In traitement_inscription.php, hash with Argon2id:

$hash = password_hash($motDePasse, PASSWORD_ARGON2ID);
// puis stocker $hash en base

b) In espace_client.php, verify with password_verify() (which automatically handles salt and parameters):

if (password_verify($motDePasseSaisi, $hashEnBase)) {
    // authentification réussie
}