Changes between Version 1 and Version 2 of OtherDevelopments

Timestamp:

05/28/26 09:38:22 (2 hours ago)

Author:

221296

Comment:

--

OtherDevelopments

@@ -273 +273 @@
 
 These indexes improve reports that analyze quiz attempts by user, quiz or date.
+
+= Security and Protection Measures =
+
+== Spring Security Authentication ==
+
+The application uses Spring Security for authentication and authorization.
+
+Unauthenticated users can access public pages such as:
+
+home page
+login page
+registration page
+static resources such as CSS and JavaScript files
+
+All other requests require authentication.
+
+Example configuration:
+
+{{{
+@Bean
+public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
+
+http
+    .csrf(Customizer.withDefaults())
+    .authorizeHttpRequests(auth -> auth
+        .requestMatchers("/", "/login", "/register", "/css/**", "/js/**").permitAll()
+        .anyRequest().authenticated()
+    )
+    .formLogin(form -> form
+        .defaultSuccessUrl("/", true)
+        .permitAll()
+    )
+    .logout(logout -> logout
+        .logoutUrl("/logout")
+        .logoutSuccessUrl("/login?logout")
+        .permitAll()
+    );
+
+return http.build();
+
+}
+}}}
+
+Explanation:
+
+Spring Security protects the application by requiring users to log in before accessing protected resources.
+
+The configuration also defines login and logout behavior.
+
+== Password Hashing with BCrypt ==
+
+User passwords are not stored in plain text. The application uses BCrypt for password hashing.
+
+Example:
+
+{{{
+@Bean
+public PasswordEncoder passwordEncoder() {
+return new BCryptPasswordEncoder();
+}
+}}}
+
+During login, the raw password entered by the user is compared with the stored hashed password.
+
+{{{
+passwordEncoder.matches(rawPassword, storedHashedPassword);
+}}}
+
+Explanation:
+
+This protects user accounts because even if someone gets access to the database, the original passwords are not directly visible.
+
+== Role-Based Access Control ==
+
+The database contains a role attribute in the user_entity table.
+
+{{{
+role VARCHAR(20) NOT NULL CHECK (role IN ('USER', 'ADMIN', 'INSTRUCTOR'))
+}}}
+
+The system supports three roles:
+
+USER — can browse courses, enroll in courses and access learning content
+INSTRUCTOR — can manage courses, modules, lessons and quizzes
+ADMIN — can manage categories, subscription plans and support tickets
+
+Explanation:
+
+The CHECK constraint prevents invalid roles from being inserted into the database.
+
+The separate tables users, administrators and instructors also support the supertype/subtype structure of the system.
+
+== SQL Injection Prevention ==
+
+SQL Injection is prevented by using Spring Data JPA and parameterized queries instead of directly concatenating user input into SQL strings.
+
+Safe example:
+
+{{{
+@Query("SELECT u FROM UserEntity u WHERE u.email = ")
+Optional findByEmail(@Param("email") String email);
+}}}
+
+Unsafe example:
+
+{{{
+String query = "SELECT * FROM user_entity WHERE email = '" + email + "'";
+}}}
+
+Explanation:
+
+The safe approach treats user input as data, not as executable SQL code.
+
+This prevents malicious input from changing the meaning of the SQL query.
+
+== Database Constraints and Data Integrity ==
+
+The database schema contains constraints that protect the data from invalid states.
+
+Examples:
+
+{{{
+email VARCHAR(150) UNIQUE NOT NULL
+}}}
+
+This prevents duplicate user accounts with the same email.
+
+{{{
+progress_percentage INT CHECK (progress_percentage BETWEEN 0 AND 100)
+}}}
+
+This prevents invalid course progress values.
+
+{{{
+certificate_code VARCHAR(100) UNIQUE NOT NULL
+}}}
+
+This ensures that every certificate has a unique certificate code.
+
+{{{
+enrollment_id INT UNIQUE NOT NULL
+}}}
+
+This ensures that one enrollment can have only one certificate.
+
+Foreign keys are also used to protect relationships between tables:
+
+{{{
+FOREIGN KEY (user_id) REFERENCES users(id)
+FOREIGN KEY (course_id) REFERENCES course(course_id)
+FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id)
+}}}
+
+Explanation:
+
+These constraints prevent orphan records and keep the database consistent.
+
+== Transaction Management ==
+
+Some operations must be executed as one atomic unit.
+
+For example, when a user enrolls in a course, the system may need to:
+
+check whether the user has an active subscription
+check whether the user is already enrolled
+insert a new enrollment record
+update related data
+
+Example:
+
+{{{
+@Transactional
+public void enrollUserInCourse(Long userId, Long courseId) {
+// check active subscription
+// check existing enrollment
+// create new enrollment
+}
+}}}
+
+Explanation:
+
+If one step fails, the whole transaction is rolled back.
+
+This protects the database from partial or inconsistent changes.
+
+== Database Connection Configuration ==
+
+The application uses PostgreSQL as the database system.
+
+Example configuration:
+
+{{{
+spring.application.name=db
+spring.datasource.url=jdbc:postgresql://localhost:3307/db_202526z_va_prj_olpms
+spring.datasource.username=USERNAME
+spring.datasource.password=PASSWORD
+spring.datasource.driver-class-name=org.postgresql.Driver
+
+spring.jpa.hibernate.ddl-auto=validate
+spring.jpa.show-sql=true
+spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
+}}}
+
+Explanation:
+
+Real database credentials should not be published on the wiki page. Because of that, the username and password are replaced with placeholders.
+
+Spring Boot uses HikariCP as the default database connection pool implementation. Connection pooling improves performance by reusing already opened database connections instead of opening a new connection for every request.