3

I am authenticating users using the mysql or mysqli password() function in a SELECT query. i.e. 

select * from users where u_name='$username' and u_pword = password('$password');

I want to convert this to a prepared statement with parameters. How do I handle the password() function in a prepared statement? Does the password() function go in the prepared statement, e.g. password(?) or the in the parameters as e.g. password($var).

I have seen a similar here problem before ( Convert from mysqli_query to mysqli prepared statement using mysql PASSWORD function ) but it has remained unanswered.

It must be a common issue, perhaps it is time to raise it again. Is anybody able to help?

Community
  • 1
  • 1
SnowyOwl
  • 55
  • 1
  • 7
  • That function is deprecated, I think you should use the PHP hashing functions. `This function is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.` http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password I'm unaware of how to use mysql functions with parameterized queries though. I figure if you did `'password( ' . $password . ')'` in the bound value it would just be quoted as a string.. Also note `PASSWORD() is used by the authentication system in MySQL Server; you should not use it in your own applications. ` – chris85 Jan 02 '16 at 16:17
  • Thanks, I was coming to that conclusion myself. It seems that my next question should be about migrating to a PHP hashing system on a live mysql database. – SnowyOwl Jan 02 '16 at 16:20
  • I'd look at http://stackoverflow.com/questions/401656/secure-hash-and-salt-for-php-passwords and http://stackoverflow.com/questions/1751152/mysql-password-function Oh, also using a placeholder in the mysql function would work in parameterized queries. e.g. `SELECT * FROM `Table` where name = md5(?)`. – chris85 Jan 02 '16 at 16:26
  • Thanks, Chris, they are useful references. – SnowyOwl Jan 02 '16 at 16:50
  • You can use `?` as a placeholder anywhere that an expression is allowed. Why do you think the argument to `PASSWORD()` is any different from other parts of a query? – Barmar Jan 02 '16 at 16:56
  • Assuming that you mean that the ? goes in the prepared statement, then the binding parameter would have to be password($password) and that doesn't work. If the prepared statement contains password(?) and the parameter is $password that doesn't work either. – SnowyOwl Jan 02 '16 at 17:09
  • Possible duplicate of [Can I blindly replace all mysql\_ functions with mysqli\_?](http://stackoverflow.com/questions/26476162/can-i-blindly-replace-all-mysql-functions-with-mysqli) – worldofjr Jan 02 '16 at 22:13

1 Answers1

0

I would need more code for this, you should have done something like

<?php
$link = new mysqli( /* connect */);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno .") "$mysqli->connect_error;
}

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT * FROM `users` WHERE u_name=? AND u_pword=password(?)"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

if (!$stmt->bind_param("ss", $username, $password )) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

?>

Hope it helps. If yes, please accept as answer. Anyways, as someone suggested, you shouldn't use PASSWORD() function and moreover, I would go on and say You should use PDO. It is worth it. As long as SQL is compatible, switching your database is very easy. Even if its not, still its comparatively easy.

Ronnie
  • 512
  • 5
  • 12
  • Thanks for trying to help. This code is similar to what I was using,with the addition of the error messages. However it doesn't throw any error messages, or in fact, it doesn't throw any results either. I added printf("%d Rows found.\n", $stmt->affected_rows); But nothing is returned. – SnowyOwl Jan 02 '16 at 16:41
  • are you sure the passwords were stored using the password() function earlier correctly? – Ronnie Jan 03 '16 at 13:52