PHP, HTML & MySQL lab (SQL Injection)

This document is intended to help understand what happens by PHP back-end processing of SQL queries, how to test SQL injections and how to secure code. Our goals here are the following

Building MySQL database
Create a PHP scripts to access & query the database
HTML code as front-end
Test SQL injection
Secure the script

I also share the source code in my GitHub repository, so, let’s get started.

https://github.com/vry4n/esqiuelai

Building MySQL database

1. Start the database

sudo service mysql start
sudo service mysql status
sudo mysql -u root -p
<sql password> toor

2. Build a database

We will have vk9_db database with vk9_users & vk9_country tables

Database: vk9_database
Table: vk9_users
Table: vk9_country

3. Create the database named vk9_db

show databases;
CREATE DATABASE vk9_db;
show databases;

The database was added successfully and now showing

4. Select the database

USE vk9_db;

5. Create the tables

Users

CREATE TABLE users (id INT, full_name varchar(12), created_at TIMESTAMP, country_code INT);

Country

CREATE TABLE country (code INT, name varchar(5), continent_name VARCHAR(15));
show tables;

6. See the description of the results

DESCRIBE users;
DESCRIBE country;

7. Add data to this database

Users

id: 1

full_name: Vry4n

country_code: 506

INSERT INTO users (id, full_name, country_code) VALUES (1, ‘Vry4n’, 506);

id: 2

full_name: Itachi

country_code: 81

INSERT INTO users (id, full_name, country_code) VALUES (2, ‘Itachi’, 81);

id: 3

full_name: Cristiano

country_code: 351

INSERT INTO users (id, full_name, country_code) VALUES (3, ‘Cristano’, 351);

Show the users table

SELECT * FROM users;

Country

code: 506

continent_name: America

INSERT INTO country (code, name, continent_name) VALUES (506, ‘CR’, ‘America’);

code: 81

continent_name: Asia

INSERT INTO country (code, name, continent_name) VALUES (81, ‘JP’, ‘Asia’);

code: 351

continent_name: Europe

INSERT INTO country (code, name, continent_name) VALUES (351, ‘PT’, ‘Europe’);

Show the country table

SELECT * FROM country;

We are done with the database set up. The final step is to allow access to mysql from the script

1. edit the /etc/mysql/my.cnf add the following lines

[mysqld]
skip-grant-tables

vi /etc/mysql/my.cnf
sudo service mysql restart

2. Sometimes the password has to be set

flush privileges;
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘toor’;

Create a PHP script to access & query the database

This is divided into 2 steps

Create a connection file
Create the engine to query the database

Create a connection file

1. We will use the function mysqli to connect to the database. We will save this PHP script as vk9-db-connect.php.

2. Run it to test. No errors exit code 0

Security Recommendation

Make sure that only administrators have access to this file.

Create the engine to query the database

1. This other script is in charge of running the query and returning the results. The name of the file is going to be main-script.php

HTML code as front-end

1. This script just displays the query box, then send the value to main-script.php to proceed with the query, this file is named as index.html

2. This is the view of the site

Note

All these files have been placed into the same directory within /var/www/html/esqiuelai

Highlights of this script

Really basic
No client, nor, server side validation
Input not sanitized in any way
Open to any SQL injection technique

Test SQL injection

Now you can start testing this against SQLi, I will share a quick demo here, however, if you want to know more about SQL injection visit the links below

https://vk9-sec.com/basics-of-sql-injection/

https://vk9-sec.com/advanced-sql-injection-union-based

https://vk9-sec.com/blind-sql-injection/

https://vk9-sec.com/sqlmap-how-to/

we will work with the current query we have built