How about injecting some SQL?

SQL injection is a web security vulnerability, which permits an attacker to disrupt the queries an application sends to its database. An attacker can control the results of SQL commands execution, which gives access to sensitive data stored in the database (user data, payment data, etc.). The attacker can alter or delete it, which may also affect the application’s behavior.

SQL injection is undoubtedly one of the most critical vulnerabilities. An adversary can:

  • bypass authentication and impersonate users;
  • access data stored on the database server;
  • modify or delete data from the database;
  • use an SQL injection to initiate an attack vector on the internal network protected by a firewall.

In some cases, it’s possible to compromise the server and other back-end infrastructure.

How does SQL injection look?

Let’s suppose we have the Users table in the database and a script that gets userid from the person who uses the script and prints their user name.

This will look as follows:

$sqlQuery=”SELECT username FROM Users WHERE userId=”.$_GET[‘id’];

if ($result = $mysqli->query($sqlQuery)) {

while ($row = $result->fetch_assoc()) {

echo $row["username"];

}

}
  1. We call the script http://site.com/getUsernameById.php?id=1
  2. We get output Bob.

Herein, the SQL query will look like this:

SELECT username FROM Users WHERE userid=1

This code is vulnerable to SQL injections because it neither sanitizes nor filters user input: the value of id gets in $sqlQuery. In order to exploit the vulnerability, the adversary has to change the value of id so that it has the SQL code they want to execute. That said, since the same (but slightly modified) SQL query is sent to the database, they also have to make sure its syntax is correct. For example, if they execute the following request:

http://10.0.2.10/getUsernameById.php?id=1 or 1=1

the query to the database will look like this:

SELECT username FROM Users WHERE userid=1 or 1=1

which gives access to all user names at once.

Example

Here are two samples of attack and defending web applications taken from tasks on Hacktory.

Attack scenario

Attack scenario

Fixing code to prevent SQL injection (defense scenario)

Defense scenario

Fixes and Prevention

Primary measures:

  1. Queries should be written with prepared statements and parameterized queries.
  2. Use ORM.
  3. Use built-in framework functions to form queries.

Useful links

Hacktory are professional AppSec, Red and Blue Teams developing their game-based cybersecurity educational platform https://hacktory.ai/