I am confused on how to use PreparedStatements using Java and Derby sql server

Just a note, this is for class. I would go to the class material, but it doesn't address this(the school is kinda garbage). And when i ask the teacher, he says to google it. I've tried googling it, but my understanding is not good enough yet sadly.

My setup is as follows. Its a web application that uses DerbyDB, Glassfish 5, Java and javascript servlets.

I am a bit lost on using Prepared Statements. My Authentication java code has an sql inject vulnerability and i am trying to solve it. Everyone says to use PreparedStatements, so i am trying. My code is below. This is how it works though. It checks the usernames(an email)that was input to the user_id from thesdev_users table. then it takes the user_id and checks it in user_info table to the password stored under the user_id to see if it matches.

The prepared statements are at the bottom, but i figured yall would like to see the full thing, just in case! Authenticate.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package SDEV425_HW4;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.derby.jdbc.ClientDataSource;

/**
 *
 * @author jim
 */
public class Authenticate extends HttpServlet {

    // variables    
    private String username;
    private String pword;
    private Boolean isValid;
    private int user_id;
    private HttpSession session;

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
     * methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        try (PrintWriter out = response.getWriter()) {
            /* TODO output your page here. You may use following sample code. */
            out.println("<!DOCTYPE html>");
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet Authenticate</title>");
            out.println("</head>");
            out.println("<body>");
            out.println("<h1>Servlet Authenticate at " + request.getContextPath() + "</h1>");
            out.println("<h1>Results are " + username + "," + isValid +"," +user_id +"," +this.username + "</h1>");
            out.println("</body>");
            out.println("</html>");
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</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 {
        processRequest(request, response);
    }

    /**
     * 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 doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        // Get the post input 
        this.username = request.getParameter("emailAddress");
        this.pword = request.getParameter("pfield");
        this.isValid = validate(this.username, this.pword);
         response.setContentType("text/html;charset=UTF-8");
        // Set the session variable
        if (isValid) {
            // Create a session object if it is already not  created.
            session = request.getSession(true);
            session.setAttribute("UMUCUserEmail", username);         
            session.setAttribute("UMUCUserID", user_id);

            // Send to the Welcome JSP page              
            
            RequestDispatcher dispatcher = request.getRequestDispatcher("welcome.jsp");
            dispatcher.forward(request, response);

        } else {
            // Not a valid login
            // refer them back to the Login screen

            request.setAttribute("ErrorMessage", "Invalid Username or Password. Try again or contact Jim.");
            RequestDispatcher dispatcher = request.getRequestDispatcher("login.jsp");
            dispatcher.forward(request, response);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    // Method to Authenticate
    public boolean validate(String name, String pass) {
        boolean status = false;
        int hitcnt=0;

        try {
            ClientDataSource ds = new ClientDataSource();
            ds.setDatabaseName("SDEV425");
            ds.setServerName("localhost");
            ds.setPortNumber(1527);
            ds.setUser("sdev425");
            ds.setPassword("sdev425");
            ds.setDataSourceName("jdbc:derby");

            Connection conn = ds.getConnection();

            
            String sql = "select user_id from sdev_users  where email = '" + this.username + "'";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(user_id, 0);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                user_id = rs.getInt(1);
            }
            if (user_id > 0) {                
                String sql2 = "select user_id from user_info where user_id = " + user_id + "and password = '" + this.pword + "'";
                PreparedStatement stmt2 = conn.prepareStatement(sql2);
                stmt2.setString(user_id, pword);
                ResultSet rs2 = stmt2.executeQuery(sql2);
                while (rs2.next()) {
                    hitcnt++;
                }   
                // Set to true if userid/password match
               if(hitcnt>0){
                   status=true;
               }
            }

        } catch (Exception e) {
            System.out.println(e);
        }
        return status;
    }

}

Solution

Any user input is to be considered 'tainted'. It may be an attempt to hack your stuff.

That means anytime you just run user input as code, you've basically handed your server over to whomever wants.

You've messed this up TWICE in this code.

First time is in your HTML response:

out.println("<h1>Results are " + username + "," + isValid +"," +user_id +"," +this.username + "</h1>");
            out.println("</body>");

okay. I shall make a new user account, and make my username:

rzwitserloot <script>/* haha do evil things here */

and I got you.

You need to escape these things. Get an HTML escaper and throw all unsafe input through this.

Next one is the SQL statement.

String sql = "select user_id from sdev_users  where email = '" + this.username + "'";

Cool. I'm gonna make my username whatever';-- DROP TABLE sdev_users; and ruin your day.

This is how you use preparedstatement to escape strings:

String sql = "select user_id from sdev_users  where email = ?";
// Note: The string you feed to prepareStatement must ALWAYS be a constant.
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, email); // this replaces the first (1) ?. Safely.