php mysql md5加密,使用md5加密的PHP和Mysql查询出现问题

I am using a normal php/mysql insert query and use md5 to encrypt the password

This is the INSERT query:

$sql = mysql_query("INSERT INTO user (username, password, role, approved) values ('".$username."', '".md5($password)."', 'user', '0')");

And this is my SELECT query which I use for my login check:

$sql = "SELECT id, username, password, role, approved FROM user WHERE username = '".$username."' AND password = '".md5($password)."'";

$result = mysql_query($sql);

But when I check the inserted password and the login password, it returns 2 different values even though if I give same values.

Can anybody help me to fix this problem?

解决方案

Many of the typical caveats here apply... it can't hurt to mention them.

First, vanilla md5 for your password hashing is certainly not the best way to secure your user password within the database. There are numerous questions on stackoverflow that document better approaches, and though there are differences of opinion, they are all more secure that a regular md5, unsalted hash.

Also, you are doing no sanitization of your sql inputs, leaving your database open to sql injection attacks. A meddlesome user could manipulate your user insert to drop tables or modify your data structure. You need to escape these values using mysql_real_escape_string() or adopt a totally different database access system like PDO that has parameterized sql.

That being said, your query should check for the existence of a User row that has the correct username and password, usually achieved by doing a COUNT query first and ensuring that the user is present in the database with valid login creds.

You should ensure that your database columns are proper length and datatype to store the hashes for passwords. Truncation of either could destroy the data.

I hope this helps - SQL injection can be especially nasty!