CODEWITHSUNDEEP
javamysqldatabase
HosnyyHanyy
HosnyyHanyy

Reputation: 53

updating mysql db using java program

I am trying to add a new row in my table inside mysql db , i tried to use executeUpdate(); and executeQuery(); but both did not work, I am taking columns values from multiple JTextField and adding every one of them to a single Librarian object and then i call setLibrarian() method in main.

But I get the following error message:

java.sql.SQLException: 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 '@gmail.com , 14 , cairo , 4.6565486E7 )' at line 1

here is my code:

    public static void setLibrarian(Librarian lib){
             Connection con = null;
            Statement st = null;
            String dbURL = "jdbc:mysql://localhost:3306/universitysystem";
            String username = "root";
            String password = "";
            
            try{
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection(dbURL , username , password);
                st = (Statement) con.createStatement();
                st.executeUpdate("INSERT INTO librarians(username , password , email , address , city , contactno)"
                        + " VALUES("+lib.getName()+" , "+lib.getPassword()+" , "+lib.getEmail()+" , "+lib.getAddress()+" , "+lib.getCity()+" , "+lib.getContactNo()+" );  ");
                
               
                            con.close();  //closing connection
    
                
            }
            
            catch(ClassNotFoundException e){
                e.printStackTrace();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
            
        }

Upvotes: 2

Views: 99

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 102814

You have a security leak, you must never put user input into a query statement like this. What if someone enters as password:

thisIsMyPassword'; DROP TABLE librarians CASCADE; EXECUTE 'FORMAT C: /force'; --

You'd be quite screwed.

The proper answer is PreparedStatement, which lets you write a single constant as a query (INSERT INTO librarians(...) VALUES (?, ?, ?, ?) - with the question marks) and then provide the value for each question mark separately, and then you're safe from the above issue (then that will simply be their password, verbatim).

This, in passing, also fixes your problem here, which is either that the double isn't working out, or more likely that there are ' symbols in that gmail address.

While you're at it, look at 'try with resources java', because the way you are closing your connections isn't safe either and results in memory leaks. Finally, exception handling with e.printStackTrace() is broken. Fix your IDE; the proper 'I do not care' content is throw new RuntimeException("Uncaught", e); - what you are doing results in many errors and code in unknown states (also a security issue).

Upvotes: 2

Related Questions