SQL injection

What is SQL Injection?

SQL injection is a vulnerability that results when you give an attacker or a user the ability(type any values in the input fields) to alter the SQL queries right from a web application that passes these SQL queries to the backend database. Databases are commonly targeted for injection through an application such as a website that accepts input from the user and does a lookup based on that input in the database.

In simpler words, SQL injection is an attack in which an SQL code is inserted or typed into the application by a user that is later passed to the backend database for parsing and execution. That means SQL injection occurs only when you ask a user for input (address, the name, etc.) in the web applications, and instead of the actual input to be provided by the user, they append a SQL statement that will be parsed at the backend of the web application.

How and Why is an SQL Injection Performed?

SQL is the common language for accessing MySQL, Sybase, Microsoft SQL server, etc. database servers. Most web applications need to interact with the database and programming languages like, PHP, JAVA, NET, etc. provide ways of connecting the database with it. When the web application developer does not ensure that the values received from a form, input parameter, etc., are validated before passing them to SQL queries that will be executed on the database server, then there are the chances of occurrence of SQL injection vulnerabilities.

For a successful SQL injection attack, the user must first find vulnerable user inputs within the web application.

Simple SQL Injection Example

Let's take a web application that stores customer records in which any customer can access and view their own records by providing a username and password. The customer enters the following in the password field:

57567546 or 1=1

As 1=1 holds true for all the entered data by the user, all the details related to that customer are accessed irrespective of the username details.

Let's take a simple example that uses two database tables, i.e. Users and Contacts, which involves SQL injection.

Consider the Users table has three fields: ID, username, and password. And, the Contacts table has 5 fields : FirstName, LastName, Address, Email, Card number. Also consider the Users table has the information used for login like:

Users Table:

Now, when someone wants to log in to the website, they'll go to the login page and enter their username and password. This information is then sent to the web server, which will construct a SQL query, and this query is sent to the database server. The query would look like the below:

Impact Of Successful SQL Injection

• After a successful SQL injection, the user or the attacker has access to all the personal or private information, i.e. user details like name, address, card details, etc., from the database. Since SQL databases hold sensitive data, the loss of confidential information is a frequent problem.

• SQL injection also opens the door for hackers to peek into the administrator portal. And if poor SQL commands are used to validate and check the username and password, it may be possible to connect to a system as another user.

• Entire server can be compromised if the attacker is successful in SQL injection as this will lead to full access to the sensitive information of the users.

• If authorization information is available in the database, then it may be possible to change this information through the SQL injection, and this will lead to serious issues like password leaks and information leaks from the user's account.

• As the SQL databases hold sensitive information, loss of data and confidentiality is a serious problem, it is also possible to make changes or even delete important information with the SQL injection attack.

SQL Injection Examples

• Retrieving hidden data:

  SQL injection leads to access to hidden data of the websites in most cases. The attacker can modify the SQL query to retrieve the additional results.

  Consider an example of an online clothing store that displays a variety of products available in the store. When the user clicks on the shirts category, the browser requests the following URL :

  https://somesite.com/products?category=shirts

  This causes the application to make an SQL query to retrieve the details of only shirts from the clothing website's database. The SQL query is shown below :

  Here, the SQL query asks the database to return the following :

  • all the details (*)
  • from the products table
  • where the category is only shirts
  • and shirts that are manufactured are 1.

  The restriction ( released = 1 ) is imposed while creating the SQL query to hide those shirts that are not manufactured. For the shirts that are not manufactured, the released = 0. The application doesn't defend the SQL injection attacks, so an attacker can pass any of the SQL queries like below :

  https://somesite.com/products?category=shirts'--

  And the results in the SQL query would look like below :

  As you can see in the SQL query above, the double-dash is used in the query by the attacker. The double-dash sequence is an indicator of comment in SQL, and that means the rest of the query is never executed. Double-dash usage removes the remainder of the query, so it no longer includes AND released = 1. Hence, all the shirts that are manufactured, as well as the shirts that are not manufactured, are displayed after executing the query.

  Similarly, an attacker can cause the application to retrieve all the products in any category which they are not sure about, which is shown below :

  https://somesite.com/products?category=shirts'+OR+1=1--

  And the results in the SQL query would look like below :

  This modified query will return all the items since 1 = 1 is always true; hence the query will return all the items.

• Subverting application logic:

  This is another example of SQL injection wherein the attacker can change the query to interfere with the queries of application logic. Hence, the logic at the core is changed; thus, the attacker can misuse the application by injecting such SQL queries into the application.

  Let's take an example of how an attacker subverts the application logic using SQL queries.

  Consider an example of an application that lets the users log in with a username and a password. If a user submits the username and the password to the application for the login purpose with Kunal as the username and kunnu123 as their password, then the application checks these credentials by performing the following SQL query :

  If this query returns the details of the user, that means the query is successful; otherwise, the user can't log in, and logging in to the application is rejected.

  Here, the attacker can log in as any user and even as an administrator too without passing any password to the password field in the application simply by using the SQL comment sequence as discussed in the above example. That means the password checks can be removed from the SQL query. The below query is an example of removing the password check from the query.

  As discussed in the above example, the double-dash is the comment sequence, and anything after the double-dash is never executed by the SQL query. Hence, the above query returns the user whose username is an administrator, and the attacker can successfully log in as an administrator without providing the password.

• UNION attacks:

  UNION-based SQL injection attacks allow the user to easily extract information from the database. The application that is vulnerable to SQL injection can be attacked by the user using the UNION keyword to retrieve data from other tables within the database. This results in an SQL injection UNION attack.

  Let's take an example to understand how the UNION SQL injection takes place in the applications.

  The UNION keyword in SQL lets you execute more than one additional SELECT query, and the results of these UNION queries are appended to the results of the main query.

  Example:

  The above SQL query will return a single set with the two columns, containing values from columns a and b in table1 and columns c and d in table2.

  Now, to carry out an SQL injection UNION attack, you need to ensure that your attack meets the below two requirements:

  • How many columns are being returned from the original query?
  • The columns returned from the original query must have the same data type as that of the injected query. Hence, you would need to find out which columns are of suitable data type.

  When you have found out the above two requirements, then you are in a position to retrieve the data. Now, suppose that the original query returns two columns, both of which can hold the string data. The injection query is quoted string within the WHERE clause. And the database contains a table called users with username and password columns. In these conditions, an attacker can retrieve the users' contents by submitting the SQL query below.

  The most important information needed to perform a UNION SQL injection attack is that there is a table called users with two columns username and password.

• Examining the database:

  For a successful SQL injection attack, gathering information about the database is often necessary. Information like type and version of the database, the content of the database, what columns are present in the database, etc.

  To query the version of the database, there are different methods for different databases. The queries to determine the database version for some popular databases are as follows :

  Database type	Query
  MySQL	SELECT @@version
  PostgreSQL	SELECT version()
  Oracle	SELECT * FROM v$version

  For example, to examine the database, you could use the UNION attack with the following input :

  UNION SELECT @@version--

  The above query will confirm that the database is a MySQL server and will display the version being used.

• Blind SQL injection:

  This is the type of SQL injection where the results of the injected query are not shown in the application. Instead, it does the work at the backend of the application. That means the HTTP responses do not contain the results of relevant SQL queries or any results of database errors.

  SQL UNION injection attacks are not feasible with Blind SQL injections as the results of UNION injection attacks are visible on the application's responses, but with blind SQL injection, this is not the case. Exploiting blind SQL injections is more complex and more time-consuming for the attacker. Read more about UNION in SQL through this article.

  Blind injection is of various types, for example, time-based blind SQL injection and content-based blind SQL injection. Let us discuss them further in detail:

  • Content-Based blind SQL injection:

    In content-based blind SQL injection, an attacker performs various SQL queries that claim the database to be TRUE or FALSE. The difference between the TRUE and FALSE statements is observed. Let's consider an example to understand content-based blind SQL injection.

    The below-given link displays the details about the product with the ID 19 that is retrieved from the database.

    https://www.myonlineshop.in/product.php?id=19

    The below SQL query is used to get the above request.

    Now the attacker injects the following SQL injection payload that returns FALSE.

    https://www.myonlineshop.in/product.php?id=19 and 1=2

    Now, the SQL query looks like the below :

    If the web application is vulnerable to SQL injection, then it will probably not return anything. To make sure, the attacker will inject the query that will return TRUE.

    https://www.myonlineshop.in/product.php?id=19 and 2=2

    Now, if the content of the page that returns true is different than that of the page that returns false, then the attacker is able to distinguish when the executed query returns TRUE or FALSE.

  • Time-based blind SQL injection:

    This type of database pauses the database for a specified amount of time, then returns the results, indicating successful SQL query executing, hence indicating vulnerability to SQL injection. The most popular time-intensive operation is a *sleep operation.

    Consider the below example to understand how time-based blind SQL injection works.

    https://www.myonlineshop.in/product.php?id=19 and if(1=1, sleep(10), false)

    If the website's response is delayed by 10s, that means the website is vulnerable to SQL injection.

How to Prevent an SQL Injection?

• A developer should use authentication to validate input by the type of input and its predefined length.
• Use a regular expression for structured data to ensure strong input validation.
• The developer must validate all the inputs, not just the form inputs.
• Removal of potential code elements must be taken care of while writing SQL queries.
• It is a good practice within some of the teams to turn off the visibility of the database errors on the production site.