SQL Data Manipulation and SQL Injection
Add, update and delete records using INSERT, UPDATE and DELETE. Query multiple tables. Understand SQL injection attacks and how parameterised queries prevent them.
Reading data from a database is only half the story. Every time someone signs up for an account, buys something, or cancels a subscription, the database changes. INSERT adds new records, UPDATE modifies existing ones, and DELETE removes them. And all of this must be protected from attackers who know how to abuse SQL itself.
VALUES (1008, 'Ivy', 'James', 15, 10, TRUE, 76)
This adds one new row: StudentID 1008, Ivy James, 15 years old, Year 10, enrolled, score 76.
The order of values must match the order of fields listed in brackets. If you omit a field from the list, it must either have a default value or be nullable. The primary key must be provided and must be unique - inserting a duplicate primary key will cause an error.
SET Score = 90, IsEnrolled = TRUE
WHERE StudentID = 1003
Updates Clara Davis's score to 90 and sets her as enrolled. The WHERE clause ensures only her row is affected.
UPDATE Students SET Score = 0 would set every student's score to zero. Always include WHERE.WHERE StudentID = 1006
Removes Frank Green's record from the table. Only the row where StudentID = 1006 is deleted.
DELETE FROM Students with no WHERE clause deletes every single row in the table. The table structure is preserved but all data is gone. Always double-check your WHERE clause before running DELETE.A common 3-4 mark question asks you to write an INSERT, UPDATE or DELETE statement for a given scenario. Read carefully: if asked to update a teacher's email "for all students in Year 11", the WHERE clause must specify Year = 11. Marks are often lost for missing or incorrect WHERE conditions.
A query can retrieve data from two tables at once by listing both in FROM and specifying how they link in WHERE. This is how foreign keys are used in queries.
Students
| StudentID | Name | CourseID |
|---|---|---|
| 1001 | Alice Smith | 201 |
| 1002 | Bob Brown | 202 |
Courses
| CourseID | CourseName |
|---|---|
| 201 | GCSE Maths |
| 202 | GCSE CS |
SELECT Students.Name, Courses.CourseName
FROM Students, Courses
WHERE Students.CourseID = Courses.CourseID
The WHERE clause acts as the JOIN condition - it matches the foreign key in Students to the primary key in Courses. Only rows where the IDs match are included. The dot notation (Students.CourseID) specifies which table a field belongs to - required when two tables have fields with the same name.
SQL injection is an attack where malicious SQL code is entered into a form field. If the application inserts user input directly into a SQL query without checking it, the attacker's code becomes part of the query - and the database executes it.
The application builds this query using the username field directly:
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"
If username = ' OR '1'='1
WHERE username = '' OR '1'='1'
AND password = ''
Result: '1'='1' is always true, so this returns all rows. The attacker is logged in as the first user in the database - likely an admin account.
Beyond bypassing login: read all records from any table, delete tables (DROP TABLE), extract usernames and passwords, modify data. It is one of the most common and dangerous web vulnerabilities.
A parameterised query (also called a prepared statement) separates the SQL structure from the user input. The input is never concatenated into the SQL string - it is passed as a separate parameter and treated as plain data, never as SQL code.
query = "SELECT * FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))
Even if the attacker types ' OR '1'='1, the database looks for a user whose username is literally that string - not a SQL condition. The attack fails completely.
Test yourself
1. A student's score needs to be updated from 67 to 80. Their StudentID is 1002. Which SQL statement is correct?
2. What is the main risk of omitting the WHERE clause from a DELETE statement?
3. How do parameterised queries prevent SQL injection?
A new student joins Year 10 and needs to be added to the database: Jade King, age 15, enrolled, score 0, CourseID 201. Write the SQL statement, then explain what would happen if you forgot the WHERE clause on a subsequent UPDATE that sets her score to 65.
VALUES (1009, 'Jade', 'King', 15, 10, TRUE, 0, 201)
UPDATE Students SET Score = 65 without a WHERE clause, every student in the database has their score set to 65 - not just Jade. All original scores are permanently overwritten. This demonstrates why WHERE is mandatory when you intend to update a specific record.
Practice what you have learned
Three levels of worksheet for this lesson. Download, print and complete offline.