SQL Injection as a Feature

Looking at old applications, we always wonder who in their right mind thought of building them so badly. I encountered such an application in my career, and I was lucky enough that they had used version control to preserve its history. Let me describe how the application looked in its latest state.

This was a website that managed logs for millions of devices around the world. In the report page, you could query the most prevalent type of error that occurred in the wild and track it through time to see when it was introduced and when it was resolved. A pretty useful report.

The problem was, in the UI, you didn't have a button or a link to get to the report. Instead, you were presented with a text box where you could write the SQL queries yourself. In other words, this was a SIAAS, most commonly known as SQL Injection As A Service. The entire database was exposed for anyone who had access to the service.

Traveling through time and CSV (version control), I discovered the initial commit when the report was first started more than a decade earlier. The page was a typical report page where you could enter a date range, some keywords, and click a "Generate" button. Through time, it transformed into its final form, a pure text field where you could type your raw SQL.

How did it happen? Well, with one request at a time over the span of 10 years.

1. Add just one more field

The first request was to add an additional field to the report. The developer added the field in the hard-coded SQL string inside the application, then updated the UI to read this new field. Easy enough. Everyone was happy.

But then there was a second request. This one was a bit harder. The requested field was in a different table. When the developer joined the new table, some of the results were incorrect or incomplete. Others complained that they were not seeing the data anymore. It took some restructuring to fix the report.

But then, someone complained that they didn't want that field in the report. It was messing with their VLOOKUP. After a long discussion, it was agreed that all new fields should be appended at the end of the table. Of course, this was weird since there were now some fields appearing after the creation date field.

2. New Features requested

But the developers didn't give up. They created a dropdown where you could select the type of report you wanted to generate. This was a neat feature. You could get the exact report you wanted without having to disrupt your own workflow. But with this feature came an invitation to create new report types. It might have started with 2 reports, but now there were dozens of them. Some were the same reports with fields slightly rearranged.

New complaints came in that the names of the reports didn't reflect what they were for. And the naming convention was confusing. So naturally, the developers switched from hardcoded names in the code to database-driven report names. You could now edit the report name... but what about creating your own report?

3. Everyone gets their own Report

This idea was rejected. Giving users the ability to create their own report was too hard. Instead, developers would discuss with analysts what they wanted to see in their report and write the appropriate query for it. Now, the dozen reports shot up to 2 dozen. After the developer created them, analysts had the ability to rename them. And of course, they renamed the reports.

Someone ended up renaming a report to an empty string. Chaos followed shortly after. The solution was to limit report editing to admins only.

4. I can write my own query

Somewhere along the line, a charismatic employee convinced developers to create a secret page where they could write their own queries. This page was created but never linked directly from anywhere in the application. On the page, the sidebar had some prewritten queries that you could click on, and they ran directly on the server. This was for some super admin users only.

5. Protect yourself

But then something funny happened. In the code, someone added a string search for INSERT, UPDATE, and CREATE. If any of these strings were detected, the page returned an unauthorized response. What this tells me is that someone had started inserting and updating data in the database.

With this "robust" security measure in place, this new super admin SQL textbox replaced the old report page.

6. Slow queires

It didn't take long before another commit was added to the code. This one added a 30-second timeout to the request. Almost immediately, a 30-minute timeout was added. What this tells me is that the queries were already taking a long time to run. Links to the old report page were removed. Some informational text was added to the SQL report page, warning users not to run just any random queries.

The next commit added text to warn users to always use a LIMIT X at the end of the query. In the code, a string search for LIMIT was added. If LIMIT was not present, it was inserted at the end of the string. Later, it was added that the LIMIT string should be before the last semicolon.

The company went from a report page to a fully open SQL text box. Several warnings were added to the page not to use JOINs unless you know what you're doing. Then, "Don't run queries at night while the backup system is running."

7. I inherited this website

When I joined the company, I was entirely in charge of the tool. I made several requested changes, then I decided it was time to build a proper reporting tool. I pictured a page with a fiew components. Date range, input text, and some filtering box. Something like this:

But before I started, I looked through the source code history and old JIRA requests. I decided it was best to leave it as is. It didn't take long before someone decided to run a DELETE command, removing a single entry and throwing the whole system out of whack. Apparently, there were two tables that were commonly joined, but there were no foreign keys or relation between the tables. It took me a long while to figure out that the join was as follows:

INNER JOIN table2 t2 ON 
    DATE(t1.entry_date) = DATE(t2.entry_date)

To fix it, I inserted a dummy entry with the correct date to replace the deleted one. Then I added DELETE as one of the forbidden commands.

I saved the day. I wish I could tell you that I ended up improving this tool and making it more secure, but I never had the chance.

After lunch, two people appeared at my desk. One was a familiar long face that seemed to avoid making direct eye contact. It was Jose and his fellow security guard. He cordially informed me that he was to escort me out of the building.

Well, you know how the story goes.

---