[Help] How to make getText() return the result in case sensitive ?

Discussion in 'Java' started by tobleron, Sep 29, 2008.

  1. tobleron

    tobleron Guest

    Hi, I have an SQL statement like this :

    "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText() +"' AND
    passwd = '"+ PasswdTxt.getText() +"'"

    and I have an if statement to make selection, whether the userID and
    password which are supplied in the form are equal to MySQL data or
    not. The datas in MySQL are "test" for userID field and "myecg" for
    password field.

    When I fill the UserIDTxt with "Test" and passwd with "myecg", or with
    other configuration like "TEST" and "MyECG", the result in the if
    statement produce "OK" sign. But I want to make it case sensitive.
    Only "test" and "myecg" should be produce "OK" sign. How to do it ?

    Many thanks in advance.
     
    tobleron, Sep 29, 2008
    #1
    1. Advertising

  2. In article
    <>,
    tobleron <> wrote:

    > Hi, I have an SQL statement like this :
    >
    > "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText() +"' AND
    > passwd = '"+ PasswdTxt.getText() +"'"
    >
    > and I have an if statement to make selection, whether the userID and
    > password which are supplied in the form are equal to MySQL data or
    > not. The datas in MySQL are "test" for userID field and "myecg" for
    > password field.


    It won't matter what the password is if you store it in plain text.
    Passwords should be encrypted:

    <http://www.owasp.org/index.php/Hashing_Java>

    It won't matter what the password is if you allow SQL injection.
    Use a PreparedStatement for queries:

    <http://www.google.com/search?q=sql+injection+preparedstatement>

    [...]
    --
    John B. Matthews
    trashgod at gmail dot com
    home dot woh dot rr dot com slash jbmatthews
     
    John B. Matthews, Sep 29, 2008
    #2
    1. Advertising

  3. tobleron

    Tom Anderson Guest

    Re: [Help] How to make getText() return the result in case sensitive?

    On Mon, 29 Sep 2008, tobleron wrote:

    > Hi, I have an SQL statement like this :
    >
    > "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText() +"' AND
    > passwd = '"+ PasswdTxt.getText() +"'"


    Firstly, don't do that. Use a PreparedStatement. It's cleaner, more
    efficient, and most importantly, protects against SQL injection attacks
    and bugs. You should basically never be constructing an SQL string in an
    app, unless you have a very good reason indeed.

    Like so:

    // do this in your setup code
    PreparedStatement passwordLookup = conn.prepareStatement("SELECT * FROM user WHERE userid = ? AND passwd = ?") ;

    // do this to look up the password
    // you MUST NOT let multiple threads execute this code at once: use a
    // synchronized block if that might happen

    passwordLookup.setString(1, UserIDText.getText()) ;
    passwordLookup.setString(2, PasswdText.getText()) ;
    ResultSet result = passwordLookup.executeQuery() ;

    > and I have an if statement to make selection, whether the userID and
    > password which are supplied in the form are equal to MySQL data or not.
    > The datas in MySQL are "test" for userID field and "myecg" for password
    > field.
    >
    > When I fill the UserIDTxt with "Test" and passwd with "myecg", or with
    > other configuration like "TEST" and "MyECG", the result in the if
    > statement produce "OK" sign. But I want to make it case sensitive. Only
    > "test" and "myecg" should be produce "OK" sign. How to do it ?


    As far as i know, case sensitivity is database-specific. There will be
    special commands in your database's dialect of SQL to control it.

    However, what you can do in java is to look at the data returned. The
    contents of the fields as given in the ResultSet should be the right case
    - the case they're actually in in the database. That means you just have
    to do a case-sensitive comparison in java. Here you go:

    public class PasswordChecker {
    private PreparedStatement passwordLookup ;

    public PasswordChecker(Connection conn) throws SQLException {
    passwordLookup = conn.prepareStatement("SELECT * FROM user WHERE userid = ?") ;
    }
    public boolean checkPassword(String username, String password) throws SQLException {
    passwordLookup.setString(1, username) ;
    ResultSet results = passwordLookup.executeQuery() ;
    try {
    while (results.next()) {
    String dbUsername = results.getString(1) ;
    String dbPassword = results.getString(2) ;
    if ((dbUsername.equals(username)) && (dbPassword.equals(password))) return true ;
    }
    return false ;
    } finally {
    results.close() ;
    }
    }
    }

    tom

    --
    I am predictable. I worry about this, but then I think, "I am predictable
    but right, so I don't care." -- coffeeandink
     
    Tom Anderson, Sep 29, 2008
    #3
  4. Re: [Help] How to make getText() return the result in case sensitive?

    Tom Anderson schrieb:
    > On Mon, 29 Sep 2008, tobleron wrote:
    >
    >...
    > As far as i know, case sensitivity is database-specific. There will be
    > special commands in your database's dialect of SQL to control it.


    If I remember correct for MySQL it was the BINARY keyword.

    >...


    Other than that I agree with the "use prepared statements" as well as "don't
    store your password in plain text" comments.

    Stefan
     
    Stefan Rybacki, Sep 30, 2008
    #4
  5. Re: [Help] How to make getText() return the result in case sensitive?

    On 30-9-2008 9:16, Stefan Rybacki wrote:
    > Tom Anderson schrieb:
    >> On Mon, 29 Sep 2008, tobleron wrote:
    >>
    >> ...
    >> As far as i know, case sensitivity is database-specific. There will be
    >> special commands in your database's dialect of SQL to control it.

    >
    > If I remember correct for MySQL it was the BINARY keyword.


    Exactly:

    SELECT "abc" = "ABC"
    -> 1


    SELECT "abc" = BINARY "ABC"
    -> 0

    >
    > Other than that I agree with the "use prepared statements" as well as
    > "don't store your password in plain text" comments.
    >
    > Stefan


    I agree.
    --
    Regards,

    Roland
     
    Roland de Ruiter, Sep 30, 2008
    #5
  6. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    On Sep 30, 7:54 pm, Roland de Ruiter
    <> wrote:
    > On 30-9-2008 9:16, Stefan Rybacki wrote:
    >
    > > Tom Anderson schrieb:
    > >> On Mon, 29 Sep 2008, tobleron wrote:

    >
    > >> ...
    > >> As far as i know, case sensitivity is database-specific. There will be
    > >> special commands in your database's dialect of SQL to control it.

    >
    > > If I remember correct for MySQL it was the BINARY keyword.

    >
    > Exactly:
    >
    > SELECT "abc" = "ABC"
    > -> 1
    >
    > SELECT "abc" = BINARY "ABC"
    > -> 0
    >
    >
    >
    > > Other than that I agree with the "use prepared statements" as well as
    > > "don't store your password in plain text" comments.

    >
    > > Stefan

    >
    > I agree.
    > --
    > Regards,
    >
    > Roland


    Hi, I've tried to follow all of your suggestion, but my program always
    result to the else statements of the if selection, whatever values
    that inputted through the form. Here is my code :

    @Action public void dologin() {
    String url = "jdbc:mysql://localhost:3306/dicom?
    jdbcCompliantTruncation=false";
    Connection con;
    PreparedStatement passwordLookup ;

    try {
    Class.forName("com.mysql.jdbc.Driver");
    } catch(java.lang.ClassNotFoundException e) {
    System.err.println(e);
    }

    try {
    con = DriverManager.getConnection(url, "root", "");
    String sql = "SELECT * FROM user WHERE userid = '"+
    UserIDTxt.getText() +"' AND passwd = '"+ PasswdTxt.getSelectedText()
    +"'";
    passwordLookup = con.prepareStatement(sql);
    ResultSet result = passwordLookup.executeQuery();

    if (result.first()) {
    String dbUsername = result.getString(1) ;
    String dbPassword = result.getString(2) ;

    if ((dbUsername.equals(UserIDTxt.getText())) &&
    (dbPassword.equals(PasswdTxt.getSelectedText()))){
    setVisible(false);
    if (ECGMenuBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    ECGMenuBox = new ECGMenu(mainFrame);

    ECGMenuBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(ECGMenuBox);
    }
    else {
    setVisible(false);
    if (LoginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    LoginWarningBox = new
    LoginWarning(mainFrame);

    LoginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(LoginWarningBox);
    }
    }
    else {
    setVisible(false);
    if (LoginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    LoginWarningBox = new
    LoginWarning(mainFrame);

    LoginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(LoginWarningBox);
    }
    result.close();
    con.close();
    } catch(SQLException e) {
    System.err.println(e);
    }
    }

    The structure of my table is :

    a4identity : 1
    userid : test
    passwd : myecg
    repasswd : myecg
    phyname : test
    dept : test
    create : N
    view : N
    edit : N

    I'm using NetBeans 6.1 and MySQL 5.0.51b. Do I missed something ?
    Please help.
     
    tobleron, Sep 30, 2008
    #6
  7. Re: How to make getText() return the result in case sensitive ?

    tobleron schrieb:
    > On Sep 30, 7:54 pm, Roland de Ruiter
    > <> wrote:
    >> On 30-9-2008 9:16, Stefan Rybacki wrote:
    >>
    >>> Tom Anderson schrieb:
    >>>> On Mon, 29 Sep 2008, tobleron wrote:
    >>>> ...
    >>>> As far as i know, case sensitivity is database-specific. There will be
    >>>> special commands in your database's dialect of SQL to control it.
    >>> If I remember correct for MySQL it was the BINARY keyword.

    >> Exactly:
    >>
    >> SELECT "abc" = "ABC"
    >> -> 1
    >>
    >> SELECT "abc" = BINARY "ABC"
    >> -> 0
    >>
    >>
    >>
    >>> Other than that I agree with the "use prepared statements" as well as
    >>> "don't store your password in plain text" comments.
    >>> Stefan

    >> I agree.
    >> --
    >> Regards,
    >>
    >> Roland

    >
    > Hi, I've tried to follow all of your suggestion, but my program always
    > result to the else statements of the if selection, whatever values
    > that inputted through the form. Here is my code :
    >
    > @Action public void dologin() {
    > String url = "jdbc:mysql://localhost:3306/dicom?
    > jdbcCompliantTruncation=false";
    > Connection con;
    > PreparedStatement passwordLookup ;
    >
    > try {
    > Class.forName("com.mysql.jdbc.Driver");
    > } catch(java.lang.ClassNotFoundException e) {
    > System.err.println(e);
    > }
    >
    > try {
    > con = DriverManager.getConnection(url, "root", "");
    > String sql = "SELECT * FROM user WHERE userid = '"+
    > UserIDTxt.getText() +"' AND passwd = '"+ PasswdTxt.getSelectedText()
    > +"'";


    You are not using the prepared statement properly.
    This way it is no use at all. Try to build your sql statement like this:

    String sql="SELECT * FROM user WHERE userid = ? AND passwd = ?";

    Also add the BINARY keyword as suggested, this way you don't need the workaround.

    String sql="SELECT * FROM user WHERE userid = BINARY ? AND passwd = ?";



    > passwordLookup = con.prepareStatement(sql);


    here you replace the question marks with the actual values

    passwordLookup.setString(1, UserIDTxt.getText());
    passwordLookup.setString(2, PasswordTxt.getText());

    I just saw you are not following the naming conventions. Variable names should
    start with a lower character therefore userIDTxt and passwordTxt in your case.

    > ResultSet result = passwordLookup.executeQuery();
    >
    >...


    Hope that helps
    Stefan
     
    Stefan Rybacki, Sep 30, 2008
    #7
  8. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    On Sep 30, 10:28 pm, Stefan Rybacki <> wrote:
    > tobleron schrieb:
    >
    >
    >
    > > On Sep 30, 7:54 pm, Roland de Ruiter
    > > <> wrote:
    > >> On 30-9-2008 9:16, Stefan Rybacki wrote:

    >
    > >>> Tom Anderson schrieb:
    > >>>> On Mon, 29 Sep 2008, tobleron wrote:
    > >>>> ...
    > >>>> As far as i know, case sensitivity is database-specific. There will be
    > >>>> special commands in your database's dialect of SQL to control it.
    > >>> If I remember correct for MySQL it was the BINARY keyword.
    > >> Exactly:

    >
    > >> SELECT "abc" = "ABC"
    > >> -> 1

    >
    > >> SELECT "abc" = BINARY "ABC"
    > >> -> 0

    >
    > >>> Other than that I agree with the "use prepared statements" as well as
    > >>> "don't store your password in plain text" comments.
    > >>> Stefan
    > >> I agree.
    > >> --
    > >> Regards,

    >
    > >> Roland

    >
    > > Hi, I've tried to follow all of your suggestion, but my program always
    > > result to the else statements of the if selection, whatever values
    > > that inputted through the form. Here is my code :

    >
    > > @Action public void dologin() {
    > >         String url = "jdbc:mysql://localhost:3306/dicom?
    > > jdbcCompliantTruncation=false";
    > >         Connection con;
    > >         PreparedStatement passwordLookup ;

    >
    > >         try {
    > >             Class.forName("com.mysql.jdbc.Driver");
    > >         } catch(java.lang.ClassNotFoundException e) {
    > >             System.err.println(e);
    > >         }

    >
    > >         try {
    > >             con = DriverManager.getConnection(url, "root", "");
    > >             String sql = "SELECT * FROM user WHERE userid = '"+
    > > UserIDTxt.getText() +"' AND passwd = '"+ PasswdTxt.getSelectedText()
    > > +"'";

    >
    > You are not using the prepared statement properly.
    > This way it is no use at all. Try to build your sql statement like this:
    >
    > String sql="SELECT * FROM user WHERE userid = ? AND passwd = ?";
    >
    > Also add the BINARY keyword as suggested, this way you don't need the workaround.
    >
    > String sql="SELECT * FROM user WHERE userid = BINARY ? AND passwd = ?";
    >
    > >             passwordLookup = con.prepareStatement(sql);

    >
    > here you replace the question marks with the actual values
    >
    > passwordLookup.setString(1, UserIDTxt.getText());
    > passwordLookup.setString(2, PasswordTxt.getText());
    >
    > I just saw you are not following the naming conventions. Variable names should
    > start with a lower character therefore userIDTxt and passwordTxt in your case.
    >
    > >             ResultSet result = passwordLookup.executeQuery();

    >
    > >...

    >
    > Hope that helps
    > Stefan


    Hi, I already changed the code into this :

    @Action public void dologin() {
    String url = "jdbc:mysql://localhost:3306/dicom?
    jdbcCompliantTruncation=false";
    Connection con;
    PreparedStatement passwordLookup ;

    try {
    Class.forName("com.mysql.jdbc.Driver");
    } catch(java.lang.ClassNotFoundException e) {
    System.err.println(e);
    }

    try {
    con = DriverManager.getConnection(url, "root", "");
    String sql = "SELECT * FROM user WHERE userid = BINARY ?
    AND passwd = ?";
    passwordLookup = con.prepareStatement(sql);
    passwordLookup.setString(1, UserIDTxt.getText());
    passwordLookup.setString(2, PasswdTxt.getSelectedText());
    ResultSet result = passwordLookup.executeQuery();

    if (result.first()) {
    String dbUsername = result.getString(1) ;
    String dbPassword = result.getString(2) ;

    if ((dbUsername.equals(UserIDTxt.getText())) &&
    (dbPassword.equals(PasswdTxt.getSelectedText()))){
    setVisible(false);
    if (ECGMenuBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    ECGMenuBox = new ECGMenu(mainFrame);

    ECGMenuBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(ECGMenuBox);
    }
    else {
    setVisible(false);
    if (LoginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    LoginWarningBox = new
    LoginWarning(mainFrame);

    LoginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(LoginWarningBox);
    }
    }
    else {
    setVisible(false);
    if (LoginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    LoginWarningBox = new
    LoginWarning(mainFrame);

    LoginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(LoginWarningBox);
    }
    result.close();
    con.close();
    } catch(SQLException e) {
    System.err.println(e);
    }
    }

    But it still produce result in the else statements, whatever values
    were given (even when I left them blank). BTW, UserIDTxt and PasswdTxt
    are swing components. Shoul I change it into userIDTxt and passwdTxt ?
     
    tobleron, Sep 30, 2008
    #8
  9. tobleron

    Tom Anderson Guest

    Re: [Help] How to make getText() return the result in case sensitive?

    On Mon, 29 Sep 2008, Lew wrote:

    > Tom Anderson wrote:
    >> On Mon, 29 Sep 2008, tobleron wrote:
    >>
    >>> Hi, I have an SQL statement like this :
    >>>
    >>> "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText() +"' AND
    >>> passwd = '"+ PasswdTxt.getText() +"'"

    >>
    >> Firstly, don't do that. Use a PreparedStatement. It's cleaner, more
    >> efficient, and most importantly, protects against SQL injection attacks and
    >> bugs. You should basically never be constructing an SQL string in an app,
    >> unless you have a very good reason indeed.

    >
    > Second-most importantly. Most importantly it provides type safety.


    Type safety is a wonderful thing, but it's not a prerequisite for
    correctness. Protection against SQL injection is.

    Let me put it this way - would you rather have an incorrect or unsecure
    application written in java, or a correct and secure one written in
    python, smalltalk, or javascript?

    tom

    --
    Only the bagel has the correct aspect ratio.
     
    Tom Anderson, Sep 30, 2008
    #9
  10. tobleron

    Lew Guest

    Re: How to make getText() return the result in case sensitive ?

    On Sep 30, 1:22 pm, Tom Anderson <> wrote:
    > On Mon, 29 Sep 2008, Lew wrote:
    > > Tom Anderson wrote:
    > >> On Mon, 29 Sep 2008, tobleron wrote:

    >
    > >>> Hi, I have an SQL statement like this :

    >
    > >>> "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText() +"' AND
    > >>> passwd = '"+ PasswdTxt.getText() +"'"

    >
    > >> Firstly, don't do that. Use a PreparedStatement. It's cleaner, more
    > >> efficient, and most importantly, protects against SQL injection attacks and
    > >> bugs. You should basically never be constructing an SQL string in an app,
    > >> unless you have a very good reason indeed.

    >
    > > Second-most importantly.  Most importantly it provides type safety.

    >
    > Type safety is a wonderful thing, but it's not a prerequisite for
    > correctness. Protection against SQL injection is.
    >
    > Let me put it this way - would you rather have an incorrect or unsecure
    > application written in java, or a correct and secure one written in
    > python, smalltalk, or javascript?


    Neither. It's a false question. The real question is how much effort
    it is to create a secure, correct application in either environment.

    PreparedStatement is not requisite for protection again SQL
    injection. One can protect against those attacks with regular SQL
    strings and (unPrepared) Statements. It is the type safety of
    PreparedStatement that makes that protection easy and automatic. So
    your question should be, "If PreparedStatement weren't type-safe, how
    would it be able to protect against SQL injection in the first place?"

    --
    Lew
     
    Lew, Sep 30, 2008
    #10
  11. Re: How to make getText() return the result in case sensitive ?

    On 30-9-2008 17:54, tobleron wrote:
    > [...]
    > String sql = "SELECT * FROM user WHERE userid = BINARY ?
    > AND passwd = ?";
    > [...]
    > if (result.first()) {
    > String dbUsername = result.getString(1) ;
    > String dbPassword = result.getString(2) ;
    > [...]


    result.getString(1) and result.getString(2) would retrieve the value of
    the result set's first and second column, respectively.

    Earlier, however, you wrote:

    > The structure of my table is :
    >
    > a4identity : 1
    > userid : test
    > passwd : myecg
    > repasswd : myecg
    > phyname : test
    > dept : test
    > create : N
    > view : N
    > edit : N


    So, the 1st and 2nd column of the result set seem to be the "a4identity"
    column and the "userid" column. This doesn't match with what you want.

    You can of course correct the column index, but instead of column
    indexes, in most cases it's better to use column names when retrieving
    values of the result set. Assuming the column names are as you
    specified, use this:

    String dbUsername = result.getString("userid");
    String dbPassword = result.getString("passwd");

    --
    Regards,

    Roland
     
    Roland de Ruiter, Sep 30, 2008
    #11
  12. tobleron

    Tom Anderson Guest

    Re: How to make getText() return the result in case sensitive ?

    On Tue, 30 Sep 2008, Lew wrote:

    > On Sep 30, 1:22 pm, Tom Anderson <> wrote:
    >> On Mon, 29 Sep 2008, Lew wrote:
    >>> Tom Anderson wrote:
    >>>> On Mon, 29 Sep 2008, tobleron wrote:
    >>>>
    >>>>> Hi, I have an SQL statement like this :
    >>>>>
    >>>>> "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText() +"' AND
    >>>>> passwd = '"+ PasswdTxt.getText() +"'"
    >>>>
    >>>> Firstly, don't do that. Use a PreparedStatement. It's cleaner, more
    >>>> efficient, and most importantly, protects against SQL injection attacks and
    >>>> bugs. You should basically never be constructing an SQL string in an app,
    >>>> unless you have a very good reason indeed.
    >>>
    >>> Second-most importantly.  Most importantly it provides type safety.

    >>
    >> Type safety is a wonderful thing, but it's not a prerequisite for
    >> correctness. Protection against SQL injection is.
    >>
    >> Let me put it this way - would you rather have an incorrect or unsecure
    >> application written in java, or a correct and secure one written in
    >> python, smalltalk, or javascript?

    >
    > Neither. It's a false question. The real question is how much effort
    > it is to create a secure, correct application in either environment.


    Yes. Which i think means your answer is "the latter".

    > PreparedStatement is not requisite for protection again SQL injection.
    > One can protect against those attacks with regular SQL strings and
    > (unPrepared) Statements. It is the type safety of PreparedStatement
    > that makes that protection easy and automatic. So your question should
    > be, "If PreparedStatement weren't type-safe, how would it be able to
    > protect against SQL injection in the first place?"


    I don't see how type safety has anything to do with it. It's the idea of
    separating the text of the command and the text of the parameters that
    does it. You could have exactly the same separation, and exactly the same
    security, in a typeless language.

    Just for yuks, here's a sketch in python:

    #! /usr/bin/env python

    class ResultSet(object):
    def __init__(self, sql):
    self.sql = sql
    def __repr__(self):
    return "i am the results for [" + self.sql + "]"

    def executeQuery(sql):
    return ResultSet(sql)

    def escape(s):
    return s.replace("'", "''")

    def sqlStr(obj):
    if (obj == None):
    return "NULL"
    if (isinstance(obj, int)):
    return str(obj)
    if (isinstance(obj, str)):
    return "'" + escape(obj) + "'"
    else:
    raise ValueError, "unknown parameter type: " + obj

    class PreparedStatement(object):
    def __init__(self, sql):
    self.sql = sql
    numParams = sql.count("?")
    self.params = [None] * numParams
    def __setitem__(self, index, value):
    self.params[index] = value
    def execute(self):
    template = self.sql.replace("?", "%s")
    paramStrs = map(sqlStr, self.params)
    preparedSql = template % tuple(paramStrs)
    return executeQuery(preparedSql)

    >>> stmt = preparedstatement.PreparedStatement("SELECT * from users WHERE name = ? AND userlevel = ?")
    >>> stmt[0] = "Lew O'Canon"
    >>> stmt[1] = 42
    >>> stmt.execute()

    i am the results for [SELECT * from users WHERE name = 'Lew O''Canon' AND userlevel = 42]

    tom

    --
    GODZILLA PLEASE EAT THE FUCKIN COLDPLAY -- a poster in Bergen
     
    Tom Anderson, Oct 1, 2008
    #12
  13. Re: How to make getText() return the result in case sensitive ?

    On Tue, 30 Sep 2008 22:20:42 +0200, Roland de Ruiter wrote:

    > On 30-9-2008 17:54, tobleron wrote:
    >> [...]
    >> String sql = "SELECT * FROM user WHERE userid = BINARY ?
    >> AND passwd = ?";
    >> [...]
    >> if (result.first()) {
    >> String dbUsername = result.getString(1) ; String
    >> dbPassword = result.getString(2) ;
    >> [...]

    >
    > result.getString(1) and result.getString(2) would retrieve the value of
    > the result set's first and second column, respectively.
    >
    > Earlier, however, you wrote:
    >
    >> The structure of my table is :
    >>
    >> a4identity : 1
    >> userid : test
    >> passwd : myecg
    >> repasswd : myecg
    >> phyname : test
    >> dept : test
    >> create : N
    >> view : N
    >> edit : N

    >
    > So, the 1st and 2nd column of the result set seem to be the "a4identity"
    > column and the "userid" column. This doesn't match with what you want.
    >
    > You can of course correct the column index, but instead of column
    > indexes, in most cases it's better to use column names when retrieving
    > values of the result set. Assuming the column names are as you
    > specified, use this:
    >
    > String dbUsername = result.getString("userid"); String
    > dbPassword = result.getString("passwd");


    Why are you bothering to read the row contents seeing that you apparently
    don't use any of the values you're retrieving? A better approach would be
    to use
    SELECT count(*) FROM user WHERE userid = BINARY ? AND passwd = ?

    and then simply test the row count. If the count is 1 the user provided a
    good username and password and should be given access. Any other value is
    an authorisation failure. Its bad practice to be more explicit: NEVER
    indicate whether its the username or the password that's wrong.

    Another point: using "SELECT * FROM..." is bad practice. Always
    explicitly list the columns you want to retrieve. If you don't, then any
    change to the number and/or order of the columns in the table will break
    your application.


    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
     
    Martin Gregorie, Oct 1, 2008
    #13
  14. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    @Roland
    I already changed into :
    String dbUsername = result.getString("userid") ;
    String dbPassword = result.getString("passwd") ;

    @Martin
    I already changed into :
    "SELECT userid,passwd FROM user WHERE userid = BINARY ? AND passwd
    = ?"

    @Lew
    I already changed into :
    doLogin()
    And when I didn't call Class.forName("com.mysql.jdbc.Driver"), the
    NetBeans show error message "No suitable driver found" even I included
    MySQL driver libraries in my project.

    @All
    When I ran the program, the if (result.first()) statement always
    returns 0, so that means no record match found, even I give "test" and
    "myecg" into the form and there is "test" and "myecg" in the
    database.

    Compared to "SELECT * FROM user WHERE userid = '"+ UserIDTxt.getText()
    +"' AND passwd = '"+ PasswdTxt.getText() +"'" this statement will
    returns 1 in the if statement.

    I already changed the passwdTxt swing component from textField into
    passwordField in order to show "******" character when input is given,
    so I used passwdTxt.getSelectedText() to get the value since
    passwdTxt.getText() is forbidden for passwordField component.

    Here my code :

    @Action public void doLogin() {
    String url = "jdbc:mysql://localhost:3306/dicom?
    jdbcCompliantTruncation=false";
    Connection con;
    PreparedStatement passwordLookup ;

    try {
    Class.forName("com.mysql.jdbc.Driver");
    } catch(java.lang.ClassNotFoundException e) {
    System.err.println(e);
    }

    try {
    con = DriverManager.getConnection(url, "root", "");
    String sql = "SELECT userid,passwd FROM user WHERE userid
    = BINARY ? AND passwd = ?";
    passwordLookup = con.prepareStatement(sql);
    passwordLookup.setString(1, userIDTxt.getText());
    passwordLookup.setString(2, passwdTxt.getSelectedText());
    ResultSet result = passwordLookup.executeQuery();

    if (result.first()) {
    String dbUsername = result.getString("userid") ;
    String dbPassword = result.getString("passwd") ;

    if ((dbUsername.equals(userIDTxt.getText())) &&
    (dbPassword.equals(passwdTxt.getSelectedText()))){
    setVisible(false);
    if (ecgMenuBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    ecgMenuBox = new ECGMenu(mainFrame);

    ecgMenuBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(ecgMenuBox);
    }
    else {
    setVisible(false);
    if (loginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    mainFrame.setSize(100,80);
    loginWarningBox = new
    LoginWarning(mainFrame);

    loginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(loginWarningBox);
    }
    }
    else {
    setVisible(false);

    //TRAP CODE HERE
    JFrame mainFrame;
    mainFrame = new JFrame("No Record Found");
    mainFrame.setSize(300,150);
    mainFrame.show();
    }
    result.close();
    con.close();
    } catch(SQLException e) {
    System.err.println(e);
    }
    }
     
    tobleron, Oct 1, 2008
    #14
  15. Re: How to make getText() return the result in case sensitive ?

    On 1-10-2008 5:17, tobleron wrote:
    > @Martin
    > I already changed into :
    > "SELECT userid,passwd FROM user WHERE userid = BINARY ? AND passwd
    > = ?"
    >

    You still compare the password case insensitive. So add the BINARY
    keyword also to the password comparison:

    SELECT userid,passwd FROM user WHERE userid = BINARY ? AND passwd = BINARY ?
    --
    Regards,

    Roland
     
    Roland de Ruiter, Oct 1, 2008
    #15
  16. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    On Oct 1, 5:57 pm, Roland de Ruiter <>
    wrote:
    > On 1-10-2008 5:17, tobleron wrote:> @Martin
    > > I already changed into :
    > > "SELECT userid,passwd FROM user WHERE userid = BINARY ? AND passwd
    > > = ?"

    >
    > You still compare the password case insensitive. So add the BINARY
    > keyword also to the password comparison:
    >
    > SELECT userid,passwd FROM user WHERE userid = BINARY ? AND passwd = BINARY ?
    > --
    > Regards,
    >
    > Roland


    @Roland
    The result is the same. The flow went to the TRAP CODE which is
    indicate that there's no data found even I give the correct value.
    Should I use trimming technique in this case ?
     
    tobleron, Oct 1, 2008
    #16
  17. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    I think the problem in the usage of passwordField named passwdTxt. I
    have to use getPassword() instead getText(). But I don't know how to
    implement it in the PreparedStatement, since getPassword() function
    returns the value in the array of char. Please any one can help me ?
     
    tobleron, Oct 1, 2008
    #17
  18. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    Problem solved ! It runs well as I need. Thank you for your help. Here
    the code :

    @Action public void doLogin() {
    String url = "jdbc:mysql://localhost:3306/dicom?
    jdbcCompliantTruncation=false";
    Connection con;
    PreparedStatement passwordLookup ;

    try {
    Class.forName("com.mysql.jdbc.Driver");
    } catch(java.lang.ClassNotFoundException e) {
    System.err.println(e);
    }

    try {
    con = DriverManager.getConnection(url, "root", "");
    String sql = "SELECT userid,passwd FROM user WHERE userid
    = BINARY ? AND passwd = BINARY ?";
    passwordLookup = con.prepareStatement(sql);
    char[] passwdnya = passwdTxt.getPassword();
    String convertedChars = new String(passwdnya);
    passwordLookup.setString(1, userIDTxt.getText().trim());
    passwordLookup.setString(2, convertedChars.trim());
    ResultSet result = passwordLookup.executeQuery();

    if (result.next()) {
    String dbUsername = result.getString("userid") ;
    String dbPassword = result.getString("passwd") ;

    if ((dbUsername.equals(userIDTxt.getText().trim()))
    && (dbPassword.equals(convertedChars.trim()))){
    setVisible(false);
    if (ecgMenuBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    ecgMenuBox = new ECGMenu(mainFrame);

    ecgMenuBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(ecgMenuBox);
    }
    else {
    setVisible(false);
    if (loginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    mainFrame.setSize(100,80);
    loginWarningBox = new
    LoginWarning(mainFrame);

    loginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(loginWarningBox);
    }
    }
    else {
    setVisible(false);
    if (loginWarningBox == null) {
    JFrame mainFrame =
    Main.getApplication().getMainFrame();
    mainFrame.setSize(100,80);
    loginWarningBox = new
    LoginWarning(mainFrame);

    loginWarningBox.setLocationRelativeTo(mainFrame);
    }
    Main.getApplication().show(loginWarningBox);

    }
    result.close();
    passwordLookup.close();
    con.close();
    } catch(SQLException e) {
    System.err.println(e);
    }
    }
     
    tobleron, Oct 1, 2008
    #18
  19. Re: How to make getText() return the result in case sensitive ?

    tobleron schrieb:
    >...
    > if (result.next()) {
    > String dbUsername = result.getString("userid") ;
    > String dbPassword = result.getString("passwd") ;
    >


    You are still doing this redundant check whether username and password are
    correct which the database already did for you.

    > if ((dbUsername.equals(userIDTxt.getText().trim()))
    > && (dbPassword.equals(convertedChars.trim()))){
    > setVisible(false);
    > if (ecgMenuBox == null) {
    > JFrame mainFrame =
    > Main.getApplication().getMainFrame();
    > ecgMenuBox = new ECGMenu(mainFrame);
    >
    > ecgMenuBox.setLocationRelativeTo(mainFrame);
    > }
    > Main.getApplication().show(ecgMenuBox);
    > }
    > else {
    > setVisible(false);
    > if (loginWarningBox == null) {
    > JFrame mainFrame =
    > Main.getApplication().getMainFrame();
    > mainFrame.setSize(100,80);
    > loginWarningBox = new
    > LoginWarning(mainFrame);
    >
    > loginWarningBox.setLocationRelativeTo(mainFrame);
    > }
    > Main.getApplication().show(loginWarningBox);
    > }
    > }
    > else {
    > setVisible(false);
    > if (loginWarningBox == null) {
    > JFrame mainFrame =
    > Main.getApplication().getMainFrame();
    > mainFrame.setSize(100,80);
    > loginWarningBox = new
    > LoginWarning(mainFrame);
    >
    > loginWarningBox.setLocationRelativeTo(mainFrame);
    > }
    > Main.getApplication().show(loginWarningBox);
    >
    > }
    > result.close();
    > passwordLookup.close();
    > con.close();
    > } catch(SQLException e) {
    > System.err.println(e);
    > }
    > }
     
    Stefan Rybacki, Oct 1, 2008
    #19
  20. tobleron

    tobleron Guest

    Re: How to make getText() return the result in case sensitive ?

    @Lew
    How to solve it ? As I mention above, when I didn't call
    Class.forName("com.mysql.jdbc.Driver"), the
    NetBeans show error message "No suitable driver found" even I included
    MySQL driver libraries in my project. The program will not run without
    this call.

    @Stefan
    So, you mean the preparedStatement and executeQuery statement are
    already check the only match values and give the result in the if
    statement ? Hmm... OK, I'll re-check to make sure whether I need to
    double check the value or not. Thank you for your advise.

    @All
    BTW, there's still a WARNING when I run the program, like this :

    Oct 1, 2008 10:16:04 PM org.jdesktop.application.LocalStorage getId
    WARNING: unspecified resource Application.id using Main
    Oct 1, 2008 10:16:04 PM org.jdesktop.application.LocalStorage getId
    WARNING: unspecified resource Application.vendorId using
    UnknownApplicationVendor

    What happened ? Please help !
     
    tobleron, Oct 1, 2008
    #20
    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. Martin Dechev
    Replies:
    0
    Views:
    567
    Martin Dechev
    Apr 2, 2004
  2. =?Utf-8?B?Sm9l?=

    How to make login script case sensitive

    =?Utf-8?B?Sm9l?=, Nov 16, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    961
    jongalloway
    Nov 17, 2004
  3. Replies:
    1
    Views:
    2,505
    Mark P
    Apr 6, 2007
  4. Michael Tan
    Replies:
    32
    Views:
    1,022
    Ara.T.Howard
    Jul 21, 2005
  5. Xah Lee
    Replies:
    4
    Views:
    1,004
Loading...

Share This Page