PHP MYSQL Injection & Prevention – 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 restricted search query – SELECT * FROM `users` WHERE `group`=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 `users` WHERE `group`=1 AND `name` LIKE '%' OR 1=1 OR `name` LIKE '%', effectively showing all users.

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.

 

 

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

 

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.

 

QUICK NOTES

If you spot a bug, please feel free to comment below. I try to answer 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.

 

 

SQL INJECTION

All right, let us now get into a detailed example of an SQL injection in a simple PHP search script.

 

1) DUMMY USER DATABASE TABLE

1-users.sql
CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_group` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `user_group` (`user_group`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

INSERT INTO `users` (`user_id`, `user_group`, `user_name`) VALUES
(1, 1, 'Jon Doe'),
(2, 1, 'Jane Doe'),
(3, 1, 'Jay Doe'),
(4, 2, 'Janus Doe'),
(5, 3, 'James Doe');

First, let us start with the database table – This is but a dummy user table with 3 fields:

  • user_id Primary key, auto-increment.
  • user_group The group the user is in, users can only search for other users that are within the same group.
  • user_name Captain Obvious, the user’s name.

 

2) USER SESSION

2-search.php
// (A) JON DOE IS SIGNED IN
session_start();
$_SESSION['user'] = [
  "id" => 1,
  "group" => 1,
  "name" => "Jon Doe"
];

Next, we assume that “Jon Doe” is signed in on the system itself.

 

 

3) SEARCH FORM

2-search.php
// (B) SIMPLE SEARCH FORM ?>
<form method="post">
  <input type="text" name="search"/>
  <input type="submit" value="Search"/>
</form>

Just a simple HTML search form here, nothing special.

 

4) PHP-MYSQL SEARCH

2-search.php
// (C) PERFORM SEARCH - CAN ONLY FIND USERS IN SAME GROUP
if (isset($_POST['search'])) {
  // (C1) DATABASE SETTINGS - CHANGE TO YOUR OWN!
  define('DB_HOST', 'localhost');
  define('DB_NAME', 'test');
  define('DB_CHARSET', 'utf8');
  define('DB_USER', 'root');
  define('DB_PASSWORD', '');
 
  // (C2) CONNECT TO DATABASE
  $pdo = new PDO(
    "mysql:host=". DB_HOST .";charset=". DB_CHARSET .";dbname=". DB_NAME,
    DB_USER, DB_PASSWORD
  );
 
  // (C3) SQL QUERY
  $sql = "SELECT * FROM `users` ".
         "WHERE `user_group`=".$_SESSION['user']['group']." ".
         "AND `user_name` LIKE '%".$_POST['search']."%'";
  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  $results = $stmt->fetchAll();
 
  // (C4) RESULTS
  echo "<div>$sql</div>";
  if (is_array($results)) { foreach ($results as $r) {
    printf("<div>%s</div>", $r['user_name']);
  }}
}

Now comes the “difficult” part of connecting to the database and fetching the search results. For example, if Jon search for “ja”, the SQL query will be SELECT * FROM `users` WHERE `user_group`=1 AND `user_name` LIKE '%ja%'. Nothing suspicious here, right? Everything works OK.

 

 

5) THE INJECTION

Go ahead – Enter ' OR 1=1 OR `user_name` LIKE ' into the search box. As in the introduction, this changes the SQL query to SELECT * FROM `users` WHERE `user_group`=1 AND `user_name` LIKE '%' OR 1=1 OR `user_name` LIKE '%'.

For you beginners, that OR 1=1 part is the “magic” that outputs all the users; The original “restricted to own user group” is now broken with an SQL injection.

 

INJECTION PREVENTION

With that, you should now have a good idea of what SQL injection is. Let us walk through some of the prevention measures in this section.

 

PDO PROTECTION

2-search.php
// (C3-SAFE) SQL QUERY
$sql = "SELECT * FROM `users` ".
       "WHERE `user_group`=? ".
       "AND `user_name` LIKE ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_SESSION['user']['group'], '%'.$_POST['search'].'%']);
$results = $stmt->fetchAll();

How do we prevent SQL injections then? If you are also using PDO, the solution is simple.

  • Replace all the parameters in the query with ?.
  • Pass the parameters into the execute() function instead.

That’s all. This will automatically filter out any injection attempts.

 

 

MYSQLI PROTECTION

3-mysqli.php
// (A) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (B) CONNECT
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) { exit($mysqli->connect_error); }

// (C) PREPARE
$stmt = $mysqli->prepare("SELECT * FROM `users` WHERE `user_group` = ? AND `user_name` LIKE ?");

// (D) BIND PARAM
$group = 1;
//$search = "%ja%";
$search = "' OR 1=1 OR `user_name` LIKE '";
$stmt->bind_param("is", $group, $search);

// (E) 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 for you guys who are on MYSQLi – Prepare the statement then bind the parameters.

 

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.

 

INFOGRAPHIC CHEAT SHEET

PHP MySQL Injection (Click To Enlarge)

 

LINKS & REFERENCES

 

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 *