Teaching Kai About Cybersecurity: The CIA Triad, Strong Passwords, and SQL Injection Protection

Introduction

When it comes to cybersecurity, many people think of secret agents and high-tech gadgets. But in reality, protecting data online comes down to a simple yet powerful concept called the CIA triad — Confidentiality, Integrity, and Availability.

In this post, I’ll share a teaching session I had with Kai, walking him through these principles and showing how they relate to building secure login systems. We’ll cover why strong passwords matter, how to detect SQL injection attacks, and how to write database functions and stored procedures to keep our systems safe.

What Is the CIA Triad?

CIA stands for:

Confidentiality: Ensuring data is only accessible by those authorized to see it.
Integrity: Making sure data isn’t altered or tampered with by unauthorized users.
Availability: Guaranteeing data and systems are accessible when needed.

These three principles are the foundation of cybersecurity and help guide how we design secure applications.

The Conversation: Teaching Kai

Me: “Kai, think of the CIA triad as the pillars of security. Confidentiality keeps secrets safe, Integrity keeps data trustworthy, and Availability makes sure systems don’t go offline.”

Kai: “So how do we do that when building something like a login system?”

Me: “Great question! Let’s walk through how these ideas come to life with real SQL tables and code.”

Step 1: Building the Database Tables

CREATE TABLE dbo.Users (
    UserId INT PRIMARY KEY IDENTITY,
    Username NVARCHAR(100) UNIQUE NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);

CREATE TABLE dbo.Passwords (
    PasswordId INT PRIMARY KEY IDENTITY,
    UserId INT FOREIGN KEY REFERENCES dbo.Users(UserId),
    HashedPassword NVARCHAR(256) NOT NULL,
    Salt NVARCHAR(64) NOT NULL,
    LastUpdated DATETIME DEFAULT GETDATE()
);

Kai: “Why split passwords into another table?”

Me: “It’s a security best practice. This separation helps keep password data isolated and lets us store important details like salts alongside the hashes.”

Step 2: Protecting Against SQL Injection

SQL injection is a common way attackers try to mess with your database by inserting malicious commands.

We create a simple function to check inputs:

CREATE FUNCTION dbo.CheckSQLInjection(@Input NVARCHAR(MAX))
RETURNS BIT
AS
BEGIN
    IF @Input LIKE '%--%' OR @Input LIKE '%;%' OR 
       @Input LIKE '%DROP%' OR @Input LIKE '%OR 1=1%' OR
       @Input LIKE '%EXEC%' OR @Input LIKE '%UNION%'
       RETURN 1
    RETURN 0
END

Step 3: Enforcing Strong Passwords

Strong passwords are critical for confidentiality. Here’s a function that ensures passwords meet complexity requirements:

CREATE FUNCTION dbo.CheckWeakPassword(@Password NVARCHAR(MAX))
RETURNS BIT
AS
BEGIN
    IF LEN(@Password) < 8 OR
       @Password NOT LIKE '%[0-9]%' OR
       @Password NOT LIKE '%[A-Z]%' OR
       @Password NOT LIKE '%[a-z]%' OR
       @Password NOT LIKE '%[^a-zA-Z0-9]%'
       RETURN 1
    RETURN 0
END

Step 4: Creating a Secure Login Procedure

Finally, we combine everything in a stored procedure:

CREATE PROCEDURE dbo.CreateLoginAccount
    @Username NVARCHAR(100),
    @Password NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    IF dbo.CheckSQLInjection(@Username) = 1 OR dbo.CheckSQLInjection(@Password) = 1
    BEGIN
        RAISERROR('SQL injection detected.', 16, 1);
        RETURN;
    END

    IF dbo.CheckWeakPassword(@Password) = 1
    BEGIN
        RAISERROR('Weak password detected. Use at least 8 chars, mix of upper/lowercase, digits, and symbols.', 16, 1);
        RETURN;
    END

    DECLARE @Salt NVARCHAR(64) = CONVERT(NVARCHAR(64), NEWID());
    DECLARE @HashedPassword NVARCHAR(256) = CONVERT(NVARCHAR(256), HASHBYTES('SHA2_256', @Password + @Salt), 1);

    INSERT INTO dbo.Users (Username) VALUES (@Username);
    DECLARE @UserId INT = SCOPE_IDENTITY();

    INSERT INTO dbo.Passwords (UserId, HashedPassword, Salt)
    VALUES (@UserId, @HashedPassword, @Salt);
END

Step 5: Testing the System

Kai: “How do I know this works?”

Try these tests:

Valid user:

EXEC dbo.CreateLoginAccount @Username = 'KaiSamurai', @Password = 'Secure$Pass123!';

SQL injection attempt:

EXEC dbo.CreateLoginAccount @Username = 'admin''; DROP TABLE Users;--', @Password = 'StrongPass1!';

Weak password:

EXEC dbo.CreateLoginAccount @Username = 'WeakUser', @Password = '1234';

Why This Matters

CIA Principle	How We Addressed It
Confidentiality	Hashed and salted passwords, input validation
Integrity	Preventing malicious input to keep data accurate
Availability	Protecting database from attacks that cause downtime

Final Thoughts

Kai: “So it’s not just about stopping hackers – it’s about building smart systems that protect people’s data!”

Exactly. The CIA triad isn’t just theory – it’s a guide to real-world secure development. When we build login systems like this, we’re not only keeping attackers out – we’re respecting users’ trust.

If you want me to share more about login validation or password updates, just let me know!

Thanks for reading!
— Terry Jago

Search This Blog