Syntax error in Prepared statement while inserting into db

Question

Hi I am trying insert data into the database using prepared statement but I am getting syntax error could u please help

public boolean SignUp(String last_name, String first_name,String email, String password,String confirm_password,String phone){



        Connect connect = new Connect();
        Connection conn = connect.Connection();

        java.sql.PreparedStatement preparedStatement = null;
        //NULL is the column for auto increment
        String insertQuery = "INSERT INTO users VALUES (NULL, ?, ?, ?, ?, ?, ?)";
        preparedStatement = conn.prepareStatement(insertQuery);
        preparedStatement.setString(1, last_name);
        preparedStatement.setString(2, first_name);
        preparedStatement.setString(3, email);
        preparedStatement.setString(4, password);
        preparedStatement.setString(5, confirm_password);
        preparedStatement.setString(6, phone);

        int rs = preparedStatement.executeUpdate(insertQuery);

        conn.close();

}

here is the error message

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 '?, ?, ?, ?, ?, ?)' at line 1

You have 6 parameters in the prepared statement but only 5 set, could that be it? — Kaleb Brasee
– Kaleb Brasee, Commented Jul 26, 2012 at 17:54
Are you sure you're inputting the correct data types into the table? Are you sure that's the correct number of parameters? — jrad
– jrad, Commented Jul 26, 2012 at 17:55

gjava · Accepted Answer · 2012-10-09 23:29:04Z

5

I found the answer :)

Use preparedStatement.execute() instead of executeUpdate(sql). You have already set the sql and params - the new setting in executeUpdate(sql) overrides the bind.

answered Oct 9, 2012 at 23:29

gjava

511 silver badge1 bronze badge

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

Comments

Sergey Kalinichenko · Accepted Answer · 2012-07-26 18:21:49Z

2

You should change the statement to list the columns explicitly, and drop NULL from the list of values.

String insertQuery = "INSERT INTO users"
+  " (last_name, first_name, email, password, confirm_password, phone)"
+  " VALUES(?,?,?,?,?,?)";

This way your insert statement is no longer dependent on the order of columns in your users table, and is also immune to addition of columns to the table.

Note that although this design is probably OK for a toy or an education system, but in a real production system storing password in a table is very dangerous. Storing confirm_password is rather unusual, too: normally your system checks that password is the same as confirm_password, and then inserts a salted password hash and a salt into the table.

edited Jul 26, 2012 at 18:21

answered Jul 26, 2012 at 17:55

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

6 Comments

Srikanth Pai Over a year ago

@dasblinkenlight- Same error persists however the below one works

Srikanth Pai Over a year ago

String insertquery = "INSERT INTO details VALUES(NULL,('" + last_name 					+ "'),('" + first_name + "'),('" + email + "'),('" + password 					+ "'),('" + confirm_password + "'),('" + phone + "'))";

Sergey Kalinichenko Over a year ago

@Srikanth They are inserting into different tables (details vs. users). Is this intentional?

Srikanth Pai Over a year ago

I hate to hard code this way, for moment prepared statement is giving an error.

Sergey Kalinichenko Over a year ago

@Srikanth This is very strange, it is supposed to work. Try retyping the values(?,?,?,?,?,?) part, to make sure the ? and ',' characters are the correct ASCII characters, not look-alike equivalents from other encodings.

|

JoeG · Accepted Answer · 2012-07-26 17:57:33Z

0

Just a guess, not I'm not certain. But if one of the fields is autoincrement, then I don't think you need to insert it. Try taking out that NULL....

answered Jul 26, 2012 at 17:57

JoeG

7,75011 gold badges66 silver badges114 bronze badges

Collectives™ on Stack Overflow

Syntax error in Prepared statement while inserting into db

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related