Rare error in MySQL syntax using java eclipse

Question

'query' accumulates queries, and one is INSERT INTO root:

public StringBuffer query = new StringBuffer("");
private String tmp="";

tmp="INSERT INTO `root` (`root_`) VALUES ('";
tmp=tmp.concat(root);
tmp=tmp.concat("');");
query.append(tmp);

PreparedStatement ps = con.prepareStatement(query.toString());
ps.executeUpdate();
query.delete(0, query.length());

The first time I did this was normally compiled.

System.out.println(query); show this:

INSERT INTO `root` (`root_`) VALUES ('value1');

But the second time 'query' contains:

 INSERT INTO `rel_root_doc` (`freq`,`id_doc`, `id_root`) VALUES (1,1,1);
 INSERT INTO `root` (`root_`) VALUES ('value2');

and I'm having 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 .

'INSERT INTO `root` (`root_`) VALUES ('value2')' at line 2

But if I copy and paste the same code in MySQLWorkbench, it works perfect.

Thanks for read.

If you use a PreparedStatement, then please use it correctly to guard you against SQL injection. Do not concatenate values into the query text, use the ? parameter placeholder. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 9, 2013 at 7:32

RandomSeed · Accepted Answer · 2013-06-09 07:02:23Z

1

A Statement may only contain one SQL query. You will need one Statement (or PreparedStatement) per query.

answered Jun 9, 2013 at 7:02

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

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

2 Comments

Jean Duanner Marticorena Rios Over a year ago

is there any way to make multiple queries, so as I'm trying? thanks for the answer :)

RandomSeed Over a year ago

AFIK there is no way to execute several queries in one Statement. But it also looks like you do not need a PreparedStatement at all. You could just directly run the Statements like this: Statement s = con.createStatement(); ResultSet rs = stmt.executeQuery(query);.

Bill Karwin · Accepted Answer · 2013-06-09 17:35:59Z

1

You can enable multiple queries with the MySQL JDBC driver with a configuration property called allowMultiQueries.

However, I would not recommend to enable this option, because it weakens security. You create more opportunities for hackers to perpetrate SQL injection attacks.

And I have a recollection that multi-queries don't work with prepared statements anyway (but I haven't tested this to be certain).

edited Jun 9, 2013 at 17:35

answered Jun 9, 2013 at 7:16

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Collectives™ on Stack Overflow

Rare error in MySQL syntax using java eclipse

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related