SC 203: SQL Code Injection (35 pts)

What You Need

Installing MariaDB

On your cloud Linux server, execute these commands: 
sudo apt update
sudo apt install -y mariadb-server mariadb-client php-mysqli
sudo systemctl status mariadb
Your server should be active, as shown below.

Press Q to exit the status page.

Securing your mariaDB Server

On your cloud Linux server, execute this command: 
sudo mysql_secure_installation
Enter these values, as shown below:

Creating a User, Database, and Table

On your cloud Linux server, execute this command: 
sudo mysql -u root -p
Enter your mariaDB root password when you are prompted to, which is probably P@ssw0rd

At the MariaDB prompt, execute these commands, as shown below: 

CREATE DATABASE acmeDB;
CREATE USER 'localadmin'@localhost IDENTIFIED BY 'P@ssw0rd1';
GRANT ALL PRIVILEGES ON acmeDB.* TO 'localadmin'@localhost;
FLUSH PRIVILEGES;
USE acmeDB;
CREATE TABLE employees(
   employee_id int,
   employee_name varchar(255) not null,
   primary key(employee_id)
);
INSERT INTO employees VALUES (101, 'Charlie Brown'),(102, 'Lucy Van Pelt');
INSERT INTO employees VALUES (103, 'Linus Van Pelt'),(104, 'Peppermint Patty');
EXIT;

Enabling MySQLi

PHP 8.2 no longer allows the old, dangerous mysqli functions by default.

We want them, however, to create a vulnerable app.

On your cloud Linux server, edit your configuration file, with a command like this: 

sudo nano /etc/php/8.2/apache2/php.ini
Uncomment this line, as shown below. 
extension=mysqli
Save the file with Ctrl+X, Y, Enter.

Restarting Apache

On your cloud Linux server, execute this command: 
sudo service apache2 restart

Making an HTML Form

On your cloud Linux server, execute this command: 
sudo nano /var/www/html/SC203.htm
Paste in this code, as shown below. 
<html>
<body>
<form action="SC203.php">
    ID: <input name="id">
    Name: <input name="name"><p>
    <input type="submit" value="Search">
</form>
</body>
</html>
Save the file with Ctrl+X, Y, Enter.

Making a PHP Script

On your cloud Linux server, execute this command: 
sudo nano /var/www/html/SC203.php
Paste in this code, as shown below. 
<?php

$noname = 0;
if (!isset($_REQUEST['name'])) {
	$noname = 1;
} else {
	$name = $_REQUEST['name'];
	if (strlen($name) < 1) {
		$noname = 1;
	}
}
	
$noid = 0;
if (!isset($_REQUEST['id'])) {
	$noid = 1;
} else {
	$id = $_REQUEST['id'];
	if (strlen($id) < 1) {
		$noid = 1;
	}
}
	
if (($noname == 1) and ($noid==1)) {
	die("<h2>Error: Must specify either name or id!!</h2>");
}


$servername = "localhost";
$username = "localadmin";
$password = "P@ssw0rd1";
$db = "acmeDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password,$db);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully!
";

// Search the database
if ($noname == 0) {
	$result = mysqli_query($conn, "SELECT * FROM employees WHERE employee_name='$name'");
} else {
	$result = mysqli_query($conn, "SELECT * FROM employees WHERE employee_id=$id");
}
echo "<h2>Results</h2>";

while ($row = $result->fetch_row()) {
    printf("%s %s<br>\n", $row[0], $row[1]);
}

?>
(Note: the "2>&1" redirects stderr to stdout, so we can see error messages.)

Save the file with Ctrl+X, Y, Enter.

Testing your Form

In a Web browser, open this URL, replacing the IP address with the external IP of your server:
http://35.222.29.122/SC203.htm
Enter a Name of Charlie Brown into the form, as shown below, and click the Search button.

The next page shows the search results, as shown below.

In a Web browser, open this page:
https://samlols.samsclass.info/SC/SC203.htm
Enter the URL to your PHP page in the form for flag SC 203.1, as shown below, and click the Submit button.

Flag SC 203.1: Database Functionality (5 pts)

The flag appears, as shown below.

Observing the SQL Injection Vulnerability

In a Web browser, open this URL, replacing the IP address with the external IP of your server:
http://35.222.29.122/SC203.htm
Enter this name into the form:
x'
as shown below, and click the Submit button.

The next page shows a syntax error, as shown below.

This demonstrates a SQL injection vulnerability--the user's input can contain SQL commands that are executed.

Enter this name into the form:
x' UNION SELECT * FROM employees #
as shown below, and click the Submit button.

The next page shows all the database records, as shown below.

Exploiting a Numerical Field

In a Web browser, open this URL, replacing the IP address with the external IP of your server:
http://35.222.29.122/SC203.htm
Enter this ID into the form:
1 OR 1=1
as shown below, and click the Submit button.

Note: "1=1" caused the PHP server to hang, from Collin College in July, 2024, because the campus network filtered it out. If that happens, use 2=2 instead.

The next page shows all the database records, as shown below.

Fix 1: Removing Characters

Modify your script to remove this character from the name:
'
I recommend using the str_replace function. Test your script on your own HTML form.

When it's working, run the test in the box below to get the flag.

Flag SC 203.2: Removing Bad Characters (10 pts)

In a Web browser, open this page:

https://samlols.samsclass.info/SC/SC203.htm
Enter the URL to your PHP page in the form for flag SC 203.2 and submit the form. If your code is correct, the flag will appear.

Fix 2: Parameterized Queries

Modify your script to use parameterized queries, a superior defense against SQL injection.

I used this page as a guide.

In case that page vanishes, here are the two important figures:

Vulnerable Code

Fixed Code

(The parameter "s" stands for string)

When it's working, run the test in the box below to get the flag.

Flag SC 203.3: Parameterized Queries (20 pts)

In a Web browser, open this page:

https://samlols.samsclass.info/SC/SC203.htm
Enter the URL to your PHP page in the form for flag SC 203.3 and submit the form. If your code is correct, the flag will appear.

Posted 3-28-24
"2=2" tip added 7-18-24