Database look up problems

I’m not sure why this isn’t working. I’ve tried to add ‘check if user is in database’ function to Google login. The login worked before I add the db check.

I’ve gone through the error logs and no errors are being thrown but when I try to go in I just get returned to the login page. If somebody isn’t authorised to log in then they will be returned to the login page but should see the site if they are permitted.

I’ve got this:


$app->match('/auth/callback', function (Request $request) use ($app, $googleService) {
    // This was a callback request from google, get the token
    $googleService->requestAccessToken($request->get('code'));

    // Send a request with it
    $user = json_decode($googleService->request('https://www.googleapis.com/oauth2/v1/userinfo'), true);

    if (substr($user['email'],-11)==='@domain.com') {function findPkSimple($key, $con)
    {
		$login_email = $user['email'];
		function findPkSimple($key, $con)
    {
		$sql = 'SELECT COUNT(*) AS count FROM users WHERE email = $login_email';
        try {
            $stmt = $con->prepare($sql);
            $stmt->bindValue($login_email, $key, PDO::PARAM_INT);
            $stmt->execute();
    $result = $stmt->fetchAll();
 
    if ($result[0]['count'] > 0) {
      // User Exist
 
     $user['logged_in_date'] = date('Y-m-d');
      $app['session']->set('userinfo', $user);

      $log = new \\employee\\UserLoggedinLog();
      $log->setEmail($user['email']);
      $log->setDate(time());
      $log->save();

    } else {
      $message = \\Swift_Message::newInstance()
      ->setSubject('Alert :: unauthorized login')
      ->setFrom(array('alert@domain.com' => 'Mycroft robot'))
      ->setTo(array('adim@domain.com'))
      ->setBody(sprintf("The following email [%s] tried to login from IP [%s]", $user['email'], $request->getClientIp()));
      $app['mailer']->send($message);
      }
    
  } catch (Exception $ex) {
            Propel::log($e->getMessage(), Propel::LOG_ERR);
            throw new PropelException(sprintf('Unable to execute SELECT statement [%s]', $sql), 0, $e);
}
	}
    } else {
      $message = \\Swift_Message::newInstance()
      ->setSubject('Alert :: unauthorized login')
      ->setFrom(array('alert@domain.com' => 'Mycroft robot'))
      ->setTo(array('adim@domain.com'))
      ->setBody(sprintf("The following email [%s] tried to login from IP [%s]", $user['email'], $request->getClientIp()));
      $app['mailer']->send($message);
    }

    return $app->redirect('/');
});

Your query is currently the following:


$sql = 'SELECT COUNT(*) AS count FROM users WHERE email = $login_email';
try {
  $stmt = $con->prepare($sql);
  $stmt->bindValue($login_email, $key, PDO::PARAM_INT);
  $stmt->execute();
  // rest of php code
}

There’s a few of problems with this. The first is that the placeholder name is in the form of a variable, $login_email, when it should be in the form of :[a-z_]{1}[a-zA-Z0-9] (in regex notation). Named placeholders in PDO must begin with a colon, and then any valid variable name definition. The second problem is with your binding where the first parameter should be the named placeholder. Also, your email field is presumably expecting a string value, right? If so, then why are you binding the email as an integer?

Perhaps try the following:


$sql = 'SELECT COUNT(*) AS count FROM users WHERE email = :login_email';
try {
  $stmt = $con->prepare($sql);
  $stmt->bindParam('login_email', $login_email, PDO::PARAM_STR);
  $stmt->execute();
  // rest of php code
}

Other than that, I don’t think it is a good idea to define a function within such a nested and conditional scope (namely the findPkSimple function). If you want to keep variables out of the scope of your surrounding code, then defining a closure would be a better idea.

I tried that but it gave an error: PHP Parse error: syntax error, unexpected ‘$login_email’ (T_VARIABLE) in /home/ubuntu/web/auth.php on line 47

I had to add the findPkSimple function because because it was giving me an error before saying PHP Fatal error: Call to a member function prepare() on a non-object

I didn’t realise you defined the findPkSimple function twice, and that you aren’t defining a $con variable.

Your first error can be fixed by deleting the two function declaration statements, since the $login_email variable isn’t within the scope of the second findPkSimple function.

In order to fix the second error, you must instantiate the PDO class and assign the created PDO object to the $con variable. If you choose to do this outside of the $app->match() invocation, then you will need to pass the $con variable into the use clause of the closure you’ve defined as the second argument to the $app->match() method.

I’m really sorry to ask such a dumb question but how do I do that, instantiate the PDO class and assign it to the $con variable?

Just after you’ve defined your $user variable, place the following lines:


$host = '';
$dbname = '';
$username = '';
$password = '';
$con = new PDO("mysql:host={$host};dbname={$dbname}", $username, $password); // assumes you're using the MySQL database

and update the values for the host, dbname, username, and password variables to match the details of your host.

It may also be a good idea to familiarise yourself with the PDO API: Introduction to PHP PDO

it now seems to be working but doesn’t return true after checking the database for an entry that I know is there. I’m guessing the problem is with the :login_email. I’m not sure what that should be I was following this tutorial (http://www.thesoftwareguy.in/login-system-with-google-using-oauth-php-mysql/) so am not sure what that part should be.

The code I’ve now got is:

$app->match('/auth/callback', function (Request $request) use ($app, $googleService) {
    // This was a callback request from google, get the token
    $googleService->requestAccessToken($request->get('code'));

    // Send a request with it
    $user = json_decode($googleService->request('https://www.googleapis.com/oauth2/v1/userinfo'), true);

    if (substr($user['email'],-11)==='@domain.com') {
		$host = 'localhost'; 
$dbname = 'xxxxxxx'; 
$username = 'root'; 
$password = 'xxxxxxxxxxxx'; 
$con = new PDO("mysql:host={$host};dbname={$dbname}", $username, $password); // assumes you're using the MySQL database 

		$sql = 'SELECT COUNT(*) AS count FROM users WHERE email = :user';
        try {
            $stmt = $con->prepare($sql);
    $stmt->bindValue(":user", $user->email);
            $stmt->execute();
    $result = $stmt->fetchAll();
 
    if ($result[0]['count'] > 0) {
      // User Exist
 
     $user['logged_in_date'] = date('Y-m-d');
      $app['session']->set('userinfo', $user);

      $log = new \\employee\\UserLoggedinLog();
      $log->setEmail($user['email']);
      $log->setDate(time());
      $log->save();

    } else {
      $message = \\Swift_Message::newInstance()
      ->setSubject('Alert :: unauthorized login')
      ->setFrom(array('alert@domain.com' => 'robot'))
      ->setTo(array('admin@domain.com'))
      ->setBody(sprintf("The following ex-employee [%s] tried to login from IP [%s]", $user['email'], $request->getClientIp()));
      $app['mailer']->send($message);
      }
    
  } catch (Exception $ex) {
            Propel::log($e->getMessage(), Propel::LOG_ERR);
            throw new PropelException(sprintf('Unable to execute SELECT statement [%s]', $sql), 0, $e);
}
	
    } else {
      $message = \\Swift_Message::newInstance()
      ->setSubject('Alert :: unauthorized login')
      ->setFrom(array('alert@domain.com' => 'robot'))
      ->setTo(array('admin@domain.com'))
      ->setBody(sprintf("The following email [%s] tried to login from IP [%s]", $user['email'], $request->getClientIp()));
      $app['mailer']->send($message);
    }

    return $app->redirect('/');
});

before that I have this:

$googleService = $serviceFactory->createService('google', $credentials, $storage, array('userinfo_email', 'userinfo_profile'));

$app->get('/login', function () use ($app, $googleService) {
  if($app['session']->get('userinfo')) {
    return new RedirectResponse('/');
  }
  return $app['twig']->render('login.twig', array(
    'login_url' => $googleService->getAuthorizationUri()->__toString(),
    'user'      => $app['session']->get('userinfo'),
  ));
});

I’ve just looked at the error log and it says that there’s an error on line 50 PHP Notice: Trying to get property of non-object in /home/ubuntu/web/auth.php on line 50

Line 50 is: $stmt->bindValue(“:login_email”, $user->email);

so I’m sure that the problem is with the :login_email part but I’m not sure what it should be?

The :email_login is not the problem. As the error states, you’re attempting to access a property (being email) on a non-object (being user). We know the $user variable is an array (because of its usage in the substr function), and so you’re going to want to replace $user->email with $user[‘email’] on line 50.

Thank you so much that works perfectly now :slight_smile: