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.
evandro-machado · Jun 10, 2015 · f59ac95 · f59ac95
1 parent 511afa7
commit f59ac95
Show file tree

Hide file tree

Showing 2 changed files with 28 additions and 11 deletions.
diff --git a/src/java/br/com/magazine/dao/ClienteDAO.java b/src/java/br/com/magazine/dao/ClienteDAO.java
@@ -32,9 +32,14 @@ public class ClienteDAO {
     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 ";
@@ -270,7 +275,9 @@ public List<Cliente> buscarFuncionarioNome(String nome) throws SQLException, Cla
 
         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);
 
@@ -302,7 +309,9 @@ public List<Cliente> buscarFuncionarioCPF(String cpf) throws SQLException, Class
 
         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) {
@@ -333,7 +342,9 @@ public List<Cliente> buscarFuncionarioEmail(String email) throws SQLException, C
 
         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) {
@@ -363,7 +374,9 @@ public List<Cliente> buscarClienteNome(String nome) throws SQLException, ClassNo
 
         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);
 
@@ -395,7 +408,9 @@ public List<Cliente> buscarClienteCPF(String cpf) throws SQLException, ClassNotF
 
         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) {
@@ -426,7 +441,9 @@ public List<Cliente> buscarClienteEmail(String email) throws SQLException, Class
 
         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) {

diff --git a/web/BancoDeDados.sql b/web/BancoDeDados.sql
@@ -52,7 +52,7 @@ idCliente int,
 valorTotal real,
 data DateTime,
 situacao int
-)
+);
 
 
 create table itemPedido(
@@ -61,4 +61,4 @@ idPedido int,
 irProduto int,
 quantidade int,
 valorUnitario int
-)
+);