Error in syntax with inner join in java

2

Good people, I am trying to fill a combobox with a query with inner join in java that is causing me conflicts and I can not understand it, I get the following error:

My query is constructed in the following way:

     public void combobox() {
        try {
            conexion = DriverManager.getConnection("jdbc:mysql://localhost/sistema_gmg", "root", "1405");

            st = conexion.createStatement();

            String id_tutor = this.jtf_id_tutor.getText();

            rs = st.executeQuery("Select nombre_alum,ap_paterno_alum,ap_materno_alum "
                    + "from alumnos"
                    + "inner join relacion_tutor_alum"
                    + "on relacion_tutor_alum.id_alumno = alumnos.id_alumno "
                    + "where id_tutor = ''"+id_tutor+"';");

            while (rs.next()) {                                                
                this.jcb_alumno.addItem(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));
            }

        } catch (SQLException err) {

            JOptionPane.showMessageDialog(null, "Error " + err.getMessage());

        }
    }
    
asked by Miguel Angel 01.08.2017 в 02:10
source

1 answer

4

Your code, apart from the syntax error mentioned in the comments, presents a serious security error not visible to the naked eye. This error opens a gap to what is known as SQL injection .

Imagine that the variable where you collect the value of id_tutor is in a form and a malicious user writes this: 1; DROP TABLE alumnos; --

When you pass the query directly to the database, your student table will be completely deleted. If it has thousands or millions of records, we would be talking about a real catastrophe.

More serious things could happen when we leave a door open to code injection . You could erase all the BD tables by concatenating or worse, change passwords and take control of the BD and then alter the data: 1; DROP TABLE alumnos; DROP TABLE otra-tabla; DROP TABLE oootra-tabla; DROP TABLE ooooootra-tabla; UPDATE administradores SET password="otraclave" WHERE name="admin"; --

A security hole like that would be the dream of a suspended student :) to be able to alter the results. But if it is a BD of a bank, you could end up in jail :( if a lower level employee manages to alter accounts because the security of the system has left open this type of doors.

Therefore, whenever data is collected from outside, the correct practice is to use prepared queries.

Here's an example:

public void combobox() {
    try {
            conexion = DriverManager.getConnection("jdbc:mysql://localhost/sistema_gmg", "root", "1405");

            PreparedStatement preparedStatement = null;

            String strSQL = "Select nombre_alum, ap_paterno_alum, ap_materno_alum "
                + "from alumnos "
                + "inner join relacion_tutor_alum "
                + "on relacion_tutor_alum.id_alumno = alumnos.id_alumno "
                + "where id_tutor = ?";

            //Conviene convertir este dato a entero, si es del tipo INT en la BD
            String id_tutor = this.jtf_id_tutor.getText();


            preparedStatement = conexion.prepareStatement(strSQL);

            //Suponemos que id_tutor es del tipo int en la tabla, de ahí setInt
            preparedStatement.setInt(1, id_tutor);

            // Ejecutamos
            ResultSet rs = preparedStatement.executeQuery();

            while (rs.next()) {

                this.jcb_alumno.addItem(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3));

                }

    } catch (SQLException err) {

        JOptionPane.showMessageDialog(null, "Error " + err.getMessage());

    //Conviene cerrar los recursos si el caso lo amerita
    } finally {

        if (preparedStatement != null) {
            preparedStatement.close();
        }

        if (conexion != null) {
            conexion.close();
        }
    }
}

For more details about the prepared queries you can consult the Java documentation , as well like some SO questions in Spanish that deal with the subject.

    
answered by 01.08.2017 / 02:55
source