SQL Injection Attacks and How Developers Can Prevent Them

#webdev #security #sql #100daysofhacking

This week during my 100 Days of Hacking challenge, I learned how hackers can infiltrate applications that are vulnerable to SQL injection attacks, the side effects of these attacks, and how web developers can prevent them from happening. I believe that every developer should learn more about security. That being said, learning security should be more accessible to developers. So, let's go over the basics!

What is a database?

A database is how information can be stored and organized, so that it can later be accessed, managed, and updated. The most universal example of the type of data stored in a database is user information. When you register on a website you fill out a form that asks for information such as your name, username, email address, and password. After you click sign up this information is sent as a request to your backend, and your backend will communicate with the database on how to store this information. Now that the data is stored, this enables users to be able to sign into their account the next time they access the website. When the user goes to login, clicking the login button will submit a request to your backend, which will communicate with the database to make sure the input username exists in the database and that the input password matches the stored password associated with that username.

What is SQL?

SQL (Structured Query Language) is a language that is used to communicate with relational databases, meaning a data table structured with rows & columns. Using SQL queries you can retrieve, update, insert, or delete data from your database. If you want to learn more about how exactly to write SQL queries, I loved learning through SQL Zoo. It is important to note that using a NoSQL database does not mean it is safe from injection attacks! We can cover more about that in another article.

What is SQL Injection?

Because SQL databases are so common, SQL injection attacks are one of the biggest risks to websites. If you are not taking proactive steps to prevent SQL injection in your application, an attacker will be able to input their own query to your application. This means they could essentially have full access to your database and will have the ability to read, update, insert, or delete anything that they want! A bit frightening, but the goal of an attacker accessing the database isn't always just to gain sensitive information such as usernames, passwords, or payment information. Imagine if you worked for a healthcare company and your company's database was vulnerable to SQL injections. The attacker could alter patient information, which would be very dangerous! Whether they are retrieving, inserting, updating, or deleting data these could all have very serious implications for any company.

As a developer, it is important to understand how these injection attacks are discovered and performed so that you can defend against them. Let's step into the attacker's perspective to see how this is done.

How is a SQL Injection Attack Performed?

The first step is checking if the application is vulnerable to SQL injection. In this example, we are trying to log in to an account as the administrator user. By simply submitting a single quotation mark as the username, we receive an internal server error. This is a sign that the application is vulnerable! If the application wasn't vulnerable we would get back a handled error response such as 'Invalid username or password'.

To understand why this is happening let's break down what the SQL query may potentially look like for a login feature. In this scenario, if the input is administrator for the username and the password input is admin the SQL query would look like this:

However, when we just use a single quote as the input instead of valid input our SQL query looks like this:

By using a quote as the input, our SQL query is now trying to retrieve an empty input (WHERE username='') but then it runs into an additional quotation mark and the server is unable to handle it properly so it breaks the application and throws an unhandled error.

What can be done with this, now that we know the application is not properly handling user input? In SQL we have the ability to comment out part of the code, just like we do in other languages like JavaScript or Python. By commenting out part of the code, the portion that is commented out will be completely ignored. We know that our server throws an error when it reaches the additional quotation mark, so if we comment out that quotation mark and everything past it, our query that is sent to the database would look more like this:

For most databases, to write a comment we just have to input a double dash. So we will use a quote after the username to close off the query and use the double dashes to have the rest of the the query ignored.

Logging in as a user is only a small piece of all the possibilities with SQL injections. If an application is vulnerable to SQL injection attacks, an attacker is able to find out which database is being used (i.e. Oracle, MySQL, PostgreSQL), what tables exist in the database, what columns exist within the tables, and could also retrieve data from different tables within the database called a UNION attack. Even if the application doesn't return visible results of a SQL injection on the page or in the response, attackers can still access the database by using Boolean based logic, triggering time delays in processing the query, or by triggering out-of-band network interactions. These techniques can get fairly in-depth but are really interesting to learn about as you can see exactly how attackers can get around your application's defenses if they are not secured correctly.

How Can Developers Prevent SQL Injection Attacks?

The good news is that securing your application against SQLi attacks is fairly straightforward. As a developer, you should never directly execute a user's input. The best prevention method is using parameterized queries. A parameterized query is when you pass in a parameter into the query, rather than passing in the user's input directly. By doing this the query inserts the values of the parameters being passed in and it will search the database for the parameter or stores it rather than just executing the query in blind faith.

In the example above we are making a POST request to add a new company to our database. Our query to the database takes the code, name, and description that were in the request body and directly inserts them into the database. This is the incorrect way to write a query and will allow SQL injection vulnerabilities.

In this example we are correctly using parameter queries to prevent SQL injection. Rather than directly inserting the variables into the query, use $1, $2, $3 as placeholders to represent the variables that will be passed in. After the query statement, pass an array of values as the second argument to the query. In this case our array of values consists of the code, name, and description variables that we retrieved from the request body. These variables are 1-indexed and the order matters, so $1 represents the code and $3 represents the description.

Defense in Depth

Because a defender has to cover all security flaws to be successful and an attacker needs to only find one single flaw, it is best to implement multiple security practices. This is called defense in depth. If you were to own a jewelry store, you wouldn't consider it safe enough to just have a single lock on the front door. You would implement multiple security strategies such as cameras, a burglar alarm, or a guard dog! The same concept applies to defending you application. While parameter queries are the #1 recommended method of defense against SQLi vulnerabilities, there are cases there you cannot use a parameter query. Parameter queries will not work if you are trying to dynamically add a table name, column name, or SQL keyword into your query. In this instance, you would want to use a whitelist map, where you specifically only allow certain keywords. If a word is not in the whitelist, the query is rejected rather than executed. Additionally, it is recommended to follow the principle of least privilege. This means that every process only runs with the lowest level of permissions needed to perform its functionality. By doing this, it is ensured that if an attacker were to perform a SQL injection the damage would be limited . For example, web servers generally don't require permissions to execute CREATE, DROP, and MODIFY statements so don't grant them these permissions at runtime. Lastly, while we already mentioned that attackers are still able to find ways around it if your application isn't returning visible results or errors, we don't want to make it easy for them! By utilizing custom error messages rather than errors generated by our database that reveal crucial details, we can limit the amount of information that is given away about our database.

If you are interested in learning more about SQL injection vulnerabilities, exploitation, and prevention here are some resources that I recommend:

Next week I will be covering another web application vulnerability and prevention methods, so stay tuned! In the meantime, let me know if you learned anything new about SQL injection vulnerabilities and if there are any resources that you'd like to share.