Avoid manual SQL queries

문서 > 코드 분석 > Static Analysis Rules > Avoid manual SQL queries

이 페이지는 아직 한국어로 제공되지 않으며 번역 작업 중입니다. 번역에 관한 질문이나 의견이 있으시면 언제든지 저희에게 연락해 주십시오.

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()));
  }
}