Securing Account Details: A Story About SQL Injection and Bind Variables

Dave turned to his boss. "What happened?" he asked her.

"The security consultants provided the CEO with a file containing the personal details for all people using our system" she replied. "Needless to say, he's not happy about it".

Dave pondered. How could this have happened? He cast his mind back…

The past two years were a blur. Fresh out of university, Dave joined the startup Acme Co as a developer. The job sounded exciting - building a new social media platform. His first task was creating the profile pages.

The learning curve had been steep. Dave enjoyed the challenge though. He was a full stack developer, working on the UI, middle tier and Oracle database. He loved the variety and the chance to build a wide skill set.

Although his knowledge was wide it wasn't particularly deep. There were times he wished he had a better understanding of all the technologies he used. SQL in particular was a grey area. When he started his comprehension of it was a couple of dimly remembered lectures from his second year at university. So he picked it up as he went along. He wanted to learn to really get to grips with SQL and understand it in detail. The pace of change and sheer volume of other things he needed to know for his job meant he hadn't dedicated as much time as he would have liked.

His mind was drawn to the first production SQL he had written. The data to populate the profile page was stored in the accounts table. To fetch this he had written a simple select. This returned all the details for a given account by its primary key. He recalled struggling to figure out how to pass account_ids to the query. In the end he settled for building the query as a concatenated string, appending the account_id like so:

String sql = "select * from accounts where account_id = " + accountID; 

Statement stmt = connection.createStatement();

ResultSet result = stmt.executeQuery(sql);

He remembered the excitement he felt when, a couple of weeks later, this SQL and all the other code to generate the profile page were in production. It felt great to produce something tangible.

At the time Acme was tiny. The first year had seen fantastic growth. Within a year there were several million active users. With this growth came performance issues. The developers were constantly firefighting to keep the site running effectively.

At first everything worked well. When the load grew to millions of SQL queries every day it reached crisis point. The database servers were at full capacity. They had already scaled out the database. It was becoming clear that throwing hardware at the problem wasn't a viable solution.

Acme drafted in a database consultant to investigate. He noticed that a lot of the SQL wasn't using bind variables. The account selection query was a key culprit. He recommended reviewing all database access code ensure it was using bind variables. By this time the codebase was hundreds of thousands of lines long.

At the time management was desperately seeking another round of funding from investors. They needed the money to keep the company running. To reassure investors they wanted a fast resolution to the performance issues. Management pressed the consultant for an easier, quicker solution.

He said you could change the system parameter cursor_sharing to force. This would trick Oracle into thinking all literals in statements were bind variables. Changing the code was still the best solution. Not only would this make the queries faster, it would protect you from something called "SQL injection".

Desperate to get performance under control, management insisted on going with the quickest solution. The cursor_sharing parameter was set in production. Almost immediately there was a noticeable improvement in response times. Modifying the code was quickly forgotten about.

Remembering the consultant's advice, Dave had always intended to go back and modify his account access SQL. The constant pressure to develop new features meant this was always at the bottom of his to-do list. Until now.

Dave scanned the application logs. There were some bizarre page requests like:

/account/?account_id=1-pow(1,1)
/account/?account_id=1-power(1,1)
/account/?account_id=1 or 1=1
/account/?account_id=1 union select * from user_tables;

Fortunately Dave had just attended an IOUG conference. One of the presenters had demonstrated how queries that didn't use bind variable are vulnerable to SQL injection. This enables hackers to run arbitrary SQL statements and gain access to information that they should not be able to see. His mind immediately jumped to his accounts query.

Could this be the source of the leak?

He opened up the profile page in the development environment and tried different SQL injection strings. Adding a simple "or 1=1" enabled him to return all the account details. Clearly this was a likely candidate for the breach.

He was annoyed with himself. "I should have fixed the code earlier," he thought to himself.

He quickly loaded the database access code and searched for his accounts query. There it was, just as he'd written it two years ago. He now knew what to do. He had to change the query to use bind variables. Working fervently he updated the code to use a prepared statement:

String sql = "select * from accounts where account_id = ?";

PreparedStatement stmt =
       connection.prepareStatement(sql);

stmt.setInt(1, accountID);

ResultSet result = stmt.executeQuery();

After deploying the changes he repeated the SQL injection attempts. All passed. No matter what he tried only the details of the current account were returned. Success!

The change was fast tracked into production. A few hours later the new code was live.

Dave stayed at work to see the release. He wanted to be sure that his changes had worked. Once it was live he repeated the SQL injection attempts in production. As expected, his attempts all came back negative. Dave smiled to himself.

While running the tests he noticed something else. The profile page was loading quicker. A glance at the production performance dashboard revealed that database CPU usage was also down. Not only had using bind variables secured the database, it had improved performance!

It was a hard lesson. Dave took it to heart though. If you care about security and performance use bind variables!