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

[tobleron]

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.

 

[John B. Matthews]

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>

[...]

 

[Tom Anderson]

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

 

[Stefan Rybacki]

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

 

[Roland de Ruiter]

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.

 

[tobleron]

Exactly:

SELECT "abc" = "ABC"
-> 1

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





I agree.

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.

 

[Stefan Rybacki]

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

 

[tobleron]

tobleron schrieb:








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 = ?";


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.



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 ?

 

[Tom Anderson]

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

 

[Lew]

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

 

[Roland de Ruiter]

[...]
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.

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");

 

[Tom Anderson]

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

 

[Martin Gregorie]

[...]
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.

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.

 

[tobleron]

@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);
}
}

 

[Roland de Ruiter]

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

 

[tobleron]

On 1-10-2008 5:17, tobleron wrote:> @Martin

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 ?

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

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]

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);
}
}

 

[Stefan Rybacki]

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

 

[tobleron]

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