How to create a log in form using SQL server 2008 R2 and PHP

Hey guys, I have been trying to create a form where a user can enter a user name and a password and make a connection to the database. I have been messing around with examples all day long and I haven’t been successful, most of the examples use MySQL and not the SQLSRV driver syntax I need. There also seems to be many different ways to do this, I want the most secure of course, but also simplest, I am not understanding all the code in most of the examples.

I created a form, and I created a database and table that contains a userID, username and password. I need to once I get this done create a registration form, but that will be more difficult I feel. I suppose I don’t really understand how a registered user makes a connection to the database, I am so used to just putting the connection stuff in the top of all my PHP files.

Any help or advice on this would be greatly appreciated!

Many thanks!

Maybe I’m missing something…but a user doesn’t connect to the database…your application does. SQL Server works the same way as MySQL does. You connect to it via the DB specific “connect” function, then pass the needed SQL query.

Microsoft happens to offer an SQL Server driver for PHP:

Hey there, I already have a connection to my database, and a way of filling in a registration form which stores the data in my users table. I was trying to figure out how to make the account, so that the person can login using their username and password. I hope this makes my question clearer.

Thanks!

You will need a log-in form, with captcha or recaptcha.
You will then verify that the entries are correct.
If the entries are correct, you can set a session for that username.
Verify that the username is valid before allowing them to access anything you want to keep from the general public.

I actually have used log-in code I modified from New Think Tank blog entries, but you should be able to find example code by searching the web.

Is this the type of information you are wanting?

Hi, your information is great it provides me with a better idea, but I have looked at so many examples, and now confused, so I was hoping for a couple of code examples, I was looking at sessions in php and I tried to use it but I was unsure how. All I have at this point is a table that has information on users details, I don’t have an account page or that created yet, I am still working on the login thing. I understand where you are coming from, I need to check that the details entered match those in the table.

Thanks!!

@deucalion0

Hi here is a rough example of how something might work in the way you are interested. The following PDOAUTH class uses a simple Dependency Container to supply some of the dependencies, but you should be able to follow it as it is rather procedural in nature.

Steps:[LIST=1]
[]Create a form that POSTS the users to the page to access
[
]Have PDOAuth instantiated on posted to page and validate the user and their password. Also set a session with a login hash that can revalidate their authentication on subsequent pages without querying the database again.
[/LIST]Here is the PDOAuth class:


class PDOAuth { 
  protected $o_DbFactory;  
  protected $o_Session;  
  protected $o_SaltHash;
  protected $o_Tracker;
  protected $hash; 
  protected $o_Redirect;
  protected $dont_redirect_just_return_redirect;
  protected $salt = NULL;
  protected $username;
  protected $password;
  protected $stmt;
  public function __construct(DbFactory $db_factory, SaltHash $hash, Session $session, Redirect $redirect) {
      $this->o_DbFactory = $db_factory;
      $this->o_SaltHash = $hash;
      $this->o_Session = $session;
      $this->o_Redirect = $redirect; 
     $this->username = htmlentities($_POST['username']);
      return $this;
  }
  /*   * Generic property variables get and set   */
    public function __get($property){
        return $this->$property; 
   }
    public function __set($property, $value){
        $this->$property = $value; 
       return $this->$property;
    }
 /**
    * Logs the user out
    * @param boolean Parameter to pass on to Auth::redirect() (optional)
    * @return void
    * @access public
    */ 
   public function logout ($redirect_to = 'log_in.php') {
          // Unset all of the session variables.
    $_SESSION = array(); 
   /*
       Finally, destroy the session.      disabled these so that auto login can occur
    */
   $this->session->destroy();
    unset($this->session);
    $this->o_Redirect->setRedirect($redirect_to);
    return $this->o_Redirect->go(); 
}
  public function login(TrackLogins $o_TrackLogins){
      if ($this->o_Session->get('logoff') == 1){
        $this->o_Tracker->delSessions();
          return 0;
     }
      if ( empty($_POST["username"]) || empty($_POST["password"]) ) { 
         return 0;
     }
     // See if we have values already stored in the session
     if ( $this->o_Session->get('login_hash') ) {
         return $this->confirmAuth(); // Check if user is authenticated if so returns true
    }
    $this->password = htmlentities($_POST["password"]);
    $this->password = $this->o_SaltHash->getHashLinkedToUser($this->username,$this->password);
      $sql = 'SELECT 
                    COUNT(*) AS num_users
                 FROM 
                    users 
                 WHERE
                    uid = :username
                 AND password =:password;';
      $this->stmt = $this->o_DbFactory->db->prepare($sql);
      $this->stmt->bindValue(':username', $this->username);
      $this->stmt->bindValue(':password', $this->password);
     try {
              if ($this->stmt->execute()) {
                $row = $this->stmt->fetchAll();
             }         
             // There should be only 1 row;
             if ($row[0]['num_users'] != 1) {
               return 0; 
             } else {
               $this->storeAuth();
               return 1; // User is authenticated
             }
          } catch (Exception $e) {
              echo 'Caught exception: ', $e->getMessage(), "\
";
          }   
}
 /*
   * Sets the session variables after a successful login
   * @return true if valid
   * @access protected 
  */ 
   protected function storeAuth(){
        $this->o_Session->set('uid', $this->username); 
        if(!$this->o_Session->get('uid')){ 
            return 0; 
       }
        $this->o_Session->set('password', $this->password);
        if(!$this->o_Session->get('password')){
            return 0;
        } else {
            $this->hash = $this->o_SaltHash->verifyHash($this->username, $this->password);
            $this->o_Session->set('login_hash', $this->hash);
        }
       return 1;  
  }
 /*
  * Confirms that an existing login is still valid
  * @return void
  * @access private
  */  
  protected function confirmAuth() {
      $username=$this->o_Session->get('uid'); 
     $username=htmlentities($username);
      $password=$this->o_Session->get('password'); 
     $password=htmlentities($password);
      $hashKey=$this->o_Session->get('login_hash'); 
     $hashKey=htmlentities($hashKey); 
      if ($this->o_SaltHash->verifyHash($username, $password ) != $hashKey ){
           $this->logout();
          return 0; 
     } 
     return 1;
  }
}

Then and example page - say a router.php or index.php that needs to implement this Authentication:


require_once("bucket.inc.php");//include dependency injection container
require_once('TrackLogins.php');//include track login class
require_once('Redirect.php');//include redirect class
header( 'Expires: Sat, 26 Jul 1997 05:00:00 GMT' );
header( 'Last-Modified: ' . gmdate( 'D, d M Y H:i:s' ) . ' GMT' );
header( 'Cache-Control: no-store, no-cache, must-revalidate' );
header( 'Cache-Control: post-check=0, pre-check=0', false );
header( 'Pragma: no-cache' );
function __autoload($class_name) {
    require_once $class_name . '.php';
}
$o_SaltHash = new SaltHash();
// Wiring...$container = new bucket_Container(new DbFactory);
// Application code...
$container->registerImplementation('Auth', 'PDOAuth');
$auth = $container->get('Auth');
/*
  * Try to reduce Session Fixation 
 * Delete old session set to True 
 */
session_regenerate_id(true);
// If user has selected Log Out
if ( isset ( $_GET['action'] ) && $_GET['action'] == 'logout' ) {
    $auth->logout();
}
if ($_POST['token'] == $_SESSION['token']){
  /* Valid Token */
  //Get the TrackLogins class
  $o_TrackLogins = new TrackLogins($auth->o_Session);
   if($auth->login($o_TrackLogins) == 1){
      //Get the Permission class
      $container->registerImplementation('User', 'PDOUser'); 
     $perm = $container->get('User');
     /*
      * Check if user has right to access this page
      */
      if ($perm->checkPermission('page_to_go_to.php')){
        $o_Redirect = new Redirect();
        $redirect = './../../../../page_to_go_to.php';
        $o_Redirect->setRedirect($redirect);
        $o_Redirect->go();
     } else {
        $o_Redirect = new Redirect();
        $o_Redirect->setRedirect($o_Redirect->getRedirector());
        $o_Redirect->go();
     }
  } else {
      $o_TrackLogins->advanceBalance();
      $user_message = null;
      $user_message = $o_TrackLogins->loginTracker();
      if($user_message != 'redirect'){
        $auth->o_Session->set('user_message', $user_message); 
      }
      $s_token = $auth->o_Session->get('token');
      $o_Redirect = new Redirect();
      $o_Redirect->setSession($auth->o_Session);
      $o_Redirect->setSessionRedirect();
      $o_Redirect->checkTime(); 
      $o_Redirect->go();
  } 
  //Fresh login so set starting limits
   if( $auth->o_Session->get('loginX') === false){
       $o_TrackLogins->delSessions();
       $o_TrackLogins->killProperties();
       $o_TrackLogins->setLoginLimits(5,5,150);
   }
} else {  
/**CSRF tried so redirecting**/
  $o_Redirect = new Redirect();
 $o_Redirect->setSession($auth->o_Session);
 $redirect = $o_Redirect->getRedirector(); 
 $o_Redirect->setRedirect($redirect);
 $o_Redirect->go();
}

Hope this helps put you on a path of how authentication can be handled. I am sorry that the examples are so crude but I whipped it up for you so speed was of the essence.

Regards,
Steve