Coverting PHP login script to use prepared statements

Question

I'm trying to learn to convert a PHP login page to use prepared SQL statements with parameters versus just using standard script to protect from SQL injection. Its for a security class and not a programming class and my PHP is weak and be will evident. I can't figure out why I'm not getting any results from the execution. Its using PDO, and I've switch from bindParam to bindValue as suggested on other topics, but I still get a black page from the login.

My db connection is working, and I think my SQL statement and parameters are correct. I really believe the problem is in retrieving the results. Can anyone help why I can get a row count? I've also tried with $stmt->count_rows

<html>
<body>

<?php

$db_hostname  = 'localhost';
$db_username  = 'testuser';
$db_password  = '1234';
$db_dbname    = 'testdb';
$db_tablename = 'users';
$db_conn_str  = "mysql:host=" . $db_hostname . ";dbname=" . $db_dbname;

try {

    $db   = new PDO($db_conn_str, $db_username, $db_password);
    $stmt = $db->prepare("Select * from users where login = ? and passwd = ?");
    $stmt->bindValue(1, $_POST['username']);
    $stmt->bindValue(2, $_POST['password']);
    $stmt->execute();
    $stmt->store_result();
    $result = $stmt->fetchAll();

    $num = $result->rowCount();

    $stmt->close();
}
catch (PDOException $e) {
    echo "Error in PDO: " . $e->getMessage();
}


if ($num == 0) {
    echo "login failed! <br />";
} else {
    $name = $result->fetchColumn(0);
    echo "Welcome, $name!<br />";
}
?>

You should not store password as plain password in database. That's a huge no no. — Anthony, Mar 19 '18 at 17:46
`store_result` is mysqli, not PDO. `rowCount` is a part of the PDO statement, not result. If you'd check your error logs, you should see some errors relating to this. — aynber, Mar 19 '18 at 17:46
You're also missing a semi-colon at the end of your hostname declaration. — aynber, Mar 19 '18 at 17:47
Being a simple test of rewriting the PHP for prepared statements, I think they didn't want to make it more complex on hashing password values in the table. The DB, initial html, and login.php were given. — nshade404, Mar 19 '18 at 17:58
Possible duplicate of [Can I mix MySQL APIs in PHP?](https://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — miken32, Mar 19 '18 at 18:05
It is important to use password_hash and password_verify functions. It looks like you are using plain text passwords here. Also, on any site that accepts passwords make sure that you are using https and not http. — kojow7, Mar 19 '18 at 18:46
Learn how to check error logs. They will save you a lot of time in the long run. Also, learn how to enable MySQL error checking in PHP. — kojow7, Mar 19 '18 at 18:47
Thanks for all the tips everyone. Overall I think mixing up PDO and mysqli was the biggest issue minus the syntax erros — nshade404, Mar 19 '18 at 19:27

ICanNowComment · Accepted Answer · 2018-03-19T18:03:38.103

A few issues.

Syntax error after $db_hostname='localhost'
PDO does not have a store_result() method, that's a mysqli method.
Turn off emulated prepared querys. ATTR_EMULATE_PREPARES
You need to also tell PDO to throw Exceptions else it wont ERRMODE_EXCEPTION
Also $result->fetchColumn(0); will fetch the first column so presuming that id your welcome message would say Welcome, 1.
Just count fetchAll() instead of rowCount().
Don't forget htmlentities() to protect against stored XSS

Below is changed code:

<?php
$db_hostname='localhost';
$db_username='testuser';
$db_password='1234';
$db_dbname='testdb';
$db_tablename='users';
$db_conn_str="mysql:host=" . $db_hostname . ";dbname=" . $db_dbname;

try {
  $db = new PDO($db_conn_str, $db_username, $db_password);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

  $stmt = $db->prepare("SELECT * FROM users WHERE login = ? AND passwd = ?");

  $stmt->bindValue(1, $_POST['username']);
  $stmt->bindValue(2, $_POST['password']);
  $stmt->execute();

  $result = $stmt->fetchAll();

  if (count($result) == 0) {
    echo "login failed! <br />";
  } else {
    echo "Welcome, ".htmlentities($result[0]['name'])."!<br />";
  }

  $stmt->close();
} catch (PDOException $e) {
  echo "Error in PDO: " . $e->getMessage();
}
?>

You should also look into using password_* based functions instead of storing your passwords as plaintext.

Hope it helps.

Thanks. I had to look up the PDO options and just overlooked counting $results. — nshade404, Mar 19 '18 at 19:21

Coverting PHP login script to use prepared statements

1 Answers1