Java - Escape String to Prevent SQL Injection

Java - escape string to prevent SQL injection

PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:

public insertUser(String name, String email) {
   Connection conn = null;
   PreparedStatement stmt = null;
   try {
      conn = setupTheDatabaseConnectionSomehow();
      stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
      stmt.setString(1, name);
      stmt.setString(2, email);
      stmt.executeUpdate();
   }
   finally {
      try {
         if (stmt != null) { stmt.close(); }
      }
      catch (Exception e) {
         // log this error
      }
      try {
         if (conn != null) { conn.close(); }
      }
      catch (Exception e) {
         // log this error
      }
   }
}

No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.

There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use a setInt method. The PreparedStatement documentation lists all the different methods available for setting and getting data.

Can this simple String escaping prevent any SQL Injections?

One method of attack would be by "loading" the attack.

First, you inject as user name, bank transfer message, whatever into it

transfer 0.01

to: 02020.020202.200202

name: johnny tables';drop table foobar --

will be escaped to

johnny tables\';drop table foobar  --

So far so good. protection in effect. our attach failed. We try the loading attack.

Now we are going to make a scheduled payment order.

This is assuming a common error is made, that once inserted in the database, the value is "safe" because it has been checked once.

transfer 0.01

to: 02020.020202.200202

name: johnny tables';drop table foobar--

schedule: 1 day from now

Storing the order in the db

'johnny tables\';drop table foobar--'

will be stored as

johnny tables';drop table foobar--

Now at midnight the scheduler kicks in and starts iterating the scheduled payments

select name from scheduled where time > x and < y

so the bank code starts to chrunch

String name = result['name'];
String acct = result['acct'];
String amt = result['amt'];
string query = "insert into payment_process (name,acct,amt) values('"+name+"','"+acct+"','"+amt+"');

and boom, your table is dropped. *

When you go the manual route, you have to ensure all, each and every instance of the variable is escaped, that all unicode characters are accounted for, that all idiocrancies of the database engine are accounted for.

Also, using prepared statements can give a significant speed boost, because you don't have to rebuild queries. You can just build them once, store them in a cache and just swap out the parameters.

Especially when iterating large lists they are a godsent.

The root problem is that he probably doesn't understand prepared statements, doesn't get them how they work. Insecurity triggered can make aggressive and protective of a certain way, even fanatical, just to prevent to admit you just don't know how they work.

Try to talk to him about it, if he doesn't wish to listen to reason go to his manager and explain the issue, and that if the site/app gets hacked, it will be on the head of your co-worker and your manager, and tell him the risks are HUGE. Point to the recent hacks where a lot of money was stolen like the swift hack.

* May not actually work, dependent on actual query, joins, unions etc.. it's a very simplified example

Escaping SQL Strings in Java

Of course it would be easier and more secure to use PreparedStatement.

ANSI SQL requires a string literal to begin and end with a single quote, and the only escape mechanism for a single quote is to use two single quotes:

'Joe''s Caffee'

So in theory, you only need to replace a single quote with two single quotes. However, there are some problems. First, some databases (MySQL for example) also (or only) support a backslash as an escape mechanism. In that case, you would need to double the backslashes (as well).

For MySQL, I suggest to use the MySQLUtils. If you don't use MySQL, then you need to check what are the exact escape mechanisms to use.

How to correct format a string to prevent against invalid characters in a statement?

This is one of the reasons to use prepared statement

first benefit of using a PreparedStatement is you can take advantage of the multitude of .setXYZ() methods, such as .setString(), which allows your code to automatically escape special characters such as quotations within the passed in SQL statement

Prevent SQL injection attacks in a Java program

You need to use PreparedStatement.
e.g.

String insert = "INSERT INTO customer(name,address,email) VALUES(?, ?, ?);";
PreparedStatement ps = connection.prepareStatement(insert);
ps.setString(1, name);
ps.setString(2, addre);
ps.setString(3, email);

ResultSet rs = ps.executeQuery();

This will prevent injection attacks.

The way the hacker puts it in there is if the String you are inserting has come from input somewhere - e.g. an input field on a web page, or an input field on a form in an application or similar.

---

Related Topics