What is SQL Injection and How to Prevent it?

SQL injection is among the vulnerabilities that are highly dangerous for web-based applications. It arises when a user adds malicious data into a SQL query. When filling out a form, for instance, if SQL injection is possible, cybercriminals create user input to bypass authentication, corrupt the records, and steal valuable data from your database. SQL injection is of varying types. However, they all share a similar cause. It concatenates the malicious data entered by an attacker using a query string. Hence, the input of the user can alter the original intent of the query. SQL injection examples include.

• Entering line comments to escape part of a query (–)
• Ending an initial SQL query and beginning a new one (; DROP DATABASE DATABASE­_NAME;)
• Using UNION to connect data from multiple tables
• Attaching a Boolean Expression that is always true to a WHERE clause (OR 1=1).

In this post, we shall look at practices that you can adopt to prevent SQL injection.

How to Prevent SQL Injection

Avoid relying on the input validation from the client-side

Input validation on the client-side has many advantages. With it, you can prevent the invalid query that a user can send to your database logic. Unfortunately, this only works for users who have good intentions and plan to use the system according to its design. Giving direct feedback to a user that a particular value of the input is not valid is a user-friendly approach, and it is helpful. Hence, to help your user experience, ensure that you use validation on the client-side.

Looking at SQL injection, you do not rely on a method. You can remove client-side validation by altering a JavaScript code on your browser. Besides, performing a basic HTTP call to the backend is pretty easy in a client-server architecture using a parameter that causes SQL injection. You can accomplish this using traditional commands like curl and tools like Postman.

Validation should be done on the server-side, as close as possible to the source. Here, it should be where you create the query. You should consider anything that a client sends you to be potentially harmful. Therefore, it is a dangerous idea to rely on client-side validation for SQL injection.

Query parameterization and using prepared statements

Various languages have features available that can help you prevent SQL injection. When you are writing an SQL query, ensure that you use prepared statements for compiling the query. Prepared statements allow you to perform parameterization of queries. What is query parameterization? It is a method for creating dynamic SQL statements. You can achieve this by creating a base query with some placeholders and attaching the parameters given by the user to them.

The database can shield itself from escaping when you implement parameterized queries and real prepared statements. It starts by building an execution plan for the query on the SQL query string that has placeholders. Then, the trusted parameters are sent to the database. Since we have created the query plan already, the parameters do not influence it.

Using an additional layer of Object Relational Mapping

You can also consider adding a layer of object-relational mapping (ORM) to prevent SQL injection in your database. An ORM changes the data stored in the database into objects and vice-versa. By using an ORM library, you can reduce the explicit SQL queries. Hence, the vulnerability to SQL injection reduces.

Java’s Hibernate, Sequelize in JavaScript, and Entity framework in C# are examples of ORM libraries. Mapping between the objects and databases is possible because Java and C# are both strongly typed. In this way, there is no need to get involved with SQL queries.

There is a problem when you are creating custom queries. Hibernate has a query language called Hibernate query language (HQL). Ensure that you use the createQuery () function that works similarly to a prepared statement.

Using Sequelize, you can define various values and specific types in a database map. The ORM library will ultimately translate the logic back to the structured query language. Therefore, we can only trust that the libraries have a proper implementation of parameter escaping.

It is crucial to ensure that the ORM library that you use does not contain SQL injection problems. Routinely scan the libraries for various known vulnerabilities. You can one day land into trouble if you use outdated versions of ORM libraries.

Input validation

Always validate the inputs coming into your system. It is better to have multiple layers against SQL injection. Ensure that you have multiple layers of defense, albeit prepared statements that have parameterized queries being the best way to defend your database from SQL injection. Similar to imposing limitations on a database user’s privileges, validating the inputs from all users is an advisable practice for lowering the risks to your application.

However, this mechanism is not supported by some languages and older database systems. They do not allow a user to supply as parameters the user inputs. In such a case, the acceptable alternative is a validation of user inputs.

The agreed-upon way for input validation is to allow listing. It would be best to create rules that describe all the patterns allowed using a regular expression or an adequately maintained library. When you combine this with parameterization of queries and prepared statements, they all give you a solid defense mechanism against SQL injection.

Regularly scanning for SQL injection vulnerabilities in your code

It is easy to create custom code, although you can also easily make mistakes. For this reason, we have pair programming and source code reviews. Can the code reviewer be good with security? Can they see an SQL injection bug in your code? Examining your custom code for potential loopholes is critical in ensuring that it is secure. Today, there are automated ways for inspecting the code for SQL injection. We can do this using Static Application Security Testing (SAST) tools.

Conclusion

Beyond the methods above, there are many others that you can use to prevent SQL injection. Each method has its merits and shortcomings. Therefore, it is advisable to use several methods and bot detection techniques. It ensures that they complement each other. As a result, they form a formidable force against any attempt of injecting malicious SQL codes into your database.