CODEWITHSUNDEEP
phpmysqldatabase
Kárpáti András
Kárpáti András

Reputation: 1239

Best Option? MySQL query actions

I've came to a point where I must decide which method to use:

I made a ChangePassword.php file, where I validate the datas and then change the password. Which is better?

or

A. version:

// Check for valid session

$qry = $db->prepare('SELECT id FROM userData WHERE id = :userId and session = :session');
$qry->bindParam(':userId', $userId, PDO::PARAM_INT);
$qry->bindParam(':session', $session, PDO::PARAM_STR);
if ($qry->rowCount() <= 0) {

  // Invalid session

  exit("Invalid Session");
}

// Valid session

// Check for correct password

$qry = $db->prepare('SELECT id FROM userData WHERE id = :userId and password = :passwordHashed');
$qry->bindParam(':userId', $userId, PDO::PARAM_INT);
$qry->bindParam(':passwordHashed', $passwordHashed, PDO::PARAM_STR);
$qry->execute();

if ($qry->rowCount() <= 0) {

  // Incorrect password

  exit("Incorrect Password");
}

// Update Password

$qry = $db->prepare('UPDATE userData SET password = :newPasswordHashed WHERE id = :userId');
$qry->bindParam(':userId', $userId, PDO::PARAM_INT);
$qry->bindParam(':passwordHashed', $passwordHashed, PDO::PARAM_STR);
$qry->bindParam(':newPasswordHashed', $newPasswordHashed, PDO::PARAM_STR);
$qry->execute();

if ($qry->rowCount() > 0) {

  // Successful edit

  exit("SUCCESS!");
} else {

  // Unsuccessful edit

  exit("Uknown Error...");
}

B. version:

// Check for valid session, correct password and update password too

$qry = $db->prepare('UPDATE userData SET password = :newPasswordHashed WHERE id = :userId AND session = :session AND password = :passwordHashed');
$qry->bindParam(':userId', $userId, PDO::PARAM_INT);
$qry->bindParam(':session', $session, PDO::PARAM_STR);
$qry->bindParam(':passwordHashed', $passwordHashed, PDO::PARAM_STR);
$qry->bindParam(':newPasswordHashed', $newPasswordHashed, PDO::PARAM_STR);
$qry->execute();

if ($qry->rowCount() > 0) {

  // Successful edit

  exit("SUCCESS!");
} else {

  // Unsuccessful edit

  exit("Maybe invalid session, or wrong password, or unknown error? Decide! :D");
}

What is your opinion?

Solution A would be more important for me because I would like to inform the users about the error, but also I am curius whether It would slow down the database due to the lot queries..

Upvotes: 1

Views: 48

Answers (4)

Eztronics
Eztronics

Reputation: 518

When you are talking about security features as is a change password module you should take the option which provides best error handling (Option A). You should study the factor that not everyone on the same time will change their password or will do it frequently (it won't happen daily, weekly or monthly).

You can have many queries in ChangePassword.php file because people won't use it frequently and at the same time. People normally change their password when the system tell them they have to (every 6 months for example).

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562310

I wouldn't worry much about the number of queries run by your ChangePassword.php page. How many times per second are users going to be submitting this form? It doesn't seem like an page that will be used 1 time in 1000 compared to a more common page like your home page.

In other words, optimize most carefully the pages that will be requested frequently. Don't be overly concerned about fine optimizations for pages that are requested rarely.

I would also say that if the performance of validating a session was important for the scalability of your app, you wouldn't store the session in a persistent database — you'd store it in a cache like Memcached or Redis.

I could even say further that if your site had so much user traffic that the "ChangePassword" page was requested many times per second and needed to be highly scalable, then you would use a different back-end language, like Java or Go, not PHP.

Upvotes: 4

Geoffrey
Geoffrey

Reputation: 11353

Solution B would be my suggestion, you do not want to inform the user as to the exact error as it provides unnecessary information that could aid them in attacking the website. It is also more efficient as the extra select will be placing additional load on the database.

Upvotes: 0

l13
l13

Reputation: 529

the solution two is better (if you can choice less query is always better)

Upvotes: 0

Related Questions