What Is SQL Injection and How to Stop It
October 8, 2015 | 3 minute readDeveloper 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
Chris Saxon
Developer Advocate
Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!
To help you with this he blogs at All Things SQL. He also creates videos combining SQL and magic on YouTube at the The Magic of SQL.
If you have questions about working with Oracle Database technology, please reach out to him. You can do this via Twitter or on Ask Tom.