VINS CHRISTIAN COLLEGE OF ENGINEERING

CHUNKANKADAI

DEPARTMENT OF COMPUTER SCIENCE AND

ENGINEERING (Cyber Security)

CB3412-DATABASE MANAGEMENT SYSTEMS

AND SECURITY LABORATORY
 VINS CHRISTIAN COLLEGE OF ENGINEERING
Chunkankadai, Nagercoil, Kanyakumari District-629 807

REGISTER NUMBER:

Certified that is a bonafide record of work done by the candidate

Mr./Mrs._________________________________________________________

Of B.E CSE (Cyber Security)

This record is submitted for Anna University Practical Examination,

held on __________________________________________________________

Signature of the Head of the Department Signature of the lab-in-Charge

INTERNAL EXAMINER EXTERNAL EXAMINER

 INDEX

Sl. Page
Date Program Name Signature
No. No
 Ex.No:1 Create a database table, add constraints (primary key,
unique, check, Not null), insert rows, update and delete
rows using SQL DDL and DML commands.
Date :

AIM:

To create a database table with appropriate constraints and perform DML operations like
INSERT, UPDATE, and DELETE using SQL.

ALGORITHM:

Step 1: Start SQL Server / MySQL / PostgreSQL client.

Step 2: Create a new database (if not using an existing one).
Step 3: Create a table using the CREATE TABLE statement.
Step 4: Add constraints such as:
 PRIMARY KEY
 UNIQUE
 NOT NULL
 CHECK
Step 5: Insert rows into the table using the INSERT INTO statement.
Step 6: Update existing data using the UPDATE statement.
Step 7: Delete records using the DELETE statement.
Step 8: Query the table to verify operations using the SELECT statement.

PROGRAM:

Step 1: Create a new database

CREATE DATABASE StudentDB;

USE StudentDB;

Step 2: Create table with constraints

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

 Email VARCHAR(100) UNIQUE,

Age INT CHECK (Age >= 18),

Course VARCHAR(50) NOT NULL

);

Step 3: Insert rows into the table

INSERT INTO Students (StudentID, Name, Email, Age, Course)

VALUES

(1, 'Alice Johnson', 'alice@example.com', 20, 'Computer Science'),

(2, 'Bob Smith', 'bob@example.com', 22, 'Mathematics'),

(3, 'Charlie Brown', 'charlie@example.com', 19, 'Physics');

Step 4: Update a row

UPDATE Students

SET Age = 21

WHERE StudentID = 3;

Step 5: Delete a row

DELETE FROM Students

WHERE StudentID = 2;

Step 6: Display final table

SELECT * FROM Students;

OUTPUT:

StudentID Name Email Age Course

1 Alice Johnson alice@example.com 20 Computer Science
3 Charlie Brown charlie@example.com 21 Physics

RESULT:

The database table was successfully created with constraints. Data insertion, update,
and deletion operations were performed correctly and verified using the SELECT statement.
 Ex.No:2
Create set of tables, add foreign key constraints
Date : and incorporate referential integrity.

AIM:

To design and create multiple tables in a relational database, apply foreign key constraints,
and maintain referential integrity using SQL DDL and DML commands.

ALGORITHM:

Step 1: Start the SQL environment (MySQL/PostgreSQL/SQL Server).

Step 2: Create a new database.
Step 3: Create a parent table (e.g., Departments).
Step 4: Create a child table (e.g., Employees) referencing the parent table
Step 5: Apply appropriate constraints:
 PRIMARY KEY
 UNIQUE
 NOT NULL
 CHECK

Step 6: Insert valid rows into both tables.

Step 7: Attempt to insert invalid foreign key data to observe referential integr ity.
Step 8: Query to confirm table data and relationships.

PROGRAM

Step 1: Create a new database

CREATE DATABASE CompanyDB;

USE CompanyDB;

Step 2: Create the parent table (Departments)

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50) NOT NULL UNIQUE

);

Step 3: Create the child table (Employees) with foreign key

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(100) NOT NULL,

Age INT CHECK (Age >= 18),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

ON DELETE CASCADE

ON UPDATE CASCADE

);

Step 4: Insert data into Departments

INSERT INTO Departments (DeptID, DeptName)

VALUES

(101, 'Human Resources'),

(102, 'Engineering'),

(103, 'Finance');

Step 5: Insert data into Employees

INSERT INTO Employees (EmpID, EmpName, Age, DeptID)

VALUES

(1, 'Alice Johnson', 28, 102),

(2, 'Bob Smith', 35, 101),

(3, 'Carol Williams', 30, 103);

Step 6: Try inserting invalid foreign key value (should fail)

-- This will fail because DeptID 999 does not exist in Departments

INSERT INTO Employees (EmpID, EmpName, Age, DeptID)

VALUES (4, 'David Brown', 40, 999);

Step 7: Display records with JOIN

SELECT E.EmpID, E.EmpName, E.Age, D.DeptName

FROM Employees E

JOIN Departments D ON E.DeptID = D.DeptID;

OUTPUT:

After successful operations, the joined result should be:

EmpID EmpName Age DeptName

1 Alice Johnson 28 Engineering
2 Bob Smith 35 Human Resources
3 Carol Williams 30 Finance

Invalid insert attempt:

Error: Cannot add or update a child row: a foreign key constraint fails.

RESULT:

The system calls are demonstrated, and the child process successfully executes a new program. The
parent process waits for completion and lists directory contents.
 Ex.No:3
Query the database tables using different ‘where’ clause
conditions and also implement aggregate functions.
Date :

AIM:

To query database tables using various WHERE clause conditions and apply aggregate
functions to analyze data using SQL.

ALGORITHM:

1. Start the SQL environment and create a new database.

2. Create a table (e.g., Employees) with columns suitable for applying filters and aggregation.
3. Insert sample data into the table.
4. Query the table using WHERE with conditions:
o Relational operators (=, !=, >, <, etc.)
o Logical operators (AND, OR, NOT)
o Range (BETWEEN)
o Set membership (IN)
o Pattern matching (LIKE)
5. Use aggregate functions:
o COUNT()
o SUM()
o AVG()
o MAX()
o MIN()
6. Display and interpret the results.

PROGRAM:

Step 1: Create a new database

CREATE DATABASE QueryLabDB;

USE QueryLabDB;

Step 2: Create a table

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(100),

Department VARCHAR(50),
 Salary DECIMAL(10,2),

Age INT

);

Step 3: Insert sample data

INSERT INTO Employees (EmpID, Name, Department, Salary, Age)

VALUES

(1, 'Alice', 'Engineering', 75000, 28),

(2, 'Bob', 'HR', 50000, 35),

(3, 'Carol', 'Engineering', 82000, 30),

(4, 'David', 'Finance', 60000, 45),

(5, 'Eve', 'Engineering', 78000, 25),

(6, 'Frank', 'HR', 52000, 32);

✅ PART 1: WHERE Clause Examples

1. Employees with salary greater than 60,000

SELECT * FROM Employees

WHERE Salary > 60000;

2. Employees in Engineering department

SELECT * FROM Employees

WHERE Department = 'Engineering';

3. Employees aged between 30 and 40

SELECT * FROM Employees

WHERE Age BETWEEN 30 AND 40;

4. Employees not in the HR department

SELECT * FROM Employees

WHERE Department != 'HR';

5. Employees whose names start with 'A'

SELECT * FROM Employees

WHERE Name LIKE 'A%';

✅ PART 2: Aggregate Function Examples

1. Total number of employees

SELECT COUNT(*) AS TotalEmployees FROM Employees;

2. Average salary of all employees

SELECT AVG(Salary) AS AverageSalary FROM Employees;

3. Highest salary in the company

SELECT MAX(Salary) AS MaxSalary FROM Employees;

4. Total salary paid to Engineering department

SELECT SUM(Salary) AS TotalEngineeringSalary

FROM Employees
WHERE Department = 'Engineering';

5. Minimum age of employees in HR

SELECT MIN(Age) AS YoungestInHR

FROM Employees
WHERE Department = 'HR';

OUTPUT:

Sample Output for WHERE:

EmpID Name Department Salary Age

1 Alice Engineering 75000 28
3 Carol Engineering 82000 30
5 Eve Engineering 78000 25

Sample Output for Aggregate:

 TotalEmployees: 6
 AverageSalary: 66166.67
  MaxSalary: 82000
 TotalEngineeringSalary : 235000
 YoungestInHR: 32

RESULT:

Successfully queried the database using various WHERE conditions and implemented
aggregate functions to extract meaningful information from the dataset.
 Ex.No:4
Query the database tables and explore sub queries and simple join
operations.
Date :

AIM:

To retrieve data from database tables using subqueries and join operations to explore
relational data and inter-table relationships.

ALGORITHM:

Step 1: Start the SQL environment (MySQL/PostgreSQL/SQL Server).

Step 2: Create a new database.
Step 3: Create two or more related tables (e.g., Departments, Employees).
Step 4: Add appropriate primary and foreign key constraints.
Step 5: Insert sample data into both tables.
Step 6: Write subqueries:
 In SELECT, WHERE, or FROM clauses.
Step 7: Write JOIN queries:

 INNER JOIN

 LEFT JOIN

Step 8: Display the results using the SELECT statement.

PROGRAM:

Step 1: Create a new database

CREATE DATABASE JoinLabDB;

USE JoinLabDB;

Step 2: Create tables

Departments Table (Parent)

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

 DeptName VARCHAR(50) NOT NULL

);

Employees Table (Child)

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Salary DECIMAL(10,2),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

Step 3: Insert data into Departments

INSERT INTO Departments (DeptID, DeptName)

VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Finance');

Step 4: Insert data into Employees

INSERT INTO Employees (EmpID, Name, Age, Salary, DeptID)

VALUES
(101, 'Alice', 28, 70000, 2),
(102, 'Bob', 35, 55000, 1),
(103, 'Carol', 30, 80000, 2),
(104, 'David', 45, 60000, 3),
(105, 'Eve', 25, 75000, 2);

✅ PART 1: Subqueries
1. Find employees who earn more than the average salary

SELECT Name, Salary

FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

2. Get department names where at least one employee works

SELECT DeptName
FROM Departments
WHERE DeptID IN (SELECT DISTINCT DeptID FROM Employees);
 ✅ PART 2: Join Operations

1. Display employee details along with their department name (INNER JOIN)

SELECT E.EmpID, E.Name, E.Salary, D.DeptName

FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID;

2. Display all departments and their employees (LEFT JOIN)

SELECT D.DeptName, E.Name AS EmployeeName

FROM Departments D
LEFT JOIN Employees E ON D.DeptID = E.DeptID;

Output:

Output 1: Employees earning more than average salary

Name Salary
Alice 70000
Carol 80000
Eve 75000

Output 2: INNER JOIN - Employees with department names

EmpID Name Salary DeptName

101 Alice 70000 Engineering
102 Bob 55000 HR
103 Carol 80000 Engineering
104 David 60000 Finance
105 Eve 75000 Engineering

Output 3: LEFT JOIN - All departments and employees

DeptName EmployeeName
HR Bob
Engineering Alice
Engineering Carol
Engineering Eve
Finance David
RESULT:

Successfully queried data using subqueries and JOINs. These operations helped in
understanding data relationships across multiple tables and applying nested logic for more
powerful queries.
 Ex.No:5
Query the database tables and explore natural,
Date : equi and outer joins

AIM:

To understand and implement different types of joins—natural join, equi join, and outer
join—on relational database tables using SQL.

ALGORITHM:

Step 1: Create two sample database tables (Students and Courses) with related fields.
Step 2: Insert appropriate sample data.
Step 3: Perform Natural Join to retrieve matching tuples from both tables automatica lly
based on common attributes.
Step 4: Perform Equi Join to retrieve records where a common attribute satisfies a
specific condition.
Step 5: Perform Outer Joins (Left, Right, Full) to retrieve matching and non-
matching tuples.
Step 6: Display the output of each SQL query.
PROGRAM:

-- Step 1: Create Sample Tables

CREATE DATABASE CollegeDB;

USE CollegeDB;

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50),

CourseID INT

);
CREATE TABLE Courses (

CourseID INT PRIMARY KEY,

CourseName VARCHAR(50),

Instructor VARCHAR(50)

);

-- Step 2: Insert Sample Data

INSERT INTO Students VALUES (1, 'Alice', 101), (2, 'Bob', 102), (3, 'Charlie', 103);

INSERT INTO Courses VALUES (101, 'Math', 'Dr. Smith'), (102, 'Physics', 'Dr. Johnson');

-- Step 3: Perform Joins

-- Natural Join (Implicitly joins on common column "CourseID")

SELECT Name, CourseName, Instructor

FROM Students NATURAL JOIN Courses;

-- Equi Join (Explicitly joins on "CourseID" using WHERE clause)

SELECT Name, CourseName, Instructor

FROM Students INNER JOIN Courses

ON Students.CourseID = Courses.CourseID;

-- Left Outer Join (All students, even if no matching courses exist)

SELECT Name, CourseName, Instructor

FROM Students LEFT JOIN Courses

ON Students.CourseID = Courses.CourseID;
-- Right Outer Join (All courses, even if no matching students exist)

SELECT Name, CourseName, Instructor

FROM Students RIGHT JOIN Courses

ON Students.CourseID = Courses.CourseID;

-- Full Outer Join (Retrieves all records with NULLs for non-matching values)

SELECT Name, CourseName, Instructor

FROM Students FULL JOIN Courses

ON Students.CourseID = Courses.CourseID;

Output:

Natural & Equi Join Output:

Name CourseName Instructor

Alice Math Dr. Smith

Bob Physics Dr. Johnson

Left Outer Join Output:

Name CourseName Instructor

Alice Math Dr. Smith
Bob Physics Dr. Johnson
Charlie NULL NULL

Right Outer Join Output:

Name CourseName Instructor

Alice Math Dr. Smith
Bob Physics Dr. Johnson
NULL Biology Dr. Thomas
Right Outer Join Output:

Name CourseName Instructor

Alice Math Dr. Smith
Bob Physics Dr. Johnson
Charlie NULL NULL
NULL Biology Dr. Thomas

RESULT:

The natural, equi, and outer joins were successfully executed, demonstrating how relational
database tables retrieve matching and non-matching records effectively.
 Ex.No:6
Write user defined functions and stored procedures
in SQL.
Date :

AIM:

To write and execute User Defined Functions and Stored Procedures in SQL using a
newly created database and tables.

ALGORITHM:

For User Defined Function

1. Create a database and table with sample data.

2. Define a function using the CREATE FUNCTION statement.
3. The function accepts input parameters and returns a result.
4. Call the function in a SQL query or SELECT statement.

For Stored Procedure

1. Create a database and insert sample data in a table.

2. Define a stored procedure using CREATE PROCEDURE .
3. The procedure may accept input/output parameters.
4. Use CALL or EXEC to run the procedure.
5. Observe and verify the output.

PROGRAM:

Step 1: Create a New Database and Tables

-- Create database

CREATE DATABASE CompanyDB;

USE CompanyDB;

-- Create Employee table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(100),
 Salary DECIMAL(10,2),

Department VARCHAR(50)

);

-- Insert sample data

INSERT INTO Employee VALUES

(1, 'Alice', 50000, 'HR'),

(2, 'Bob', 60000, 'Finance'),

(3, 'Charlie', 70000, 'IT'),

(4, 'David', 40000, 'HR');

Step 2: User Defined Function Example

Function to calculate annual salary

-- Create Function

DELIMITER //

CREATE FUNCTION GetAnnualSalary(monthly_salary DECIMAL(10,2))

RETURNS DECIMAL(10,2)

DETERMINISTIC

BEGIN

RETURN monthly_salary * 12;

END;

//

DELIMITER ;
-- Use the function

SELECT EmpName, Salary, GetAnnualSalary(Salary) AS AnnualSalary FROM Employee;

STEP 3:

Stored Procedure Example

Procedure to get employees from a specific department

-- Create Stored Procedure

DELIMITER //

CREATE PROCEDURE GetEmployeesByDept(IN deptName VARCHAR(50))

BEGIN

SELECT EmpName, Salary FROM Employee WHERE Department = deptName;

END;

//

DELIMITER ;

-- Call the procedure

CALL GetEmployeesByDept('HR');
OUTPUT:

For User Defined Function:

EmpName Salary AnnualSalary

Alice 50000.00 600000.00
Bob 60000.00 720000.00
Charlie 70000.00 840000.00
David 40000.00 480000.00

For Stored Procedure:

EmpName Salary
Alice 50000.00
David 40000.00

RESULT:

 Successfully created and executed User Defined Functions and Stored Procedures
in SQL.

 Validated results using function calls and procedure execution with sample data.
 Ex.No:7 Execute complex transactions and realize DCL and TCL
commands.
Date :

AIM:

To implement complex transactions using DCL and TCL commands and understand their
role in database management.

ALGORITHM:

1. Initialize Database
o Create a new database.
o Design and create tables with necessary constraints.
2. Insert Sample Data
o Populate tables with relevant data.
3. Execute Transaction Control Commands (TCL)
o Start a transaction using BEGIN TRANSACTION.
o Perform operations such as INSERT, UPDATE, or DELETE.
o Use COMMIT to save changes.
o Use ROLLBACK to undo changes if needed.
o Demonstrate SAVEPOINT functionality.
4. Execute Data Control Commands (DCL)
o Grant permissions using GRANT statement.
o Revoke permissions using REVOKE statement.
5. Verify Results
o Check the impact of TCL commands by examining table data.
o Validate permission changes using DCL commands.

PROGRAM:

-- Step 1: Create Database

CREATE DATABASE LabDB;

USE LabDB;

-- Step 2: Create Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

 Name VARCHAR(50),

Salary INT

);

-- Step 3: Insert Data

INSERT INTO Employee VALUES (101, 'Alice', 50000);

INSERT INTO Employee VALUES (102, 'Bob', 60000);

-- Step 4: Begin Transaction

BEGIN TRANSACTION;

-- Step 5: Update Data

UPDATE Employee SET Salary = Salary + 5000 WHERE EmpID = 101;

-- Step 6: Create Savepoint

SAVEPOINT BeforeRollback;

-- Step 7: Further Modification

DELETE FROM Employee WHERE EmpID = 102;

-- Step 8: Rollback to Savepoint

ROLLBACK TO BeforeRollback;

-- Step 9: Commit Transaction

COMMIT;
-- Step 10: Grant and Revoke Permissions

GRANT SELECT ON Employee TO user1;

REVOKE SELECT ON Employee FROM user1;

OUTPUT:

Initial State of Employee Table

Before executing any transaction:

EmpID Name Salary

101 Alice 50000
102 Bob 60000

After Updating Alice's Salary

UPDATE Employee SET Salary = Salary + 5000 WHERE EmpID = 101;

Current State:

EmpID Name Salary

101 Alice 55000
102 Bob 60000

After Deleting Bob’s Record

DELETE FROM Employee WHERE EmpID = 102;

Current State:

EmpID Name Salary

101 Alice 55000

After Rolling Back to Savepoint (BeforeRollback)

Bob’s record deletion is undone using:

ROLLBACK TO BeforeRollback;

Restored state:
 EmpID Name Salary
101 Alice 55000
102 Bob 60000

After Committing Transaction

Final saved state:

EmpID Name Salary

101 Alice 55000
102 Bob 60000

RESULT:
The execution of complex transactions using TCL commands (BEGIN TRANSACTION,
COMMIT, ROLLBACK, SAVEPOINT) and DCL commands (GRANT, REVOKE) was
successfully demonstrated.
 Ex.No:8
Write SQL Triggers for insert, delete, and update
operations in database table.
Date :

AIM:

To create and implement SQL triggers that automatically execute actions upon inserting,
updating, and deleting records in a database table.

ALGORITHM

1. Create a new database.

2. Create the primary table with necessary columns.
3. Create a log table to store trigger-based changes.
4. Define triggers for:

 INSERT trigger: Logs details when a new record is added.

 UPDATE trigger: Captures modifications to existing records.
 DELETE trigger: Stores information of deleted records.

5. Test the triggers by performing insert, update, and delete operations.

6. Check the log table for recorded changes.

PROGRAM:

Step 1: Create Database

CREATE DATABASE LabDB;

USE LabDB;

Step 2: Create Main Table

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

Salary INT

);
Step 3: Create Log Table

CREATE TABLE EmployeeLog (

LogID INT AUTO_INCREMENT PRIMARY KEY,

ActionType VARCHAR(10),

EmpID INT,

Name VARCHAR(50),

Salary INT,

ChangeTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Step 4: Create INSERT Trigger

DELIMITER //

CREATE TRIGGER After_Insert

AFTER INSERT ON Employee

FOR EACH ROW

BEGIN

INSERT INTO EmployeeLog (ActionType, EmpID, Name, Salary)

VALUES ('INSERT', NEW.EmpID, NEW.Name, NEW.Salary);

END;

//

DELIMITER ;

Step 5: Create UPDATE Trigger

DELIMITER //

CREATE TRIGGER After_Update

AFTER UPDATE ON Employee

FOR EACH ROW

BEGIN

INSERT INTO EmployeeLog (ActionType, EmpID, Name, Salary)

 VALUES ('UPDATE', NEW.EmpID, NEW.Name, NEW.Salary);

END;

//

DELIMITER ;

Step 6: Create DELETE Trigger

DELIMITER //

CREATE TRIGGER After_Delete

AFTER DELETE ON Employee

FOR EACH ROW

BEGIN

INSERT INTO EmployeeLog (ActionType, EmpID, Name, Salary)

VALUES ('DELETE', OLD.EmpID, OLD.Name, OLD.Salary);

END;

//

DELIMITER ;

Step 7: Perform Insert, Update, and Delete Operations

-- Insert a record

INSERT INTO Employee VALUES (101, 'Alice', 50000);

-- Update a record

UPDATE Employee SET Salary = 55000 WHERE EmpID = 101;

-- Delete a record

DELETE FROM Employee WHERE EmpID = 101;

OUTPUT:

LogID ActionType EmpID Name Salary ChangeTime

1 INSERT 101 Alice 50000 2025-05-14 16:35:00
2 UPDATE 101 Alice 55000 2025-05-14 16:36:00
3 DELETE 101 Alice 55000 2025-05-14 16:37:00

RESULT:

The execution of SQL triggers for INSERT, UPDATE, and DELETE operations was
successfully demonstrated. The triggers automatically logged each action in the EmployeeLog
table.
 Ex.No:9 Use SQLi to authenticate as administrator, to get unauthorize d
access over sensitive data, to inject malicious statements into
Date : form field.

AIM:

To demonstrate how SQL Injection vulnerabilities can be exploited and, more importantly, how to
prevent them using secure coding practices.
ALGORITHM

1. Set up DVWA on a local machine or Docker.

2. Open the "Login" page in DVWA.
3. Select low security from the DVWA security settings.
4. Observe how the login bypass works.
5. Repeat the process on medium and high security to see how it's prevented.

PROGRAM:

This is a simplified vulnerable PHP snippet for educational discussion only:

<?php
// Vulnerable code snippet for educational use (DO NOT use in production)
$conn = mysqli_connect("localhost", "root", "", "testdb");

$username = $_POST['username'];
$password = $_POST['password'];

$query = "SELECT * FROM users WHERE username = '$username' AND password =

'$password'";

$result = mysqli_query($conn, $query);

if(mysqli_num_rows($result)) {
echo "Logged in!";
} else {
echo "Invalid credentials.";
}
?>

Testing Input (for vulnerable login form):

Username: ' OR '1'='1

Password: anything
Prevention Code (Secure Version Using Prepared Statements):

<?php
// Secure version using prepared statements
$conn = new mysqli("localhost", "root", "", "testdb");

$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");

$stmt->bind_param("ss", $username, $password);

$username = $_POST['username'];
$password = $_POST['password'];

$stmt->execute();
$result = $stmt->get_result();

if($result->num_rows > 0) {
echo "Logged in!";
} else {
echo "Invalid credentials.";
}
?>

OUTPUT:

1. With vulnerable code and injection:

Logged in!
2. With secure code and injection:
Invalid credentials.

RESULT:

This experiment demonstrated how insecure SQL queries can be exploited using SQL Injection
and how using parameterized queries (prepared statements) protects the application.
 Ex.No:10 Write programs that will defend against the SQLi attacks
given in the previous exercise.
Date :

AIM:

To develop a secure database application that is resistant to SQL injection attacks using proper
validation and prepared statements.

ALGORITHM:

1. Establish a secure database connection.

2. Use parameterized queries to prevent SQL injection.
3. Implement user input validation and sanitization.
4. Avoid dynamic SQL queries with direct user input.
5. Apply least privilege access principles in the database.
6. Use an ORM framework for secure database interactions.
7. Execute queries securely and verify expected behavior.

PROGRAM:

(Python with SQLite as an example):

import sqlite3

# Secure database connection

def create_connection():

return sqlite3.connect("secure_database.db")

# Secure user input handling with parameterized query

def fetch_user_data(user_id):

conn = create_connection()

cursor = conn.cursor()

# Using parameterized query to prevent SQL injection

cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

 result = cursor.fetchall()

conn.close()

return result

# Testing SQL injection protection

user_input = input("Enter user ID: ")

try:

user_data = fetch_user_data(int(user_input)) # Input validation

print("User Data:", user_data)

except ValueError:

print("Invalid Input: Please enter a numeric user ID.")

Database Setup (New Database and Secure Schema):

conn = sqlite3.connect("secure_database.db")

cursor = conn.cursor()

# Creating a secure user table

cursor.execute("""

CREATE TABLE IF NOT EXISTS users (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

email TEXT NOT NULL UNIQUE

""")

# Insert test data

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice",
"alice@example.com"))

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Bob",

"bob@example.com"))

conn.commit()

conn.close()

OUTPUT:

Enter user ID: 1

User Data: [(1, 'Alice', 'alice@example.com')]

RESULT:

The program successfully resists SQL injection attacks by:

 Using parameterized queries instead of dynamic SQL strings.

 Validating user input to ensure proper data types.
 Restricting direct user input manipulation in queries.
 Ex.No:11 Write queries to insert encrypted data into the database and to
retrieve the data using decryption.
Date :

AIM:

To securely store sensitive data in a database using encryption and retrieve it using
decryption techniques.

ALGORITHM:

1. Establish a secure database connection.

2. Use a strong encryption algorithm (AES-256 for example) to encrypt sensitive data before
insertion.
3. Store encrypted data securely in the database.
4. Retrieve encrypted data and decrypt it using the corresponding key.
5. Ensure key management is handled securely.
6. Validate the integrity of the decrypted data.

PROGRAM:

(Python with SQLite and Cryptography Library):

import sqlite3

from cryptography.fernet import Fernet

# Generate and save encryption key (should be securely stored and reused)

key = Fernet.generate_key()

cipher_suite = Fernet(key)

# Secure database connection

def create_connection():

return sqlite3.connect("secure_encryption_db.db")
# Create a table for storing encrypted data

def setup_database():

conn = create_connection()

cursor = conn.cursor()

cursor.execute("""

CREATE TABLE IF NOT EXISTS secure_users (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

email TEXT NOT NULL UNIQUE,

encrypted_info TEXT NOT NULL

""")

conn.commit()

conn.close()

# Insert encrypted data

def insert_encrypted_data(name, email, sensitive_info):

conn = create_connection()

cursor = conn.cursor()

encrypted_info = cipher_suite.encrypt(sensitive_info.encode())

cursor.execute("INSERT INTO secure_users (name, email, encrypted_info) VALUES (?, ?, ?)",

(name, email, encrypted_info))

 conn.commit()

conn.close()

# Retrieve and decrypt data

def retrieve_decrypted_data(user_id):

conn = create_connection()

cursor = conn.cursor()

cursor.execute("SELECT name, email, encrypted_info FROM secure_users WHERE id = ?",

(user_id,))

result = cursor.fetchone()

conn.close()

if result:

decrypted_info = cipher_suite.decrypt(result[2].encode()).decode()

return f"Name: {result[0]}, Email: {result[1]}, Sensitive Info: {decrypted_info}"

else:

return "User not found."

# Testing the encryption and decryption functions

setup_database()

insert_encrypted_data("Alice", "alice@example.com", "This is secret data")

insert_encrypted_data("Bob", "bob@example.com", "Another confidential info")

print(retrieve_decrypted_data(1))

print(retrieve_decrypted_data(2))

OUTPUT:

Name: Alice, Email: alice@example.com, Sensitive Info: This is secret data

Name: Bob, Email: bob@example.com, Sensitive Info: Another confidential info

RESULT:

 Successfully encrypts and stores sensitive data.

 Ensures only authorized access can decrypt and retrieve the original data.

 Uses AES-based encryption techniques for security.