When SQL Injections are still a thing…

At first, I couldn’t believe that they put it on top of the list. It can’t happen, not with libraries and tooling developers use nowadays. OWASP Top 10 still features injections as the number one threat for web applications security in 2017.SQL Injections in particular. SQL Injections!? What are the chances of encountering one in a project? It’s like a warning against flu in the age of modern medicine. Sure it can kill you, but what are the chances?
Yet every day brings us new surprises. When the potential client asked us to take over the development of his existing project built from scratch a few years back, I was far from suspecting how it’s codebase will look like. You guessed! It was far from perfect.

What are SQL injections?

SQL queries are text commands that you can retrieve or store data with. They allow filtering data searching for specific records. For example, you can search for a user with username and password inputted in the login form using a simple query.

This SQL command is built by concatenating base query with values from the form available in variables named login and password. Let’s assume that values are ‘foo’ and ‘bar’, then the actual query sent to the database will be:

If the query results in at least one record then we can be sure that the user with login ‘foo’ and password ‘bar’ exists in the database. It means that user-provided correct credentials can be authenticated and authorized to use the application. Pretty simple, right? Nothing can go wrong here… or can it?

Let’s hack it!

The — symbol in SQL means that all we can find after it is a comment and should be ignored by the database. How can we use it to crack the security mechanism presented above? Let’s type following into the login box:
foo'; --
Now it does not matter if you know the password or not. The query that will be sent to the database will look like this:

BOOYAH! We are logged in. The user was returned because the whole password check part was ignored and now we are authorized as ‘foo’ despite we didn’t know his password at all. And that’s not all. Having one place like this in your app and with just a little bit of luck, anyone can read or delete the whole database. Are you scared yet?

The discovery

I was. With the client I’ve mentioned we went the usual way. When you come to us with existing product we always do code audit, before we can estimate the costs of the product development. A security check is always part of it.
This time it was easier than ever. It was flashing in the most obvious form. Handwritten SQL queries everywhere in the code with raw user input inserted into them without any additional measures. It was allowing not only to log in as any user, log in as superuser, but also to delete any data from the database without having an account in the system.
Frankly, I was not only scared of potential consequences, but also surprised that code like this is a part of the real digital product. As I’ve mentioned – I thought that times when SQL Injections can be found in apps are like mammoths – long gone.

Why don’t we see it anymore?

In Gorrion, we connect to databases using Object Relational Mappers (or ORMs for short). ORMs allows us to query the database without writing SQL ourselves. They are taking care of all parameters coming from the outside, making them safe to insert into queries they build for us. Of course, security is just one of the pros here. Using ORMs we spend a lot less time on the app development as we operate on the database on a really high level.
Of course rewriting the app to use ORM was not an option here. It would take some time and we wanted to secure production database as soon as possible. It’s not like there were no mechanisms back then to secure the queries manually.

The solution

Client’s app used pure SQLClient built-in .NET to execute queries on the database. The only thing we need to do was to use so-called named parameters. Instead of concatenating strings into a query we’ve changed the query to be a command with placeholders for arguments and we used parameters mechanism to feed it with username and password values. It provided security measures to filter out potentially dangerous characters from them.

The client was informed about the vulnerability the day he gave us access to the source code, we suggested this as a solution and the fix was ready the next day in the morning. It was the weekend.

Conclusion?

The conclusion is that when you start a project, then it’s superimportant to check whether your software services provider has the right knowledge and uses the right technology to provide your application security. Right tools can help developers to avoid dangers and make security flaws at such basic level a faraway concern.
The code, the technologies used, the safety features included in the apps we make are not something that customers can see with their eyes. Although I hope that knowing we are capable of providing proper security measures matters to them and makes them confident about the products they get.