MYSQL Delete statement not working with Java Servlet

Question

I have a web page which allows the user to enter a word and when that word is submitted, it will be deleted from the mysql database. The problem however is that the statement is not being executed.

My form:

<form id="rem1" action="removeGER" method="GET">
        <input type="text" name="wordToRemoveGER" placeholder="Entry (GER)">
        <input type="submit" id="removeBtnGER" value="Remove Entry">
 </form>

My servlet:

@WebServlet(name = "removeGER", urlPatterns = {"/removeGER"})
public class removeGER extends HttpServlet {

/**
 * Handles the HTTP <code>POST</code> method.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html");
    String wordGER = request.getParameter("wordToRemoveGER");
    PrintWriter out = response.getWriter();

    out.println("<!DOCTYPE html>");
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Remove Word</title>");
    out.println("</head>");
    out.println("<body>");
    Connection conn = null;

    try {
        // Database Checking Area
        SimpleDataSource.init("/database.properties");
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(Register.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        conn = SimpleDataSource.getConnection();
        Statement stat = conn.createStatement();
        //ResultSet result = 
        stat.executeQuery(" DELETE FROM `word` WHERE `word` = \""+wordGER+"\"");
        out.println("<h2>You have successfully removed the word " + wordGER + "!</h2>");
    } catch (SQLException ex) {
        Logger.getLogger(removeID.class.getName()).log(Level.SEVERE, null, ex);
    }
    out.println("</body>");
    out.println("</html>");
}

}

I know that the String is correctly being retrieved from the form, and I know that the try block is being executed. The problem seems to be with the mysql statement itself.

You can only use one query per executeQuery()-command. You have two (SET and DELETE command). — maja
– maja, Commented Apr 28, 2015 at 15:11
You have successfully created a possible SQL Injection! You should use prepared statements.. — maja
– maja, Commented Apr 28, 2015 at 15:13

Luiggi Mendoza · Accepted Answer · 2015-04-28 15:28:16Z

4

You have the following issues:

You're executing multiple statements and probably your connection doesn't allow it.
When you execute multiple statements, you should use Statement#execute, not Statement#executeUpdate.
If you need to pass arguments to your query, don't append it directly in your sql, this is cause of SQL Injection attacks. Use PreparedStatement instead.

In order to do this, make sure your connection allows the execution of multiple statements by adding this parameter into your connection URL: allowMultiQueries=true

Example of how the connnection URL should look:

jdbc:mysql://<server>:<port>/<database>?allowMultiQueries=true

How the code will look like if you use PreparedStatement:

conn = SimpleDataSource.getConnection();
String sql = "SET SQL_SAFE_UPDATES = 0; DELETE FROM `word` WHERE `word` = ?";
PreparedStatement stat = conn.prepareStatement(sql);
stat.setString(1, wordGER);
stat.execute();

Now that you use a single delete statement, then you only need to use PreparedStatement#executeUpdate rather than executeQuery.

edited Apr 28, 2015 at 15:28

answered Apr 28, 2015 at 15:11

Luiggi Mendoza

85.9k16 gold badges160 silver badges354 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JonK Over a year ago

Also the fact that they're simply dumping an unsafe string directly into the query rather than using proper parameters

maja Over a year ago

@JonK is right, especially because the statement already contains an SQL injection problem

hjalpmig Over a year ago

Currently just trying to get the basics working before adding security measures.

JonK Over a year ago

I've just tested it locally to be sure, and parameters do work with PreparedStatement#execute

hjalpmig Over a year ago

Thanks, I am now currently implementing prepared statements to protect against SQL injection

aengus · Accepted Answer · 2015-04-28 15:11:27Z

-2

use

    executeUpdate('')

instead of

    executeQuery('')

You should also make sure that you close the statement, and connection when you are finished with them

answered Apr 28, 2015 at 15:11

aengus

6066 silver badges14 bronze badges

2 Comments

hjalpmig Over a year ago

I did try using executeUpdate instead of executeQuery before but it didn't work, but is now working.

Luiggi Mendoza Over a year ago

@hjalpmig it is because now you're using a single statement.

Collectives™ on Stack Overflow

MYSQL Delete statement not working with Java Servlet

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related