The rapid development of new technologies has given businesses not only a competitive edge and a profitability boost but also a major cybersecurity headache. Nowadays, hackers can perform both manual and automated attacks, which grow more sophisticated day by day. The funny thing is that although some of the most popular software vulnerabilities are well-known and can be easily detected, they are still being actively exploited.
For instance, SQL injection was first discovered in 1998, yet it is still the number one security risk in web applications, according to OWASP. On top of that, SQL injections, along with brute force and the use of stolen credentials, account for a staggering 80% of data breaches worldwide.
Considering how widespread this web vulnerability is, we’ve compiled a comprehensive guide on SQL injections, featuring the following questions:
In this article, we’ll cover:
What Is SQL Injection?
Before we jump straight to the definition of the SQL injection, let’s first talk about SQL itself. SQL (Structured Query Language) is a programming language used to access and manipulate databases.
SQL is used by some of the most popular database management systems, such as MySQL and Microsoft SQL.
SQL injection (SQLi) is a cybersecurity attack that targets websites and web apps using SQL databases. It is a code injection technique that relies on placing malicious SQL statements via web input. In other words, a threat actor or the “bad guy” tries out a range of SQL commands to manipulate the database and receive a response that will hopefully reveal some sensitive data.
In case of a successful SQL injection, the hacker can do one of the following things:
- Bypass the authentication
- Steal the identity of a user, including a C-level executive
- Retrieve, add, modify, or completely destroy records in the database
- Become the administrator
- Perform other manipulative behaviors
SQL injections are so common because of the prevalence of websites using SQL databases and fairly easy implementation.
Inexpensive
Easy to execute
High attractiveness of the target (sensitive, high value data)
popularity of
shared database infrastructure = multiple apps affected at once
How Does SQL Injection Work?
SQL injections are possible when a website or web app doesn’t have a proper input sanitization process in place. In simple terms, input sanitization prevents hackers from using special characters to inject malicious code into the data entry field.
A legitimate SQL query is nothing but an interaction between the user and the database. For example, by entering a username and password, the user requests access to the software. If some of the entered characters do not match the credentials stored on the server, the access is denied.
However, if developers were uncareful and failed to implement strong input sanitization, the hacker can use input forms to send their own requests to the database by entering strings of executable SQL code.
Before we show how to do a SQL injection, let’s first go over SQL injection types to better understand the principles making this web-based attack possible.
SQL Injection Types
Depending on the threat actor’s intent and the system’s security controls, different SQL injection techniques are applied. Let’s go through some of the most common SQL injections types to be aware of the scenarios that give the green light to hackers.
Classic SQL Injections
The classic SQL injection, also known as in-band, relies on one communication channel to both perform the attack and gather the data. It is considered the easiest to implement, and it can exfiltrate data through:
- Error messages. In this case, the black hat deliberately enters the input that will result in an error to gain insights on the database structure. We’re sure you’ve seen those confusing error messages that bring no value for end-users yet contain vital technical details – exactly what hackers hope to retrieve.
- UNION operators. Hackers can use the UNION keyword to extend their original query by combining two or more SELECT statements. This type of attack allows performing cross-table queries.
Blind SQL Injections
This type of injection requires more patience on the hacker’s part because no data gets displayed on the web page and database enumeration is done character by character. It is applicable when the database shows only generic error messages yet the code may still be vulnerable. Blind SQL injections require some brute force techniques and countless requests; however, this process can also be automated thanks to tools like SQLMap. Blind SQL injections are further subdivided into:
- Content-based. The threat agent asks a series of true or false questions and determines if the statement was true or false based on the difference in the app’s reponse.
- Time-based. The hacker uses different time-based functions of SQL databases to find out which type of database is used as different databases use different functions for the same operations. Also, by using sleep or similar SQL commands, they can easily identify if the query is true or false: immediate response – false; the database responds with the mentioned delay – true.
Out-of-Band SQL Injections
This SQL injection type is less common as it depends on the server’s ability to create DNS or HTTP requests to transmit data to the hacker. The latter features may not be enabled on every app’s database server, limiting the success rate of this malicious endeavor.
Out-of-band SQL injections are called so because the hacker cannot use the same channel to carry out an attack. For example, when the server’s response is too slow or unstable, it becomes difficult to work with inferential SQLi.
vulnerability
vector globally
SQL Injection Attack Example
While it is always a great idea to equip yourself with theoretical knowledge, it is even more beneficial to gain practical expertise in the question under study.
Without further rambling on, let’s have a look at the basics that can give you a better understanding of how to perform a SQL injection.
SQLi Code Examples
SQLi types are indeed plentiful; however, the easiest and the most popular ones revolve around manipulations with the UPDATE, INSERT, and SELECT statements as well as WHERE and ORDER BY clauses.
The examples below cover the basics and are only possible if there are no security controls against placing hazardous syntax into input fields.
Retrieving Data with WHERE Clause
Suppose you’re browsing through an e-commerce site and stopped on the Accessories section. A typical URL in this case would look something like this:
https://e-commerce-website.com/products?category=Accessories
What happens under the hood looks slightly different:
| 1 2 3 | SELECT * FROM products WHERE category = 'Accessories' AND released = 1 |
Here, the web app makes an SQL query to the database to display only the available accessories to the end-user. This request can be easily modified by inserting some “smart” SQL syntax like double dashes to comment out part of the query and thus make it irrelevant.
https://insecure-website.com/products?category=Accessories’––
Here is the SQL code for the same query:
| 1 2 3 | SELECT * FROM products WHERE category = 'Accessories'--' AND released = 1 |
In this case, the hacker can also view all the unreleased items as the restriction released=1 is disabled.
The vulnerability can be further abused by adding inputs that are always true, such as OR 1=1.
https://insecure-website.com/products?category=Accessories’+OR+1=1––
What the database receives is the following:
| 1 2 3 | SELECT * FROM products WHERE category = 'Accessories' OR 1=1--' AND released = 1 |
In the above case, the database will return all items, both released and unreleased, from all other categories.
Logging In with No Credentials
Another use case of using smart syntax is gaining access to an app having only a username at one’s disposal. The strategy is the same: use the double dash sequence to gray out part of the code.
Here is the basic SQL code making the user sign-in possible:
| 1 2 3 | SELECT * FROM users WHERE username = 'john' AND password = 'johndoe123' |
Here is the injected version of the same query:
| 1 2 3 | SELECT * FROM users WHERE username = 'administrator'--' AND password = '' |
If the hacker assumes that the user name is “administrator” or “admin”, which is still may be the case, and uses the SQL comment indicator before the password part of the query, they can log in as the actual administrator or the user who happened to have this username.
What is even more shocking is that in some cases even the username is not required.
| 1 2 3 | SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""="" |
By tricking the app with always-true statements, like one empty string is always equal to another empty string, the hacker can successfully log in having no credentials whatsoever.
Maximizing the Gains
In case an initial SQLi was successful and the app responds with the actual data from the targeted table, it is an opportunity hackers cannot afford to miss. Most likely, they will proceed with a UNION attack.
Hackers may exploit the UNION keyword capabilities, such as executing one or more additional SELECT queries and appending the results to the original query, to access data from other tables.
A UNION attack does require certain database structure knowledge and other prerequisites to be met. However, if we assume that a threat agent knows the number of columns the original query returns, the type of data they can hold, the name of the table and its columns, they can go from this
| 1 2 3 | SELECT name, description FROM products WHERE category = 'Accessories' |
to this
| 1 2 3 | ' UNION SELECT username, password FROM users--, |
which will allow them to retrieve not only the products and their descriptions but also respective usernames and passwords.
SQLi Real-life Examples
To prove that we do not exaggerate the real menace of SQLi, we’ve compiled a list of fairly recent SQLi attacks and the heavy losses the victim companies have suffered or could have suffered if it weren’t for white hat hackers.
3.7 mln hashed passwords leaked
Simple Google Hack to Find DB Vulnerabilities
You may also wonder how hackers know which website (out of almost 2 billion) to target to increase their chances of a successful SQLi attack. Well, there is a special technique known as Google hacking or Google dorking. The latter is used for retrieving all kinds of sensitive information – from email addresses and payment card details to vulnerable servers and exposed internet cameras.
Google hacking was born back in 2002 when Johnny Long, a recognized computer security expert, decided to document advanced Google search queries that revealed vulnerable systems or sensitive information. Over the years, the initial list of Google Dorks, in other words advanced search strings, has grown to an entire database, which is now maintained by Offensive Security.
Using Google hacking for finding SQLi is just a small subset of what you can do with this advanced search technique. And here is how you can do it.
Let’s say we go to Google and type something like this:
| 1 2 3 | site:com inurl:id "You have an error in your SQL syntax" |
This query asks the search engine to crawl all .com websites that have any kind of syntax errors in their SQL code. Those syntax errors are likely to be purposely placed injections.
One more thing to keep in mind is that Google is not the only engine supporting advanced search. You can achieve the same results with Bing, Yahoo, or DuckDuckGo; the only difference is in the search operator syntax that slightly differs aсross the engines.
How to Prevent SQL Injection?
After consuming all this scary information on the SQLi dangers, the reasonable question that strikes your mind is how to prevent SQL injection. Because this attack has been around for a while, the security experts have learned how to fight it and future-proof web apps against different SQLi types.
While each case is unique and requires professional investigation on the part of security consultants and pentesters, these security best practices will do the job for most organizations and reduce the risk of SQLi considerably.
Here are some of the well-known SQL injection prevention techniques that actually work.
Refactor Legacy Code
Legacy software is known to cause an array of issues, with security being one of the most detrimental ones.
The reason why legacy apps are the low-hanging fruit for hackers is their inability to integrate efficient security controls due to incompatibility or other technical limitations. Therefore, if the app cannot be entirely rewritten and migrated to the modern stack, at least partial refactoring of the most vulnerable modules should be considered.
Use Server-side Input Validation
Any data input requires validation for the user to be able to gain access or perform other actions. Client-side validation is often considered a fast initial check within the browser allowing the user to quickly notice any input mistakes. Whether built-in HTML5 or customized with Javascript, client-side validation is easy to bypass and regarded as insecure and unreliable.
Therefore, strong input validation can only be done on the server-side. Moreover, server-side input validation is further subdivided into allowlist or positive and blocklist or negative. The former is considered more effective in combating SQLi.
Here is how allowlist server-side input validation works: the server only accepts the data that has been defined as good and acceptable. For instance, verifying the credit card number details may involve such steps as checking for the expected number of digits, only numeric input, and the Luhn formula pass.
Moreover, server-side validation should be carried out both on the syntactic (correctness of date, currency symbols) and semantic levels (validating the input in relation to the business context).
Even though allowlisting can be quite comprehensive, server-side input validation should not be viewed as the primary security measure against SQLi, rather an extra step in the multi-layer security program.
Restrict Database User Privileges
The principle of least privilege (POLP) is well-known in the information security field. And it is pretty straightforward: limit the database access based on individual user roles and their everyday functions.
Usually, only a limited number of people need to create or delete smth in the database. Therefore, it is vital to make sure the majority of the users only have the read access and partial table views strictly necessary for their operations.
Another crucial factor to keep in mind is that you need to change the default DBMS privileges to restricted, something that will not give the hacker complete control over the system even in case of a successful SQLi attack.
In case of multiple apps using the same database, each app requires a separate database user account with distinct access rights. This approach also helps minimize the damage inflicted by potential SQLis.
All in all, to avoid the snowball effect when the hacker gains the root rights via SQLi, make your access policy as granular as possible.
Use Prepared Statements with Parameterized Queries
According to OWASP, the use of prepared statements with parameterized queries is the primary defense against SQLi. Prepared statements are so effective against SQLi because they make it easy for the database to clearly distinguish between the code and the user-supplied input.
A typical prepared statement will follow a simple algorithm:
- an SQL statement template is created and sent to the database
- the SQL query goes through parsing and semantics check
- the SQL query is compiled with placeholder text (binding)
- the placeholders get replaced with the user-supplied input
- the query is stored in the cache
- the database executes the statement
As seen from these steps, the user-provided data cannot affect the intent of a query as it is separated from the executable code and will always be interpreted as a simple string.
Here arises another question: If prepared statements are so secure, why do we still have so many SQLi incidents? The matter is that, in certain cases, prepared statements can negatively affect the app’s performance, which is why developers opt not to use them and resort to other, less effective security techniques.
Implement Stored Procedures the Right Way
A stored procedure is a prepared SQL code that can be saved and used multiple times in the future. The difference between a stored procedure and a prepared statement is that the SQL code for the former is defined and stored in the database itself: when the query needs to be executed, it is just called from the app.
Stored procedures are considered as effective as prepared statements under the condition they are implemented safely. Otherwise, they are also vulnerable to SQLi.
Developers need to be cautious not to include unsafe dynamic SQL inside the stored procedure. If generating a dynamic SQL inside a stored procedure is entirely unavoidable, we advise parameterizing the queries inside the stored procedure instead of concatenating the parameters.
Escape All User-Supplied Input
This approach is quite a desperate remedy: it should only be used if nothing else is feasible. A typical use case would be protecting legacy software and having a limited budget for fully-fledged input validation.
Here is how escaping works: the escape function encodes special characters, such as “/”, “?”, “$”, for the database not to confuse the user-supplied input with the code from the developer.
While each DBMS has its own escaping scheme, the purpose is the same – to prevent the user-supplied input from being interpreted as an executable command.
Once again, this technique cannot guarantee 100% protection against SQLi. Ideally, the app should be rewritten from scratch using parameterized queries or stored procedures.
Another escaping method is hex-encoding all user-supplied input. This scenario presupposes encoding not only special characters, but every character in the input before including it in the SQL query.
Conceal Database Errors
What caused the database error is very useful for the developer but not the end-user. It’s vital to ensure that error messages do not give away any sensitive information that a hacker could use.
For example, instead of displaying the SQL statement revealing where exactly the error occurred, show generic, customer-oriented pop-ups like, “Sorry, we’re experiencing technical issues. Please try again later.”
Encrypt Sensitive Data
Leaving highly confidential data in plain text is never a good idea. This is why so many businesses, especially in the fintech, healthcare, and media sectors, have integrated data encryption into their systems – the cost of data exposure is just too high (about $4 mln on average).
Another critical factor to keep in mind is that encryption alone is no silver bullet. Suppose a business has password hashing in place, which means the actual passwords are never stored, only their hashed equivalents. However, if users didn’t bother to create strong passwords, which is often the case, their credentials can be easily compromised with hash or rainbow tables that can map keys to values.
Salting the encrypted hashes would provide an additional security layer. In simple terms, salting is when a random piece of data is added to the password before it is hashed. Salted passwords reduce the likelihood of weak user passwords to be found in a hash table.
Perform Regular Code Inspection
SQLi is often the result of poor software development practices. Therefore, it is only reasonable to start the prevention plan from the source code by letting third-party security experts perform an unbiased code audit.
Equally important is conducting regular penetration testing to spot red flags and detect the vulnerabilities overseen by scanners and fuzzers. For a skilled penetration tester, discovering a SQLi vulnerability is an easy task; moreover, they can explicate the outcome of other potential or existing exploits and offer a SQL injection remediation plan matching the company’s status quo.
Summing Up
SQL injection is a vulnerability so old that it is shameful to know it still poses a threat to modern websites and apps. It’s widely popular because of relatively easy implementation, the prevalence of SQL DBMSs, and the immense value of the business data. The good news – it’s preventable. All it takes is aligning your engineering efforts with the latest security practices, continuous monitoring and inspection, and independent sql injection testing to close any remaining gaps.
What’s your experience with combatting SQLi? We’d love to know; please share via email or in the comments!
Forewarned is forearmed: The ultimate
SQLi prevention cheat sheet
