Lesson2.md

Lesson 2: Practical Database Usage

In this class, students will learn how to use more complex SQL queries to retrieve information across tables, and interact with data including write operations.

Objective: Students should be able to build CRUD functionality using SQL statements, including INSERT INTO, UPDATE, etc.

Pre-Class Readings

• Inserting, Updating, and Deleting
• The ACID Database Model
• Conflicts and Isolation
• Planning Your Database
• The Data Definition Language
• Understanding Stored Procedures and Injection Attacks

Main Topics

• INSERT INTO
• UPDATE
• Writing SQL in your application
  • Raw SQL strings
  • Parameter validation
  • Escaping
  • Prepared statements
  • Stored procedures
• Security
  • SQL Injection
  • User GRANTS
  • Enumeration

Reference Material

• OWASP on SQL Injection
• Parameter Validation on Wikipedia
• Node MySQL Escaping Query Values
• Node MySQL Preparing Queries (automatic escaping)
• MySQL SHOW GRANTS
• Rewatch the previously recorded session: part 1
• Rewatch the previously recorded session: part 2
• Rewatch the previously recorded session: part 3

Homework

• Write the necessary SQL statements to complete the functionality of your application. This probably includes, at the minimum, the following functionality:
  • Adding a new todo item
  • Changing the name, the due date, or the status of a todo item
  • Marking a todo item as completed
  • Deleting a todo item