com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException | JAVA

Question

i want to get the Number of Entries in a table in My java Project.

This is my code:

int count = 0;
Statement st = Mysql.con.createStatement();
ResultSet rs= st.executeQuery("select count(*) from keys where spieler =" +p.getName());
while (rs.next()){
    count = rs.getInt(1);
}

I am getting following SQLEXCEPTION:

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 'keys where spieler =Pit910' at line 1

What is worng?

Thanks!

You should never use manually concatenated queries, always go for a PreparedStatement, otherwise you are vulnerable to SQL Injeqtion. see bobby-tables.com — Sean Patrick Floyd
– Sean Patrick Floyd, Commented Apr 6, 2014 at 11:15

Reimeus · Accepted Answer · 2014-04-06 17:55:43Z

2

keys is a reserved MySQL keyword which needs to be escaped (either that or ALTER the table to give it a different name). Try this

PreparedStatement preparedStatement = 
    con.prepareStatement("select count(*) from `keys` where spieler = ?");
preparedStatement.setString(1, p.getName());
ResultSet rs= preparedStatement.executeQuery();
...

edited Apr 6, 2014 at 17:55

answered Apr 6, 2014 at 11:22

Reimeus

160k16 gold badges225 silver badges282 bronze badges

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

2 Comments

Pit910 Over a year ago

Thanks that works for me. I have a Insert Command with the Same error: Mysql.Update("INSERT INTO keys (spieler,key) VALUES ('" + p.getName() + "', '" + key + "')");

Reimeus Over a year ago

same applys there - con.prepareStatement("INSERT INTO ``keys`` (spieler, key) VALUES (?, ?)") - some formatting issues should be single backticks around keys – Reimeus 6 mins ago

Rupam · Accepted Answer · 2014-04-06 11:22:26Z

1

Use quotes around text field values, You can optionally use backtick around field name or use table_name.field_name

ResultSet rs= st.executeQuery("select count(*) from 
              `keys` where spieler = '" + p.getName() + "' ");

edited Apr 6, 2014 at 11:22

answered Apr 6, 2014 at 11:11

Rupam

1,71215 silver badges24 bronze badges

1 Comment

Pit910 Over a year ago

Now i get this Error: [13:13:42 INFO]: 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 'keys where spieler = ' Pit910 '' at line 1

Collectives™ on Stack Overflow

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException | JAVA

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related