Ravindra BagaleCourses & study guides मराठी Track your progress

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

10.10 Subqueries

By Last updated:

Subquery mhanje query chya aat query – aadhi aatli chalte, tiche uttar baherchi vaprte.

-- students who paid more than the average
SELECT name, fees_paid FROM students
WHERE fees_paid > (SELECT AVG(fees_paid) FROM students);

-- students enrolled in 'Ethical Hacking'
SELECT name FROM students
WHERE student_id IN (
  SELECT student_id FROM enrollments
  WHERE course_id = (SELECT course_id FROM courses WHERE course_name = 'Ethical Hacking')
);

-- EXISTS: students with at least one score above 85
SELECT s.name FROM students s
WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.score > 85);

-- subquery in FROM (derived table)
SELECT city, total FROM (
  SELECT city, SUM(fees_paid) AS total FROM students GROUP BY city
) AS t WHERE total > 20000;

Why this matters for security

Blind SQL injection relies on subqueries: AND (SELECT SUBSTRING(password,1,1) FROM users LIMIT 1) = 'a' asks the database a yes/no question one character at a time. Tools like sqlmap automate thousands of such subqueries – that is why prepared statements, not input "cleaning", are the real fix.

Ravindra Bagale's Tip

If you write WHERE id = (SELECT ...) and the subquery returns more than one row, you get the error "Subquery returns more than 1 row". Many students get stuck here. If there can be several values, use IN instead of =.

Practice task

Find the student(s) with the highest score using a subquery, and the courses that have no enrollment using NOT IN.

Thodkyaat sangaycha tar

  • Install: mariadb105-server (Amazon Linux 2023) or mysql-server (Ubuntu); run mysql_secure_installation; keep 3306 private.
  • CREATE DATABASE, USE, CREATE TABLE, DESCRIBE, INSERT INTO t (cols) VALUES (...).
  • SELECT cols FROM t WHERE ... ORDER BY ... LIMIT n; DISTINCT, aliases with AS.
  • LIKE (%, _), IN, BETWEEN (inclusive), IS NULL.
  • Aggregates COUNT SUM AVG MIN MAX + GROUP BY; WHERE filters rows, HAVING filters groups.
  • JOINs: INNER, LEFT, RIGHT, SELF; subqueries with IN, EXISTS, derived tables.
  • OR 1=1, ORDER BY n, UNION SELECT and subqueries are also the building blocks of SQL injection.

Samjla ka? Nasel tar pratyek query punha type kara aani output bagha. Aata pudhe jaauya – data badalne, keys aani users.

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)

  1. Write the lesson title, date and the exact lab target you are allowed to use.
  2. List three owned systems (VMs, apps, or cloud accounts) where this lesson applies.
  3. Run one safe check from the lesson on a lab system only. Paste the command and output.
  4. Write a two-sentence fix you would give a teammate.
  5. 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.