error in jdbc code -> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax

Question

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@gmail.com' at line 1

I don't know where is the problem

public List<UserModel> listUser(String emailParam) throws SQLException {
    List<UserModel> users = new ArrayList<>();

    Connection conn = null;
    PreparedStatement pre = null;
    ResultSet rs = null;

    // Get Connection
    conn = dataSource.getConnection();

    // fetch query
    String fetchUser = "SELECT * FROM user WHERE email = " + emailParam;
    pre = conn.prepareStatement(fetchUser);

    // execute query
    rs = pre.executeQuery();

    // fetch data using resultSet interface;
    while (rs.next()) {
        Integer id = rs.getInt("id");
        String firstName = rs.getString("firstName");
              ...
        String email = rs.getString("email");
        Boolean isActive = rs.getBoolean("isActive");
        Boolean isLibrarian = rs.getBoolean("isLibrarian");

        // insert into user constructor
        UserModel theUser = new UserModel(id, firstName, lastName, gender,
                department, idNo, contactNo, address, email, null,
                isLibrarian, isActive);

        // insert into ArrayList
        users.add(theUser);
    }

    // close connection
    close(conn, pre, rs);

    return users;
}

where is the problem Thanks in advance.

Answer 1

As a general thought, preparing statements has two main usages: 1) Minimise preparation time when executing a query 2) For security - among other things "query rewriting"

I also have an observation about a syntax error in your select at the bottom of this post.

If you are going to prepare statements, then it is better to do it once, then "remember" the preparedStatement that you get back. Do not prepare the same query over and over.

Most, if not all, DBMS's work as follows for prepared query processing: 1) you send the template query to the DBMS for parsing and optimisation. The output of this is known by a few different names, but for the purposes of this we can call this the "executable plan". This is the PrepareXXX call. 2) The DBMS remembers all of those details for the second stage i.e. when you send the data up as a result of the prepdQuery.executeQuery() (or similar) call. this has the effect of sending up the data and plugging it into the prepared query's executable plan.

This will always involve two network trips (one to prepare and one to execute). However ....

... If you need to run the same query again with different data (e.g. a different email), just execute the second step - this bypasses the overheads associated with parsing and planning. Which will increase your throughput significantly - especially for single row operations such as the insert (and most likely the select) shown above.

The alternative is the string concatenation method which will always require parsing etc and execution - but at least it will be only one trip over the network. This works best for longer running queries (where parse time is insignificant compared to execution times) or where the query logic is dynamic (made up at run time based upon user input).

However, if you do send the query text concatenated with user input, make sure you address point 2 above (query rewriting).

Also, finally, your concatenated SQL is missing single quotes. The query must look like this (the text must be quoted) select ... from ... where email = 'email@domain.com';

Thus your concatenation must look like this: String fetchUser = "SELECT * FROM user WHERE email = '" + emailParam + "'";

What is query rewriting? Imaging if the emailParam entered by the user looked like this: emailParam = "'; delete from user all; select 'hello" Try plugging that into your select BUT DO NOT RUN IT unless you have a backup copy of your users table (or you get lucky). Also, note that you never put quote marks around the ? placeholders in prepared queries - even if the parameter is a text or date value.

Answer 2

The error is here, in listUser():

    // fetch query
    String fetchUser = "SELECT * FROM user WHERE email = " + emailParam;
    pre = conn.prepareStatement(fetchUser);

You managed to use a prepared statement when inserting the user, and you need to do the same here when querying:

    // fetch query
    String fetchUser = "SELECT * FROM user WHERE email = ?";
    pre = conn.prepareStatement(fetchUser);
    pre.setString(1, emailParam);