Why some query can execute in mysqlclient but can not in jdbc

[Baby Lion]

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
AND I GOT:

select host,port,username,password,LastCrawlState from HostTable where
id =1
Exception in thread "main" java.lang.NullPointerException
at Host.<init>(Host.java:65)
at TestTwo.main(TestTwo.java:9)

BUT THIS ONE CAN EXECUTE IN SQLCLIENT:
select host,port,username,password,LastCrawlState from HostTable where
id =1

 

[Brandon McCombs]

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
AND I GOT:

select host,port,username,password,LastCrawlState from HostTable where
id =1
Exception in thread "main" java.lang.NullPointerException
at Host.<init>(Host.java:65)
at TestTwo.main(TestTwo.java:9)

BUT THIS ONE CAN EXECUTE IN SQLCLIENT:
select host,port,username,password,LastCrawlState from HostTable where
id =1

The query syntax isn't at fault. What is on line 65 of Host.java? You
are getting a null pointer exception and that is your problem, not the
query syntax. You'll need to provide some source code so we can figure
out what you aren't instantiating that is causing the null pointer.

 

[Lew]

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

 

[Baby Lion]

Thank you so much for your remind . ^_^
Lew 写é“：