week 8
Teaching and Learning Methods to be Employed
1. Lecture on security mechanisms with live SQL demonstrations
2. SQL injection attack demonstration and prevention
3. Lab: Creating users, granting/revoking privileges in MySQL
4. Discussion: NDPR compliance for Nigerian database systems
Learning Outcomes / Objectives
By the end of this week, students should be able to:
1. Describe the major threats to database security.
2. Explain authentication, authorisation, and access control in DBMS.
3. Use SQL DCL commands (GRANT, REVOKE) to manage user privileges.
4. Explain how database views provide security through data abstraction.
5. Describe SQL injection and how to prevent it.
6. Explain encryption and audit trails as security mechanisms.
7. Describe the requirements of NDPR for database-driven applications.
8.5 SQL Injection — Attack and Prevention
SQL injection is one of the most common and dangerous web application vulnerabilities. An attacker inputs malicious SQL code into a form field that gets incorporated into a database query.
ATTACK EXAMPLE: A login form sends the query:
SELECT * FROM USERS WHERE Username='' + input_username + '' AND Password='' + input_password + ''
If the attacker enters: Username = ' OR '1'='1 and Password = ' OR '1'='1
The query becomes: SELECT * FROM USERS WHERE Username='' OR '1'='1' AND Password='' OR '1'='1'
'1'='1' is always TRUE, so the attacker gains access without a valid password!
PREVENTION METHODS:
1. Parameterised Queries (Prepared Statements) — NEVER concatenate user input directly into SQL
2. Input Validation — Validate and sanitise all user inputs
3. Stored Procedures — Use pre-compiled SQL
4. Principle of Least Privilege — DB user for the app should have minimal rights
5. Web Application Firewalls (WAF) — Detect and block injection attempts
SAFE QUERY (Java PreparedStatement):
PreparedStatement stmt = conn.prepareStatement('SELECT * FROM USERS WHERE Username=? AND Password=?');
stmt.setString(1, inputUsername);
stmt.setString(2, inputPassword);