Post Page Advertisement [Top]



Click here to send WhatsApp On Unsaved Mobile Numbers For Free

 

Ethical Hacking SQL Injection Fundamentals — Understanding the Vulnerability & How to Prevent It |  Day 16
Ethical Hacking

🛡️ Ethical Hacking — Day 16

SQL Injection Fundamentals — Understanding the Vulnerability & How to Prevent It

⚠️ LEARNING PURPOSE ONLY: This tutorial is strictly for educational and defensive cybersecurity learning. Perform the practical exercises only against your local OWASP Juice Shop lab, applications you own, or systems for which you have explicit permission. Never test SQL injection against random websites, production systems, or databases you do not own.

Welcome to Day 16.

Today we begin one of the most important topics in web application security:

💉 SQL Injection — SQLi

SQL Injection happens when an application incorrectly combines untrusted user input with SQL code, allowing the input to influence the structure or meaning of the database query. OWASP recommends parameterized queries/prepared statements as the primary defense. (OWASP Cheat Sheet Series)


🎯 Today's Learning Objectives

By the end of Day 16, you should understand:

  • What SQL is

  • What a database query looks like

  • How web applications communicate with databases

  • What SQL Injection means

  • Why string concatenation is dangerous

  • Safe vs unsafe SQL queries

  • Input vs SQL code

  • Error-based indicators

  • Boolean-based testing concepts

  • SQL Injection through Burp Suite

  • Parameterized queries

  • Prepared statements

  • Input validation

  • Least-privilege database accounts

  • Practical exercises on:

    • 🐧 Linux

    • 🪟 Windows

    • 🍎 macOS

  • Safe SQLi testing inside your local lab


1. What Is SQL?

SQL stands for:

Structured Query Language

It is used to interact with relational databases.

Examples of relational databases include:

  • PostgreSQL

  • MySQL

  • MariaDB

  • Microsoft SQL Server

  • Oracle Database

  • SQLite

A simple query:

SELECT * FROM users;

means:

Retrieve all columns from the users table.


2. Basic SQL Concepts

A relational database might look like:

Database
│
├── users
│    ├── id
│    ├── name
│    ├── email
│    └── role
│
├── products
│    ├── id
│    ├── name
│    └── price
│
└── orders
     ├── id
     ├── user_id
     └── total

Think of:

Database
   ↓
Tables
   ↓
Rows
   ↓
Columns

3. Example Table

Imagine:

idnameemailrole
1Alicealice@example.testuser
2Bobbob@example.testuser
3Adminadmin@example.testadmin

A query:

SELECT name, email
FROM users
WHERE id = 1;

returns Alice's information.


4. How a Web Application Uses SQL

Imagine a web application has:

GET /user?id=1

The application might need to retrieve:

User #1

The server may execute a query.

Conceptually:

Browser
   │
   │ id=1
   ▼
Web Application
   │
   │ SQL query
   ▼
Database
   │
   │ Result
   ▼
Web Application
   │
   ▼
Browser

This is where SQL Injection can become relevant.


5. Unsafe Query Construction

Consider this example:

String id = request.getParameter("id");

String sql =
    "SELECT * FROM users WHERE id = " + id;

The problem is that user-controlled input is directly concatenated into SQL.

OWASP specifically identifies dynamically constructed queries using string concatenation with user input as a common cause of SQL Injection. (OWASP Cheat Sheet Series)


6. Why Is This Dangerous?

The application intended:

User Input
   ↓
Data

But unsafe construction can cause the database to interpret part of that input as:

SQL syntax

The fundamental problem is:

DATA
  +
SQL CODE
  ↓
Ambiguous interpretation

7. The Important Concept

Remember:

SQL Injection is fundamentally a separation problem.

The application should keep:

SQL CODE

separate from:

USER DATA

A secure parameterized query does exactly that.


8. Safe Query

Instead of:

String sql =
    "SELECT * FROM users WHERE id = " + id;

use:

String sql =
    "SELECT * FROM users WHERE id = ?";

Then bind the value separately:

PreparedStatement ps =
    connection.prepareStatement(sql);

ps.setInt(1, id);

Now the database knows:

SQL structure → code
id            → data

OWASP recommends prepared statements/parameterized queries because they keep SQL code and parameter data separate. (OWASP Cheat Sheet Series)


9. Unsafe vs Safe

❌ Unsafe

"SELECT * FROM users WHERE name='" + username + "'"

✅ Safer

"SELECT * FROM users WHERE name=?"

and:

setString(1, username)

10. SQL Injection Impact

Depending on the application and database configuration, SQL Injection can potentially allow unauthorized:

READ
CREATE
UPDATE
DELETE

operations.

In serious cases, SQL Injection can expose large amounts of sensitive information or allow modification of database contents. OWASP notes that SQL Injection can affect confidentiality, integrity, and sometimes other system capabilities. (OWASP Cheat Sheet Series)

That's why it is important to understand.


11. SQL Injection Classes

OWASP broadly describes SQL Injection as including:

In-band

The application directly returns useful database results through the same channel.

Request
  ↓
SQL Injection
  ↓
Database
  ↓
Response contains result

Blind / Inferential

The application doesn't directly return the data, but behavior changes based on the database result.

Request
   ↓
Database
   ↓
Different behavior
   ↓
Observe response

Out-of-band

Information is returned through a separate channel.

For today's lesson, focus on the first two concepts. (OWASP Cheat Sheet Series)


12. Our Safe Learning Model

We're going to use:

Your Computer
     ↓
Docker
     ↓
OWASP Juice Shop
     ↓
Burp Suite

Not:

❌ Public website
❌ Production database
❌ Customer application
❌ Random API

13. Start Juice Shop

Check the container.

Linux

docker ps

Windows PowerShell

docker ps

macOS

docker ps

If it isn't running:

docker start <container-id>

14. Test Juice Shop

Linux/macOS

curl -I http://127.0.0.1:3000

Windows

curl.exe -I http://127.0.0.1:3000

You should get an HTTP response.


15. Open Burp

Open:

Burp Suite
   ↓
Proxy
   ↓
Open Browser

Visit:

http://127.0.0.1:3000

Make sure your Burp target scope contains:

127.0.0.1:3000

16. Find a Search Request

Juice Shop contains search functionality.

Use the application normally.

For example, enter a normal search term such as:

apple

Then open:

Proxy
   ↓
HTTP history

Look for the request generated by the search.

It may resemble:

GET /rest/products/search?q=apple HTTP/1.1
Host: 127.0.0.1:3000

The exact endpoint can vary by Juice Shop version.


17. Understand the Request

Identify:

Method:
GET

Endpoint:
/rest/products/search

Parameter:
q

Value:
apple

So conceptually:

/rest/products/search?q=apple
                       │
                       └── User input

This is exactly the type of input that should be investigated during an authorized security assessment.


18. Send It to Repeater

Right-click the request:

Send to Repeater

Open:

Repeater

First, send the original request.

This is your baseline.

Record:

Status:
Response length:
Response type:

19. Baseline First

Suppose:

q=apple

returns:

200 OK

with:

Normal search results

That's your baseline.

Always establish:

Normal input
     ↓
Normal response

before testing anything unusual.


20. Test Input Handling First

Before SQL-specific testing, try harmless inputs.

For example:

q=apple123

or:

q=test

Observe:

Status
Response length
Results
Errors

This teaches you how the application handles ordinary input.


21. Special Characters

In your local Juice Shop lab, you can test how the search parameter responds to a single quote:

'

For example:

q='

In URL form, it may appear encoded.

The purpose is not to exploit the application.

We're observing whether unusual input causes:

Normal response
Validation error
Database-related error
Different response

22. Why Does ' Matter?

A single quote can have special meaning inside SQL string literals.

For example:

SELECT *
FROM products
WHERE name = 'apple';

The quote characters define the string.

If unsafe application code inserts user input directly into that query, unexpected quote characters can affect query construction.

That's why this simple input can be useful as a first diagnostic indicator.


23. Important Warning

A response such as:

500 Internal Server Error

does not automatically prove SQL Injection.

It could be caused by:

Input validation
Application bug
Encoding issue
Unhandled exception
Database error
Something else

You need evidence.


24. Error-Based Indicator

Suppose:

q=normal

returns:

200 OK

but:

q='

returns:

500 Internal Server Error

This is interesting.

Document:

Input:
'

Baseline:
200

Test:
'

Result:
500

Then investigate further inside the lab.


25. Don't Dump Database Information

Today's goal is to understand the vulnerability.

Do not turn a simple lab test into:

❌ Extract every table
❌ Dump all users
❌ Extract passwords
❌ Modify records
❌ Delete records

Even in a training lab, learn the professional workflow:

Detect
 ↓
Confirm minimally
 ↓
Understand
 ↓
Document
 ↓
Fix

26. Boolean-Based Concept

A common SQL Injection testing concept is comparing two logically different conditions.

For example:

Condition A

versus:

Condition B

If the application responds differently, that may indicate that user input is influencing query logic.

For training, think:

Input
  ↓
Database condition
  ↓
True / False
  ↓
Different response?

This is the foundation of blind SQL Injection testing.


27. Don't Memorize Payloads

You will find hundreds of SQL Injection payload lists online.

Don't make this course about memorizing strings.

Instead understand:

Input
 ↓
Application
 ↓
SQL Query
 ↓
Database
 ↓
Response

Once you understand that pipeline, SQLi becomes much easier to reason about.


28. SQL Query Example

Imagine the application intends to execute:

SELECT * FROM products
WHERE name = 'apple';

The developer's intention is:

name = user input

But if user input becomes part of the SQL syntax itself, the query's meaning can change.

That's the fundamental vulnerability.


29. Parameterized Version

Secure approach:

SELECT *
FROM products
WHERE name = ?;

Then:

Parameter:
apple

is supplied separately.

Conceptually:

SQL:
SELECT * FROM products WHERE name = ?

             +

Data:
apple

The database treats apple as data rather than SQL syntax.


30. Java Example

Since you've worked with Java/Spring Boot, this is particularly important.

❌ Vulnerable

String sql =
    "SELECT * FROM users WHERE username = '" + username + "'";

Statement statement = connection.createStatement();

ResultSet result =
    statement.executeQuery(sql);

31. Java PreparedStatement

✅ Safer

String sql =
    "SELECT * FROM users WHERE username = ?";

PreparedStatement ps =
    connection.prepareStatement(sql);

ps.setString(1, username);

ResultSet result =
    ps.executeQuery();

This separates SQL code from the user-provided value. OWASP specifically recommends this pattern. (OWASP Cheat Sheet Series)


32. Spring Data / JPA

If you're using Spring Boot with JPA, prefer repository methods or parameterized queries.

For example:

@Query("""
    SELECT u
    FROM User u
    WHERE u.username = :username
""")
Optional<User> findByUsername(
    @Param("username") String username
);

The parameter:

:username

is not string-concatenated into the query.


33. Laravel Example

Since you've worked with Laravel, compare:

❌ Avoid

DB::select(
    "SELECT * FROM users WHERE email = '$email'"
);

✅ Better

DB::select(
    'SELECT * FROM users WHERE email = ?',
    [$email]
);

Or preferably use Laravel's query builder/Eloquent appropriately:

User::where('email', $email)->first();

34. MySQL Example

A vulnerable pattern:

SELECT *
FROM users
WHERE username = 'USER_INPUT';

Safe application design:

SELECT *
FROM users
WHERE username = ?;

The application binds the value separately.


35. PostgreSQL Example

The same principle applies to PostgreSQL:

SELECT *
FROM users
WHERE username = $1;

The application binds:

$1 = user input

rather than constructing SQL with string concatenation.


36. Database Least Privilege

Parameterized queries are the primary defense, but defense-in-depth matters too.

Suppose your application only needs:

SELECT

It shouldn't necessarily have:

DROP DATABASE
CREATE ROLE
SUPERUSER

privileges.

OWASP recommends minimizing database privileges as an additional defense. (OWASP Cheat Sheet Series)


37. Why Least Privilege Matters

Imagine an application has a SQL Injection vulnerability.

If the database account has:

SELECT only

the potential impact can be much smaller than if the account has:

Full database administration

So:

Parameterized Queries
        +
Least Privilege
        +
Input Validation
        +
Secure Error Handling

provides stronger defense.


38. Input Validation

Validation can be a useful additional defense.

For example, if an ID must be an integer:

1
25
100

reject:

abc
unexpected-format

But remember:

Input validation is not a replacement for parameterized queries.

OWASP recommends parameterized queries as the primary defense and allow-list validation where appropriate. (OWASP Cheat Sheet Series)


39. Don't Rely on Escaping Alone

A common misconception is:

"I'll escape the quote character and SQL Injection is solved."

OWASP strongly discourages relying on escaping all user input as the primary defense because it is fragile and database/context dependent. (OWASP Cheat Sheet Series)

Prefer:

Prepared Statements
+
Parameterized Queries

40. Error Messages

A vulnerable application may expose database errors.

For example:

SQL syntax error
Database exception
Table name
Column name
Internal stack trace

Production applications should avoid exposing unnecessary internal details.

A user-facing response should be generic:

Something went wrong.

while detailed information goes to secure server-side logs.


41. SQL Injection Testing Workflow

Memorize this:

Define Scope
     ↓
Identify Input
     ↓
Establish Baseline
     ↓
Send Harmless Test
     ↓
Observe Response
     ↓
Look for Differences
     ↓
Confirm Minimally
     ↓
Document
     ↓
Recommend Fix

🧪 Day 16 Practical Lab — Part 1

Step 1 — Start Juice Shop

Linux/macOS

docker ps

Windows

docker ps

Step 2 — Open Juice Shop

http://127.0.0.1:3000

Step 3 — Start Burp

Proxy
 ↓
Open Browser

Step 4 — Search

Use a normal search term:

apple

Step 5 — HTTP History

Open:

Proxy
 ↓
HTTP history

Find the search request.


Step 6 — Send to Repeater

Right click
 ↓
Send to Repeater

Step 7 — Baseline

Send:

q=apple

Record:

Status:
Length:
Response:

🧪 Day 16 Practical Lab — Part 2

Now test harmless variations.

Test A

q=test

Test B

q=apple123

Test C

A single quote:

q='

Record:

TestInputStatusLengthObservation
Aapple______Baseline
Btest_________
Capple123_________
D'_________

Again:

A changed response is an indicator, not automatically proof of SQL Injection.


🧪 Day 16 Practical Lab — Part 3

Try URL encoding.

For example, compare:

'

with its URL-encoded representation:

%27

Observe whether your local application treats them differently.

This teaches an important concept:

Browser Input
    ↓
URL Encoding
    ↓
HTTP Request
    ↓
Application Decoding
    ↓
Database Layer

🐧 42. Linux Practical Commands

Check Docker:

docker ps

Check Juice Shop:

curl -I http://127.0.0.1:3000

Search:

curl "http://127.0.0.1:3000/rest/products/search?q=apple"

Verbose:

curl -v "http://127.0.0.1:3000/rest/products/search?q=apple"

URL-encoded quote:

curl "http://127.0.0.1:3000/rest/products/search?q=%27"

Only use these against your local Juice Shop instance.


🪟 43. Windows Practical Commands

Check:

docker ps

Test:

curl.exe -I http://127.0.0.1:3000

Search:

curl.exe "http://127.0.0.1:3000/rest/products/search?q=apple"

Verbose:

curl.exe -v "http://127.0.0.1:3000/rest/products/search?q=apple"

Encoded quote:

curl.exe "http://127.0.0.1:3000/rest/products/search?q=%27"

🍎 44. macOS Practical Commands

Check:

docker ps

Test:

curl -I http://127.0.0.1:3000

Search:

curl "http://127.0.0.1:3000/rest/products/search?q=apple"

Verbose:

curl -v "http://127.0.0.1:3000/rest/products/search?q=apple"

Encoded quote:

curl "http://127.0.0.1:3000/rest/products/search?q=%27"

⚠️ Important About Juice Shop Endpoints

The exact Juice Shop API routes and behavior can change between releases.

Therefore, don't assume the example endpoint above exists in your installation.

The correct approach is:

Browse Juice Shop
       ↓
HTTP History
       ↓
Find actual request
       ↓
Copy actual endpoint
       ↓
Send to Repeater

This is also a valuable ethical-hacking skill.


45. Analyze the Request in Burp

You might see:

GET /rest/products/search?q=apple HTTP/1.1
Host: 127.0.0.1:3000
Accept: application/json

Identify:

Method:
GET

Endpoint:
/rest/products/search

Parameter:
q

Value:
apple

46. Analyze the Response

Look for:

HTTP Status
Content-Type
Content-Length
Response Body

For example:

HTTP/1.1 200 OK
Content-Type: application/json

Then inspect the JSON.


47. Build a SQLi Evidence Table

Target:
127.0.0.1:3000

Endpoint:
____________________

Parameter:
____________________

Baseline:
____________________

Test Input:
____________________

Status Change:
____________________

Response Change:
____________________

Error Message:
____________________

Evidence:
____________________

48. Vulnerable vs Secure Code Review

Vulnerable

String sql =
    "SELECT * FROM products WHERE name = '"
    + search
    + "'";

statement.executeQuery(sql);

Why?

User input
   ↓
String concatenation
   ↓
SQL query

49. Secure

String sql =
    "SELECT * FROM products WHERE name = ?";

PreparedStatement ps =
    connection.prepareStatement(sql);

ps.setString(1, search);

ps.executeQuery();

Flow:

SQL structure
     +
Parameter
     ↓
Database

50. Secure Code Review Checklist

When reviewing code, search for:

"SELECT ... " + userInput
"UPDATE ... " + userInput
"DELETE ... " + userInput
"INSERT ... " + userInput

These patterns deserve investigation.

But remember:

Finding string concatenation alone doesn't prove exploitability; understand how the query is constructed and executed.


51. ORM Does Not Automatically Mean Safe

Using:

JPA
Hibernate
Eloquent
Django ORM
SQLAlchemy

can reduce risk.

But developers can still introduce unsafe raw/dynamic SQL.

OWASP notes that ORMs and stored procedures can still be used unsafely if they ultimately construct dynamic SQL. (OWASP Cheat Sheet Series)


52. SQL Injection in Your Own Applications

Since you develop web applications, this lesson is especially useful during code review.

Whenever you create:

Search
Login
Filter
Sort
Report
Export
Advanced Search

ask:

Does user input become part of an SQL statement?

If yes:

Use parameterization.

53. Example: Search Feature

❌ Don't do:

$sql = "SELECT *
        FROM products
        WHERE name LIKE '%$search%'";

✅ Do:

$sql = "SELECT *
        FROM products
        WHERE name LIKE ?";

and bind:

%search%

as the parameter.


54. Example: PostgreSQL

With a PostgreSQL client/library, use its parameter-binding mechanism.

Conceptually:

SELECT *
FROM users
WHERE email = $1;

Then:

$1 = user-provided email

Do not build:

"... WHERE email = '" + email + "'"

55. Example: MySQL

Use placeholders supported by your driver:

SELECT *
FROM users
WHERE email = ?;

Then bind the email separately.


56. SQL Injection Prevention Formula

Remember:

                    ┌─────────────────────┐
                    │ Parameterized Query │
                    └──────────┬──────────┘
                               │
User Input ────────────────────┤
                               ▼
                         Database

Not:

User Input
    │
    ▼
String Concatenation
    │
    ▼
SQL Query

57. Defense in Depth

A strong application should combine:

Parameterized Queries
        +
Input Validation
        +
Least-Privilege DB Account
        +
Safe Error Handling
        +
Secure Logging
        +
Regular Security Testing

OWASP recommends parameterized queries as the primary defense, with allow-list validation and least privilege as additional controls. (OWASP Cheat Sheet Series)


📝 Day 16 Assignment

Answer these questions:

1.

What does SQL stand for?

2.

What is SQL Injection?

3.

Why is string concatenation dangerous when constructing SQL?

4.

What is a parameterized query?

5.

What is a prepared statement?

6.

Why is a single quote potentially interesting during SQLi testing?

7.

Does a 500 response automatically prove SQL Injection?

8.

What is the difference between in-band and blind SQL Injection?

9.

Why should you establish a baseline before testing?

10.

Why is input validation not a replacement for parameterized queries?

11.

Why should a web application's database account use least privilege?

12.

Can an ORM still be used incorrectly?

13.

Why should SQL Injection testing be performed only against authorized targets?

14.

What information should you redact from screenshots?

15.

What is the safest primary defense against SQL Injection?


🏆 Day 16 Challenge

Create a comparison between vulnerable and secure code.

Vulnerable

User Input
    ↓
String Concatenation
    ↓
SQL Query
    ↓
Database

Secure

User Input
    ↓
Parameter
    ↓
Prepared Statement
    ↓
Database

Then write:

"SQL Injection occurs when untrusted input can influence SQL syntax. The primary defense is to separate SQL code from data using parameterized queries/prepared statements."


📊 Day 16 Lab Report

Complete:

FieldResult
OSLinux / Windows / macOS
Juice Shop URL______
Endpoint tested______
Parameter______
Normal input______
Normal status______
Test input______
Test status______
Response difference______
SQL error observed?Yes / No
Evidence______
Conclusion______

🔐 Day 16 Ethical Rule

LEARNING PURPOSE ONLY: SQL Injection can potentially expose, modify, or destroy database information. Today's exercises are strictly for your local Juice Shop lab or another environment where you have explicit authorization.

If you discover a potentially vulnerable endpoint:

Don't:
❌ Dump the database
❌ Extract passwords
❌ Modify production data
❌ Delete records
❌ Continue testing unrelated systems

Do:
✅ Confirm minimally
✅ Record evidence
✅ Stop when sufficient
✅ Understand the root cause
✅ Recommend a fix

✅ Day 16 Summary

Today you learned:

  • SQL

  • Databases

  • Tables and rows

  • SQL queries

  • Web application/database communication

  • SQL Injection

  • Unsafe string concatenation

  • Prepared statements

  • Parameterized queries

  • SQLi indicators

  • Baselines

  • Error-based indicators

  • Boolean/blind SQLi concepts

  • URL encoding

  • Burp Repeater

  • SQLi code review

  • Java/JDBC examples

  • Spring/JPA examples

  • Laravel examples

  • PostgreSQL

  • MySQL

  • Linux commands

  • Windows PowerShell commands

  • macOS commands

  • Least-privilege database accounts

  • SQLi prevention

🔑 Most Important Lesson

Never build SQL by concatenating untrusted user input into the query. Keep SQL code and user data separate with parameterized queries/prepared statements. (OWASP Cheat Sheet Series)


🔜 Day 17 — SQL Injection: Deeper Lab Analysis

Tomorrow we'll go deeper into SQL Injection analysis inside the local Juice Shop lab, including:

  • How to distinguish a normal error from a SQLi indicator

  • Query parameters vs body parameters

  • Boolean-response comparison

  • Burp Repeater methodology

  • URL encoding

  • Reading database-related errors

  • Blind SQLi concept

  • Secure code review

  • Parameterized queries in Java, PHP, Laravel and PostgreSQL

  • Linux, Windows and macOS practical exercises

  • How developers remediate SQL Injection

🔐 LEARN → OBSERVE → TEST ONLY YOUR LAB → ANALYZE → FIX → SECURE.

No comments:

Post a Comment

Bottom Ad [Post Page]

rrkksinha.