user2603089 user2603089 - 4 months ago 25
SQL Question

MySQL password() function to PHP

I tried researching this but still have no answer for it. A program my friend designed writes to the MySQL db passwords using the MySQL password() function.

I am looking for a way to use this through the web front I designed but still have no luck. Does anyone have any suggestions?

The passwords look just like this example

mysql> SET old_passwords = 0;
mysql> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+


I just need to figure out how to turn this into a function i.e
function password_hash

Here's the rest the login query for an example

if (isset($_POST["username"], $_POST["password"], $_POST[$CONF["LOGIN_SIGNAL_TRIGGER"]])) {

/*
If we got a login signal, a password and a username, we will
proceed to check login information. We will first extract
the user row from the db.
*/
$user = myF(myQ("
SELECT `username`,`password`,`id`,`disable_until`,`active`
FROM `[x]users`
WHERE LCASE(`username`)='".strtolower($_POST["username"])."'
"));

if (!$user["id"]) $GLOBALS["LOGIN_FAIL_TYPE"] = "e.user";
elseif ($user["active"] != 1 && $CONF["LOGIN_REQUIRE_ACTIVE"]) $GLOBALS["LOGIN_FAIL_TYPE"] = "e.active";

else {
/*
If the user's account 'disabled' value is greater than
the actual date value, and that the bruteforce protection
system is enabled, we will show an error message
*/
if (($user["disable_until"] > date("U")) && ($CONF["LOGIN_BRUTEFORCE_PROTECT:ENABLE"])) {
$GLOBALS["LOGIN_FAIL_TYPE"] = "e.bruteforce";
(isset($_SESSION["loginFailCount"])?session_unregister('loginFailCount'):false);
}

/*
Account is not disabled
*/
else {
if ((isset($_SESSION["loginFailCount"])) && ($_SESSION["loginFailCount"] > $CONF["LOGIN_BRUTEFORCE_FAILCOUNT"])) {

myQ("UPDATE `[x]users`
SET `disable_until` = ".(date("U")+$CONF["LOGIN_BRUTEFORCE_DISABLE_DURATION"])."
WHERE LCASE(`username`)='".strtolower($_POST["username"])."'
LIMIT 1"
);

(isset($_SESSION["loginFailCount"])?session_unregister('loginFailCount'):false);
$GLOBALS["LOGIN_FAIL_TYPE"] = "e.bruteforce";
}

else {

/*
All the information correct, we will proceed to login
*/
if ($user["password"] == md5(trim($_POST["password"]))) {
$_SESSION["id"] = (integer)$user["id"];

session_write_close();

/*
Update the last login key
*/
$me_last_login = me("last_login");
myQ("UPDATE `[x]users` SET `last_login`='".date("U")."' WHERE `id`='".me('id')."'");

/*
Route the user
*/
if (!$GLOBALS["WAP_MODE"]) {
header("Location: ".(!$me_last_login?$CONF["LOGIN_FIRST_ROUTE_TO"]:$CONF["LOGIN_ROUTE_TO"]));
} else header("Location: {$CONF["WAP_LOGIN_ROUTE_TO"]}");

}

else {
(isset($_SESSION["loginFailCount"])?$_SESSION["loginFailCount"]++:$_SESSION["loginFailCount"]=1);
$GLOBALS["LOGIN_FAIL_TYPE"] = "e.password";
}
}
}
}
}

if ((isset($_GET[$CONF["LOGOUT_SIGNAL_TRIGGER"]])) && (!isset($_POST[$CONF["LOGIN_SIGNAL_TRIGGER"]]))) {

/*
Handle admin swapping
*/
if (isset($_SESSION["swap_id"])) {
$_SESSION["id"] = $_SESSION["swap_id"];
session_unregister("swap_id");
header("Location: ?L=admin.index");
}

else {
(isset($_SESSION["id"])?session_unregister('id'):false);
(isset($_SESSION["SELF_USER_DATA"])?session_unregister('SELF_USER_DATA'):false);

header("Location: {$CONF["LOGOUT_ROUTE_TO"]}");
}
}

Answer

If I understand you correctly there is no need to reproduce PASSWORD() in php do all your validation in one go on mysql side using PASSWORD() in your select like this

SELECT `username`,`password`,`id`,`disable_until`,`active` 
  FROM `[x]users`
 WHERE `username` = 'user1' 
   AND `password` = PASSWORD('password')

Here is SQLFiddle demo

Unless you use case sensitive collation don't use LCASE() on username column in your statements. It prevents MySql from using an index (indices) if any is defined on that column and cause a full scan on the table.

On a side note: your code is vulnerable to sql-injections. Consider to use prepared statements.

Comments