Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Can you help me convert this function from MySQLi to PDO?

Codes here !

Moderators: macek, egami, gesf

Can you help me convert this function from MySQLi to PDO?

Postby jacobpressures » Sun Apr 21, 2013 7:28 pm

Here is the code. I can't figure out what to convert it into a PDO prepared parameterized query.

The best i can do is
PDO Attempt
Code: Select all
function login2($user, $password, $pdo) {
    // Using prepared Statements means that SQL injection is not possible.
   $params = array(':user' => '$user', ':userID' => '$user',':password' => '$password');
    if ($stmt = $pdo-> prepare("SELECT username, id, email, password, salt FROM members WHERE username = :user")) {
        $pdo-> execute($params);
   $count = $stmt->rowCount();
        $password = hash('sha512', $password.$salt); // hash the password with the unique salt.

        if ($count == 1) { // If the user exists


MYSQLI original
Code: Select all
function login($user, $password, $mysqli) {
    // Using prepared Statements means that SQL injection is not possible.
    if ($stmt = $mysqli-> prepare("SELECT username, id, email, password, salt FROM members WHERE username = ? LIMIT 1")) {
        $stmt-> bind_param('s', $user); // Bind "$user" to parameter.
        $stmt-> execute(); // Execute the prepared query.
        $stmt-> store_result();
        $stmt-> bind_result($username, $user_id, $email, $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) {         
                           // Alternatively, 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!

                    $ip_address = $_SERVER['REMOTE_ADDR']; // Get the IP address of the user.
                    $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['email'] = $email;   
                              $_SESSION['username'] = $username;            
                    $_SESSION['login_string'] = hash('sha512', $password.$ip_address.$user_browser);
                    // Login successful.
                    return true;
                } else {
                    // Password is not correct
                    // We record this attempt in the database
                    $now = time();
                              $ip = $_SERVER['REMOTE_ADDR'];
                    $mysqli-> query("INSERT INTO login_attempts (user_id, email, time, ip) VALUES ('$user_id','$email', '$now', '$ip')");
                    return false;
                }
            }
        } else {
            // No user exists.
            return false;
        }
    }
}
jacobpressures
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sun Apr 21, 2013 7:21 pm

Re: Can you help me convert this function from MySQLi to PDO

Postby seandisanti » Sun Apr 21, 2013 11:09 pm

For a single run query, preparing the statement offers no benefit. Untested, but this should work...
Code: Select all
<?php

include 
'Database.php';

function login($user, $password, $mysqli) {
    $db = Database::getInstance();
    $sql = "SELECT username, id, email, password, salt FROM members WHERE username = " . $db - quote($user) . " LIMIT 1";
    $result = $db->query($sql);
    if (!$result || $result->rowCount() == 0) {
        return false;
        ;
    }
    $row = $result->fetch(PDO::FETCH_ASSOC);
    $password = hash('sha512', $password . $salt); // hash the password with the unique salt.
    if ($row['password'] == $password) { // Check if the password in the database matches the password the user submitted.
        // Password is correct!
        $ip_address = $_SERVER['REMOTE_ADDR']; // Get the IP address of the user.
        $user_browser = $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user.
        $user_id = preg_replace("/[^0-9]+/", "", $row['user_id']); // XSS protection as we might print this value
        $_SESSION['user_id'] = $row['user_id'];
        $username = preg_replace("/[^a-zA-Z0-9_\-]+/", "", $row['username']); // XSS protection as we might print this value
        $_SESSION['email'] = $row['email'];
        $_SESSION['username'] = $row['username'];
        $_SESSION['login_string'] = hash('sha512', $password . $ip_address . $user_browser);
        // Login successful.
        return true;
    } else {
        // Password is not correct
        // We record this attempt in the database
        $now = time();
        $ip = $_SERVER['REMOTE_ADDR'];
        $mysqli->query("INSERT INTO login_attempts (user_id, email, time, ip) VALUES ('$user_id','$email', '$now', '$ip')");
        return false;
    }
}


and here's the Database.php that I include to use... ***edit*** just supply your db info in place of the constants I use.
Code: Select all
<?php

/*
 * Database class only one connection is allowed.
 */

class Database extends PDO{

    private $connection;
    private static $instance;

    public static function getInstance() {
        if (!self::$instance) {
            
            self
::$instance = new self("mysql:host=" . DBHOST . ";dbname=" . DB,DBUSER,DBPASS);
        }
        return self::$instance;
    }



    /*
     * empty clone magic method to prevent duplication
     *
     */

    private function __clone() {

    }

}
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Can you help me convert this function from MySQLi to PDO

Postby jacobpressures » Mon Apr 22, 2013 8:04 am

Thanks. You've given me some ideas, but I'm trying to do this as a prepared statement. If I can get this to work on smaller problems, I can do it on more complicated stuff.

Im not sure I can use " $row = $result->fetch(PDO::FETCH_ASSOC); " with " $stmt-> execute($params);
" Everything I've seen uses "$stmt-> query()" including your code. But you don't appear to be using a prepared statement which is what I'm trying to do.

This is the lastest code statement. I don't know how to debug this stuff.
Should I have quotes around the $variable names in the following line or not? I'm not sure. " $params = array(':user' => '$user', ':userID' => '$user',':password' => '$password'); "


Code: Select all
function login2($user, $password, $pdo) {
    // Using prepared Statements means that SQL injection is not possible.
    $params = array(':user' => '$user', ':userID' => '$user',':password' => '$password');
    if ($stmt = $pdo-> prepare("SELECT username, id, email, password, salt FROM members WHERE username = :user")) {
        $stmt-> execute($params);
        $count = $stmt->rowCount();
        $row = $result->fetch(PDO::FETCH_ASSOC);
        $username = $row['username'];
        $user_id = $row['id'];
        $email = $row['email'];
        $db_password = $row['password'];
        $salt = $row['salt'];
       
        $password = hash('sha512', $password.$salt); // hash the password with the unique salt.
       
       
        if ($count == 1) { // If the user exists
            // We check if the account is locked from too many login attempts

            if (checkbrute($user_id, $pdo) == true) {           
                                    // Alternatively, 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!

                    $ip_address = $_SERVER['REMOTE_ADDR']; // Get the IP address of the user.
                    $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['email'] = $email;   
                    $_SESSION['username'] = $username;               
                    $_SESSION['login_string'] = hash('sha512', $password.$ip_address.$user_browser);
                    // Login successful.
                    return true;
                } else {
                    // Password is not correct
                    // We record this attempt in the database
                    $now = time();
                    $ip = $_SERVER['REMOTE_ADDR'];
             
                    $stmt = "INSERT INTO login_attempts (user_id, email, time, ip) VALUES (':user_id',':email', ':now', ':ip')";
                    // from sample may need to remove single quotes $sql = "INSERT INTO books (title,author) VALUES (:title,:author)";
                    $q = $pdo->prepare($stmt);
                    $q->execute(array(':user_id'=>$user_id,':email'=>$email,':now'=>$now,':ip'=>$ip));
                    return false;
                }
            }
        } else {
            // No user exists.
            return false;
        }
    }
}
jacobpressures
New php-forum User
New php-forum User
 
Posts: 2
Joined: Sun Apr 21, 2013 7:21 pm

Re: Can you help me convert this function from MySQLi to PDO

Postby seandisanti » Mon Apr 22, 2013 10:07 am

For a single use query like a login, a prepared statement is not the way to go, it offers no advantage but suffers from greater overhead and slower performance. For usage on PDO::prepare() check out http://php.net/manual/en/pdo.prepare.php There are examples and documentation there.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron