In the final week, the students will be introduced to SQL injections and common security practices around MySQL permissions. MongoDB, as an example of a non-relational database, will also be introduced, as well as the benefits and drawbacks of relational and non-relational models.
Objective: the students should know how SQL injections happen, and how to define user permissions in MySQL to limit the potential damage that can be done with a SQL injection. The students should also be able to compare and contrast relational (like MySQL) and NoSQL databases (considering their benefits and drawbacks).
Please read the following pages that explains the ACID database model:
Also the students should watch this video: https://youtu.be/HSt4nlUIF-A
- Security
- SQL Injection
- User GRANTS
- Introduction to non-relational data with MongoDB
- MongoDB vs. SQL
- Create/Drop database
- Insert/Update/Delete data
- Query data
- Relationships: embedded vs. referenced
- Replication and sharding
- Atomicity
- Advanced database schema exercise
- OWASP on SQL Injection
- Parameter Validation on Wikipedia
- Node MySQL Escaping Query Values
- Node MySQL Preparing Queries (automatic escaping)
- MySQL SHOW GRANTS
Need to brush up on the homework setup process? Check this out before you get into some git confusion!
For the homework this week we will create the database and queries for an application we will continue working on in the node js class.
For those who haven't finished this part of the homework last week, now it's the time to finish :)
The application will be a meal sharing website, where users can book a reservation at a meal another user has created.
So as a user you can both
- Create a new meal for people to join
- Book a reservation at a meal
It will be kind of similar to this website: https://www.mealsharing.com/
Lets first start with creating the data model.
Create all the sql for creating this data model: https://dbdiagram.io/d/5f0460690425da461f045a29
Create these queries
| Queries to write |
|---|
| Get all meals |
| Add a new meal |
| Get a meal with any id, fx 1 |
| Update a meal with any id, fx 1. Update any attribute fx the title or multiple attributes |
| Delete a meal with any id, fx 1 |
| Queries to write |
|---|
| Get all reservations |
| Add a new reservation |
| Get a reservation with any id, fx 1 |
| Update a reservation with any id, fx 1. Update any attribute fx the title or multiple attributes |
| Delete a reservation with any id, fx 1 |
| Queries to write |
|---|
| Get all reviews |
| Add a new review |
| Get a review with any id, fx 1 |
| Update a review with any id, fx 1. Update any attribute fx the title or multiple attributes |
| Delete a review with any id, fx 1 |
Now add a couple of different meals, reservations and reviews with different attributes. With those meals create the following queries
| Functionality |
|---|
| Get meals that has a price smaller than a specific price fx 90 |
| Get meals that still has available reservations |
Get meals that partially match a title. Rød grød med will match the meal with the title Rød grød med fløde |
| Get meals that has been created between two dates |
| Get only specific number of meals fx return only 5 meals |
| Get the meals that have good reviews |
| Get reservations for a specific meal sorted by created_date |
| Sort all meals by average number of stars in the reviews |
Need to brush up on the homework hand-in process?
Check this resource to remember how to hand in the homework correctly!
And finally, please take two minutes to answer the survey here to give feedback to the staff and mentors.