When developing any application, we consider possible threats that the application might be exposed to in the later IT environment. Especially for applications that communicate with databases, SQL injection attack is a threat. These can be carried out in a fairly simple way.
These attacks take place, for example, via forms on websites. In this case, an SQL expression is added to the data entered in the form. If the application is not protected against this type of attack, the SQL expression is passed and executed along with the application’s base request.
In this way, the attacker can change the behavior of the base query and, for example, prompt the database to return all user data in the system or perform a login without entering a password.
Here is an example of an attack that causes a login without password entry. The login code looks like this in our example:
After entering data into the form, e.g. Login: Jan and Password: Hasło, the SQL query sent looks like this:
The SQL query executed by the database then has the form:
The query is created by joining text fragments. In a very simple way, you can now enter your own text as login or password, the special character ‘. This completes the text and you can start writing your own SQL statement. So if you enter Login: Jan’ OR 1=1 and no password, the SQL query sent by the application will look like this:
The SQL query executed in the database will then have the form:
In this rather simple way, an OR statement has been inserted, which is always true, and a comment that blocks the rest of the query. This causes the database to return a positive response, as if we had logged in regularly with password.
These attacks can cause great damage and should not be underestimated. While the nature of the attack is quite straightforward, it is just as easy to protect against it. One should basically follow this basic rule: Never trust the user and always check the input parameters.
Input parameters can be checked manually, which is a time-consuming task that can lead to errors.
In .NET, there are various pre-built solutions that can be used to perform database queries in a safe manner.
1. Parameter passing via SqlParameter
Instead of performing queries manually, you can use SqlCommand. Here the fixed text of the query is passed as CommandText. All parameters are inserted into the body of the text with an @ sign. Then the values of each parameter can be passed dynamically using the Parameters Add method.
2. Using EntitiyFramework (or other frameworks as long as they are protected against SQL injection attacks)
EntityFramework is one of the most popular database frameworks in the .NET environment. It can be used to create database queries with C# and LINQ code. The code is then converted into a database query by Frankework itself. EntityFramework has built-in protection against this form of attack, so queries can be created freely and no additional validation of parameters or even special parameter passing (as when using SqlCommand) is required.
The query could look like the following:
