X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • October 8, 2015

What Is SQL Injection and How to Stop It

Chris Saxon
Developer Advocate


Data breaches are a common occurrence. As described in The Securing Account Details story, SQL injection is a simple way to access data from exposed sites.

How easy is SQL injection and how bad can it be?

In this post we'll take a look at how it's possible. We'll see how easy it is to access information from a database that is vulnerable to SQL injection. We'll finish up by showing how you can prevent it.

Let's start with a simple HTML form. It accepts an email address. It passes this to a Java servlet. This looks up subscription details for the email.

The form is:
<body>
<form action='HelloInjection'>

<input type='text' name='emailAddress' value=''/>
<input type='submit' value='Submit'/>
</form>
</body>
The following Java code processes the values:
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
PrintWriter out = response.getWriter();
Connection conn = null;

try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@//db_server:1521/service_name";
String userName = "XXXX";
String password = "XXXX";

conn = DriverManager.getConnection(url, userName, password);

String email = request.getParameter("emailAddress");
Statement st = conn.createStatement();

String query= "select * from email_subscriptions where email_address = '" + email + "'";

out.println("Query : " + query + "<br/>");

ResultSet res = st.executeQuery(query);

out.println("Results:" + "<br/>");
while (res.next()) {
String s = res.getString("email_address");
out.println(s + "<br/>");
}

} catch (Exception e) {
e.printStackTrace();
}
}
The key part of this the query:
 String query= "select * from email_subscriptions where email_address = '" + email + "'";
This takes whatever the user inputs and concatenates it to the string. For example, if we submit chris.saxon@oracle.com, the query becomes:
 select * from email_subscriptions where email_address = 'chris.saxon@oracle.com'
What's so bad about this?

We can supply whatever we want to go between the quotes. This enables us to add more conditions to the where clause. To do this, just close the quotes. Then add our additional criteria. For example, if we submit the following:
 ' or 1='1
This query becomes:
 select * from email_subscriptions where email_address = '' or 1='1'
The expression 1='1' is always true. True or anything is true. Therefore this query returns all the email addresses stored in the table! The code loops through all the results, displaying them all.

This means anyone can get their hands on the table's contents. If it contains sensitive or private data we're in trouble.

But, you may say, our application only uses string concatenation for queries against public or non-sensitive data. For example, lookup tables such as countries and currencies. It can't be that bad, can it?

Yes it can.

Remember that you can union queries together. With this simple operation, we can view the contents of any table in the database user has access to!

What if they don't know the names of our tables?

That's not a problem. Just submit the following:
 ' union all select table_name from all_tables where 1 = '1
And the query becomes (formatted for clarity):
 select * from email_subscriptions where email_address = '' 
union all
select table_name from all_tables where 1 = '1
Hmmm. This returns all the tables the user has access to. Using a similar query, we can also find all the columns they can access. Armed with this information a hacker could union the original query with any other table, potentially leaving your whole database exposed.

So how can we stop this?

Simple. Use bind variables.

To do this in the Java code above, change the Statement to a PreparedStatement. Then modify the query string to have a question mark (?) instead of concatenating the email address into it. Finally set the value of the variable to the supplied email address.

Putting this all together gives:
    String query= "select * from email_subscriptions where email_address = ?";
PreparedStatement st = conn.prepareStatement(query);

st.setString(1, email);
ResultSet res = st.executeQuery();
Now, regardless of what someone types in the form, the query will always be:
 select * from email_subscriptions where email_address = ?
Hacking attempts such as ' or 1='1 now return no results.

Going further ensure your application user and table owner are different database users. Then follow the principle of least privilege to ensure that application users only have access to the tables they need. This limits what hackers can see if they do manage to find a loophole.

Even better, build PL/SQL APIs to fetch and modify data. Grant application users permissions to just these APIs. With no direct access to tables, you've further decreased the risk of people accessing something they shouldn't.

Above all: to ensure your data is safe, use bind variables!

Image from Togo picture gallery maintained by Database Center for Life Science (DBCLS) CC-BY-3.0

Join the discussion

Comments ( 5 )
  • Andrew Wolfe Saturday, December 12, 2015

    A critical and pervasive condition of SQL injection attacks is allowed by connecting the web application to the database as the table owner ("One Big Database User"), or a user with otherwise more privileges than you want.

  • guest Monday, December 14, 2015

    Very true Andrew. Separating the table owners and the application users is a great idea and helps limit the damage attackers can do.

  • guest Tuesday, September 20, 2016

    I would like to propose a Java Annotation as a solution for this.
    You can take a look at this tiny library : https://github.com/rkpunjal/sql-injection-safe/

    This verifies if datastrings are sql-injection-safe. This provides an annotation. The usage looks something like this.

    private @SQLInjectionSafe String id;

    and a utility Method whose usage is like this:

    SqlSafeUtil.isSqlInjectionSafe("my-string-value")

    You could also refer to this article to know more about it:

    https://dzone.com/articles/custom-annotation-in-java-for-sql-injection-safe-p

  • guest Thursday, October 6, 2016

    Here is an idea that could get rid of SQL Injection.

    Database servers take the incoming SQL query and run it through a parser resulting in a parse tree. Then they turn the tree into a plan and execute the plan.

    The essence of injection is that the parser produces a tree different from the one intended by the programmer.

    So the fix is to be able to detect unusual parse trees. Walk the tree after parsing and produce a string in canonical form minus the data values. Compute a SHA hash of the string. Keep a table of known hashes for the application/database user. Warn or abort if the server sees an unknown hash.

    Obviously, there is a startup problem. So the programmer would have to run the application in a testing mode, extract the hashes after exhaustive testing, and the load the server with the hashes on application startup. Then turn on abort.

    SQL Injection eliminated at the server where it matters. Filtering can not achieve the same result.

  • Chris Saxon Thursday, October 6, 2016

    Interesting idea. You're proposing a whitelist of permitted statements based on their hash?

    Oracle already computes a hash based on the text. That's what the SQL_ID is. So in theory this is possible.

    If you're not using bind variables each statement will hash to a different value though (unless you have cursor_sharing = force; something you should only do as a last resort). This makes it tricky to implement a whitelist.

    This also it doesn't completely stop unwanted SQL. If someone has (or can arrange) access to manage the whitelist they can add their own hashes to allow malicious statements.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services