Security Vulnerability – SQL Injection

To build a web product and run it locally is easy. To deploy and make it public to the world is much more difficult. One of the difficulties we must face is the possibility of our website being hacked by millions of hackers surrounding the world. Therefore, knowing how to deploy is one thing, but knowing how to avoid being attack by all the bad guys is much more important. Day by day, hackers try to scan every hole in the security wall to attack the website. Therefore, as a developer, we are also a protector of our children, need to improve our knowledge of security. This series aims to identify some popular vulnerabilities a web product may encounter.

Part 1: SQL Injection

Part 2: XSS Attack

Part 3: Password Management

Part 4: CSRF and “Silhouette” Threat

---

The images using for examples may be taken from https://www.hacksplaining.com/, A very good website for security vulnerability teaching.

SQL Injection

This is one of the most popular and dangerous attacks. The damage it causes can lead to the leak of user/system information in the database. Hackers can then use those pieces of information to shut down the whole server, steal money, blackmail user. SQL Injection is popular because it is super easy to do and you don’t need to be an experienced hacker to perform it.

Let’s take an example with a simple login form, where it asks users to type in username and password:

When communicating with the database, it can be very often that the developer purely query like this

let SQL = "SELECT * FROM Users WHERE username = '" + request.username + "' AND password = '" + request.password + "'";

If user enter username = user123 and password = password. The query will look like this:

SELECT * FROM Users WHERE username= 'user123' AND password = 'password'

Well, it looks fine. The logic is correct though. However, hackers can use this to add some query. For example, they can type password = ‘ or 1=1–.

The query then becomes like this:

SELECT * FROM Users WHERE username= 'user123' AND pass = '' or 1=1--

For SQL Logic, we all know that the condition always returns true. And by doing this, the backend will return a successful authentication of this user.

The website having this SQL Injection vulnerability can be easily detected by hackers. They can try to send the password as ‘. If the server return unexpected error occurred (but not the usual wrong password error), it would mean that the SQL injection can be used.

Using SQL Injection, they can write some simple SQL queries to get all the information form all tables or drop all tables in the system ( this is the evilest thing).

Big Companies also encounter this

This vulnerability is so simple but easily be ignored. A lot of big companies still encountered this in the past.

How to prevent this?

Filtering Request: This is the easiest solution. We can write a filter to get rid of all quotes or semicolon characters. We can also filter all DDL and DML keywords. A lot of frameworks have been built for this. We can either escape input or sanitize the input. Modern front-end frameworks usually offer sanitization by default.

Parameterized Statements: BE frameworks usually have options to query database using parameters instead of directly concatenating the request fields. Using stored procedures is also helpful.

ORM/Persistence Framework: Nowadays, developers can utilize ORM or persistence framework ( for example Hibernate or myBatis in Spring). However, not all ORM is immune to injection, so developers need to choose wisely.

Not showing exception/error to clients: This is a good practice in general security. Never show the real error to clients, a hacker can utilize this to find the loophole.

Database Role/Permission: Developers should also allocate clearly who can read. who can edit, who can delete information in the database.

The example above is only one way to do SQL Injection. Hackers can also spoil the system in many ways, as long as it interacts with server API ( for example: through URL query). Therefore, developers need to be aware of all possibilities.