Java syntax problem

General Web Dev
1

Hi everyone,

I have a code to insert data from java to MySQL, but I´m having a problem. Some titles have commas, for example: Tom Clancy’s Ghost Recon. That should be one title, but java it does not understand that way, it understand the title is Tom Clancy and the rest as it is nowhere, the program stops running.

The code I have

public class Conectate {
    private String driver ="com.mysql.jdbc.Driver";
    private String cadenaConexion ="jdbc:mysql://localhost/XboxOne";
    private String pass = "";
    private String usuario = "root";
    public Connection con;
        
    //public Conectate(Map<String,  Map<String, Item>> gamesByCountry, Map<String, String> codesByTitle,Map<String, String> countries) {
    public Conectate(ArrayList<Item> games) {    
        
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(cadenaConexion, usuario, pass);
            System.out.println("¡Conectado!");
            
            
            //CREAMOS LA TABLA
            Statement st = con.createStatement();

            st.executeUpdate("CREATE TABLE IF NOT EXISTS info_XboxOne (id INT AUTO_INCREMENT, PRIMARY KEY(id), "
                    + "Juego_vinculado VARCHAR(500), Juego VARCHAR(500), Tipologia VARCHAR (500), Pertenece VARCHAR (500), "
                    + "Nota VARCHAR (10), Descripcion_Ingles TEXT(4000), Descripcion_Castellano TEXT(4000), Pegi VARCHAR(10), Descripcion_Pegi VARCHAR(200),"
                    + "Lanzamiento VARCHAR (50))");

            System.out.println( "Tabla creada!");
            

            for (Item game : games) {
                
                String titulo = game.getName();
                ResultSet rs = st.executeQuery("SELECT * FROM info_XboxOne WHERE juego = '" + titulo + "'");
                
                if (!rs.next()) { //si se cumple esta condicción significa que el juego no está incluido, con lo que lo metemos
                    PreparedStatement ps = con.prepareStatement("INSERT INTO info_XboxOne (Juego, Tipologia, Pertenece, "
                    + "Nota, Descripcion_Ingles, Descripcion_Castellano, Pegi, Descripcion_Pegi"
                    + ") VALUES (?,?,?,?"
                    + ",?,?,?,?"
                    + ")");
                    
                    ps.setString(1,game.getName());
                    ps.setString(2,game.getValues().get(Constants.TIPOLOGIA));
                    ps.setString(3,game.getValues().get(Constants.PERTENECE));                                            
                    ps.setString(4,game.getValues().get(Constants.NOTA));
                    ps.setString(5,game.getValues().get(Constants.DESCRIPCION_INGLES));
                    ps.setString(6,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
                    ps.setString(7,game.getValues().get(Constants.PEGI));
                    ps.setString(8,game.getValues().get(Constants.DESCRIPCION_PEGI));    
                
                    ps.executeUpdate();
                    
                } else {
                    String query = "UPDATE info_XboxOne SET Tipologia = ?, Pertenece = ?, "
                    + "Nota = ?, Descripcion_Ingles = ?, Descripcion_Castellano = ?, "
                    + "Pegi = ?, Descripcion_Pegi = ? WHERE juego = " + titulo;
                    PreparedStatement ps = con.prepareStatement(query);
                    
                    ps.setString(2,game.getValues().get(Constants.TIPOLOGIA));
                    ps.setString(3,game.getValues().get(Constants.PERTENECE));                                            
                    ps.setString(4,game.getValues().get(Constants.NOTA));
                    ps.setString(5,game.getValues().get(Constants.DESCRIPCION_INGLES));
                    ps.setString(6,game.getValues().get(Constants.DESCRIPCION_CASTELLANO));
                    ps.setString(7,game.getValues().get(Constants.PEGI));
                    ps.setString(8,game.getValues().get(Constants.DESCRIPCION_PEGI));
                    
                    ps.executeUpdate();
                }           
            }
           
    } catch (Exception e) {
                JOptionPane.showMessageDialog(null, "No se ha podido establecer la conexión con la DB" + e);
                e.printStackTrace();
            }
        
    }
    
    public String ConvertirObjectToString(Object Obj) {
    String Str="";
    if(Obj!=null){
        Str = Obj.toString();
    }
    return Str;
}
    
}

This is the error I get: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Rainbow Six Siege'' at line 1

2

It’s the way you’re executing the select statement. The single quote in the title is breaking the query. You’ll either need to escape the title or better yet, use parameterized queries.

(I apologize ahead of time if the syntax is wrong. It’s been YEARS since I did straight java, so I might be off, but this should give you a starting point…).

String strQuery = "SELECT * FROM info_XboxOne WHERE juego = ? ";

PreparedStatement preparedStatement = con.prepareStatement(strQuery);
            preparedStatement.setString(1, game.getName());

            ResultSet rs = preparedStatement.executeQuery(strQuery);
1 Like
3

Hello;

You have 2 options:

a. Use prepared statement (as Dave suggested this is the recommended option):

String query = "SELECT * FROM info_XboxOne WHERE juego = ? ";

PreparedStatement pst= con.prepareStatement (query);
pst.setString(1, game.getName());
ResultSet rs = preparedStatement.executeQuery(query);
pst.executeUpdate();

b. Escape the single quotes by using double single quotes (’ → ‘’):

String titulo = game.getName();
String changedTitle = titulo.replace("'","''");

Also I would like to add that the problem with single quotes is pretty much SQL injection, but of course the single quote trick is not the only approach for SQL injection:

4

First of all, thanks for your answer. I tried with your code but it throws me an error. I have finally solved the problem using the following code:

ResultSet rs = st.executeQuery("SELECT * FROM info_XboxOne WHERE juego = \"" + titulo + "\"");

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.