Simple SQL Injection In PHP MYSQL (Plus Prevention)

Welcome to a tutorial and example of PHP MYSQL injection, plus how to prevent it. So you have heard of this “SQL injection” thing from all over the Internet, and wonder why it is such a big deal?

In simple terms, SQL injection is an attempt to change the original statement to do something else entirely. For example:

  • We have a search query that will only show active products – SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE "%SEARCH%"
  • An injection can be done by entering the search term – " OR 1=1 OR `name` LIKE "
  • This alters the SQL query to SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE "%" OR 1=1 OR `name` LIKE "%", effectively showing all products instead.

That covers the basics, but let us walk through a detailed example and prevention measures – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a test database and import 1-products.sql.
  • Change the database settings in 2-db.php to your own.
  • Access 3a-bad.php in the browser and do an injection search " OR 1=1 OR `name` LIKE " – This will show all entries.
  • Access 3b-good.php, the same " OR 1=1 OR `name` LIKE " search will no longer work.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

SQL INJECTION & PREVENTION

All right, let us now get into a detailed example of SQL injection with a simple PHP search script – AND how we can prevent it.

 

PART 1) DUMMY PRODUCTS DATABASE

1-products.sql
-- (A) PRODUCTS TABLE
CREATE TABLE `products` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `status` (`status`);

ALTER TABLE `products`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- (B) DUMMY DATA
INSERT INTO `products` (`id`, `name`, `status`) VALUES
(1, 'Apple', 1),
(2, 'Beet', 1),
(3, 'Carrot', 0),
(4, 'Dill', 1),
(5, 'Eggplant', 1),
(6, 'Feijoa', 0),
(7, 'Grape', 0),
(8, 'Hazelnut', 1),
(9, 'Icaco', 1),
(10, 'Jalapeno', 1);

First, let us start with a simple product table and some dummy fruits/veggies.

  • id Primary key.
  • name The product name.
  • status 1 for active, 0 for hidden.

 

 

PART 2) PHP DATABASE SEARCH

2-db.php
<?php
// (A) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (B) CONNECT TO DATABASE
$pdo = new PDO(
  "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
  DB_USER, DB_PASSWORD, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);
 
// (C) RUN SQL SEARCH
if (!isset($data)) { $data = null; } 
$stmt = $pdo->prepare($sql);
$stmt->execute($data);
$results = $stmt->fetchAll();
$stmt = null;
$pdo = null;
 
// (D) OUTPUT HTML RESULTS
echo "<code class='sql'>$sql</code>";
if (count($results)==0) { echo "<div class='row'>No results</div>"; }
else { foreach($results as $r) {
  printf("<div class='row'>%u %s</div>", $r["status"], $r["name"]);
}}

Next, we have a PHP snippet that will search the database, and it requires 2 variables:

  • $sql An SQL query.
  • $data Search parameters.

 

PART 3) SQL INJECTION & PREVENTION

3A) SQL INJECTION SEARCH

3a-bad.php
<!-- (A) SEARCH FORM -->
<form method="post">
  <p>* Search for any product normally - Will only extract products with <code>status=1</code>.</p>
  <p>* Then try <code>" OR 1=1 OR `name` LIKE "</code> - This will extract everything.</p>
  <input type="text" name="search">
  <input type="submit" value="Search">
</form>
 
<!-- (B) SEARCH RESULTS -->
<div id="results"><?php
  if (isset($_POST["search"])) {
    $sql = "SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE \"%".$_POST["search"]."%\"";
    $data = null;
    require "2-db.php";
  }
?></div>

  1. Just a good old regular HTML search form.
  2. Remember that the above search script takes in $sql and $data? Here is the common newbie mistake – Directly adding the search terms into the $sql string itself. That is, allowing users to inject and alter the SQL query.

 

 

3B) SQL INJECTION PREVENTION

3b-good.php
<!-- (A) SEARCH FORM -->
<form method="post">
  <p><code>" OR 1=1 OR `name` LIKE "</code> no longer works.</p>
  <input type="text" name="search">
  <input type="submit" value="Search">
</form>
 
<!-- (B) SEARCH RESULTS -->
<div id="results"><?php
  if (isset($_POST["search"])) {
    $sql = "SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE ?";
    $data = ["%".$_POST["search"]."%"];
    require "2-db.php";
  }
?></div>

  1. Same old HTML search form.
  2. The SQL injection is prevented with just a small change.

To prevent SQL injections in PHP and MYSQL:

  • Set the values in the SQL string to ? – $stmt = $pdo->prepare("SELECT * FROM `TABLE` WHERE `COLUMN`=?")
  • Feed the data into the execute function instead – $stmt->execute(["DATA"])

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

EXTRA) HOW ABOUT MYSQLI?

4-mysqli.php
<?php
// (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_USER", "root");
define("DB_PASSWORD", "");
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) { exit($mysqli->connect_error); }
 
// (B) PREPARE & BIND
$stmt = $mysqli->prepare("SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE ?");
$stmt->bind_param("s", "%".$_POST["search"]."%");
 
// (C) FETCH
$stmt->execute();
$stmt->bind_result($a, $b, $c);
while ($stmt->fetch()) { echo "<div>$a $b $c</div>"; }
mysqli_close($mysqli);

It’s the same process. Don’t feed the search term directly into the SQL string, put it through bind_param().

 

 

EXTRA) HOW ABOUT INSERT UPDATE REPLACE?

It’s the same story – Insert, update, replace, and delete are vulnerable to SQL injections. Set the values to ? in the SQL query, and feed the data into execute().

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEAT SHEET

PHP MySQL Injection (Click To Enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *