An Introduction To SQL Injection

Many web applications aren’t simply made up of the static pages you see, you’ll find that the majority connect to some form of database. The use of databases means web applications can become far more dynamic and so scaling up becomes much more maintainable. The most common databases are built around the use of SQL “structured query language” as this essentially allows the developers to have their applications talk with the database with the use of queries. Here are a few examples of databases that use SQL.

  • MySQL
  • Postgresql
  • SQLite
  • MSSQL

So, what exactly is SQL injection? Simply put, SQL injection is the process of injecting unsanitized commands into a website and altering the original database query. Let’s give a simple example.

Say we have a website that stores information on all the books in the world, that’s a lot. Rather than make a new document for every book we can simply create one blank document and have it fetch the book information from a database. To know which book the user wants to view the application will often make use of URL parameters, the website is then able to take this parameter, we’ll call it book_url_id and pass it to the query, this query is then sent to the database so the information can be passed back. See an example of this query below.

SELECT `book_title`,`book_author`,`book_preview` FROM `books` WHERE `book_id`='125';

Now, in the example above the value 125 is what would appear in the browser URL bar, the URL would read something like – bookstore.com/view_book.php?book_url_id=125. However, as the book_url_id is controlled by the user this becomes a potential point of failure. I’m sure you’re starting to see where this is going but let’s ask the question anyway… What happens if the user adds information that’s not a number to the book_url_id? Well if that information has not been appropriately sanitized then there would be potential for the user to add additional SQL and modify the existing query. This is called SQL Injection, I’ll provide a sample query below so you’re able to better understand how the website/database would see the injected query.

SELECT `book_title`,`book_author`,`book_preview` FROM `books` WHERE `book_id`='' UNION SELECT `admin_id`, `admin_username`, `admin_password` FROM `restricted_admin_table`--;

With the new injected query we can see that the application is still requesting the book information just like before, however, you’ll notice that book_id is now empty. This means no book information is being returned as all of the books will have a unique ID. Instead, the user has added an additional query which is fetching information from the restricted_admin_table, they would then be able to use this information to access restricted areas of the website and access additional functionality.

How SQL injection Can Be Used To Bypass Login Fields

Another instance in which SQL injection could be used would be login fields. Like the previous example, a login field must compare the submitted information (username and password) to that which is stored in the database. However, as SQL comes with conditional operators these login fields can occasionally be bypassed by simply entering the right condition. I’ll give an example below.

 SELECT `user_firstname`, `user_secondname` FROM `users` WHERE `username`='kingjerry43' AND `password`='superSecurePassword'; 

In this example the user is submitting their username and password which are then inserted into the database query, should both the username and password both match up with the information that’s stored then it will return the user information. However, like with the bookstore, if the username or password which has been supplied via the website has not been correctly sanitized then the opportunity for SQL injection could arise. For example, what would happen if the user were to enter the following random_username’ OR 1=1– -? Well, let’s take a look at the query.

SELECT `user_firstname`, `user_secondname` FROM `users` WHERE `username`='random_username' OR 1=1-- - AND `password`='wrong-password';

As you can see above, the use of OR means that even if the username provided is incorrect because 1 is equal to 1 the condition would pass, this is then followed by — – which is commenting out (essentially removing) the remainder of the query. As 1 is equal to 1 the condition is okay and the user would be logged in.

Other scenarios where SQL injection can occur

Aside from the cases previously shown, SQL injection can occur anytime data is handed directly to a database query. SQL uses many statements to interact with a database and manipulate the information which is stored. Here are some examples of these additional queries;

UPDATE – Updates information within a database table.

DELETE – Deletes a row from a selected database table.

INSERT – Inserts additional data to the specified table.

Mitigations

SQL injection occurs due to data not being sanitized before being passed into a database query, depending on the programming language there are several ways to clear or escape harmful characters within the provided data. However, although this approach may work, developers can occasionally fail to add these checks before each query resulting in an SQL injection vulnerability.

To avoid these simple errors, language creators came up with prepared statements. A prepared statement is where the developer has to write or “prepare” the SQL query separate from the provided data, this means the language knows what is user-supplied data and is able to sanitize this before sending it off to the database.

Here is an example of PHP’s prepared statements, this makes use of the PDO framework.

https://www.php.net/manual/en/pdo.prepare.php

<?php
    $statement = $db->prepare('SELECT name, age FROM people WHERE username = ?');
    $statement->execute(['SuperSandra']);
?>

Written By arcan3

Spread the love