Concatenating values directly in the query is problematic?

Question

Concatenating values directly in the query is problematic?

Navigation

#1 by (2 votes)

1

These days, a guy told me: "Avoid forming SQLs by concatenating strings. Doing this is asking for security issues with SQL injection, and for that reason it's considered a bad programming practice." >

What about merge variables into a String to build a SQL, type this:

 con.atualizar("INSERT INTO CLIENTES (nome, sexo, nascimento, cpf, renda"
                    + " VALUES: ('"+nome+"', '"+sexo+"', '"+nasc+"', '"+cpf+"', '"+renda+"');");

And the update method:

public void atualizar(String sql)throws SQLException{

        PreparedStatement st = con.prepareStatement(sql);

        try{
        st.executeUpdate(sql);

        }catch (SQLException e){
            System.out.println("Erro na atualização. RollBack será efetuado.");
            con.rollback();
        }
    }

Can it be problematic too?

sql java

asked by anonymous 10.02.2018 / 20:44

1 answer

Select with entity framework in N: M with multiple objects Import data from an excel spreadsheet into the Database- Epplus

score 2 · Accepted Answer

Yes, it can cause problems because it would allow the user to manipulate the query. In this answer is a simple but dangerous demonstration of how an attacker could exploit this. So there is the PreparedStatement class to provide data directly from the user. In this other answer is a good explanation of why to use this class.

The PreparedStatement has the characteristic of allowing parameterization of user entries to be concatenated with the query. In the code presented, despite using this class, you just created the variable, but you have not parameterized anything and it is passing directly in the query.

To parameterize, you need to isolate the data received from the query and leave the% of the concatenation in charge. Notice the type of data received so that you can call the correct equivalent set method. Assuming some of the types of your variables, it would look something like this:

con.atualizar(nome, sexo, nasc, cpf, renda);

[...]           

public void atualizar(String nome,char sexo, java.util.Date nasc, String cpf, double renda)throws SQLException{

    String query = "INSERT INTO CLIENTES (nome, sexo, nascimento, cpf, renda)"
                + " VALUES: (?, ?, ?, ?, ?);"

    try{

      PreparedStatement st = con.prepareStatement(query);

      st.setString(1, nome);
      st.setString(2, sexo);
      st.setDate(3, new java.sql.Date(nasc.getTime()));
      st.setString(4, cpf);
      st.setDouble(5, renda);

      st.executeQuery();

    }catch (SQLException e){
        System.out.println("Erro na atualização. RollBack será efetuado.");
        con.rollback();
    }
}

Of course you can not just rely on it as the only way to prevent injection cracks, it's just one layer of the various things you should have to protect your application.