SQL Injection

This article will explore the issue in greater detail

Photo by Snowing on Freepik

In my last published article, I said goodbye to Stored Procedures, briefly mentioning SQL injection and providing a conclusion in the context of the advantages and disadvantages of stored procedures. The big selling point for stored procedures is that it naturally prevents SQL injection. Unfortunately, this may not always be the case, and one would argue that keeping good code practices will most likely make SQL injection attacks virtually impossible, regardless of whether a stored procedure is used, or not.

This article, designed for beginners, will explore the issue in greater detail.

Case study

The issue comes from mixing both data and SQL instructions within one string, which is passed to the SQL server for execution. Let us take a look at a classic example first.

We will introduce some bad practices that most likely show up in designs that are vulnerable to SQL injection.

As shown below, our sample database contains two tables:

Please note, that contrary to the column name, the password is not hashed (and salted). This is intentional and we will refer to it later.

Example web page allowing login to the service:

To produce this bad example, I used Razor Pages, so the back-end script for Index.cshtml looks like below:

using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc.RazorPages;
using UnsecureWebApp.Models;
using UnsecureWebApp.Infrastructure.Database;

namespace UnsecureWebApp.Pages
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        
        private readonly DatabaseContext _database;

        [BindProperty]
        public User Form { get; set; }

        public IndexModel(ILogger<IndexModel> logger, DatabaseContext database)
        {
            _logger = logger;
            _database = database;
        }

        public IActionResult OnGet()
        {
            ViewData["Info"] = "Please provide credentials.";
            return Page();
        }

        public IActionResult OnPost(User Form) 
        {
            if (ModelState.IsValid)
            {
                if (IsUserAuthenticated(Form.UserEmail, Form.UserPassword))
                {
                    ViewData["Info"] = "Validated.";
                }
                else
                {
                    ViewData["Info"] = "Incorrect login/password.";
                }
            }
            else
            {
                ViewData["Info"] = "Model is invalid.";
            }

            return Page();
        }

        /// <summary>
        /// Example of SQL Injection vulnerability. Never use such code!
        /// </summary>
        /// <param name="email"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        private bool IsUserAuthenticated(string email, string password) 
        {
            var result = _database.Users
                .FromSqlRaw($"SELECT Id FROM dbo.Users WHERE EmailAddress = '{email}' AND HashedPassword = '{password}'");

            return result.Any();
        }
    }
}

The sample code above may look good, but it is not, essentially the IsUserAuthenticated method takes two arguments from inputs and makes SQL string by concatenating SQL instructions with data. This will make our code exposed to SQL injection.

Now, to perform an attack, one would simply put attack’ or 1=1 — as the email address and any value for the password or nothing. This will make our SQL string passed to the server look like this:

Once executed, it will return a positive result making our attacker able to log in and use the service.

Please note that injected SQL code ends with two hyphens (comments within SQL statements), so whatever happens after in the original code, naturally will never be executed. This is the common scenario.

Let us look at another example, this time we will assume that a potential attacker has already gained knowledge about our database schema and now tries to get to the sensitive data by exploiting another web page, a page which was designed to show laptops assigned to different users. The web page uses query string as shown below:

There is one Dell laptop assigned. For the sake of simplicity, we do not display the default list when an argument is not passed. The back-end script for the page Laptops.cshtml looks like this:

using System.Threading.Tasks;
using System.Collections.Generic;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc.RazorPages;
using UnsecureWebApp.Models;
using UnsecureWebApp.Infrastructure.Database;
using UnsecureWebApp.Infrastructure.Domain.Entities;

namespace UnsecureWebApp.Pages
{
    public class LaptopsModel : PageModel
    {
        private readonly ILogger<LaptopsModel> _logger;
        
        private readonly DatabaseContext _database;

        [BindProperty]
        public List<Laptop> Form { get; set; }

        public LaptopsModel(ILogger<LaptopsModel> logger, DatabaseContext database)
        {
            _logger = logger;
            _database = database;
        }

        public async Task<IActionResult> OnGetAsync(string brand)
        {
            Form = new List<Laptop>();

            if (string.IsNullOrEmpty(brand)) 
                return Page();
            
            var laptops = await ReturnLaptopsAsync(brand);                              
                
            foreach (var laptopData in laptops.Select(ALaptop => new laptop
            {
                Brand = laptop.Brand,
                SerialNo = laptop.SerialNo,
                Userid = laptop.UserId
            }))
            {
                Form.Add(laptopData);
            }

            return Page();
        }

        /// <summary>
        /// Example of SQL Injection vulnerability. Never use such code!
        /// </summary>
        /// <param name="brand"></param>
        /// <returns></returns>
        private async Task<List<Laptops>> ReturnLaptopsAsync(string brand) 
        {
            var data = await _database.Laptops
                .FromSqlRaw($"SELECT Id, Brand, SerialNo, UserId FROM Laptops WHERE Brand = '{brand}'")
                .ToListAsync();
            
            return data;
        }
    }
}

An attacker can start to play with a query string value, if the website is built the way that it is mixing data and SQL instructions, the following SQL code should return the list of user emails with their passwords:

Here we use the UNION operator to return results from two SELECT statements. The first one will be empty, but the second should return the content of the Users table:

Knowing this, an attacker can also decide to delete everything:

This will produce and make SQL server to execute:

This way an attacker can wipe out both tables. There are three statements (separated with the semicolon for clarity), among them two that order to delete the whole content of both tables.

Stored procedure to the rescue?

The answer is yes and no. As with everything in programming, it depends. The stored procedure can be created in a way that is vulnerable to SQL injection, most particularly dynamic SQL within stored procedure, example follows:

Therefore, as long as we use static procedure, we are safe in terms of SQL injection. Nevertheless, if we must use dynamic SQL within store procedure, one must be mindful of the possible danger and take additional precautions.

What to do and what not to do?

Whenever we create a web application or just a website with some inputs from users, it is important to always follow the below points, with no exceptions.

• On the front-end side, always validate user input. If a user is supposed to provide a telephone number, allow digits only. If a user is supposed to provide a first name or email address, allow string and validate with a regular expression (Regex) to exclude any possible SQL statements. Such validation should also be performed on the back-end side (double-check) because such JavaScript validation might be cracked by a skilled attacker.
• If we use the OR/M tool with custom dynamic SQL, we should always use parameterization, so we never make SQL string by concatenating SQL instructions with data.
• We must be careful when using dynamic SQL within a stored procedure, this may expose stored procedure to SQL injection attack.
• All the sensitive data should be masked (i.e. functionality offered by Azure database) or encrypted or hashed and salted. The choice usually depends on many factors, but in the case of passwords, we should always store passwords that are hashed and salted, the common practice is to use the BCrypt library. I recommend reading an article by Dan Arias: “Hashing in Action: Understanding bcrypt”.
• Never allow your application to have access to a database with administrator rights. Always create a separate user with limited privileges.
• Always follow security best practices and coding best practices so you will never end up with lazy and unsecured code.

A good piece of advice when building a multi-tier web application in NET Core: use a library like Validate.js for the front-end validation and FluentValidation (or similar) for the backend validation.

Conclusion

This straightforward article for beginners shows the idea of standing behind SQL injection. In our examples, instead of old good ADO.NET, I have used Entity Framework Core (lightweight OR/M tool) to perform raw SQL queries. I present points to consider when making a web application or a web site that accepts user input and uses a database(s).

Thank you for reading this article! Please leave a comment should you have any questions or have a different experience.