Wrong SQL in Java

Question

I am trying to run a SQL statement in Java and I am getting syntax

String number,key; //populate number and key.
rs = stmt.executeQuery("select count(*) 
                          from users_transition 
                         where phoneNumber =" +number +"and randKey="+key);

In MySQL database, phoneNumber is BigInt(20) and key is Int(11).

Also, according to this link. The table 5.1 says that types in MYSQl can be converted to what types in Java. Doesnt the other way round would work too?

Here's the 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 'randKey=9999' at line 1

Answer 1

I'll tell you what is wrong, and then I'll tell you what is very wrong.

What is wrong

First, you are building a query which where has missing spaces (and possibly missing quotes):

String number,key; //populate number and key.
rs = stmt.executeQuery("select count(*) "
                     + "from users_transition "
                     + "where phoneNumber =" +number +" and randKey="+key)";
//                                                     ^ you missed a space here

What is very wrong

Your query is vulnerable to SQL Injection Attacks (please read the link, it provides a humorous example and tips on solving the problem). Use prepared statements to do this kind of thing:

String number, key;
PreparedStatement ps = conn.prepareStatement("select count(*) "
                                           + "from users_transition "
                                           + "where phoneNumber=? "
                                           + "  and randKey=?");
// The question marks are place holders for values
// You can assign this values with setXXX() methods
ps.setString(1, number);
ps.setString(2, randKey);
ResultSet rs = ps.executeQuery();
// Do whatever you need to do with the ResultSet

Answer 2

You are missing a space between the number and the AND operator:

rs = stmt.executeQuery("select count(*) from users_transition where phoneNumber =" +number +" and randKey="+key);
//                                                                                           ^

You should replace the query with prepared statement, and use query parameters. This would help you not only avoid simple errors like this, but also make your code immune to SQL injection attacks.

String sql = "select count(*) from users_transition where phoneNumber =? and randKey=?";
PreparedStatement getCount = con.prepareStatement(sql);
getCount.setBigDecimal(1, new BigDecimal(number));
getCount.setBigDecimal(2, new BigDecimal(randKey));