java sql syntax error insert into

Question

I have problems running this sql statement. It works fine if I run it in mysql but in java I get this error:

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

The database has an id(pk) autogenerated, varchar, int, varchar;

Can someone help me?

int i = statement.executeUpdate("INSERT INTO sala values('','"+ nume.getText() + "', "+ Integer.parseInt(capacitate.getText())+ ", '" + sunet.getText()+"'");

Please please please read this and take heed: sommarskog.se/dynamic_sql.html#SQL_injection. In general, you're probably best off creating the query in a separate variable, then passing that variable to ExecuteUpdate. That way you can see the whole contents of the statement before you try and run it, and might be able to spot the syntax error. — Bridge
– Bridge, Commented May 18, 2012 at 16:19
what are the text that you are passing?? I meant what text is passing in nume.getText(), capacitate.getText() ?? — Fahim Parkar
– Fahim Parkar, Commented May 18, 2012 at 16:19
I dont meant that.. what are the text that is passed as input?? ex. nume.getText() will have data as "first input" — Fahim Parkar
– Fahim Parkar, Commented May 18, 2012 at 16:27

Jon Skeet · Accepted Answer · 2012-05-18 16:24:24Z

5

Don't just try to fix this code by tweaking the SQL as per adarshr's answer. You have a fundamental security problem here which you should fix right now. You're open to SQL injection attacks due to including user data directly in your SQL.

You should use a PreparedStatement, with the parameters declared as placeholders in the SQL, but then given values separately. Exactly how you'll do that will depend on your JDBC provider, but it'll look something like this:

// TODO: Fix the column names, and close the statement in a try/finally block
PreparedStatement pst = conn.prepareStatement(
    "INSERT INTO sala (nume, capacitate, sunet) VALUES (?, ?, ?)");
pst.setString(1, nume.getText());
pst.setInt(2, Integer.parseInt(capacitate.getText()));
pst.setString(3, sunet.getText());
pst.executeUpdate();

Note that if you can get capacite in a way which doesn't require integer parsing, that would be good. Otherwise, consider using NumberFormat which is more locale-friendly. Also note that I've added the column names into the SQL to make this more robust in the face of schema changes.

edited May 18, 2012 at 16:24

answered May 18, 2012 at 16:19

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

1 Comment

anna karenina Over a year ago

I have the try/catch block , did not post it. Will user PreparedStatement. Thanks for teaching me to code the right way!

Community · Accepted Answer · 2017-05-23 10:34:42Z

5

You haven't closed your query.

int i = statement.executeUpdate("INSERT INTO sala values('','"+ nume.getText() + "', "+ Integer.parseInt(capacitate.getText())+ ", '" + sunet.getText()+"')");
                                                                                                                                                          ^

But more than all this, you must use PreparedStatement as Jon suggested below.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered May 18, 2012 at 16:19

adarshr

62.8k24 gold badges146 silver badges175 bronze badges

5 Comments

Jon Skeet Over a year ago

... and we still have a SQL injection attack opportunity. This is a bad way of solving the most immediate problem.

adarshr Over a year ago

@Jon You couldn't wait until I linked your answer, could you? :)

anna karenina Over a year ago

Wow, how sloppy of me, thanks a lot, been looking for this error for about an hour.

Jon Skeet Over a year ago

@adarshr: The problem is that by showing the insecure way, the OP may well just take it and not fix the bigger problem.

anna karenina Over a year ago

I will use the PreparedStatement, will code the right way. Thanks guys for your time!

Collectives™ on Stack Overflow

java sql syntax error insert into

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related