com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Insert mysql error

Question

This is the whole message I receive:

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 ''user','age','school','password') values ('Admin','22','tei','admin')' at line 1

And this is the code:

String user = textField.getText().trim();
String age = textField_3.getText().trim();
String school = textField_4.getText().trim();
String password = String.valueOf(passwordField.getPassword());
String password1 = String.valueOf(passwordField_1.getPassword());

if(password.equals(password1)){


Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/users","root","1234");
PreparedStatement st = con.prepareStatement("insert into user ('user','age','school','password') values ('"+user+"','"+age+"','"+school+"','"+password+"')");
int rs = st.executeUpdate();

JOptionPane.showMessageDialog(frame, "Data Saved Successfully");

Any ideas?

kmera · Accepted Answer · 2014-01-18 17:20:10Z

5

The point of prepared statements is, among others, to not concatenate your queries yourself.

You want to do the following:

//first you "prepare" your statement (where the '?' acts as a kind of placeholder)
PreparedStatement st = con.prepareStatement("insert into user (user,age,school,password) values (?,?,?,?);");
//now you bind the data to your parameters
st.setString(1, user);
...
//and then you can execute it
st.executeUpdate()

For more details see the official tutorial.

There are a couple of things happening behind the scenes that make the query safe, like escaping special characters that would otherwise allow altering the statement (google SQL injections if you want to know more)

edited Jan 18, 2014 at 17:20

answered Jan 18, 2014 at 17:06

kmera

1,74510 silver badges23 bronze badges

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

9 Comments

pap Over a year ago

I get the same message again! :/

kmera Over a year ago

try to remove the ' from your column names, does that help?

jmsalcido Over a year ago

you are forgetting the semicolon at the end of the statement.

pap Over a year ago

Now I am getting another error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'user' in 'field list'

pap Over a year ago

I found it! Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/users","root","1234"); PreparedStatement st = (PreparedStatement) con.prepareStatement("INSERT INTO user (username,age,school,password) VALUES (?,?,?,?)"); st.setString(1, user); st.setString(2, age); st.setString(3, school); st.setString(4, password); st.executeUpdate(); st.close(); con.close();

|

Collectives™ on Stack Overflow

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException Insert mysql error

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related