ERREUR : Unknown column 'Accessoires' in 'where clause'-CodePudding

My query is throwing up this error while i have column Accessoires in table categorie Can anyone see why?

public int rechercheParCat(String test) {
    int idcat = 0;
    try {
        String query = "SELECT id_cat FROM categorie WHERE titre=" test;
        PreparedStatement pst = cnx2.prepareStatement(query);
        ResultSet rs = pst.executeQuery(query);
        idcat = rs.getInt(1);
    } catch (SQLException ex) {
        System.err.println(ex.getMessage());
    }

    return idcat;
}

I FIXED IT LIKE THIS:

       int idcat = 0;
       try {
           String query = "SELECT id_cat FROM categorie WHERE titre=? ";
           PreparedStatement pst = cnx2.prepareStatement(query);
           pst.setString(1, test);
           ResultSet rs = pst.executeQuery();
           rs.first();
           idcat = rs.getInt(1);
       } catch (SQLException ex) {
           System.err.println(ex.getMessage());
       }

       return idcat;
   }```

CodePudding user response：

Using bound parameters with a prepared statement likely fixes your bug and also solves the severe security issue.

public int rechercheParCat(String test) {
    int idcat = 0;
    try {
        String query = "SELECT id_cat FROM categorie WHERE titre = ?";
        PreparedStatement pst = cnx2.prepareStatement(query);
        pst.setString(1, test);
        ResultSet rs = pst.executeQuery(query);
        idcat = rs.getInt(1);
    } catch (SQLException ex) {
        System.err.println(ex.getMessage());
    }

    return idcat;
}

The likely reason your code has failed is that test was "Accessoires", so the resulting SQL statement was:

SELECT id_cat FROM categorie WHERE titre=Accessoires

when in fact it should have been:

SELECT id_cat FROM categorie WHERE titre='Accessoires'

Even if you added quotes to the concatenated statement, you'd still have a problem. Just imagine what happens if somebody passes a value with quotes, e.g. O'Connor. This will just break the code. But a more clever person can inject SQL clauses.