What is SQL Injection and How to prevent it?

Overview

SQL, or Structured Query Language, is a database management and manipulation language that has progressively gained recognition in many open-source and commercial databases.

As a result, many hackers use the SQL Injection attack technique, which is one of the most common internet hacking strategies.

SQL injection, often known as SQLI, is a typical vector attack that employs malicious SQL code to manipulate backend databases in order to access information that was not intended to be displayed.

This information may include sensitive company data, user lists, or confidential consumer information.

In this article, we will gain deep knowledge of SQL injection, the types of SQL injection, and how to prevent SQL injections.

What is SQL Injection?

SQL Injection (SQLi) is a kind of injection attack that allows malicious SQL commands to be executed. These statements operate a database server that is hidden behind a web application.

SQL Injection vulnerabilities allow attackers to bypass application security protections.

They can bypass the authorization and authentication of a web page or web application and access the complete content of the SQL database. SQL Injection can also be used to add, alter, and delete records in the database.

Any website or web application that uses a SQL database, such as Oracle, MySQL, SQL Server, or others, may be vulnerable to SQL Injection. Criminals may use it to get illegal access to your sensitive data, including customer information, personal information, trade secrets, intellectual property, and other information.

SQL injection attacks are one of the most common, widespread, and deadly web application vulnerabilities.

How does SQL Injection work?

SQL injections are often carried out through web pages or application input. Input forms are frequently encountered in features such as form fields, search boxes, and URL parameters.

In order to carry out a SQL injection attack, bad actors must first find vulnerabilities in a web page or application. Attackers develop harmful payloads and submit their input content to execute malicious commands after discovering a target.

In certain circumstances, bad actors may simply use an automated tool to do an SQLi on their behalf—all they need is the URL of the target website to collect stolen data from the victim.

Example of SQL injection

Let's use two database tables created using SQL for this SQL injection example: Users and Contacts. The Users table will have as few as three fields: ID, username, and password.

More information about the users may be found in the Contacts database, which includes UserID, FirstName, LastName, Email, Address1, payment card number, and security code.

The Users table contains login information such as:
1) kishu, P@$$w0rd
2) sarka, 18feb2001
3) rnishant, $iamperfect$

*Note: Passwords should never be saved in cleartext and should always be hashed and salted when stored in a database.
*

When a user wishes to log in, they will navigate to the login page and enter their username and password.

This data is then delivered to the website, which creates a SQL query and sends it to the database server. As an example, consider the following query:

Select ID from Users where username=’kishu’ and password=’P@$$w0rd’

SQL then performs a true or false comparison for each row that is requested by the query. In our example, the query instructs the database to search the Users table and provide the ID value for each record where the username is "kishu" and the password is "P@$$w0rd".

Often, the website will next check to see what the database server returns and if it is a number. In our situation, the webserver would return a 1 and allow the user to proceed past the login screen.

But what if we want to be malicious with it? We can fool the database server into thinking we have successfully authenticated since it conducts the true-or-false check.

We may accomplish this by appending an OR to the password. If we use 'x' or 1=1 as our password, we'll get a new SQL query that looks like this:

Select ID from Users where username=’kishu’ and password=’x’ or 1=1

This will work for us since the database server will check the second condition if x is not kishu's password. Is 1 equal to 1 if x is not kishu's password?

Yes, it does! The ID is returned to the program, and the user is successfully authenticated.

This does not have to be a 1=1 condition. Any two equal values, such as 99=99, 426=426, or simply a=a, will work.

If a web page can display data, it may also be allowed to print more information on the screen. We can attempt to obtain the data by chaining two SQL calls together.

In addition to our 'or 1=1' expression, we can add a second statement such as UNION SELECT LastName, credit card number, and security code from Contacts.

Extra clauses like this may require additional work, but the ultimate purpose of a SQL injection attack is data access.

Another way for blind SQL injection, in which no data is returned to the screen, is to insert additional hints. We can put the server to sleep using a condition similar to our "or 1=1" condition. We can add "or sleep(10)" and this will appear to perform what we want.

It will instruct the database server to take a 10-second nap, and all responses will be delayed.

Types of SQL Injection

Injections were ranked the number one threat to online application security in the OWASP Top 10, and SQL injection vulnerabilities can be attacked in a variety of ways.

SQL injections are commonly performed by executing commands on the database server, requesting data based on errors, or interfering with query logic.

In-band SQL injection - It is a kind of SQL injection in which the attacker receives the result as a direct response over the same communication channel.

For example, if the attacker attempts the attack manually in a web browser, the results will be shown in the same web browser. In-band SQL injection is also known as classic SQL injection.

In-band SQL injections are classified into two types: Error-based SQL injection and Union-based SQL injection.

Error-based SQL injection - In an error-based SQL injection, attackers take advantage of database errors caused by unsanitized inputs on a web page or application.

During an attack, this technique uses error messages to return complete query results and disclose secret database information.

Using the error message, you can determine what database it uses, the server version where the handlers are stored, etc.

This method can also be used to determine whether a website or online application is vulnerable and to get extra information in order to reconstruct malicious queries.

Union-based SQL injection - This is the most common type of SQL injection used by attackers. This injection technique permits bad actors to take data from the database by expanding the results of the original query.

It employs the UNION SQL operator to combine the results of two or more SELECT queries created by the database into a single result, which is then returned as part of the response.

You can create your searches within the URL or combine multiple statements within the input fields and try to get a response.

Blind SQL injection - In this injection, the data is not transferred via the web application. The attacker cannot see the result of an in-band attack.

Blind SQL injections are typically more sophisticated and difficult to execute than other types of injections since they occur when generic error signals are received from the target.

Blind SQL injections differ from conventional SQL injections in the way they retrieve information from the database.

When used in conjunction with time-based attacks, bad actors query the database for true or false queries, then decide the answer based on the response, as well as the time it takes to obtain a server response.

Boolean-based SQL injection - In this injection, the attacker will send a SQL query to the database, instructing it to return a different result based on whether the query returns True or False.

This kind of attack replaces the query's logic and criteria with its own. It is frequently used in permission or authentication queries to trick the database into thinking they have elevated access or the correct credentials.

Boolean-based SQL injections are also employed in blind SQL injections, which extract data from the database by elimination.

Attackers can determine what data is saved based on the result of the operation by sending a large number of queries, each with a slightly different condition than the previous ones.

Time-based SQL injection - Time-based SQL injections are frequently used to determine the presence of vulnerabilities in an online application or website, as well as in conjunction with Boolean-based techniques during Blind SQL injections.

In this injection, the attacker sends a SQL query to the database, causing the database to wait for a set period of time before sharing the result.

The attacker can use the response time to determine whether a query is True or False.

Bad actors can easily read the text as it is returned during a normal SQL injection.

However, when attackers are unable to get information from a database server, they frequently switch to time-based SQL injections to achieve their goals. This is accomplished by employing activities that take a long time to complete—often many seconds.

How to prevent SQL injection?

Attackers commonly target websites with known vulnerabilities. SQL injections during targeted attacks are also commonly caused by unknown, unpatched, or zero-day vulnerabilities.

The simplest strategy to defend your website from SQL injections is to maintain all third-party software and components up to date. However, there are many techniques available to assist prevent SQL injection vulnerabilities.

1) Use Prepared Statements with Parameterized Queries

Prepared statements are used to ensure that no dynamic variables in a query can escape their location. The core query is defined first, followed by the arguments and their types.

Since the query knows what form of data is expected, such as a string or a number, they know exactly how to include it in the query without causing issues.

In the above example, even if the login or password variables try to escape their query, the prepared statements effectively escape their characters to prevent unexpected behavior or SQLi.

2) Make use of stored procedures

Stored procedures are common SQL operations that are saved on the database and differ only in their arguments. Since stored procedures cannot be dynamically inserted within queries, they make it far more difficult for attackers to execute malicious SQL.

3) Object-relational mapping

Most development teams choose to use Object Relational Mapping frameworks to more smoothly transfer SQL result sets into code objects.

4) Allowlist input validation

As a general guideline, do not rely on user-submitted data. Allowlist validation can be used to compare user input to a predefined set of known, approved, and defined input.

When data is received that does not match the set values, it is discarded, thus safeguarding the application or website against malicious SQL injections in the process.

5) Implement the Principle of Least Privilege

The Principle of Least Privilege is a computer science principle that improves access limits to your website in order to reduce security risks.

To put this principle into practice and protect against SQL injections, do the following:

To accomplish an action, use the least amount of rights available on your system.

Allow privileges only for the time that the action is necessary.

Do not grant admin-level access to application accounts.
Reduce the rights granted to each database account in your environment.

6) Remove User-supplied Input

Bad actors can just read the text as it is returned during a typical SQL injection. When attackers are unable to get information from a database server, they frequently switch to time-based SQL injections to achieve their objectives.

This is accomplished by employing activities that take a long time to complete—often many seconds.

Time-based SQL injections are frequently used to determine the presence of vulnerabilities in an online application or website, as well as in conjunction with Boolean-based techniques during Blind SQL injections.

7) Make use of a Web Application Firewall

A web application firewall can protect against generic SQL injections. Web application firewalls can detect and prevent SQL injections by blocking potentially dangerous web requests.

For example, if you detect a SQL injection vulnerability in open-source code, using an Acunetix scan, you may not be able to fix it right away but in such cases, you can utilize a web application firewall to temporarily clean your input.

How to prevent SQL injection - General Guidelines

SQL Injection vulnerabilities are difficult to avoid. Specific preventive strategies vary depending on the SQLi vulnerability subtype, the SQL database engine, and on the programming language.

However, there are some broad strategic concepts you should adhere to keep your web application secure.

1) Develop and maintain awareness

To make your web application safe, everyone involved in its development must be aware of the risks of SQL Injections. All of your developers, QA employees, DevOps, and SysAdmins should be provided with appropriate security training.

2) Do not rely on any user input

Consider all user input to be untrustworthy. Any user input used in a SQL query increases the danger of SQL Injection. Treat input from authenticated and/or internal users similarly to public input.

3) Use whitelists rather than blacklists

Do not use blacklists to filter user input. A skilled attacker will always find a way around your blacklist. If possible, only utilize strict whitelists to check and filter user input.

4) Adopt the latest technologies

SQLi protection is not available in older web development technologies. Use the most recent version of the development environment and language, as well as any latest connected technologies. For example, use PDO instead of MySQLi in PHP.

5) Employ verified mechanisms

Don't try to create SQLi security from scratch. Most recent development technologies include SQLi protection mechanisms. Instead of reinventing the wheel, use such mechanisms. For example, use parameterized queries or stored procedures.

Conclusion

SQL injection (SQLi) is the manipulation of SQL code to gain access to protected resources, such as sensitive data, or to execute malicious SQL commands.

When used correctly, a SQL injection can disclose intellectual property, client data, or a private company's administration credentials.

SQL injection attacks are one of the most common, widespread, and damaging vulnerabilities in web applications.

This attack can harm any website or web app that uses a SQL database, such as MySQL, Oracle, SQL Server, or any other.

Some ways to prevent SQL injection include using prepared statements with parameterized queries, making use of stored procedures and web application firewalls, and many more.

Thanks for reading till here, hope you have learnt something new today :). You can find more article by me here -

Blog