DEV Community

Prosper Yong
Prosper Yong

Posted on

SQL Injection 101

Security flaws such as SQL injection allow an attacker to meddle with a database query. It allows an attacker to access data that they would not usually be able to. Data belonging to other users, or any other data that the programme itself has access to, may be included in this. As a result, the application's content or behaviour can be permanently altered by an attacker in many instances.

Sometimes, an attacker can use a denial-of-service attack in conjunction with a SQL injection attack to compromise a server.

SQL Injection - What is It?

SQL Injection (Sqli) is a sort of injection attack that allows malicious SQL queries to be executed. These statements are used to operate a database server that is hidden behind a web app. By exploiting SQL Injection vulnerabilities, attackers are able to circumvent application security controls. SQL Injection is a technique that allows hackers to add, edit, or remove entries in a database by bypassing the authentication and permission of a web page or online application.
Any website or online application that uses a SQL database, such as Oracle, SQL Server,MySQL, or others, may be vulnerable to the SQL Injection vulnerability. Cybercriminals may use it to obtain illegal access to your sensitive data, including your customer information, personal information, and more, Security experts consider SQL Injection attacks to be one of the web's oldest, most common, and most serious weaknesses. Injections are the number one danger to online application security, according to the OWASP organisation (Open Web Application Security Project).

An SQL Injection Attack: How It Works and Why it Is

An attacker must first discover immune - compromised user inputs on a web page or web application in order to launch a SQL Injection attack against them. Such user input is used directly in a SQL query on a web page or web application that contains a SQL Injection vulnerability. The attacker has the ability to produce input material and modify it. Malicious payloads are commonly used to describe this type of material. As soon as the attacker delivers this material to the database, malicious SQL instructions are performed.

In relational databases, SQL is the query language of choice. Access, change and delete data is possible with it. As a result, SQL databases are used by many online apps and websites to store all the data. Additionally, you can utilise SQL commands to perform operating-system commands in specific situations. SQL Injection attacks can have significant repercussions if they succeed.

  • It is possible for hackers to discover other users' passwords by using SQL Injections on the database. Then, they can pose as these users and get access to their accounts. Alternatively, the impersonated user may be an administrator of a database with full access capabilities.
  • SQL allows you to retrieve and produce data from a database using a query language. Unpatched SQL Injection flaws might enable a hacker access to all of the information contained within an organization's database server.
  • Some database servers allow you to connect to the operating system through the database server itself. This might be deliberate or unintentional. SQL Injection might be used in such a scenario to attack the internal network behind a firewall.
  • SQL also allows you to add and edit data in a database. Example: An attacker might use SQL Injection in a banking software to manipulate balances and move money to their account.
  • As well as deleting entries, you can also drop tables using SQL. In spite of database backups, loss of data might impair app availability until backups are restored, assuming they are made. In addition, backups may not include the most current data, which might be problematic.

These attacks can be classified as in-band (using database faults or UNION commands), blind, and out-of-band (using a remote server). Please see the following articles for further information about them: SQL Injections.

An Example of SQL Injection

To begin, let's look at a basic example. An attacker can circumvent application security by exploiting a SQL Injection vulnerability to authenticate as the administrator.

A web server executes the following pseudocode. Authentication with a username and password is demonstrated in this example. Username and password are stored in a table named users in the sample database.

# Define POST variables
user = $_POST['username'];
pass = $_POST['password'];

# SQL query vulnerable to SQLi
sql = “SELECT id FROM users WHERE username=’” + user + “’ AND password=’” + pass + “’”

# Execute the SQL statement
Enter fullscreen mode Exit fullscreen mode

SQL Injection is possible in these input fields. An attacker might utilise SQL instructions in the input to change the SQL statement that the database server executes. They could, for example, use a single quote technique to change the pass field to:badguy' OR 1=1
So, the database server executes this SQL query:
SELECT id FROM users WHERE username='username' AND password='password' OR 1=1'
The WHERE clause returns the first id from the users table  regardless what the username and password are because of the OR 1=1statement. The administrator is frequently the initial user id in a database. The attacker not only gets administrator access but also bypasses authentication.

How to Prevent an SQL Injection

SQL Injection issues can be difficult to prevent. SQLi vulnerability subtypes, SQL database engines, and programming languages all have different protection methods. If you want to make your online application safe, there are a few broad strategic guidelines to follow.

Step 1: Train yourself and stay mindful

If you want to make your online application safe, everyone engaged in creating it has to know about SQL Injections. All your developers, QA personnel, DevOps, and SysAdmins should receive appropriate security training. You might start by directing them to this website.

Step 2: Don't rely on any input from the user.

You should consider all user input to be suspect. As long as a user input is utilised in a SQL query, there is a possibility of SQL Injection occurring. Use the same approach when dealing with input from authorised and/or internal users.

Step 3:Take advantage of the newest technology.

There's no SQLi security in older web development tools. Ensure that you're using the newest version of the development environment and language, as well as the latest technology associated with those environments/languages Use PDO instead of MySQLi, for example, in PHP.

Step 4: Using whitelists instead of blacklists.

The usage of blacklists should not be used to censor the input of users. A smart attacker will almost always find a method to get around your blacklist. Whenever feasible, only employ tight whitelists to validate and restrict user inputs.
Step 5: Scan on a frequent basis.
It is possible for your developers to add SQL Injections, or for other libraries/modules/software to do so. A web vulnerability scanner should be used to check your online applications on a regular basis.

Step 6: Verify your methods.

Don't try to create SQLi protection from the ground up. Almost all of today's programming tools have techniques for protecting against SQLi. Such techniques should be used instead of attempting to try something new from scratch Use structured query language, for example, or stored procedures.

Discussion (2)

stuartcmd profile image

Excellent advice. Thanks!