SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2012
    Posts
    28
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How can I store value from database to variables using PDO statement...?

    Am still new to PHP and I will be happy if I can find solution so what am facing here...I come across this script that use bind_param, store_result and bind_result to get and store value from database to variables...

    PHP Code:
    function login($email$password$mysqli) {
       
    // Using prepared Statements means that SQL injection is not possible. 
       
    if ($stmt $mysqli->prepare("SELECT id, username, password, salt FROM members WHERE email = ? LIMIT 1")) { 
          
    $stmt->bind_param('s'$email); // Bind "$email" to parameter.
          
    $stmt->execute(); // Execute the prepared query.
          
    $stmt->store_result();
          
    $stmt->bind_result($user_id$username$db_password$salt); // get variables from result.
          
    $stmt->fetch(); 

    my own script.....

    PHP Code:
    function login($phoneNumber$password$pdo) {
       
    // Using prepared Statements means that SQL injection is not possible.
       
    if ($s $pdo->prepare("SELECT id, firstName, password, salt FROM mem WHERE phoneNumber = :phoneNumber LIMIT 1")) { 
          
    $s->bindParam(':phoneNumber'$phoneNumber);  // Bind "$phoneNumber" to parameter.
          
    $s->execute(); // Execute the prepared query.
               
        
    list($userId$firstName$dbPassword$salt) = $s->fetch( ); 
    Am using PDO object within my own script and I want to store the value from the database to variable so that I can use it in my script one after the other.....am getting error that values that comes from the database do not match with the variable....I want the value from id, firstName password, salt to be store in variable $userId, $firstName, $dbPassword, $salt....any kind of help is welcome...thanks...

  2. #2
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    Bind/store result are only available in mysqli. You will have to use fetch and $row with PDO

    Here's a good starter overview of PDO:

    http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/



    PHP Code:
    function login($phoneNumber$password$pdo) {
       
    // Using prepared Statements means that SQL injection is not possible.
       
    if ($s $pdo->prepare("SELECT id, firstName, password, salt FROM mem WHERE phoneNumber = :phoneNumber LIMIT 1")) { 
          
    $s->bindParam(':phoneNumber'$phoneNumber);  // Bind "$phoneNumber" to parameter.
          
    $s->execute(); // Execute the prepared query.

    while($row=$s->fetch()){ //for each result, do the following
         
    $userId=$row['id'];
         
    $firstName=$row['firstName'];
         
    $dbPassword=$row['password'];
         
    $salt=$row['salt'];

        
    //do something with the variables

    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2012
    Posts
    28
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Force Flow View Post
    Bind/store result are only available in mysqli. You will have to use fetch and $row with PDO

    Here's a good starter overview of PDO:

    http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/



    PHP Code:
    function login($phoneNumber$password$pdo) {
       
    // Using prepared Statements means that SQL injection is not possible.
       
    if ($s $pdo->prepare("SELECT id, firstName, password, salt FROM mem WHERE phoneNumber = :phoneNumber LIMIT 1")) { 
          
    $s->bindParam(':phoneNumber'$phoneNumber);  // Bind "$phoneNumber" to parameter.
          
    $s->execute(); // Execute the prepared query.

    while($row=$s->fetch()){ //for each result, do the following
         
    $userId=$row['id'];
         
    $firstName=$row['firstName'];
         
    $dbPassword=$row['password'];
         
    $salt=$row['salt'];

        
    //do something with the variables


    Thanks for getting back to me...I will be glad if you can help me with your little time to help me correct what am doing wrong within the script below....Am working on the script to work with PDO am using with my database but am just getting error that I have set to invalid phone or password...please help me correct the code....


    Here is the code am working on so that I can use it within my own development script..........

    Secure login function......
    PHP Code:

    function login($email$password$mysqli) {
       
    // Using prepared Statements means that SQL injection is not possible. 
       
    if ($stmt $mysqli->prepare("SELECT id, username, password, salt FROM members WHERE email = ? LIMIT 1")) { 
          
    $stmt->bind_param('s'$email); // Bind "$email" to parameter.
          
    $stmt->execute(); // Execute the prepared query.
          
    $stmt->store_result();
          
    $stmt->bind_result($user_id$username$db_password$salt); // get variables from result.
          
    $stmt->fetch();
          
    $password hash('sha512'$password.$salt); // hash the password with the unique salt.
     
          
    if($stmt->num_rows == 1) { // If the user exists
             // We check if the account is locked from too many login attempts
             
    if(checkbrute($user_id$mysqli) == true) { 
                
    // Account is locked
                // Send an email to user saying their account is locked
                
    return false;
             } else {
             if(
    $db_password == $password) { // Check if the password in the database matches the password the user submitted. 
                // Password is correct!
     
     
                   
    $user_browser $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user.
     
                   
    $user_id preg_replace("/[^0-9]+/"""$user_id); // XSS protection as we might print this value
                   
    $_SESSION['user_id'] = $user_id
                   
    $username preg_replace("/[^a-zA-Z0-9_\-]+/"""$username); // XSS protection as we might print this value
                   
    $_SESSION['username'] = $username;
                   
    $_SESSION['login_string'] = hash('sha512'$password.$user_browser);
                   
    // Login successful.
                   
    return true;    
             } else {
                
    // Password is not correct
                // We record this attempt in the database
                
    $now time();
                
    $mysqli->query("INSERT INTO login_attempts (user_id, time) VALUES ('$user_id', '$now')");
                return 
    false;
             }
          }
          } else {
             
    // No user exists. 
             
    return false;
          }
       }


    checkbrute function....

    PHP Code:
    function checkbrute($user_id$mysqli) {
       
    // Get timestamp of current time
       
    $now time();
       
    // All login attempts are counted from the past 2 hours. 
       
    $valid_attempts $now - (60 60); 
     
       if (
    $stmt $mysqli->prepare("SELECT time FROM login_attempts WHERE user_id = ? AND time > '$valid_attempts'")) { 
          
    $stmt->bind_param('i'$user_id); 
          
    // Execute the prepared query.
          
    $stmt->execute();
          
    $stmt->store_result();
          
    // If there has been more than 5 failed logins
          
    if($stmt->num_rows 5) {
             return 
    true;
          } else {
             return 
    false;
          }
       }


    Login check function..

    PHP Code:
    function login_check($mysqli) {
       
    // Check if all session variables are set
       
    if(isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
         
    $user_id $_SESSION['user_id'];
         
    $login_string $_SESSION['login_string'];
         
    $username $_SESSION['username'];
     
         
    $user_browser $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user.
     
         
    if ($stmt $mysqli->prepare("SELECT password FROM members WHERE id = ? LIMIT 1")) { 
            
    $stmt->bind_param('i'$user_id); // Bind "$user_id" to parameter.
            
    $stmt->execute(); // Execute the prepared query.
            
    $stmt->store_result();
     
            if(
    $stmt->num_rows == 1) { // If the user exists
               
    $stmt->bind_result($password); // get variables from result.
               
    $stmt->fetch();
               
    $login_check hash('sha512'$password.$user_browser);
               if(
    $login_check == $login_string) {
                  
    // Logged In!!!!
                  
    return true;
               } else {
                  
    // Not logged in
                  
    return false;
               }
            } else {
                
    // Not logged in
                
    return false;
            }
         } else {
            
    // Not logged in
            
    return false;
         }
       } else {
         
    // Not logged in
         
    return false;
       }

    Processing...
    PHP Code:

    include 'functions.php';
    sec_session_start(); // Our custom secure way of starting a php session. 
     
    if(isset($_POST['email'], $_POST['p'])) { 
       
    $email $_POST['email'];
       
    $password $_POST['p']; // The hashed password.
       
    if(login($email$password$mysqli) == true) {
          
    // Login success
          
    echo 'Success: You have been logged in!';
       } else {
          
    // Login failed
          
    header('Location: ./login.php?error=1');
       }
    } else { 
       
    // The correct POST variables were not sent to this page.
       
    echo 'Invalid Request';



    Here is my own script...I know that there is something wrong with it...please help me....
    PHP Code:
    function login($phoneNumber$password$pdo) {
       
    // Using prepared Statements means that SQL injection is not possible.
       
    if ($s $pdo->prepare("SELECT id, firstName, password, salt FROM members WHERE phoneNumber = :phoneNumber LIMIT 1")) { 
          
    $s->bindParam(':phoneNumber'$phoneNumber);  // Bind "$phoneNumber" to parameter.
          
    $s->execute(); // Execute the prepared query.
        
        
    while($row=$s->fetch()){ //for each result, do the following
         
    $userId=$row['id'];
         
    $firstName=$row['firstName'];
         
    $dbPassword=$row['password'];
         
    $salt=$row['salt'];
        
    //do something with the variables
    }

         
    /**$s->store_result();
          $s->bind_result($userId, $firstName, $dbPassword, $salt); // get variables from result.
          $s->fetch();****/
          
    $password hash('sha512'$password.$salt); // hash the password with the unique salt.
     
          
    if($s->num_rows == 1) { // If the user exists
             // We check if the account is locked from too many login attempts
             
    if(checkbrute($userId$pdo) == true) { 
                
    // Account is locked
                
    $accountLocked "Account Locked. Too many login failed attempts";
                
    // Send an email to user saying their account is locked
                
    return false;
             } else {
             if(
    $dbPassword == $password) { // Check if the password in the database matches the password the user submitted. 
                // Password is correct!
     
     
                   
    $userBrowser $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user.
     
                   
    $userId preg_replace("/[^0-9]+/"""$userId); // XSS protection as we might print this value
                   
    $_SESSION['userId'] = $userId
                   
    $firstName preg_replace("/[^a-zA-Z0-9]+/"""$firstName); // XSS protection as we might print this value
                   
    $_SESSION['firstName'] = $firstName;
                   
    $_SESSION['loginString'] = hash('sha512'$password.$userBrowser);
                   
    // Login successful.
                   
    return true;    
             } else {
                
    // Password is not correct
                // We record this attempt in the database
                
    $now time();
                
    $pdo->query("INSERT INTO loginattempts (userId, time) VALUES ('$userId', '$now')");
                return 
    false;
             }
          }
          } else {
             
    // No user exists. 
             
    return false;
          }
       }

    PHP Code:

    function checkbrute($userId$pdo) {
       
    // Get timestamp of current time
       
    $now time();
       
    // All login attempts are counted from the past 2 hours. 
       
    $validAttempts $now - (60 60); 
     
       if (
    $s $pdo->prepare("SELECT time FROM loginattempts WHERE userId = :userId AND time > '$validAttempts' ")) { 
          
    $s->bindParam(':userId'$userId); 
          
    // Execute the prepared query.
          
    $s->execute();
          while(
    $row=$s->fetch()){ //for each result, do the following
             
    $userId=$row['id'];
         }
       
          
    // If there has been more than 5 failed logins
          
    if($s->num_rows 5) {
             return 
    true;
          } else {
             return 
    false;
          }
       }


    PHP Code:
    function login_check($pdo) {
       
    // Check if all session variables are set
       
    if(isset($_SESSION['userId'], $_SESSION['firstName'], $_SESSION['loginString'])) {
         
    $userId $_SESSION['userId'];
         
    $loginString $_SESSION['loginString'];
         
    $firstName $_SESSION['firstName'];
     
         
    $userBrowser $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user.
     
         
    if ($s $pdo->prepare("SELECT password FROM members WHERE id = :id LIMIT 1")) { 
            
    $s->bindParam(':id'$userId); // Bind "$userId" to parameter.
            
    $s->execute(); // Execute the prepared query.
            
    while($row=$s->fetch()){ //for each result, do the following
             
    $password=$row['password'];
         }
            
            
    //$s->store_result();
     
            
    if($s->num_rows == 1) { // If the user exists
            
            
             /**  $s->bind_result($password); // get variables from result.
               $s->fetch(); **/
               
    $loginCheck hash('sha512'$password.$userBrowser);
               if(
    $loginCheck == $loginString) {
                  
    // Logged In!!!!
                  
    return true;
               } else {
                  
    // Not logged in
                  
    return false;
               }
            } else {
                
    // Not logged in
                
    return false;
            }
         } else {
            
    // Not logged in
            
    return false;
         }
       } else {
         
    // Not logged in
         
    return false;
       }

    PHP Code:

     
    if(isset($_POST['phoneNumber'], $_POST['password'])) { 
       
    $phoneNumber $_POST['phoneNumber'];
       
    $password $_POST['password']; // The hashed password. 
       
       
    if(login($phoneNumber$password$pdo) == true) {
          
    // Login success
          
    header('Location: ../dashboard/');
       } else {
          
    // Login failed
          
    header('Location: .?error=1');
       }
       


    Please help me out...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •