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?

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

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

PART 1) DUMMY PRODUCTS DATABASE

-- (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

<?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

<!-- (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

<!-- (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.

 

 

EXTRA) HOW ABOUT MYSQLI?

<?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

• SQL Injection – PHP
• Prepared Statements – PHP