10. MySQL Part 1: Install, Create, Insert and SELECT

By Ravindra Bagale Last updated: 15 October 2026

Chala mitrano, aata database! Pratyek app – Instagram, bank, college portal – cha khara maal database madhe asto: users, passwords che hashes, posts, payments. Attackers la shevti hech hava asta. SQL injection samjun ghyaycha asel tar aadhi SQL swatah lihita aala pahije – SELECT, WHERE, UNION, JOIN. Aaj aapan MySQL install karun database aani tables banvnar, data takanar aani SELECT che sagle prakar shiknar aahot. Pratyek query MySQL prompt var type kara – copy-paste nako.

What you will learn in this chapter

Installing MySQL/MariaDB on Amazon Linux 2023 and Ubuntu and securing it
Creating, listing, using and dropping databases
Creating tables with the right data types, and inserting rows
SELECT with WHERE, ORDER BY, LIMIT, DISTINCT and aliases
LIKE, IN, BETWEEN and NULL checks
Aggregate functions, GROUP BY and HAVING
INNER, LEFT, RIGHT and self JOINs, and subqueries

Concepts in this chapter

The chapter recap is at the end of the last concept page.

Chapter 10: MySQL part 1 — practice tasks

Open chapter lessons · Full guided lab: mysql least privilege read only user (not repeated here)

10.1 Create an orders table and query it

EasyLogistics

Why: You must read data before you can protect it.

What: Create the table from the data file in a MySQL Docker container.

Data file: shipments table

id	customer	city	amount	currency
1	Rahul Deshmukh	Aurangabad	1250	INR
2	Emily Clark	Manchester	40	GBP
3	Ahmed Ali	Dubai	300	AED

About this file: Made-up shipment records. Each row is one shipment. Columns: id = number; customer = name; city = destination; amount = practice value (not a real fee); currency = code.

How:

docker run -d --rm --name db -e MYSQL_ROOT_PASSWORD=labpass -p 127.0.0.1:3306:3306 mysql:8
Wait 20 s, then docker exec -it db mysql -uroot -plabpass
CREATE DATABASE lab; USE lab; CREATE TABLE ship(id INT PRIMARY KEY, customer VARCHAR(50), city VARCHAR(40), amount INT, currency CHAR(3));
INSERT the 3 rows.
SELECT customer, city FROM ship WHERE currency='INR';

Expected output: One row: Rahul Deshmukh | Aurangabad.

Hint

-p 127.0.0.1:3306 keeps the DB off the network.

Answer / how to check

SELECT COUNT(*) FROM ship; = 3.

10.2 Make a read-only reporting user

MediumBanking

Why: Reports should never be able to delete data.

What: Create user 'report' with SELECT only.

How:

CREATE USER 'report'@'%' IDENTIFIED BY 'Rep0rt!lab';
GRANT SELECT ON lab.* TO 'report'@'%';
Log in as report and try DELETE FROM lab.ship;

Expected output: ERROR 1142: DELETE command denied.

Hint

See the linked least-privilege lab for more.

Answer / how to check

SHOW GRANTS FOR 'report'@'%'; lists only SELECT.

10.3 Safe query from Python vs unsafe

MediumHealthcare

Why: String-built SQL is how SQL injection happens.

What: Query by city with a parameter, not string join.

How:

pip install pymysql
Unsafe: cur.execute("SELECT * FROM ship WHERE city='"+c+"'") with c="x' OR '1'='1" → all rows.
Safe: cur.execute('SELECT * FROM ship WHERE city=%s',(c,))
Compare row counts.

Expected output: Unsafe returns 3 rows; safe returns 0.

Hint

Java: PreparedStatement; Node: mysql2 with ? placeholders.

Answer / how to check

Safe version returns 0 rows for the trick input.

Extended teaching notes

Why this lesson matters

Use this lesson to strengthen systems you own: understand the risk, practise safely in a lab, then write and apply the fix.

Apply the fix across stacks

Same class of bug, same idea in every language.

Java / Spring Boot

Parameterise queries, encode output, CSRF tokens, limited Actuator, dependency scanning.

ASP.NET Core

EF Core / parameterised SQL, Razor encoding, HTTPS, Data Protection.

Node.js / Express

helmet, parameterised SQL, npm audit, avoid eval and shell concat.

Python / Django / Flask / FastAPI

ORM, autoescape, pip-audit, Bandit; never shell=True with user input.

PHP / Laravel

PDO/Eloquent, Blade escaping, composer audit.

Go / Ruby

database/sql placeholders / ActiveRecord; html/template / ERB escaping; govulncheck / Brakeman.

nginx / Apache / Tomcat

Hide versions, TLS, security headers, least-open ports.

MySQL / PostgreSQL / MongoDB / Redis

Private networks, auth on, least-privilege users, no 0.0.0.0/0.

AWS / Azure / GCP

Roles not long-lived keys, private data stores, cloud audit logs on.

Mini case study

Suppose we are on a purple-team exercise for an Indian mid-size company (sample data). Red notes a technique from this lesson against a lab clone. Blue must show the control that stops it, the log that would have fired, and the ticket that hardens production. That is the standard this course trains you for.

Defender's worksheet (do this today)

Write the lesson title, date and the exact lab target you are allowed to use.
List three owned systems (VMs, apps, or cloud accounts) where this lesson applies.
Run one safe check from the lesson on a lab system only. Paste the command and output.
Write a two-sentence fix you would give a teammate.
Name one related public advisory (CERT-In, CISA, NVD, or a vendor bulletin) with its date.

Indian workplace (sample data)

Suppose we are a junior analyst at Infosys, TCS, Wipro or a fintech SOC in Bengaluru/Pune. Turn this lesson into one checklist item with an owner, a due date and a place to store evidence (ticket + screenshot).

Global practice

Compare your notes with OWASP, CIS Benchmarks, NIST CSF/IR guidance or a cloud Well-Architected security question. Write five lines on what overlaps.

Everyday analogy

Explain the risk and the fix to a family member without jargon. If they can repeat the warning, you understand it well enough to teach juniors.

Key terms

Control: a safeguard (patch, config, process, detection).
Finding: a weakness backed by evidence.
Remediation: the fix plus how you verified it.
Scope: systems you have written permission to touch.
Least privilege: only the access the task needs.
Defence in depth: several layers so one failure does not end the story.

Common mistakes

Skipping written permission "just for a quick scan".
Practising on hostel, office or public targets without authorisation.
Collecting tool output without writing the fix.
Copying attack steps onto production to "verify". Defenders verify with versions, configs and patches.
Publishing screenshots that contain secrets, cookies or internal hostnames.

Recap

Evidence, fix, owner, date.
Own lab only.
Fix > spectacle.