What are SQL Injections?

What is SQL?

SQL (Structured Query Language) is a common query language that applications use to communicate with their database. Applications use the database to store and retrieve data.

Let's go through a few basic things. Below is a carefully selected list of things that hackers specifically need for SQL injection attacks.

Simple example

Here is a query that retrieves (SELECT) the name and email (name, email) columns from the user table (FROM users). Try running it! Hacking Academy has a browser-emulated SQL database where you can play around.

All columns

If you want to restore all columns, write an asterisk (*) in the column selector.

Filtering the results

Usually you don't want to retrieve every row from the database, but filter them based on some search criteria. For this purpose, there is the WHERE clause.

Sorting Results

If you want to arrange the results based on, for example, the user's name, you can do so with the ORDER BY clause.

ORDER BY clause can be added to the end of the statement to indicate the order, from first to last (ASC, default) or from last to first (DESC).

ORDER BY can also take a number, such as 1, which would mean to "order by the first column" (which happens to be id in the query).

Merging Surveys

If you want to make two queries, for example "name of all users" and "name of all animals", and return the results of both queries combined, you can use the UNION SELECT statement.

Comments

A comment can be added to a SQL query using the -- characters, which will interrupt the query and the rest of the "query" will be interpreted as a note, not as SQL. It is important to note that there must always be a space after the -- comment, otherwise the SQL will be in the wrong format and the query will not pass (it may pass in this emulated SQL environment, but generally it will not pass in a real database).

Comments can sometimes be made, depending on the database solution, also with a hash sign (#).

It is possible to open and close a comment during a survey using the format /* comment */.

What is SQL injection?

SQL injection, like any other injection vulnerabilities, occurs when an attacker is allowed to manipulate the structure of the protocol, leading to the attacker being able to cause mischief within the application within the limits allowed by the protocol. In this case, the protocol is SQL and the mischief usually refers to the attacker gaining arbitrary access to the database and sometimes even to the entire server.

Practical example

Let's assume that we have an application where the user can log in with an email address and password.

The code of the application could be as follows.

def login_button_pressed():
  email = email_text_field_value()
  password = password_field_value()
  query = f"SELECT id FROM users WHERE email='{email}' AND password='{password}'"
  rows_returned_from_the_database = make_sql_query(query)
  if len(rows_retrieved_from_database) == 0:
    redirect_user("/login-failed")
  else:
    first_retrieved_user_id = rows_retrieved_from_database[0]
    register_user_in(first_returned_user_id)
  redirect_user("/welcome")

When the user attempts to log in with the address john.doe@example.com and the correct password (s3cr3t), the database returns one row and the application logs the user in with the corresponding username contained in that row. Everything is working correctly at this stage.

When the user tries to log in with an incorrect password, the database does not return any rows, and an error message is given to the user indicating that the login failed. Everything else continues to function as it should.

But what happens if the user provides a password with a single quote (')? The query no longer goes through, and the application crashes uncontrollably because suddenly the SQL is no longer properly formatted and the database query fails. There is one quote too many. The database server expected that there would be a counterpart for the additional quote as well. If you send a quote (or quotation marks) to the application and receive an error message back, always suspect SQL injection vulnerability.

What if the attacker modifies the SQL in the way they want and manages to make it work again? What if the user would provide the password kissa' OR 1=1-- ?

The query suddenly returns all users from the database, and the application incorrectly logs in the user as the first user that happened to be returned from the database, even if the password was incorrect. 1=1 is always true, so every row was returned, and the application's own added quote mark was removed from breaking the query structure by commenting it out.

This was a simple, and one could say very classical, example of an SQL injection vulnerability. In this course, we will cover numerous techniques for identifying and exploiting vulnerabilities.

How can you protect yourself from vulnerability?

The best way is to not build SQL by hand at all and let a modern programming library (ORM / Object Relational Mapper) handle the low-level communication with the database. Another option is to use a library (prepared statements) that allows safe parameterization of SQL queries so that the library handles the handling of dangerous characters (such as in this case, the possibility for quotation marks to "escape" from the string and into the query structure).