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

    Join Date
    Mar 2010
    Posts
    1
    Rep Power
    0

    Question Updating rows in a table (MySQL)


    Hey I im trying to udpate the rows PassWord, email, Age in my membersys table of my mysql database. With the code im using its only saving the age and nothing else.

    Also the the new info is coming from a form using the POST method

    PHP Code:
    <?php
    session_start
    ();

    $con mysql_connect("localhost","MyUser","MySecretPass");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    mysql_select_db("membersys"$con);

    $ui $_POST['username'];
    $pi $_POST['password'];
    $ei $_POST['email'];
    $ag $_POST['age'];
    $user =  $_SESSION['UserName'];

    mysql_query("UPDATE Member SET PassWord=$ui WHERE UserName='$user'");
    mysql_query("UPDATE Member SET email=$ei WHERE UserName='$user'");
    mysql_query("UPDATE Member SET Age=$ag WHERE UserName='$user'");

    Header("Location: acc_content.php?account=updated");

    mysql_close($con);
    ?>
    Any Ideas?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    first of all, you seriously need to start thinking about security. This script is a playground for script kiddies, it has "Hack me!" written all over it.

    • The mysql_error() will show your database username and password on the website in case of a connection error
    • Since you just dump the user input directly into your query strings, users can freely manipulate the queries and fetch or change critical data (see SQL injection)
    • You store the passwords as plaintext, so anybody with access to the database can steal them and gain access to not only your own website but probably other websites like Facebook, Amazon and wherever the member might have reused their password. The previously mentioned vulnerabilities make it even worse, because pretty much anybody with basic SQL knowledge can access your database. So you might as well display a list of your members' login data.
    • You don't require the user to repeat the old password before changing it, so attackers only need to steal the session ID to capture the whole account.


    Whatever online "tutorial" or book you got this from, please throw it away. A much better reference is the PHP manual itself, which even has a whole chapter on security.

    You have to be aware that not everybody on the internet has good intentions, so you need to secure your website. If you don't, this can compromise your whole server (which your webhoster might not find very funny). It will also affect your members and put their personal data at risk. I know this is "just a school project", "just for testing", and you'll "add security later". But that's the wrong attitude. Write secure code from the beginning to make sure no vulnerability will every go online.

    Note that the "mysql_" functions are obsolete, so if you can, replace them with one of the contemporary database extensions. The new extensions support prepared statements (parameterized queries), which are a much safer way of passing values to queries.

    There's also something wrong with your database structure. A separate database just for your members makes no sense. All data for your website should be in a single database (in different tables). Don't use different cHaRaCtEr CaSeS in your table names. As pretty as that might be, it's very error-prone and will get you into trouble as soon as you switch to a different operating system.

    Last but not least, you have to check the session before you update the user data.

    So you'll want something like the following:

    config.php
    PHP Code:
    <?php

    /* -- SECURITY -- */

    // download the PHPass library to generate strong password hashes: http://www.openwall.com/phpass/
    require dirname(__FILE__) . '/libraries/phpass/PasswordHash.php';

    // initialize password hashing
    $password_hasher = new PasswordHash(10false);                    // 2^10 rounds, don't use weak MD5 algorithm

    // make sure to escape any string before outputting it
    function escape_html($raw) {
        return 
    htmlentities($rawENT_COMPAT'UTF-8');                // SET THIS TO THE RIGHT CHARACTER ENCODING
    }


    /* -- ERROR HANDLING -- */

    /*
    You don't want internal error messages to show up on your website, so you have to define a custom exception handler.
    As long as you're still testing, you can output the messages directly. But when you put the page online, you need
    to write the messages to a logfile and *not* display them.
    */
    function exception_handler($exception) {
        echo 
    escape_html$exception->getMessage() ), '<br />';        // REPLACE THIS BEFORE PUTTING IT ONLINE!
    }
    set_exception_handler('exception_handler');
    error_reporting(-1);                                            // SET THIS TO 0 BEFORE PUTTING IT ONLINE!


    /* -- DATABASE CONNECTION -- */

    // open database connection
    $database = new PDO'mysql:host=localhost;dbname=your_db''your_username''your_password', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION) );
    PHP Code:
    <?php

    session_start
    ();

    // was there even a submit?
    if ( $_POST ) {
        if ( isset(
    $_SESSION['member_id']) ) {
            
    // is the submit valid?
            
    $form_fields = array(
                
    'password'
                
    'old_password'
                
    'age'
            
    );
            foreach (
    $form_fields as $field)
                if ( !isset(
    $_POST[$field]) )
                    throw new 
    Exception('Missing form field: ' $field);
            
    // check password
            
    $change_password false;
            if ( 
    trim($_POST['password']) !== '' ) {
                
    // check password length
                
    $password_length strlen($_POST['password']);
                if (
    $password_length 6)                // password too short
                    
    echo escape_html('The password must be at least 6 characters long.');
                elseif (
    $password_length 72)            // limit password length so that hashing won't take too long
                    
    echo escape_html('The password must not be longer than 72 characters.');
                else {
                    
    // check old password
                    
    $password_stmt $database->prepare('
                        SELECT
                            password_hash
                        FROM
                            members
                        WHERE
                            member_id = :m_id        
                    '
    );
                    
    $password_stmt->execute( array(
                        
    ':m_id' => $_SESSION['member_id']
                    ) );
                    
    $old_password $password_stmt->fetchColumn();
                    if ( 
    $password_hasher->CheckPassword($_POST['old_password'], $old_password) )
                        
    $change_password true;
                    else
                        echo 
    escape_html('The old password does not match.');
                }
            }
            
    // create prepared statement for the update
            
    $update_stmt $database->prepare('
                UPDATE
                    members
                SET
                    password_hash = ' 
    . ($change_password ':pw_hash' 'password_hash') . '
                    , age = :age                                                                        -- this should be validated beforehands
                WHERE
                    member_id = :m_id
            '
    );
            
    // pass data to prepared statement and execute it
            
    $update_data = array(
                
    ':m_id' => $_SESSION['member_id']
                , 
    ':age' => $_POST['age']
            );
            if (
    $change_password)
                
    $update_data[':pw_hash'] = $password_hasher->HashPassword($_POST['password']);
            
    $update_stmt->execute($update_data);
            echo 
    escape_html('Update successful!');
        } else
            echo 
    escape_html('You are not logged in.');
    }
    I know this is a lot of code, and you probably won't grasp everything immediately. But that's because many books and tutorials do such an incredibly poor job in teaching secure PHP. There's a big different between "simple" code, which will only work until the next script kiddie comes around. And secure code, which will actually withstand attacks (well, at least the common ones).

    I've left out the email change, because this requires additional measures. You have to check the password and send out a confirmation link. If you don't, an attacker can capture a member's account just by stealing the session ID. Because all I need to do is change the email address to my own address, claim to have forgotten the password and then set my own password.

IMN logo majestic logo threadwatch logo seochat tools logo