PHP MYSQL Injection & Prevention (A Simple Example)

Welcome to a tutorial and example of PHP MYSQL injection – Plus how to prevent it. You have probably heard of this “SQL injection” thing from all over the Internet and wondering why it is such a big deal.

In simple terms, SQL injection is the 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 show 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.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Injection & Prevention Useful Bits & Links
The End

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

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 an SQL injection in a simple PHP search script – And how we can prevent it.

 

PART 1) DUMMY PRODUCTS TABLE

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

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;

First, let us start with a simple products table with only 3 fields:

  • id Primary key, auto-increment.
  • name The product name.
  • status 1 for active, 0 for out of stock.

 

PART 2) DUMMY DATA

2-dummy.sql
-- (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);

Just some fruits and veggies. But as you can see, some products are not available.

 

 

PART 3) SEARCH FORM

3-search-form.html
<form method="post" action="4-bad-search.php" target="_blank">
  <input type="text" name="search"/>
  <input type="submit" value="Search"/>
</form>

Just a regular harmless search form.

 

PART 4) PHP MYSQL SEARCH INJECTION

4-bad-search.php
<?php
if (isset($_POST["search"])) {
  // (A) CONNECT TO DATABASE - CHANGE SETTINGS TO YOUR OWN!
  define("DB_HOST", "localhost");
  define("DB_NAME", "test");
  define("DB_CHAR", "utf8");
  define("DB_USER", "root");
  define("DB_PASSWORD", "");
  $pdo = new PDO(
    "mysql:host=".DB_HOST.";charset=".DB_CHAR.";dbname=".DB_NAME,
    DB_USER, DB_PASSWORD
  );
 
  // (B) BAD - INJECTION PRONE SQL QUERY
  $sql = "SELECT * FROM `products`
          WHERE `status`=1
          AND `name` LIKE \"%".$_POST["search"]."%\"";
  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  $results = $stmt->fetchAll();
 
  // (C) OUTPUT RESULTS
  echo "<div>$sql</div>";
  foreach ($results as $r) {
    printf("<div>%u %s - %s</div>", $r["id"], $r["name"], $r["status"]);
  }
}

Yep, the SQL query should only fetch the active products – SELECT * FROM `products` WHERE `status=1 AND `name` LIKE "%SEARCH%". Looks good? Really? Try doing a magic search with " OR 1=1 OR `name` LIKE ".

That’s right. You have just performed an SQL injection.

 

 

PART 5) INJECTION PREVENTION

5-good-search.php
// (B) GOOD - SAFE QUERY
$sql = "SELECT * FROM `products`
       WHERE `status`=1
       AND `name` LIKE ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(["%".$_POST["search"]."%"]);
$results = $stmt->fetchAll();

How do we prevent an injection then? Very simply:

  • We don’t feed the search term directly into the SQL string – AND `name` LIKE ?.
  • Instead, feed it into execute(["%".$_POST["search"]."%"]).

That’s all, the PDO library will automatically “fix and prevent injections”. Go ahead and test this again (just point the search form to 5-good-search.php).

 

EXTRA) I DON’T USE PDO. I USE MYSQLI.

6-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
$sql = "SELECT * FROM `products`
       WHERE `status`=1
       AND `name` LIKE ?";
$stmt = $mysqli->prepare($sql);
$search = "%".$_POST["search"]."%";
$stmt->bind_param("s", $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().

 

 

USEFUL 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.

 

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 *