Thread: Mysql to PDO

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    2

    Mysql to PDO


    I have the following two versions. I am converting my (depreciated) mysql query to PDO. The second one doesn't show the email, or send it. I think the PDO error has to do with "if(execute($link)==1)".

    OLD CODE:
    PHP Code:
    // If there are no errors
            
    $_POST['password'] = mysql_real_escape_string($_POST['password']);
            
    $pass $_POST['password'];
            
    // Generate a random password
            
            
    $_POST['email'] = mysql_real_escape_string($_POST['email']);

            
    $_POST['username'] = mysql_real_escape_string($_POST['username']);
            
    // Escape the input data
            
            
            
    mysql_query("    INSERT INTO tz_members(usr,pass,email,regIP,dt)
                            VALUES(
                            
                                '"
    .$_POST['username']."',
                                '"
    .md5($_POST['password'])."',
                                '"
    .$_POST['email']."',
                                '"
    .$_SERVER['REMOTE_ADDR']."',
                                NOW()
                                
                            )"
    );
            
            if(
    mysql_affected_rows($link)==1)
            {
                
    send_mail(    'no-reply@mcss-portal.com',
                            
    $_POST['email'],
                            
    'MCSS-Portal - Your Password!',
                            
    'Your username is: '.$_POST['username'],
                            
    'Your password is: '.$pass);

                
    $_SESSION['msg']['reg-success']='We sent you an email with your password (for reference)!';
            }
            else 
    $err[]='This username is already taken!'


    NEW CODE (That won't work):
    PHP Code:
    // If there are no errors
                    
    $pass $_POST['password'];
            
    $_POST['password'] = mysql_real_escape_string($_POST['password']);
            
            
    // Generate a random password
            
            
    $_POST['email'] = mysql_real_escape_string($_POST['email']);

            
    $_POST['username'] = mysql_real_escape_string($_POST['username']);
            
    // Escape the input data
                    
                    
                    
    try {
                        
                        
    $userMYSQL '*******';
                    
    $passMYSQL '*******';
                    
                        
    $dbh = new PDO('mysql:host=localhost;dbname=mcss'$userMYSQL$passMYSQL);
                        foreach(
    $dbh->query("INSERT INTO tz_members(usr,pass,email,regIP,dt) VALUES('".$_POST['username']."', '".md5($_POST['password'])."', '".$_POST['email']."', '".$_SERVER['REMOTE_ADDR']."', NOW())") as $row) {
                            
                            if(
    execute($dbh)==1) {
                                    
    send_mail(              'no-reply@mcss-portal.com',
                                                            
    $_POST['email'],
                                                            
    'MCSS-Portal - Your Password!',
                                                            
    'Your username is: '.$_POST['username'],
                                                            
    'Your password is: '.$pass);

                                    
    $_SESSION['msg']['reg-success']='We sent you an email with your password (for reference)!';
                            }
                            else 
    $err[]='This username is already taken!';
                        
                        }
                        
    $dbh null;
                    } catch (
    PDOException $e) {
                        print 
    "Error!: " $e->getMessage() . "<br/>";
                        die();
                    } 
    Thanks.
    Last edited by tjswebdev; March 28th, 2013 at 06:45 PM.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    That foreach you added is the problem. Why is it there? You aren't trying to get anything from the database - only add to it.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    2
    Originally Posted by requinix
    That foreach you added is the problem. Why is it there? You aren't trying to get anything from the database - only add to it.
    Oh. Good point. What should I change it to?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    this all doesn't really make sense.

    The "new" database extensions no longer use this pattern of escaping variables by hand and injecting the result into the query string. They use prepared statements instead.

    Forget mysql_real_escape_string(). That's the old MySQL extension. It's just as deprecated as all the other functions. Should you ever need to escape a variable by hand (which you shouldn't), you have to call PDO->quote()

    Also note that plain MD5 hashes today are nothing more than a bit of obfuscation. A Radeon HD 7970 can calculate about 8 billion MD5 hashes per second(!), so "cracking" your whole database shouldn't take more than a few minutes (if your users have very good passwords with special characters, it will take a bit longer).

    And this PDOException stuff makes no sense at all.

    Check the links in my signature.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    2
    Originally Posted by Jacques1
    Hi,

    this all doesn't really make sense.

    The "new" database extensions no longer use this pattern of escaping variables by hand and injecting the result into the query string. They use prepared statements instead.

    Forget mysql_real_escape_string(). That's the old MySQL extension. It's just as deprecated as all the other functions. Should you ever need to escape a variable by hand (which you shouldn't), you have to call PDO->quote()

    Also note that plain MD5 hashes today are nothing more than a bit of obfuscation. A Radeon HD 7970 can calculate about 8 billion MD5 hashes per second(!), so "cracking" your whole database shouldn't take more than a few minutes (if your users have very good passwords with special characters, it will take a bit longer).

    And this PDOException stuff makes no sense at all.

    Check the links in my signature.
    Please only worry about the problem at hand. I know md5 is stupid. That's my next job. I just don't know what to do for the whole for each thing.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    I gave you a link about prepared statements. It might be helpful to actually read that.

    There's also a great wiki for people you have just switched to PDO.

    By the way, didn't you recently complain about us telling people to use PDO?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    85
    Rep Power
    2
    I'm really confused with this. Could you kindly help me write this?
    Last edited by tjswebdev; March 28th, 2013 at 08:28 PM.

IMN logo majestic logo threadwatch logo seochat tools logo