---

Overview

SQL (Structured Query Language) is a programming language used to interact with relational databases. It stores, retrieves, and manipulates structured data organized in a table-based format. Understanding SQL is foundational for web development, database administration, and identifying injection vulnerabilities.

---

Terminology

Term	Definition
Relational Database	Stores data in structured tables with defined relationships between them
NoSQL	Non-relational database storing data in flexible, non-tabular formats
Table	Collection of rows and columns storing structured data
Column	Defines an attribute and its data type within a table
Row	A single record within a table
Primary Key	Unique identifier for each record; only one per table
Foreign Key	Links records between two tables; multiple allowed per table
DBMS	Database Management System; software used to create and manage databases
CRUD	Create, Read, Update, Delete — the four basic database operations
Clause	SQL keyword used to filter or organize query results

---

Core Concepts

Database Types

Type	Structure	Best For
Relational (SQL)	Tables with rows and columns	Structured, consistent data
Non-Relational (NoSQL)	Flexible non-tabular formats	Unstructured or user-generated data

Common DBMS: MySQL, MariaDB, MongoDB, SQLite

Relational databases link tables together through keys rather than storing all related data in one place — a users table and a posts table can each stay lean by referencing a shared id/user_id key instead of duplicating user details inside every post. This table-to-table relationship is called a schema.

Relational Database Table Relationships

NoSQL databases skip tables, rows, columns, and schemas entirely in favor of flexible storage models — useful when the data isn’t well-structured or the shape varies between records. Four common storage models:

• Key-Value — every entry is a key paired with a value (string, JSON, or any object); resembles a dictionary/hash map
• Document-Based — stores self-contained documents (e.g., JSON/BSON) rather than fixed rows
• Wide-Column — rows can have a different set of columns from one another, unlike a fixed relational schema
• Graph — stores nodes and the relationships between them, optimized for traversing connections

MongoDB is a common example of a document-based NoSQL database.

NoSQL Key-Value Storage Example

Key-Value example:

{
  "100001": {
    "date": "01-01-2021",
    "content": "Welcome to this web application."
  },
  "100002": {
    "date": "02-01-2021",
    "content": "This is the first post on this web app."
  }
}

The key is usually a string; the value can be a string, a nested object, or any class object — comparable to a dictionary literal in Python or PHP ({'key': 'value'}).

NoSQL databases are exploited differently than relational ones — NoSQL injection targets the query syntax of the specific NoSQL engine (e.g., MongoDB operator injection) rather than SQL syntax.

DBMS Core Features

Feature	Description
Concurrency	Ensures multiple simultaneous users can interact with the database without corrupting or losing data
Consistency	Keeps data valid and consistent across the database despite concurrent interactions
Security	Fine-grained access control through authentication and permissions, preventing unauthorized viewing or editing
Reliability	Databases can be backed up and rolled back to a previous state after data loss or a breach
Structured Query Language	SQL provides an intuitive, standardized syntax for interacting with the database

Architecture

A typical web application does not talk to the database directly:

1. The application server receives user interactions (logins, comments, searches) via API calls or HTTP requests
2. Middleware translates these events into the format the DBMS expects, using drivers/libraries specific to that DBMS
3. The DBMS executes the requested operation (insert, retrieve, delete, update) and returns data or an error code

The application server and DBMS can run on the same host, but databases handling large amounts of data are typically hosted separately for performance and scalability.

Table Structure

• Columns — define attributes and data types (string, integer, float, date)
• Rows — individual records stored in the table
• Primary Key — unique identifier per record; one per table
• Foreign Key — references a primary key in another table to link data; multiple allowed

MySQL Usage

Connecting

mysql | mysql -u root -p

Database Operations

Command	Description
CREATE DATABASE name;	Create a new database
SHOW DATABASES;	List all databases
USE name;	Select a database to work with
DROP DATABASE name;	Delete a database

Table Operations

Command	Description
CREATE TABLE name (...);	Create a new table
SHOW TABLES;	List all tables in current database
DESCRIBE name;	Show table structure and column types
ALTER TABLE name ADD col INT;	Add a new column
ALTER TABLE name RENAME COLUMN old TO new;	Rename a column
ALTER TABLE name MODIFY col DATE;	Change a column’s data type
ALTER TABLE name DROP col;	Remove a column
DROP TABLE name;	Delete a table

Column Properties

Set when creating or altering a table to define constraints on individual columns:

Property	Effect
NOT NULL	Column is required — cannot be left empty
UNIQUE	Every value inserted into the column must be distinct
DEFAULT	Specifies a fallback value if none is provided (e.g., DEFAULT NOW() for a timestamp)
AUTO_INCREMENT	Automatically increments an integer column (typically an ID) by one per new row
PRIMARY KEY (col)	Uniquely identifies each record in the table

Example:

CREATE TABLE logins (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    date_of_joining DATETIME DEFAULT NOW(),
    PRIMARY KEY (id)
);

CRUD Operations

Operation	Command
Create	INSERT INTO table VALUES (...);
Read (all)	SELECT * FROM table;
Read (specific)	SELECT col1, col2 FROM table;
Update	UPDATE table SET col=value WHERE condition;
Delete	DELETE FROM table WHERE condition;

Clauses

Clause	Purpose
FROM	Specifies the source table
WHERE	Filters rows by condition
DISTINCT	Returns only unique values
GROUP BY	Groups rows sharing a value
ORDER BY	Sorts results (ASC/DESC)
HAVING	Filters grouped results

Examples: SELECT DISTINCT name FROM books; | SELECT * FROM books ORDER BY published_date ASC;

Operators

Operator	Description
LIKE	Pattern matching
AND	All conditions must be true
OR	At least one condition must be true
NOT	Negates a condition
BETWEEN	Value within a range

Comparison Operators: Equal Sign != < > <= >=

Symbol equivalents: && (AND), || (OR), ! (NOT)

Operator Precedence

Order of evaluation, highest to lowest:

1. Division (/), Multiplication (*), Modulus (%)
2. Addition (+), Subtraction (-)
3. Comparison (Equal Sign, >, <, <=, >=, !=, LIKE)
4. NOT
5. AND
6. OR

Arithmetic inside a condition is resolved first, then comparisons, then logical operators top to bottom. In MySQL any non-zero value evaluates as true (typically returned as 1); 0 is false.

Functions

• String: CONCAT() GROUP_CONCAT() SUBSTRING() LENGTH()
• Aggregate: COUNT() SUM() MAX() MIN()

SQLite Usage

Command	Description
sqlite3 example.db	Open a SQLite database
.tables	List all tables
PRAGMA table_info(name);	Show table structure
SELECT * FROM name;	Query all records

---

Related Concepts

• Website Innerworkings
• OWASP Top 10 - 2021

Related Techniques

• SQL Injection

---

References / Images

• Relational Database Table Relationships
• NoSQL Key-Value Storage Example
• SQL syntax reference examples