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

Discussion in 'Java' started by Baby Lion, Oct 1, 2006.

  1. Baby Lion

    Baby Lion Guest

    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
    Baby Lion, Oct 1, 2006
    #1
    1. Advertising

  2. 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.
    Brandon McCombs, Oct 1, 2006
    #2
    1. Advertising

  3. Baby Lion

    Lew Guest

    > 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
    Lew, Oct 1, 2006
    #3
  4. Baby Lion

    Baby Lion Guest

    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
    Baby Lion, Oct 1, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?S2VubmV0aCBQ?=

    Connecting to MySql.Data.MySqlClient namespace

    =?Utf-8?B?S2VubmV0aCBQ?=, Dec 15, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    9,568
    Patrick Olurotimi Ige
    Jan 13, 2005
  2. Mr. SweatyFinger
    Replies:
    2
    Views:
    1,815
    Smokey Grindel
    Dec 2, 2006
  3. ZER0K3WL

    Using mysqlclient library -- odd crash

    ZER0K3WL, Sep 30, 2004, in forum: C Programming
    Replies:
    2
    Views:
    283
    Peter Slootweg
    Oct 1, 2004
  4. Carmine [www.thetotalsite.it]
    Replies:
    1
    Views:
    1,089
    Carmine [www.thetotalsite.it]
    Dec 1, 2007
  5. Amit Jain
    Replies:
    5
    Views:
    6,219
    Jim Garrison
    Apr 29, 2009
Loading...

Share This Page