Prevent SQL Injection Attacks in a Java Program

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.

Securing Java Applications from SQL injection

Ultimately this depends on the database and its JDBC driver.

For example the MySQL server supports prepared statements natively (ServerPreparedStatement and that means that when you execute query.executeQuery() the driver sends to the server the following data:

1. the prepared SQL statement (select * from users where userId=?)
2. the value of the first parameter ("abcd' or '1'='1")

The MySQL server in turn doesn't need to parse a complete SQL statement ("select * from users where userId= 'abcd'") and extract the comparison value ("abcd") from that.

Instead it can parse the prepared statement ("select * from users where userId=?") and take the comparison value from the second data element.

Caveat: you need to inform the MySQL connector that you want to use server side prepared statements by setting the useServerPrepStmts=true connection property, see the MySQL Connector documentation

---

If the SQL server implementation doesn't support prepared statements (or is not configured the use server side prepared statements), it is the job of the JDBC driver to change

1. the prepared SQL statement (select * from users where userId=?)
2. the value of the first parameter ("abcd' or '1'='1")

into a safe SQL query

select * from users where userId='abcd'' or ''1''=''1'

In the case of this simple query it is easy enough to escape the single quotes within the value, but for more complex values this is much more involved (see https://www.netsparker.com/blog/web-security/sql-injection-cheat-sheet/ for possible examples used in SQL injection attacks).

If you try to implement the escaping yourself chances are high that you miss some special case.

How to prevent SQL injection from special characters

Basically, the database engine parses the SQL string with placeholders and remembers that only that part is to be executed.

Once it has done this, it will accept the data and replace the placeholders with the actual data.

This means that if someone somehow manages to get an SQL string into one of your variables to corrupt the SQL and make it do something else, the database engine will treat is as text that won't be executed.

Because of this, you could potentially input full SQL statements into a text field, and it will just get inserted as text instead of being parsed and executed by the database engine and so, your code is "safe" (there's always something you missed somewhere that can be a potential vulnerability)

This is also not exclusive to JAVA, it also works this way for PHP etc, it's just telling the database to handle queries in a different way than it would normally do.

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.

How to build query in Java to prevent SQL injection using prepared statement

Your first fragment of code is unsafe and vulnerable to SQL injection. You should not use that form.

To make your first fragment safe, you would need to manually escape the value to prevent SQL injection. That is hard to do correctly, and choosing the wrong way of handling values could potentially reduce performance depending on the underlying database (eg some database systems will not use an index if you supply a string literal for an integer column).

The second fragment is the standard way. It protects you against SQL injection. Use this form.

Using a prepared statement with parameter placeholders is far simpler, and it also allows you to reuse the compiled statement with different sets of values. In addition, depending on the database, this can have additional performance advantages for reusing query plans across connections.

How can I modify the code to avoid SQL-injection attack?

Never, ever, concatenate values into a query string like that. Always use prepared statements with parameters when executing queries, especially with user-sourced values.

A simple solution for your case is to use a list of values for each parameter you add, and then set the values collected for those parameters before execute:

StringBuilder sb = new StringBuilder("select * from business where 1=1 ");
List<String> parameters = new ArrayList<>();
if (businessName != null) {
    sb.append("and businessName like '%' || ? || '%' ");
    parameters.add(businessName);
}
if (businessAddress != null) {
    sb.append("and businessAddress like '%' || ? || '%' ");
    parameters.add(businessAddress)
}

try (Connection con = DBUtil.getConnection();
     PreparedStatement pst = con.prepareStatement(sb.toString())) {
    int index = 1;
    for (String parameter : parameters) {
        pst.setString(index++, parameter);
    }
    
    try (ResultSet rs = pst.executeQuery()) {
        // ...
    }
}

If you have parameters of varying types, use a List<Object> and setObject instead.

---

The solution in the answer by MT0 also works, but not all database systems optimize that type of query well (especially if you have a lot of such conditions), which might affect performance. For only a few conditions, the solution by MT0 is more readable, while having same/similar performance.

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