Finals Study Databases

Digital Crime

Crimes done using computers/networks.
Same goals as old crimes, but easier to scale.
ex. Fraud, theft, vandalism happening online.

Digital Version of Old Crimes

Traditional crimes re-done through digital systems.
Helps you recognize the “crime type” even when tech is new.
ex. Spam = junk mail, phishing = fraud, hacking = theft.

Access (What Changed)

Attackers can reach systems remotely.
No physical break-in needed; global reach.
ex. Attacker not in the same building/country.

Cross-Border Law / Jurisdiction

Countries try to enforce laws on content/services across borders.
Rules get messy when data is reachable worldwide.
ex. A service hosted in one country affected by another’s laws.

SQL Injection (SQLi)

Using input to make a dynamic SQL query do something it shouldn’t.
Can expose or change data, or bypass login.
ex. Input changes the logic of a WHERE clause.

Dynamic Query

A query that is built at runtime using user input.
This is where SQLi happens if input is trusted.
ex. Server code “builds” SQL from typed text.

String Concatenation (in Query Building)

Building SQL by sticking strings + user input together.
The classic cause of SQLi.
ex. Query text has the user’s text directly inside it.

User Input (Untrusted Data)

Anything the user sends (forms, URLs, search boxes).
Attackers control it.
ex. Text fields, query parameters, login fields.

Search Box SQLi

Manipulating a search input so the query returns more than intended.
Often leaks entire tables.
ex. A search meant for one product returns everything.

WHERE Clause

The part of SQL that filters which rows you get.
SQLi often targets this logic.
ex. Conditions like Name LIKE …, User=… AND Password=…

LIKE Operator

SQL pattern-matching operator (often used for searching).
Wildcards can be abused to match everything.
ex. WHERE Name LIKE '%text%'

Wildcard (%)

Pattern that matches “anything” in a LIKE search.
Can force results to include all rows.
ex. LIKE '%' matches every string.

OR Operator

Makes a condition true if either side is true.
Attackers use OR to bypass intended filters.
ex. condition1 OR condition2

Always-True Condition

A condition that is true for all rows.
Turns filtering into “return everything.”
ex. 1=1 (or '1'='1') inside a WHERE clause.

AND vs OR Precedence

AND is evaluated before OR (unless parentheses change it).
Injection exploits rely on how SQL groups conditions.
ex. User=… AND Pass=… OR 1=1 may become true due to OR at the end.

Authentication

Proving you are who you claim to be.
SQLi can let attackers authenticate without real credentials.
ex. Login form → server query checks username/password.

Authorization

What you’re allowed to do after you’re authenticated.
SQLi can skip authentication and get unauthorized access.
ex. Seeing admin-only pages/data.

Authentication Query Pattern (COUNT(*))

Login query that returns a count of matching users.
If injection makes count > 0, login passes.
ex. COUNT(*) used instead of returning full user rows.

COUNT(*)
ex. SELECT COUNT(*) …

Returns number of rows matched by the query.
Used in authentication checks; must be tightly controlled.

Authentication Bypass

Getting “logged in” without the correct username/password.
High-impact SQLi outcome.
ex. Login succeeds with nonsense input.

Data Exposure (Overbroad Results)

Query returns more rows/columns than intended.
Leaks private or unreleased data.
ex. A single search returns the full product table.

Unreleased Products / Hidden Data

Data present in the database but not meant for the public.
SQLi can reveal internal or future info.
ex. Upcoming products appearing in results.

Denial-of-Service via Huge Result Sets

Forcing queries to return massive data.
Slows the server and increases load.
ex. LIKE '%' / always-true conditions returning everything.

Plaintext Password

Password stored exactly as typed.
If stolen, it’s immediately usable.
ex. Database contains readable passwords.

Password Hashing

Storing a one-way transformed value instead of the real password.
Stolen hashes aren’t directly the original password.
ex. Database stores hash strings, not real passwords.

One-Way Hash

A function that’s easy to compute but hard to reverse.
Core idea behind secure password storage.
ex. You can verify matches, but can’t “unhash” easily.

Linux /etc/shadow (Concept)

System file that stores hashed passwords on many Linux systems.
Example of how passwords should be stored (hashed).
ex. Accounts have hashes, not plaintext passwords.

Web Request Flow (Browser → Server → Database)

Typical path: browser sends input → server turns it into DB actions → database runs query.
SQLi happens when server builds SQL from untrusted input.
ex. Form submit → server code → SQL query.

Server Layer (Hidden Part)

The backend that converts user input into database operations.
This is where you must defend against SQLi.
ex. Backend code building queries.

Database Layer (Executes Valid SQL)

The database runs any valid SQL it’s asked to run.
The DB won’t “know” the query is malicious.
ex. If the SQL is valid, it executes.

Guarding Against SQLi

Preventing user input from changing query meaning.
Stops data leaks and bypasses.
ex. Validation + safer coding + safe errors + audits.

Defense Strategy: Detection

Trying to catch bad input before it becomes SQL.
Blocks many attacks early.
ex. Input checks, regex checks, allowed-character rules.

Defense Strategy: Avoidance

Coding so damage is limited even if input is weird.
Reduces worst-case outcomes.
ex. Authentication must only return 0 or 1; anything else is error.

Input Validation

Checking input meets rules before using it.
Prevents “extra SQL” from sneaking in.
ex. Rejecting unexpected characters or formats.

Sanitizing Input

Cleaning/inspecting input so only safe data is used.
Main practical defense discussed in slides.
ex. Removing/rejecting quotes or disallowed symbols.

Responsibility Gap (Who Sanitizes?)

In multi-layer systems, each layer may assume another layer validated input.
Can result in nobody validating anything.
ex. “The layer before me will handle it” mindset.

Escape Characters

Characters that change how SQL interprets text (ex: quotes).
They let attackers break out of strings or change meaning.
ex. ' used to start/end strings.

Whitelist

“Only these characters are allowed.”
Stronger than blacklist for SQLi defense.
ex. Allow letters/numbers/space only.

Blacklist

“These characters are not allowed.”
Weaker—easy to miss something dangerous.
ex. Block a few symbols, attacker uses another one.

Regular Expression (Regex)

Pattern language for matching text.
Used to enforce whitelists on input.
ex. A pattern that flags invalid characters.

Regex Character Class [ … ]

Matches one character from a set.
Building blocks for input rules.
ex. [abc] matches a or b or c.

Regex Negated Class [^ … ]

Matches one character NOT in the set.
Great for detecting disallowed characters.
ex. [^0-9] matches any non-digit.

Regex Range (e.g., [a-z])

Matches any character in a range.
Lets you allow letters/digits easily.
ex. [0-9], [A-Za-z].

Whitelist Regex Example

Regex that flags characters outside allowed set.
Simple and practical SQLi detection.
ex. Anything not letter/number/space is invalid.

Invalid Query (Intentional Error)

Attacker sends input that breaks SQL.
Used to learn from error behavior.
ex. Wrong column name or broken quotes.

Error-Based Recon

Learning system details from raw error messages.
Errors can reveal DB type, tables, columns.
ex. Error mentions “MySQL”, table names, or column names.

Safe Error Handling

Showing generic errors instead of raw DB errors.
Stops info leaks that help attackers.
ex. “Something went wrong” instead of SQL stack trace.

Table Names / Attribute Names Leakage

Accidental reveal of schema details through errors.
Helps attackers craft better SQLi.
ex. Error references USERS table or Password column.

Code Audit

Actively looking through code for vulnerabilities.
Finds SQLi patterns before attackers do.
ex. Reviews + tools + tests focused on security.

Code Review

Humans inspect code changes.
Experienced devs spot risky patterns.
ex. Peer review catches string-built SQL.

Static Code Analysis

Tools scan code for known bad patterns.
Scales across big codebases.
ex. Tool flags unsafe query concatenation.

Penetration Testing (Pen Testing)

Controlled attempts to break into the system.
Tests real-world attack paths.
ex. Security team tries SQLi against endpoints.

Automated Testing

Tests that run automatically to catch issues.
Prevents regressions (bugs coming back).
ex. A test that fails if SQLi becomes possible again.

Regression (Security Regression)

A fixed bug reappears after changes.
Automated tests help prevent this.
ex. A later update reintroduces SQLi risk.

Real-Life SQLi: Sony (2011)

SQLi-associated breach affecting PlayStation accounts.
Shows scale of impact.
ex. Massive account compromise + major cost.

Real-Life SQLi: Heartland (2008)

SQLi-associated breach exposing huge numbers of card records.
Shows business and financial damage.
ex. Card data theft + large monetary loss.

Real-Life SQLi: Yahoo (2012)

SQLi-associated breach leaking many credentials.
Shows credential exposure risk.
ex. Email/password leak (and weak storage worsens it).

Concurrency

Multiple things happening “at the same time” (or overlapping in time).
In DBs: many users/transactions using the same data safely.
ex. Two users updating accounts while others are reading.

Parallelism

Work literally happening at the same time on multiple CPU cores.
Concurrency can exist without true parallelism.
ex. Two threads scheduled on one core vs two cores.

Process

A mostly independent unit of work managed by the OS.
Harder for one process to mess up another.
ex. Chrome vs Word running separately.

Thread

A mostly independent unit of work inside a process.
Threads share the process’s memory and code.
ex. A GUI thread and a main thread in one app.

Process vs Thread (key difference)

Processes are isolated; threads share a lot (heap, codebase).
Sharing makes threads faster to coordinate but easier to mess up.
ex. Two threads accidentally changing the same object.

Rise of threading (why it became common)

When clockspeeds stopped rising fast, CPUs added more cores.
To use cores, programs needed multiple things happening at once.
ex. Background work thread + UI thread.

Shared memory

Memory visible to multiple threads in the same process.
Anything shared and editable can cause bugs without sync.
ex. Two threads updating the same counter.

Resource (sync context)

Anything multiple threads/transactions might use.
Can be memory, file, object, row, table, etc.
ex. A work queue; a database row.

Synchronization

Regulating access to a shared resource.
Needed only when 2+ threads might access/modify the same resource.
ex. Locking before updating shared memory.

Serialize access

Forcing one-at-a-time access to a resource.
Prevents chaos, but reduces concurrency.
ex. Only one thread can pop from a queue at once.

Lock

A mechanism that blocks others from using a resource until released.
In code: mutex; in DBs: row/table locks.
ex. Transaction locks a row until commit/rollback.

Mutex

Mutual exclusion lock: only one thread can hold it at a time.
Other threads block until it’s unlocked.
ex. “Door” that lets one thread into a critical section.

Critical section

Code that touches shared data and must not interleave with others.
Protected with a lock.
ex. Pop from shared queue + update shared state.

Winner-take-all mutex

Regular mutex doesn’t care if you read or write—only one thread enters.
Simple but can block too much.
ex. Readers blocked even though they don’t change data.

Read-write lock

Two locks: read lock (many can hold) and write lock (only one).
No one can read while a write lock is held.
ex. Many readers of cached data; single writer for updates.

Read lock

Permission to read shared data.
Multiple readers allowed at once.
ex. Several users SELECT the same row.

Write lock

Permission to modify data.
Exclusive: blocks other reads and writes.
ex. UPDATE on a row blocks other access.

Lock contention

Multiple workers want the same lock.
Causes waiting and slower throughput.
ex. Many users updating the same hot row.

Over-synchronizing

Locking more than needed.
Can turn multithreaded behavior into single-threaded behavior.
ex. Locking everything “just in case.”

Race condition

Unsynchronized (or poorly synchronized) access that depends on timing.
Hard to spot because it only happens with “just wrong” timing.
ex. Two threads pop jobs and accidentally duplicate work.

Dueling writes

Two writers change the same value close together.
Last write “wins,” but result can be wrong or inconsistent.
ex. Two updates overwrite each other.

Simultaneous read/write problem

Reading while another thread writes can return an unpredictable value.
This is why we lock around modifications.
ex. Read gets half-updated state.

Work queue race example

Two threads both read the next job before either pops it.
Result: duplicate job processing and losing another job.
ex. T1 and T2 both “save” Job A, then pop A and B.

Deadlock

Two threads/transactions block each other forever.
Each holds a lock the other needs.
ex. T1 holds Lock A waiting for Lock B; T2 holds Lock B waiting for Lock A.

Deadlock symptom

No crash—things just stop.
Hard to debug without tools/timeouts.
ex. Requests hang forever.

Blocking

A thread/transaction is paused waiting for a lock.
It resumes when the lock is released.
ex. UPDATE waits for another transaction to commit.

Thread-safe

Safe to use with multiple threads at once without extra user locking.
Often handled by libraries/OS.
ex. Many memory allocators are thread-safe.

Thread dangers (core idea)

Threads can’t “watch” each other; you don’t know what others are doing.
Any shared editable data must be synchronized.
ex. Shared list modified without a lock.

Database as a process

A DBMS runs as a process and uses OS-level locking concepts.
DB users are like threads interacting with shared data.
ex. Many client connections acting at once.

Database user vs thread analogy

Users don’t know what other users are doing.
The database must keep them from hurting each other.
ex. Two users updating the same record.

Database lock

DB version of a mutex: prevents unsafe overlapping access.
Can be row/table/etc. depending on system.
ex. Lock a row during UPDATE.

Transaction

A group of SQL statements treated as one unit.
Changes are hidden until commit or undone by rollback.
ex. Transfer money: multiple statements but one transaction.

Commit

Finish a transaction and make its changes permanent and visible.
Also releases locks.
ex. After commit, other users can see updates.

Rollback

Cancel a transaction and undo its changes.
Also releases locks.
ex. Error occurs → rollback restores old values.

Autocommit

Each statement is its own transaction by default in some DBs.
Can reduce mistakes but loses multi-statement atomic behavior.
ex. Every UPDATE commits immediately unless you disable autocommit.

ACID

Properties transactions should satisfy: Atomicity, Consistency, Isolation, Durability.
Used to reason about correctness in multiuser DBs.
ex. Banking transfer must be correct even with many users.

Atomicity

All-or-nothing: either all statements happen or none do.
Also “appears to outsiders to happen all at once.”
ex. If transfer fails mid-way, no partial balances remain.

Consistency preservation

DB constraints/rules hold at start and end of a transaction.
No constraints violated after commit.
ex. Foreign keys still valid after changes.

Isolation

Transactions should act like they run separately even if they overlap.
One transaction shouldn’t see another’s partial work.
ex. Your SELECT shouldn’t change mid-transaction due to someone else.

Durability

After commit finishes, committed data cannot be lost.
Typically requires writing to disk and logging.
ex. Power loss after commit still keeps the update.

Transaction visibility

Other users can’t see a transaction “in progress.”
They see either the old state or the committed new state.
ex. Dirty reads should not happen.

Lost update

Two users update the same item; one overwrites the other.
Happens without proper locking/isolation.
ex. Account balance update disappears.

Dirty read

Reading a value written by a transaction that later rolls back.
You saw data that never truly “happened.”
ex. See temporary balance that gets undone.