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 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 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 potential client asked us to take over development of his existing project built from scratch 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 to filter data searching for specific records. For example you can search for a user with username and password inputted in the login form using 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 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 login box:
Now it does not matter if you know the password or not. The query that will be send 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 knew 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 whole database. Are you scared yet?
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. Security check is always part of it.
This time it was easier than ever. It was flashing in the most obvious form. Hand written SQL queries everywhere in the code with raw user input inserted into them without any additional measures. It was allowing not only to login as any user, login as superuser, but also to delete any data from database without having 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 lot less time on the app development as we operate on database on the 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.
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.
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 next day in the morning. It was the weekend.
The conclusion is that when you start a project, then it’s superimportant to check weather 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.