Databases

Prologue

Walt

Debbie Day Taybass

Yalenchka

Fiona

Yusa

Seth

Adjunct Professor Vance

Yalenchka 'Y' Burdukovskychev

"Y, Why do I need a database?"

Why You Might Need a Database

A good interface for structured data

Finds data you want quickly

Built to work over networks

Walt Isa

"Walt Isa database?"

What a Database Is

In essence, a database is a file with different interface.

Yusa "Data" Bass

"How do I Yusa 'Data' Bass?"

Primary Keys

Has to be Unique

Data is Clustered Based on this Value

User Feedback!

"Wow! This app is fun, but how am I am supposed to know about all this random stuff? I only got 3." "Jeez, I wish I could at least have a chance at some of these questions." "GG2EZ SCRUBLORDS. GET REKT M8! LOLOLOLOL"

Foreign Keys

• Save Space
• Easy Updates
• Keep All Values in One Spot

Seth Querity

"Sounds like you need help with Seth Querity!"

Password Hashing!

function Register(string Username, string Password) {
  string HashValue = hash(Password);

  string registerUserQuery = "INSERT INTO users (username, password)" +
    "VALUES ('" + Username + "', '" + HashValue + "');";

  SqlConnection.query(registerUserQuery);
}
          

INSERT INTO users (username, hashed_password)
VALUES ('Seth', 'aa21e1e64b5ca4f6e7bd20ed945b8b79');
          

How do I Authenticate?

function boolean Authenticate(string Username, string Password) {
  string HashValue = hash(Password);

  string authQuery = "SELECT * FROM trivia_users WHERE username='" + username
    + "' AND hashed_password='" + HashValue + "'";

  SqlResult userSelect = SqlConnection.query(authQuery);

  return userSelect.num_rows > 0;
}
          

SELECT * FROM trivia_users 
WHERE username='Seth' AND hashed_password='aa21e1e64b5ca4f6e7bd20ed945b8b79';
          

The Database is empty.

How?

A SQL Injection

Why?

Someone wanted to break your project

Who?

Arun.

Always Use Protection

Determine What's Valid

• Deny All Unacceptable Strings
• Escape All Strings Before Entry to Database
• Escape All Strings Before Display on Webpage

A Failed SQL Injection

void Register(string Username, string Password) {
  // ...
  Username.replace("'", "\'");
  // ...
}
            

void Register("Seth', ''); DROP TABLE trivia_questions; --", "LOLHAX");

INSERT INTO users (username, hashed_password)
VALUES ('Seth\', \'\'); DROP TABLE trivia_questions; --', '6978ab23a8f309433addd76793a55ddf');
            

The Results are In...

In First place...

DEBBIE DAY TAYBASS

But our story doesn't end here.

Ad. Vance Seaqual-Kweries

"Can you help me optimize my game with Ad. Vance Seaqual-Kweries?"

The High Score Table

ArrayList<Triple<String, Category, int>> GetHighScoreTable(int UserScore, int Radius) {
    ArrayList<Triple<String, Category, int>> highScoreTable =
      new ArrayList<Triple<String, Category, int>>();

    int UpperBound = UserScore + Radius;
    int LowerBound = UserScore - Radius;

    String selectScoresQuery = "SELECT * FROM trivia_scores WHERE score<=" + UpperBound
      + " AND score>=" + LowerBound + ";";
    SqlResult nearbyScores = SqlConnection.query(selectScoresQuery);

    String selectCategoriesQuery = "SELECT * FROM trivia_categories;";
    SqlResult categories = SqlConnection.query(selectCategoriesQuery);

    foreach (Score in nearbyScores) {
      String getUserNameQuery = "SELECT first_name FROM triva_users WHERE id=" + Score.user_id + ";";

      SqlResult userName = SqlConnection.query(getUserNameQuery);

      String category = categories[Score.category_id];

      highScoreTable.add(new Triple(userName.first_name, category, Score.score));
    }

    return highScoreTable;
}
          

Calculating Ranking

int GetRank(int UserScore, int Category) {
  String selectUsersQuery = "SELECT user_id FROM trivia_scores WHERE score <= "
    + UserScore + " AND category_id=" + Category + ";";

  SqlResult betterUsers = SqlConnection.query(selectUsersQuery);

  int count = 0;
  foreach (User in BetterUsers) {
    count++;
  }

  return count;
}
          

Aggregations

SELECT count(*)
FROM trivia_scores
WHERE category_id=5;
          

Returns 1 Row

Thank you

Visit my Github for the source where I left more comments.
http://github.com/dLopreiato

Made with reveal.js

Character Design by Emma

In loving memory of Noah Sequil