Lecture 26: More SQL: Insert and Injections

Agenda

1. SQL Recap
2. Inserting from Forms
3. Insert

Announcements & Reminders

• HW5 is out now: Due Wed, Dec 4 before 11pm.
• Final Exam: Tue, Dec 10 @ 2:30PM – 4:20PM in JHN102 (here)
  • We'll be updating the Exams Page with more info, practice exams, etc.

SQL

Structured Query Language, with a couple (for our purposes) main categories:

• Data Definition: What does our data look like?
  • CREATE TABLE, ALTER TABLE
• Data Manipulation:How do we create and access our data?
  • SELECT, INSERT, UPDATE

Separates out the concerns of the behavior of our backend (e.g., how do we respond to a GET request? what do we do on a POST?) and the content (e.g., what information are we saving?)

SELECT

Like a filter or a search:

• Choose (SELECT) these attributes
• FROM this table (or tables)
• Filtering on particular values (WHERE attributes match as given)

Example:

CREATE TABLE

Creates a new place for data to live.

-- Author: Andrew Fitz Gibbon
-- Last updated: 11.22.19
-- Sets up a new posts table in a blog database.
CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `author` VARCHAR(200) DEFAULT 'Fitz',
  `title` VARCHAR(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`)
); 

Node.js + MySQL

const mysql = require('mysql2/promise');

const db = mysql.createPool({
  // Variables for connections to the database.
  host: process.env.DB_URL || 'localhost',
  port: process.env.DB_PORT || '8889',
  user: process.env.DB_USERNAME || 'root',
  password: process.env.DB_PASSWORD || 'root',
  database: process.env.DB_NAME || 'Blog'
});

JS

Note: this process.env stuff is required for HW5

Later...

let [rows, fields] = await db.query("SELECT * FROM posts");

SQL + User Input

demo

Little Bobby Tables

XKCD 327

Alt: "Her daughter is named Help I'm trapped in a driver's license factory."

Another Example

Suppose we had a POST endpoint to validate a user logging in:

let user = req.body.username;
let pw = req.body.password;
let qry = "SELECT * FROM users WHERE username='" + user +
        "' AND password='" + pw + "'";
// e.g. SELECT * FROM users WHERE username='foo' AND password='bar';
let result = await db.query(qry);
if (result.length === 0) {
res.type("text");
res.status(400).send("user not found");
} else {
res.json(result); // send the user info data (a single row) as JSON
}

JS

What if a user predicts we're using SQL with their parameters?

CONDITION_A OR CONDITION_B evaluates to TRUE in SQL if either condition is true. 1=1 always evaluates to TRUE. What if we have many users in the users database? What user data could be leaked in such a response?

let user = req.body.username; // "' OR 1=1"
let pw = req.body.password; // "oops :)"
let qry = "SELECT * FROM users WHERE username='" + user +
        "' AND password='" + pw + "'";
// e.g. SELECT * FROM users WHERE username='' OR 1=1 -- AND password='oops :)'
let result = await db.query(qry);
if (result.length === 0) {
res.type("text");
res.status(400).send("user not found");
} else {
res.json(result); // send the user info data (a single row) as JSON
}

JS

Result - Returning All User Data

Improved Solution: ? Placeholders

let user = req.body.username;
let pw = req.body.password;
let qry = "SELECT * FROM users WHERE username=? AND password=?";
// the qry will be escaped internally and no rows will be returned
let result = await db.query(qry, [user, pw]);
if (result.length === 0) {
res.type("text");
res.status(400).send("user not found");
}

JS

Result - Preventing SQL Injection

INSERT

Now that we know that inserting user input raw is bad...

Let's finish out that TODO in the blogs...

INSERT