Coders Conquer Security: Share & Learn - SQL Injection

In simple terms, SQL (or Structured Query Language) is the language used to communicate with relational databases; it's the query language used by developers, database administrators and applications to manage the massive amounts of data being generated every day.

Our data is fast becoming one of the world's most valuable commodities... and when something is valuable, bad guys will want to get their hands on it for their benefit.

Attackers are using SQL injection -- one of the oldest (since 1998!) and peskiest data vulnerabilities out there -- to steal and change the sensitive information available in millions of databases all over the world. It's insidious, and developers need to understand SQL injection (as well as how to defend against it) if we are to keep our data safe.

To that end, we'll discuss three key aspects of SQL injection:

• How it works
• Why it's so dangerous
• How to defend against it

Understand SQL Injection

SQL injection can be understood by using one word: context.

Within an application, two contexts exist: one for data, the other for code. The code context tells the computer what to execute and separates it from the data to be processed.

SQL injection occurs when an attacker enters data that is mistakenly treated as code by the SQL interpreter.

One example is an input field on a website, where an attacker enters ‘’’ OR 1=1" and it is appended to the end of a SQL query. When this query is executed, it returns "true" for every row in the database. This means all records from the queried table will be returned.

The implications of SQL injection can be catastrophic. If this occurs on a login page, it could return all user records, possibly including usernames and passwords. If a simple query to take data out is successful, then queries to change data would too.

Let's take a look at some vulnerable code so that you can see what an SQL injection vulnerability looks like in the flesh.

Check out this code:

String query = "SELECT account balance FROM user_data WHERE user_name = "
+ request.getParameter("customerName");
try {
   Statement statement = connection.createStatement( ... );
   ResultSet results = statement.executeQuery( query );
}

The code here simply appends the parameter information from the client to the end of the SQL query with no validation. When this happens, an attacker can enter code into an input field or URL parameters and it will be executed.

The key thing is not that attackers can only add ‘’’ OR 1=1" to each SELECT query but that an attacker can manipulate any type of SQL query (INSERT, UPDATE, DELETE, DROP, etc.) and extend it with anything the database supports. There are great resources and tools available in the public domain that show what is possible.

We'll learn how to correct this issue soon. First, let's understand how much damage can be done.

Why SQL Injection is So Dangerous

Here are just three examples of breaches caused by SQL injection:

• Illinois Board of Election website was breached due to SQL injection vulnerabilities. The attackers stole the personal data of 200,000 U.S. citizens. The nature of the vulnerability found meant that the attackers could have changed the data as well, although they didn't.
• Hetzner, a South African website hosting company, was breached to the tune of 40,000 customer records. A SQL injection vulnerability led to the possible theft of every customer record in their database.
• A Catholic financial services provider in Minnesota, United States, was breached using SQL injection. Account details, including account numbers, of nearly 130,000 customers were stolen.

Sensitive data can be used to take over accounts, reset passwords, steal money, or commit fraud.

Even information not considered sensitive or personally identifiable can be used for other attacks. Address information or the last four digits of your government identification number can be used to impersonate you to companies, or reset your password.

When an attack is successful, customers can lose trust in the company. Recovering from damage to systems or regulatory fines can cost millions of dollars.

But it doesn't have to end that way for you.

Defeat SQL Injection

SQL injection can be defeated by clearly labeling parts of your application, so the computer knows whether a certain part is data or code to be executed. This can be done using parameterized queries.

When SQL queries use parameters, the SQL interpreter will use the parameter only as data. It doesn't execute it as code.

For example, an attack such as ‘’’ OR 1=1" will not work. The database will search for the string "OR 1=1" and not find it in the database. It'll simply shrug and say, "Sorry, I can't find that for you."

An example of a parameterized query in Java looks like this:

Most development frameworks provide built-in defenses against SQL injection.

Object Relational Mappers (ORMs), such as Entity Framework in the .NET family, will parameterize queries by default. This will take care of SQL injection without any effort on your part.

However, you must know how your specific ORM works. For example, Hibernate, a popular ORM in the Java world, can still be vulnerable to SQL injection if used incorrectly.

Parameterizing queries is the first and best defense, but there are others. Stored procedures also support SQL parameters and can be used to prevent SQL injection. Keep in mind that the stored procedures must also be built correctly for this to work.

// This should REALLY be validated too
String custname = request.getParameter("customerName");
// perform input validation to detect attacks
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";

PreparedStatement pstmt = connection.preparedStatement( query );
pstmt.setString(1, custname);
ResultSet results = pstmt.executeQuery( );

Always validate and sanitize your inputs. Since some characters, such as "OR 1=1" are not going to be entered by a legitimate user of your application, there's no need to allow them. You can display an error message to the user or strip them from your input before processing it.

In saying that, don't depend on validation and sanitization alone to protect you. Clever humans have found ways around it. They're good Defense in Depth (DiD) strategies, but parameterization is the surefire way to cover all bases.

Another good DiD strategy is using "least privilege'within the database and whitelisting input. Enforcing least privilege means that your application doesn't have unlimited power within the database. If an attacker were to gain access, the damage they can do is limited.

OWASP has a great SQL Injection Cheat Sheet available to show how to handle this vulnerability in several languages and platforms... but if you want to go one better, you can play a SQL injection challenge in your preferred language on our platform right now; here's some of the more popular ones to get started:

SQL injection in C#

SQL injection in Node.js

SQL injection in Python Django

SQL injection in Java Spring

The Journey Begins

You've made some great progress towards understanding SQL injection, and the steps needed to fix it. Awesome!

We've discussed how SQL injection occurs; typically with an attacker using input to control your database queries for their own nefarious purposes.

We've also seen the damage caused by the exploitation of SQL injection vulnerabilities: Accounts can be compromised and millions of dollars lost... a nightmare, and an expensive one at that.

We've seen how to prevent SQL injection:

• Parameterizing queries
• Using object relational mappers and stored procedures
• Validating and whitelisting user input

Now, it's up to you. Practice is the best way to keep learning and building mastery, so why not check out our Learning Resources on SQL injection, then try our free demo of the platform? You'll be well on your way to becoming a Secure Code Warrior.