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. |
<body>
<form action='HelloInjection'>
<input type='text' name='emailAddress' value=''/>
<input type='submit' value='Submit'/>
</form>
</body>
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();
}
}
String query= "select * from email_subscriptions where email_address = '" + email + "'";
select * from email_subscriptions where email_address = 'chris.saxon@oracle.com'
' or 1='1
select * from email_subscriptions where email_address = '' or 1='1'
' union all select table_name from all_tables where 1 = '1
select * from email_subscriptions where email_address = ''
union all
select table_name from all_tables where 1 = '1
String query= "select * from email_subscriptions where email_address = ?";
PreparedStatement st = conn.prepareStatement(query);
st.setString(1, email);
ResultSet res = st.executeQuery();
select * from email_subscriptions where email_address = ?
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 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.