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.

Luke Woodward · Accepted Answer · 2018-03-10 20:21:25Z

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);

answered Mar 10, 2018 at 20:21

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

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

3 Comments

M Siddique Over a year ago

Thanks Luke Woodward but it is still not forwarding RequestDispatcher have any idea how to dispatcher workable.

Luke Woodward Over a year ago

@MuhammadSiddique: not without any further information from you. You haven't included the structure of your project, your web.xml file (or @WebServlet annotations), nor any clear explanation of what you mean by 'not working'. Do you get an error message (if so, what)? Does it hang? Does it redirect to the wrong URL? Or something else?

M Siddique Over a year ago

dear, i have web.xml structure and also following @webServlet and but here I have not shown n it was working but suddenly it was stuck and now it's working. thanks

GMc · Accepted Answer · 2018-03-12 06:51:23Z

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 protected]';

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related