So I have a script that retrieves a list of items that were used on a product within a time period. Our database is 'jerry-rigged', so to say, where we do not have these items listed as their code, but by a description. I can run this description through another table on our database to retrieve an item code, but I run into an issue with items that have a double quote in them, like '2" Self Tapping Screw', where the double quotes causes an issue with my query. Below are the issue lines I have:
String description = resultSet.getString(1); //description = "2" Self Tapping Screw
String sql = "Select itemcode from database.table where description "
+ "= \"" + description + "\";
How can I replace these double quotes with the regex character? I tried
description = description.replace("\"","\\"");
but I do not think I am getting it right, because it just changes every '"' to "\".
Use PreparedStatement:
Connection conn = DriverManager.getConnection("<Your connection string>");
String sql = "Select itemcode from database.table where description = ?";
PreparedStatement prep = con.prepareStatement(sql);
prep.setString(1, resultSet.getString(1));
prep.executeUpdate();
This is much safer than trying to escape Strings by yourself.