02.12.2023

Tips

What is SQL injection? How to prevent it?

SQL injection (SQLi) attacks may destroy your database. These attacks are one of the most common attacks on websites (it is in the OWASP Top Ten). This attack relies on injecting code into SQL database queries through the user input.

WARNING: We’re not responsible for damage caused by SQL injection! Malicious hacking is a computer crime and you may face legal consequences! This post is meant to gain awareness about SQL injection and give a way to prevent those vulnerabilities.

The impact of SQL injection

SQL injection attacks may result in unauthorized access to sensitive data, such as:

Passwords
Credit card information
Personal user information
Information normally hidden from website users

SQL injection attacks have been used in many data breaches over the years, which then caused damage to the reputation and regulatory fines.

SQL injection types

SQL injection can be divided to:

Error-based SQL injection - injection is done with help of error messages.
Union-based SQL injection - injection is done with help of UNION clause.
Blind SQL injection - it is possible to extract the data with help of generated content
Time-based SQL injection - injection is done with help of response delays

Example: vulnerable web application

Sorry, we didn’t do it in server-side JavaScript this time…

This is an example of PHP web application vulnerable to SQL injection:

<!DOCTYPE html>
<html>
  <head>
    <title>Student average grades</title>
    <meta name="viewport" content="width=device-width, inital-scale=1.0">
    <meta charset="UTF-8">
  </head>
  <body>
    <?php
      mysqli_report(MYSQLI_REPORT_OFF);
      $conn = mysqli_connect('localhost','dbuser','dbpassword','studentdb') or die("Can't connect to the database!");
      
      //SQL query error handling
      function handle_sql_error($conn) {
        mysqli_close($conn);
        die("There was a problem with SQL query.");
      }
    ?>
    <h1>Student average grades</h1>
    <?php
      $result = mysqli_query($conn, "SELECT COUNT(id) AS count FROM students;") or handle_sql_error($conn);
      print "<p>There are ".mysqli_fetch_assoc($result)["count"]." students in the student database.</p>";
    ?>
    <form method="post">
      <label for="q">Search query:</label>
      <input type="text" name="q" id="q">
      <input type="submit" value="Search!">
    </form>
    <?php
      if(isset($_POST["q"]) and trim($_POST["q"]) != "") {
        $q = str_replace(array('!','%','_','['), array('!!','!%','!_', '!['), $_POST["q"]);  //So that "%" will not break searches
        //WARNING! Code below is vulnerable to SQL injection!!!
        $result = mysqli_query($conn, "SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE '%$q%' GROUP BY grades.studentid;") or handle_sql_error($conn);
        $count = mysqli_num_rows($result);
        print "<p><b>Result count: $count</b></p>";
        print "<ul>";
        while($arr = mysqli_fetch_assoc($result)) {
          //We don't make it vulnerable to XSS for now...
          $studentname = htmlentities($arr["name"]);
          $studentaverage = htmlentities($arr["average"]);
          print "  <li><b>$studentname</b> &mdash; $studentaverage</li>";
        }
        print "</ul>";
        mysqli_close($conn);
      }
    ?>
  </body>
</html>

If you want to try it, there is a database structure in SQL, along with mock data (the database name is studentdb, DBMS is MySQL/MariaDB):

-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Dec 02, 2023 at 07:19 PM
-- Server version: 10.3.38-MariaDB-0ubuntu0.20.04.1
-- PHP Version: 7.4.3-4ubuntu2.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `studentdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `grades`
--

CREATE TABLE `grades` (
  `gradeid` int(11) NOT NULL,
  `studentid` int(11) NOT NULL,
  `grade` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `grades`
--

INSERT INTO `grades` (`gradeid`, `studentid`, `grade`) VALUES
(1, 4, 2),
(2, 5, 3),
(3, 9, 3),
(4, 2, 5),
(5, 6, 6),
(6, 6, 5),
(7, 2, 3),
(8, 4, 3),
(9, 7, 5),
(10, 5, 4),
(11, 2, 1),
(12, 2, 6),
(13, 10, 5),
(14, 2, 4),
(15, 4, 5),
(16, 3, 4),
(17, 6, 2),
(18, 5, 3),
(19, 7, 2),
(20, 1, 3),
(21, 8, 5),
(22, 3, 3),
(23, 10, 6),
(24, 8, 5),
(25, 5, 4),
(26, 0, 5),
(27, 0, 5),
(28, 6, 4),
(29, 9, 6),
(30, 8, 5),
(31, 3, 2),
(32, 9, 2),
(33, 4, 3),
(34, 2, 2),
(35, 9, 6),
(36, 10, 2),
(37, 3, 6),
(38, 2, 3),
(39, 0, 6),
(40, 1, 5),
(41, 3, 6),
(42, 5, 2),
(43, 3, 6),
(44, 3, 5),
(45, 1, 3),
(46, 7, 2),
(47, 0, 5),
(48, 1, 1),
(49, 3, 1),
(50, 1, 4);

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `name`) VALUES
(1, 'Tasha Martin'),
(2, 'Shelly Watts'),
(3, 'Mariam Larson'),
(4, 'Uma Austin'),
(5, 'Malachi Hensley'),
(6, 'Mercedes Mcbride'),
(7, 'Raja Dominguez'),
(8, 'Craig Wooten'),
(9, 'Jasmine Hoffman'),
(10, 'Micah Salazar');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `grades`
--
ALTER TABLE `grades`
  ADD PRIMARY KEY (`gradeid`);

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `grades`
--
ALTER TABLE `grades`
  MODIFY `gradeid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51;

--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

If you type something (for example “John Smith”), then the SQL query will look like this:

SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE '%John Smith%' GROUP BY grades.studentid;

And the web application will look up student names containing the substring “John Smith”. With this example set of data, web application would return 0 results, because there are no matching entries.

The problem is the lack of database input sanitation. What happens, if the input was ' OR 1=1#?
The SQL query will then look like this:

SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE '%' OR 1=1#%' GROUP BY grades.studentid;

There will be then OR 1=1 in the query, which is always true, thus returning all the students’ average grades. The result page will display only one result due to removal of GROUP BY clause. The “#” character makes rest of the query a comment.

Does the SQL query above look a bit dangerous? What if the input was asd' UNION SELECT @@hostname, @@version #?
The SQL query will then look like this:

SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE '%asd' UNION SELECT @@hostname, @@version #%' GROUP BY grades.studentid;

Then the server hostname and DBMS version will be leaked.

Data is leaked via SQL injection.

Some websites even allow batched SQL statements like with asd'; DROP TABLE students# input. The SQL query will then look like this:

SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE '%asd'; DROP TABLE students#%' GROUP BY grades.studentid;

Then the students table will be deleted. The data will be lost.

SQL injection using batched SQL statements is summarized in this xkcd comic:
xkcd Comic #327: Exploits of a Mom

Oh no! Hackers will hack that site pretty easily! What you can do?

SQL injection prevention

Fortunately there are many ways of protection against SQL injection.

If you want to use escaping, then it depends on programming language you use. In PHP and mysqli driver, you can use mysqli_real_escape_string($conn, $input) function. In server-side JavaScript and mysql library, you can use mysql.escape(input) function (where mysql is a instance of mysql module).

There is an example of fixed web application (using mysqli_real_escape_string function):

<!DOCTYPE html>
<html>
  <head>
    <title>Student average grades</title>
    <meta name="viewport" content="width=device-width, inital-scale=1.0">
    <meta charset="UTF-8">
  </head>
  <body>
    <?php
      mysqli_report(MYSQLI_REPORT_OFF);
      $conn = mysqli_connect('localhost','dbuser','dbpassword','studentdb') or die("Can't connect to the database!");
      
      //SQL query error handling
      function handle_sql_error($conn) {
        mysqli_close($conn);
        die("There was a problem with SQL query.");
      }
    ?>
    <h1>Student average grades</h1>
    <?php
      $result = mysqli_query($conn, "SELECT COUNT(id) AS count FROM students;") or handle_sql_error($conn);
      print "<p>There are ".mysqli_fetch_assoc($result)["count"]." students in the student database.</p>";
    ?>
    <form method="post">
      <label for="q">Search query:</label>
      <input type="text" name="q" id="q">
      <input type="submit" value="Search!">
    </form>
    <?php
      if(isset($_POST["q"]) and trim($_POST["q"]) != "") {
        $q = mysqli_real_escape_string($conn, str_replace(array('!','%','_','['), array('!!','!%','!_', '!['), $_POST["q"]));  //So that "%" will not break searches
        $result = mysqli_query($conn, "SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE '%$q%' GROUP BY grades.studentid;") or handle_sql_error($conn);
        $count = mysqli_num_rows($result);
        print "<p><b>Result count: $count</b></p>";
        print "<ul>";
        while($arr = mysqli_fetch_assoc($result)) {
          //We don't make it vulnerable to XSS for now...
          $studentname = htmlentities($arr["name"]);
          $studentaverage = htmlentities($arr["average"]);
          print "  <li><b>$studentname</b> &mdash; $studentaverage</li>";
        }
        print "</ul>";
        mysqli_close($conn);
      }
    ?>
  </body>
</html>

In this example, mysqli_real_escape_string function escapes various characters causing database problems. Escaped characters will then be treated as regular characters instead of string terminators, thus mitigating SQL injection vulnerability.

But there is one problem, it doesn’t work for number inputs. In this case, you can validate the input using a regular expression, like this:

if(preg_match("/^[0-9]+$/", $input)) {
  //Do SQL queries...
} else {
  die("Invalid input!");
}

This code checks using a regular expression if the input is a number. If it’s not a number, then it rejects the input with an Invalid input! error message.

You can also use prepared statements. Prepared statements separates SQL from data, effectively mitigating SQL injection vulnerability. Example code:

<!DOCTYPE html>
<html>
  <head>
    <title>Student average grades</title>
    <meta name="viewport" content="width=device-width, inital-scale=1.0">
    <meta charset="UTF-8">
  </head>
  <body>
    <?php
      mysqli_report(MYSQLI_REPORT_OFF);
      $conn = mysqli_connect('localhost','dbuser','dbpassword','studentdb') or die("Can't connect to the database!");
      
      //SQL query error handling
      function handle_sql_error($conn) {
        mysqli_close($conn);
        die("There was a problem with SQL query.");
      }
      function handle_stmt_error($stmt, $conn) {
        mysqli_stmt_close($stmt);
        handle_sql_error($conn);
      }
    ?>
    <h1>Student average grades</h1>
    <?php
      $result = mysqli_query($conn, "SELECT COUNT(id) AS count FROM students;") or handle_sql_error($conn);
      print "<p>There are ".mysqli_fetch_assoc($result)["count"]." students in the student database.</p>";
    ?>
    <form method="post">
      <label for="q">Search query:</label>
      <input type="text" name="q" id="q">
      <input type="submit" value="Search!">
    </form>
    <?php
      if(isset($_POST["q"]) and trim($_POST["q"]) != "") {
        $q = str_replace(array('!','%','_','['), array('!!','!%','!_', '!['), $_POST["q"]);  //So that "%" will not break searches
        $stmt = mysqli_prepare($conn, "SELECT students.name AS name, ROUND(AVG(grades.grade), 2) AS average FROM grades INNER JOIN students ON students.id = grades.studentid WHERE students.name LIKE CONCAT('%',?,'%') GROUP BY grades.studentid;") or handle_stmt_error($stmt, $conn);
        mysqli_stmt_bind_param($stmt, 's', $q) or handle_stmt_error($stmt, $conn);
        mysqli_stmt_execute($stmt) or handle_stmt_error($stmt, $conn);
        $result = mysqli_stmt_get_result($stmt);
        $count = mysqli_num_rows($result);
        print "<p><b>Result count: $count</b></p>";
        print "<ul>";
        while($arr = mysqli_fetch_assoc($result)) {
          //We don't make it vulnerable to XSS for now...
          $studentname = htmlentities($arr["name"]);
          $studentaverage = htmlentities($arr["average"]);
          print "  <li><b>$studentname</b> &mdash; $studentaverage</li>";
        }
        print "</ul>";
        mysqli_stmt_close($stmt);
        mysqli_close($conn);
      }
    ?>
  </body>
</html>

In this example, SQL query and search query data are kept separated, thus mitigating SQL injection.

With these mitigations, your website will be harder for hackers to break.

UPDATE: PHP code edited to not exhaust MySQL/MariaDB database connection limit.