CVE-2015-10061: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in evandro-machado Trabalho-Web2. It has been classified as critical. This affects an unknown part of the file src/java/br/com/magazine/dao/ClienteDAO.java. The manipulation leads to sql injection. The patch is named f59ac954625d0a4f6d34f069a2e26686a7a20aeb. It is recommended to apply a patch to fix this issue. The associated identifier of this vulnerability is VDB-218427.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2023-01-17T16:15Z
Repository: https://github.com/evandro-machado/Trabalho-Web2
Language: SQL
Files changed: src/java/br/com/magazine/dao/ClienteDAO.java, web/BancoDeDados.sql
Commit date: 2015-06-10 00:15:20 -0300
Commit message: ClienteDAO - alterados statements para utilizar o metodo setString do PreparedStatement para evitar SQL Injection
- alterados statements para utilizar ilike ao inves de like para pesquisar sem case sensitivity
BancoDeDados - colocado ponto e virgula apos todas as queries.
Diff: diff --git a/src/java/br/com/magazine/dao/ClienteDAO.java b/src/java/br/com/magazine/dao/ClienteDAO.java
index 3177119..854b31f 100644
--- a/src/java/br/com/magazine/dao/ClienteDAO.java
+++ b/src/java/br/com/magazine/dao/ClienteDAO.java
@@ -30,13 +30,18 @@ public class ClienteDAO {
     private final String stmtBuscarClienteId = "select * from Cliente where (idcliente = ? and inativo=false) order by nome";
 //    private final String stmtListaCliente = "select * from Cliente";
     private final String stmtRemoveCliente = "update Cliente set inativo = true where idCliente = ?";
 //    private final String stmtRemoveItemPedidoCliente = "delete from itempedido where idpedido = (select idpedido from pedido where idcliente = ?)";
 //    private final String stmtRemovePedidoCliente = "delete from pedido where idpedido = (select idpedido from pedido where idcliente= ? )";
-    private final String stmtBuscarNome = "select * from Cliente where nome like ";
-    private final String stmtBuscarCPF = "select * from Cliente where cpf like ";
-    private final String stmtBuscarEmail = "select * from Cliente where email like ";
+    private final String stmtBuscarNomeCliente = "select * from Cliente where nome ilike ? and perfil = 1 and inativo=false order by nome";
+    private final String stmtBuscarCPFCliente = "select * from Cliente where cpf ilike ? and perfil = 1 and inativo=false order by nome";
+    private final String stmtBuscarEmailCliente = "select * from Cliente where email ilike ? and perfil = 1 and inativo=false order by nome";
+    
+    private final String stmtBuscarNomeFuncionario = "select * from Cliente where nome ilike ? and perfil in (2,3) and inativo=false order by nome";
+    private final String stmtBuscarCPFFuncionario = "select * from Cliente where cpf ilike ? and perfil in (2,3) and inativo=false order by nome";
+    private final String stmtBuscarEmailFuncionario = "select * from Cliente where email ilike ? and perfil in (2,3) and inativo=false order by nome";
+    
     private final String stmtBuscarEmailExato = "select * from Cliente where email = ?";
 //    private final String stmtProcuraSobreNome = "select * from Cliente where sobrenome like ";
 //    private final String stmtProcuraCPF = "select * from Cliente where cpf like ";
 
     public void cadastrarCliente(Cliente cliente) throws ClassNotFoundException {
@@ -268,11 +273,13 @@ public class ClienteDAO {
         PreparedStatement stmt = null;
         ResultSet rs = null;
 
         try {
             con = ConnectionFactory.getConnection();
-            stmt = con.prepareStatement(stmtBuscarNome + "'%" + nome + "%' and (perfil = 2 or perfil = 3) and inativo=false order by nome");
+            nome = "%"+nome+"%";
+            stmt = con.prepareStatement(stmtBuscarNomeFuncionario);
+            stmt.setString(1,nome);
             rs = stmt.executeQuery();
             return montaListaClientes(rs);
 
         } catch (SQLException e) {
             throw new RuntimeException(e);
@@ -300,11 +307,13 @@ public class ClienteDAO {
         PreparedStatement stmt = null;
         ResultSet rs = null;
 
         try {
             con = ConnectionFactory.getConnection();
-            stmt = con.prepareStatement(stmtBuscarCPF + "'%" + cpf + "%' and (perfil = 2 or perfil = 3) and inativo=false order by nome");
+            cpf = "%"+cpf+"%";
+            stmt = con.prepareStatement(stmtBuscarCPFFuncionario);
+            stmt.setString(1, cpf);
             rs = stmt.executeQuery();
             return montaListaClientes(rs);
         } catch (SQLException e) {
             throw new RuntimeException(e);
         } finally {
@@ -331,11 +340,13 @@ public class ClienteDAO {
         PreparedStatement stmt = null;
         ResultSet rs = null;
 
         try {
             con = ConnectionFactory.getConnection();
-            stmt = con.prepareStatement(stmtBuscarEmail + "'%" + email + "%' and (perfil = 2 or perfil = 3) and inativo=false order by nome");
+            email = "%"+email+"%";
+            stmt = con.prepareStatement(stmtBuscarEmailFuncionario);
+            stmt.setString(1,email);
             rs = stmt.executeQuery();
             return montaListaClientes(rs);
         } catch (SQLException e) {
             throw new RuntimeException(e);
         } finally {
@@ -361,11 +372,13 @@ public class ClienteDAO {
         PreparedStatement stmt = null;
         ResultSet rs = null;
 
         try {
             con = ConnectionFactory.getConnection();
-            stmt = con.prepareStatement(stmtBuscarNome + "'%" + nome + "%' and perfil = 1 and inativo=false order by nome");
+            nome = "%"+nome+"%";
+            stmt = con.prepareStatement(stmtBuscarNomeCliente);
+            stmt.setString(1,nome);
             rs = stmt.executeQuery();
             return montaListaClientes(rs);
 
         } catch (SQLException e) {
             throw new RuntimeException(e);
@@ -393,11 +406,13 @@ public class ClienteDAO {
         PreparedStatement stmt = null;
         ResultSet rs = null;
 
         try {
             con = ConnectionFactory.getConnection();
-            stmt = con.prepareStatement(stmtBuscarCPF + "'%" + cpf + "%' and perfil = 1 and inativo=false order by nome");
+            cpf = "%"+cpf+"%";
+            stmt = con.prepareStatement(stmtBuscarCPFCliente);
+            stmt.setString(1, cpf);
             rs = stmt.executeQuery();
             return montaListaClientes(rs);
         } catch (SQLException e) {
             throw new RuntimeException(e);
         } finally {
@@ -424,11 +439,13 @@ public class ClienteDAO {
         PreparedStatement stmt = null;
         ResultSet rs = null;
 
         try {
             con = ConnectionFactory.getConnection();
-            stmt = con.prepareStatement(stmtBuscarEmail + "'%" + email + "%' and perfil = 1 and inativo=false order by nome");
+            email = "%"+email+"%";
+            stmt = con.prepareStatement(stmtBuscarEmailCliente);
+            stmt.setString(1,email);
             rs = stmt.executeQuery();
             return montaListaClientes(rs);
         } catch (SQLException e) {
             throw new RuntimeException(e);
         } finally {
diff --git a/web/BancoDeDados.sql b/web/BancoDeDados.sql
index 8865db5..321d685 100644
--- a/web/BancoDeDados.sql
+++ b/web/BancoDeDados.sql
@@ -50,15 +50,15 @@ create table pedido(
 idPedido serial,
 idCliente int,
 valorTotal real,
 data DateTime,
 situacao int
-)
+);
 
 
 create table itemPedido(
 idItemPedido serial,
 idPedido int,
 irProduto int,
 quantidade int,
 valorUnitario int
-)
+);

Vulnerable code:     private final String stmtBuscarNome = "select * from Cliente where nome like ";
    private final String stmtBuscarCPF = "select * from Cliente where cpf like ";
    private final String stmtBuscarEmail = "select * from Cliente where email like ";
            stmt = con.prepareStatement(stmtBuscarNome + "'%" + nome + "%' and (perfil = 2 or perfil = 3) and inativo=false order by nome");
            stmt = con.prepareStatement(stmtBuscarCPF + "'%" + cpf + "%' and (perfil = 2 or perfil = 3) and inativo=false order by nome");
            stmt = con.prepareStatement(stmtBuscarEmail + "'%" + email + "%' and (perfil = 2 or perfil = 3) and inativo=false order by nome");
            stmt = con.prepareStatement(stmtBuscarNome + "'%" + nome + "%' and perfil = 1 and inativo=false order by nome");
            stmt = con.prepareStatement(stmtBuscarCPF + "'%" + cpf + "%' and perfil = 1 and inativo=false order by nome");
            stmt = con.prepareStatement(stmtBuscarEmail + "'%" + email + "%' and perfil = 1 and inativo=false order by nome");
)
)
Fixed code:     private final String stmtBuscarNomeCliente = "select * from Cliente where nome ilike ? and perfil = 1 and inativo=false order by nome";
    private final String stmtBuscarCPFCliente = "select * from Cliente where cpf ilike ? and perfil = 1 and inativo=false order by nome";
    private final String stmtBuscarEmailCliente = "select * from Cliente where email ilike ? and perfil = 1 and inativo=false order by nome";
    
    private final String stmtBuscarNomeFuncionario = "select * from Cliente where nome ilike ? and perfil in (2,3) and inativo=false order by nome";
    private final String stmtBuscarCPFFuncionario = "select * from Cliente where cpf ilike ? and perfil in (2,3) and inativo=false order by nome";
    private final String stmtBuscarEmailFuncionario = "select * from Cliente where email ilike ? and perfil in (2,3) and inativo=false order by nome";
    
            nome = "%"+nome+"%";
            stmt = con.prepareStatement(stmtBuscarNomeFuncionario);
            stmt.setString(1,nome);
            cpf = "%"+cpf+"%";
            stmt = con.prepareStatement(stmtBuscarCPFFuncionario);
            stmt.setString(1, cpf);
            email = "%"+email+"%";
            stmt = con.prepareStatement(stmtBuscarEmailFuncionario);
            stmt.setString(1,email);
            nome = "%"+nome+"%";
            stmt = con.prepareStatement(stmtBuscarNomeCliente);
            stmt.setString(1,nome);
            cpf = "%"+cpf+"%";
            stmt = con.prepareStatement(stmtBuscarCPFCliente);
            stmt.setString(1, cpf);
            email = "%"+email+"%";
            stmt = con.prepareStatement(stmtBuscarEmailCliente);
            stmt.setString(1,email);
);
);