Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Why some query can execute in mysqlclient but can not in jdbc

Reply
Thread Tools

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

 
 
Baby Lion
Guest
Posts: n/a
 
      10-01-2006
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

 
Reply With Quote
 
 
 
 
Brandon McCombs
Guest
Posts: n/a
 
      10-01-2006
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
> 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.
 
Reply With Quote
 
 
 
 
Lew
Guest
Posts: n/a
 
      10-01-2006
> 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
 
Reply With Quote
 
Baby Lion
Guest
Posts: n/a
 
      10-01-2006
Thank you so much for your remind . ^_^
Lew 写道:

> > 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
org.hibernate.exception.DataException: Could not execute JDBC batchupdate Amit Jain Java 5 04-28-2009 11:21 PM
Error: "Column 'InvariantName' is constrained to be unique. Value 'MySql.Data.MySqlClient' is already present." Carmine [www.thetotalsite.it] ASP .Net 1 12-01-2007 01:27 PM
findcontrol("PlaceHolderPrice") why why why why why why why why why why why Mr. SweatyFinger ASP .Net 2 12-02-2006 03:46 PM
Connecting to MySql.Data.MySqlClient namespace =?Utf-8?B?S2VubmV0aCBQ?= ASP .Net 2 01-13-2005 08:08 AM
Using mysqlclient library -- odd crash ZER0K3WL C Programming 2 10-01-2004 06:09 PM



Advertisments