«

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.

In article
<538bfd2e-1203-4e76-82a5->,
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

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

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

On Sep 30, 7:54*pm, Roland de Ruiter
<roland.de.rui...@example.invalid> 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 schrieb:
> On Sep 30, 7:54 pm, Roland de Ruiter
> <roland.de.rui...@example.invalid> 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

On Sep 30, 10:28*pm, Stefan Rybacki <noem...@noemail.foobar> wrote:
> tobleron schrieb:
>
>
>
> > On Sep 30, 7:54 pm, Roland de Ruiter
> > <roland.de.rui...@example.invalid> 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 ?

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.

On Sep 30, 1:22*pm, Tom Anderson <t...@urchin.earth.li> 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