数据库中的{plain}密码和登录错误

Question

I am coding a login panel to access to an Administration Panel. The data (username and passw) are stored in a MySQL Database (type: InnoDB). Looking in the tables the passwords are stored as plain and in the field password I have: {plain}password.

Adapting a code that I already have, I have some problems because that {plain} thing is confusing me a bit.

My old code is:

// Construct SQL statement for query & execute 
$sql = "SELECT * FROM table WHERE user = '" . $username . "' AND password = '" . md5($password) . "'"; 
$result = $mysqli->query($sql) or die(mysql_error());

So if I replace the "{plain}password" from the field in the database with an MD5 password, the code works great, but if I modify my code to the following one:

// Construct SQL statement for query & execute 
$sql = "SELECT * FROM table WHERE user = '" . $username . "' AND password = '" . $password . "'"; 
$result = $mysqli->query($sql) or die(mysql_error());

I can't login because the password is wrong!

Any idea how to fix this?

Answer 1

If there is the prefix "{plain}" in front of the real password, you have to adjust your query to include that prefix.

$sql = "SELECT * FROM table WHERE user = '" . $username . "' AND password = '{plain}" . $password . "'"; 
$result = $mysqli->query($sql) or die($mysqli->error());

Also note that you should change mysql_error() in the die() command to use mysqli functions as well (so use $mysqli->error).

PS: You should have a look at how to store password nowadays. Storing them in plain text is not secure by any means.

EDIT

Mentioning the comment by @BrianRasmussen here as well: Make sure $username and $password have been sanitzed before being used directly (using string concat) in a query! Otherwise your code is open to SQL injections of all sorts.