Avoid manual SQL queries

Documentos > Code Analysis > Static Analysis Rules > Avoid manual SQL queries

This page is not yet available in Spanish. We are working on its translation.
If you have any questions or feedback about our current translation project, feel free to reach out to us!

Metadata

ID: java-security/sql-string-tainted

Language: Java

Severity: Error

Category: Security

CWE: 89

Description

Never build SQL queries manually. Always have the query built with parameters and then, pass the parameters to the prepared statement.

Example of good use of Java and SQL queries with a parameter:

PreparedStatement statement = connection.prepareStatement("select * from mytable where name = ?");    
statement.setString(1, name);    
ResultSet resultSet = statement.executeQuery();

Learn More

CWE-89: Improper Neutralization of Special Elements used in an SQL

Non-Compliant Code Examples

public class NotCompliant {

  @GetMapping(produces = MediaType.APPLICATION_JSON_VALUE)
  @ResponseBody
  public List<Server> sort(@RequestParam String column) throws Exception {
    List<Server> servers = new ArrayList<>();

    try (var connection = dataSource.getConnection()) {
      try (var statement =
          connection.prepareStatement(
              "select id, hostname, ip, mac, status, description from SERVERS where status <> 'out of order' order by "
                  + column)) {
        try (var rs = statement.executeQuery()) {
          while (rs.next()) {
            Server server =
                new Server(
                    rs.getString(1),
                    rs.getString(2),
                    rs.getString(3),
                    rs.getString(4),
                    rs.getString(5),
                    rs.getString(6));
            servers.add(server);
          }
        }
      }
    }
    return servers;
  }
}

public class NotCompliant {


  public String doStuff() {
    String query1 = "SELECT attr FROM table WHERE id=" + idToSearch;
    String query2 = String.format("SELECT attr FROM table WHERE id=%d", idToSearch);

    String query3 = "UPDATE table SET attr=" + idToSet + " WHERE id=" + idToSearch;
    String query4 = "DELETE FROM table WHERE id=" + idToSearch;
  }
}

Compliant Code Examples

public class NotCompliant {


  public String doStuff() {
    PreparedStatement statement = connection.prepareStatement("SELECT attr FROM table WHERE id = ?");    
    statement.setString(1, idToSearch);    
    ResultSet resultSet = statement.executeQuery();
  }

  public void doOtherStuff() {
    String.format("Framework update return empty result set for (%s, %s)", key.handle(), key.version()));
  }
}