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

name: Costa Rica

continent_name: America

  • INSERT INTO country (code, name, continent_name) VALUES (506, 'CR', 'America');

code: 81

name: Japan

continent_name: Asia

  • INSERT INTO country (code, name, continent_name) VALUES (81, 'JP', 'Asia');

code: 351

name: Portugal

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

  • SELECT id, full_name FROM users WHERE id LIKE '$id'

Demo

1. Use the application normally, enter a number to display its associated user id info. Since, this is GET it will show in the browser

  • 1
  • SELECT id, full_name FROM users WHERE id LIKE '1'

2. Try to generate an error, this time we can see an error that means this is vulnerable to SQL injection

  • Single quote = ‘
  • SELECT id, full_name FROM users WHERE id LIKE '''

3. Try to run Boolean conditions, this print all the values as 1 will always be equals 1

  • 1’ or ‘1’=’1
  • SELECT id, full_name FROM users WHERE id LIKE '1' or '1'='1'

4. Using sqlmap to exploit this vulnerability

  • sqlmap -u "http://localhost/esqiuelai/main-script.php?id=1"

Secure the script

To secure this script you can use the following functions

We need to pass the GET request with the values submitted by the user to those functions, we place them here

SQLmap test

Even using sqlmap it fails to inject into this query

  • sqlmap -u "http://localhost/esqiuelai/main-script.php?id=1"