About MD5 in mysql and Java

Hi everbody.

I am a new user.
I have the following question.
As I know, mysql support MD5 encryption to store the password.

(1) How can I set the mysql use MD5 to store password?
(2) I am using mm.mysql as Java driver, how can I set the Java also use MD5 to check the passord?


Best Tks.
Alfred

Let's say you want to set "my_password" as your password. Your SQL statement would look like this:

INSERT INTO table (password) VALUES (MD5('my_password'))

Then, when you're checking the username and password of someone trying to log in, you'd do something like this (assuming you have the variables $username and $password)

SELECT * FROM table WHERE username = '$username' AND password = MD5('$password')

If that returns any rows, then you have a match.

---John Holmes...

but when I try to execute that insert statement, it shows.

ERROR 1064: You have an error in your SQL syntax


Is there anything need to be setup first?

I forgot to include one of the closing parenthesis...that could of been it. Or it could be your mysql version. what version are you using? or you could be messing up the query...what is the table structure and query you are using?

---John Holmes...

Mysql version is :
The MySql is installed through RPM

mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686)

This is classmate table under test database

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| school_no | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| tel_no | varchar(10) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+

When I try to use the following sql statement for testing MD5 function:

mysql> connect test;
mysql> insert into classmate(school_no) values (MD5('pass'));
ERROR 1064: You have an error in your SQL syntax near '('pass'))' at line 1

It shows above error message

Looks like you need to upgrade if you want to use MD5().

---John Holmes...

Which version you are using?

Is it automatically uninstall previous version if I use RPM ?

Thank you

I don't know anything about RPMs.

There is a stable release of 3.23 out, you should load that.

---John Holmes...

Thaks advance.

I will try it later.

After I have upgraded MySql. the insert statement is okay

Thank you very much

After I have inserted value

mysql> insert into classmate(school_no) values (MD5('123'));
Query OK, 1 row affected (0.00 sec)


When I want to select it:

mysql> select * from classmate where school_no = MD5('123');
Empty set (0.00 sec)


It returns empty set. Why? Is any wrong in my MySQL server or select statement?

Best Tks

MD5() always creates a 32 character string and you're saving it in a 10 character field. So they won't match. Make your field a varchar(32)

---John Holmes...

It even says it in the manual...

Love those manuals...

---John Holmes...