Web-applications attacks, including SQL injection attacks, more than doubled in 2019, according to data from SonicWall
Taking into account the trends for more and more businesses going online, the number of applications that will be used is also projected to increase in 2020, as are the web-security threats that go with them. That’s why it’s essential to protect your website, application, or any other instance that may be publicly accessible from these threats, which include the use of SQL injection attacks.
In this blog, we’ll explore the topic of SQL injection attacks — what it is, how an SQL injection attack occurs (using a basic example), and why these attacks are so devastating. We’ll also share some SQL injection prevention tips you can use to keep your data safe.
Let’s hash it out.
What Is SQL Injection (or an SQL Injection Attack)?
If you try to Google “What is an SQL injection attack,” you will probably see a lot of results for tech-savvy people and talking about the syntax change within the SQL queries to perform authorized commands. But let’s try to explain it in simpler terms.
Imagine that you’re going to the bank to perform the wire transfer to your friend and need to give written instructions. You go to the accountant, verify your identity and give them the paper with instructions. But after you leave, some cons have modified your instructions. They added the line that apart from the mentioned transfer, one more transaction of $700 should go to some account you don’t know.
Since your identity was verified, the accountant takes your instructions and performs the two transactions — both the legitimate and fraudulent ones.
But what does all of this mean in terms of an SQL injection attack? Being a human, you will probably check the instructions before giving them to the accountant to make sure that everything is fine. Unfortunately for you, however, a web application does not have this capability, and if the server is not protected, the attack is successful. So, if we translate it to the context of SQL injection, it’s about “injecting” an SQL query (also known as a “true statement”) via the input data from the client to the application.
A successful SQL injection exploit can:
- Read sensitive data from the database;
- Modify database data (insert/update/delete);
- Execute administrative operations on the database (such as shutdown the database management system, or DBMS);
- Recover the content of a given file that’s present on the DBMS file system; and (in some cases)
- Issue commands to the operating system.
Although these types of attacks are nothing new, they still pose major threats to businesses and users alike. According to the U.S. Computer Emergency Readiness Team (CERT):
“One reason for the persistence of these problems is that their underlying causes can be found in almost any web application, regardless of implementation technology, web framework, programming language, or popularity.”
When analyzing OWASP’s Top 10 Web Application Security Risk publication, you’ll notice that SQL injection was listed as the #1 web application security risk back in 2013. It’s stayed in the #1 spot even four years later in 2017 when the Top 10 list was last republished. It would be interesting to see where it will fall on the list in the future when the list gets republished again.
How Does an SQL Injection Attack Happen?
Understanding how an SQL injection attack works is crucial to being able to identify any exploitable vulnerabilities within your web apps and forms.
Let’s break down how this attack works. We’ll start by exploring how a user’s information is added and retrieved from a database. From there, we’ll break down how a threat actor uses SQL injection attacks to gain access to sensitive information:
How to Add and Retrieve Information from a User Database
On the account creation page, once a user is created, their information is stored in a database along with all the other users. With that being said, whenever a new user is created, an insert query is running to add the user to the database as shown below (take note of the table and columns names):
After understanding how the data is inserted into the database, let’s see how it is read from the database by looking at the login screen. On the login screen, users will be able to log in to the application using the username and password combination supplied in the account creation step. To display this information, a select query is run to retrieve the appropriate user’s information from the database as shown below where user JohnDoe12 is trying to login (take note of quotations):
How an SQL Injection Attack Works
This is where SQL injection comes into play. If you notice in the query above, we return information about the user if their username and password are contained in the database. Based on how that query was written, the user’s input was directly inserted into the query statement, which is a no-go. Because of this, an attacker can insert query-altering information to get the query to return unexpected results (as shown below):
Instead of typing in JohnDoe12, the attacker types in “‘ OR 1=1 —” as the username. The single quotation to begin the attacker’s query ends the username parameter, making the query search for users where their username is ‘’ or blank.
After the single quotation, the attacker types OR 1=1. Using common math, we know 1 will always equal 1. With that being said, this expression evaluates to true. The double hyphen (—) tells the query to comment out and ignore the remaining text, hence the green color.
Understanding these parts of the attacker’s query, the query would search for users with no username or where TRUE. Because True will always evaluate to true, the query would return all (*) users from the Users table.
Here’s a great video from Hacksplaining, which is a demonstration that breaks down a simple SQL injection attack:
Example: An SQL Inject Attack Scenario
Let’s go through a basic example together to show how a SQL injection attack would happen. In this scenario, you’re an employee for a credit monitoring company. Your company’s main product is a website where users can sign up for free credit monitoring.
To get started with the basic tier, users have to create an account where they give their name, current address, email address, username, password, and social security number. For those customers that would like to set up a premium tier, they have to give the same information as basic users, plus their banking information for Pro charges.
Why would someone want to attack a credit monitoring company you may ask? One word, data! Your company is a virtual gold mine for attackers. Not only does your company have customer emails and home addresses, but they also have social security numbers and banking information.
Why SQL Injection Attacks Are So Devastating
The consequences of SQL injection attacks may be disastrous. Personal information, passwords, and other internal sensitive data can be used to commit various crimes starting from identity theft to financial fraud. Moreover, it affects not only people whose info was stolen but also the company that was a victim of a security breach. Apart from frustrated customers, lawsuits, and compensation expenses, companies face significant reputation loss that may lead to getting out of the business.
Cybercriminals hunt personal information due to the reason that the social security number, also known as an SSN, never gets old and it’s easy to commit crimes in several months or years after stealing this information. This differs from other types of sensitive information, such as bank info and credit card details, which require more timely action on the part of criminals who wish to use this information to commit fraud or to sell the obtained data to someone else. In the hacker community, there are ecommerce stores in Darknet where people are selling the stolen information to be able to act on the received info with malicious intentions.
Now that we understand what an SQL inject attack is and why it’s so devastating, let’s dive deeper into the precaution measures that can help to avoid these attacks.
4 SQL Injection Prevention Tips to Mitigate an Attack
In order to make your web application secure and unexposed to SQL injection attacks, there are certain actions that need to be performed to defend your server:
1. Say No to Dynamic SQL and Yes to Prepared Statements
Do not include users’ inputted data directly into SQL queries. In order to achieve that, it’s necessary to disable “data interpretation” so that once data is entered into the database, it is not processed. Even if it’s written in the SQL query format, the system will not start executing it but will place the data as it is.
2. Sanitize User Input
Make sure that the special syntax recognized by SQL is restricted from embedding the commands containing different data inputs. Some data that are safe to input into the JSON files may be harmful to SQL queries and SSH commands.
3. Limit Database Permissions
Practice the principle of least privilege (POLP). When a user works on the website, for sure to do some modification, the highest-level access is required. But think twice before putting a checkmark near the “all privileges” option that grants unlimited access. Instead, you must make sure that the person who requires the highest-level access actually needs it to perform their duties.
4. Limit the Display of Specific Errors
With some login screens, if a user inputs the wrong username, an error will pop up saying “User ‘JohnDoe123’ was not found.” Being this specific opens the door for attackers to brute force logins. In other words, attackers can continue inputting random usernames until the banner no longer shows. To prevent this, you will need either limit the error display or turn it off completely. This ensures that only your internal users have access to the error log to be able to troubleshoot issues when they occur.
In addition, it’s also possible to implement the procedure on SQL injections prevention within your company, so that every time the new updates are planned, you can instruct the employees on what aspects are worth attention.
Final Thoughts on SQL Injection Attacks
While the number of SQL injection attacks is increasing, luckily for us, detection technologies are becoming smarter, which makes such attacks easier to stop. First and foremost, before starting the development:
- Make sure to outline the security requirements;
- Initiate regular code reviews by system architects; and
- Don’t forget to conduct security penetration testing before releasing to production.
If you only make changes to SQL, the vulnerabilities of other systems hosted on the server may lead to a successful attack. So, it’s necessary to take care of server-level security by:
- Installing the Firewall,
- Having anti-malware protection, and
- Performing regular updates to the Operation System, SQL, PHP, and other components as soon as they are released.
The same goes for maintaining current software versions and different themes/plugins, if applicable.
Last but not least, use only licensed software from authorized vendors as backdoors may be included in it even at the purchase stage. Also, if you’re the one who operates the service, make sure to closely follow industry news to keep an eye out for new attack methods.
This article was co-written with Kevin Kline, principal program manager at SentryOne. Kline is the founder and former president of PASS and the author of popular IT books like SQL in a Nutshell. He’s a renowned database expert, software industry veteran, Microsoft SQL Server MVP, and long-time blogger at SentryOne. You can follow Kevin on Twitter and LinkedIn.