Re: Why some query can execute in mysqlclient but can not in jdbc
Baby Lion wrote:
why there is a query I can execute In mysql client ,but can not execute
in jdbc?
String sqlstring ="select host,port,username,password,LastCrawlState
from Host
Table where id ="+id;
System.out.println(sqlstring);
rs = stmt.executeQuery(sqlstring);
// THE id here equals to 1, its type is int
As a side note, beware of direct use of user-supplied data in SQL; it makes
your code potentially vulnerable to "SQL injection" attacks. Here your
variable 'id' is apparently an int that was not from user-supplied input, and
thus might be immune, but consider
String sql = "SELECT * FROM authtable WHERE user = '"
+ userSuppliedUser
+"' AND pw = '"+ userSuppliedPw +"'";
This represents a common antipattern to authorize a user via a SQL statement.
If the user-supplied 'user' were
"anything' AND 1=1 -- "
and the 'password' were "something", then the resulting SQL would be
SELECT * FROM authtable
WHERE user = 'anything' AND 1=1 -- ' AND pw = 'something'
which would return the entire authtable.
Actual attacks would be somewhat more sophisticated, but this shows the
essential principle. Better would be to use a PreparedStatement:
String sql =
"select host,port,username,password,LastCrawlState "
+"from HostTable where id =?";
PreparedStatement stmt = con.prepareStatement( sql );
stmt.setInt( 1, id );
rs = stmt.executeQuery();
Even in your case, where user-supplied input is not an issue, if you repeat
the query with different 'id' values then a PreparedStatement is likely to
give you a performance boost. You would prepare the statement only once, then
repeat only the stmt.setInt() before each different query.
- Lew