#16
  1. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by derplumo
    it keeps giving me the error:
    How can you get an error for a piece of code you've deleted? Try pasting the statement into the MySQL console on the command line (instead of using phpmyadmin, which you probably do).



    Originally Posted by derplumo
    Well I think I don't
    You can use array syntax on the names of input elements:

    Code:
    <input type="text" name="user[0]" />
    <input type="text" name="user[1]" />
    The "user" value will be an array consisting of the values of those two input elements.

    You can also use named keys or nested arrays:
    Code:
    <input type="text" name="user[0][name]" />
    <input type="text" name="user[0][age]" />
    This way you can structure the form data.

    In your case, simply use the the user ID as the index of each input element:
    Code:
    <input type="text" name="stand[5235]" />
    The stand of user 5235 is then $_POST['stand']['5235'].
    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".
  2. #17
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    ... I can't find the console but does the query have some reserved words or a newer command that I didn't see? maybe it could be that.


    And thanks for the hints, I can go further with this
  4. #18
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by derplumo
    ... I can't find the console but does the query have some reserved words or a newer command that I didn't see?
    Not that I'm aware of. Add the columns one by one:

    sql Code:
    CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY) ENGINE InnoDB;
    ALTER TABLE users ADD username VARCHAR(255) NOT NULL UNIQUE AFTER user_id;
    ALTER TABLE users ADD password CHAR(60) NOT NULL AFTER username;
    ALTER TABLE users ADD email VARCHAR(255) NOT NULL UNIQUE AFTER password;
    ALTER TABLE users ADD start_date DATETIME NOT NULL AFTER email;
    ALTER TABLE users ADD clan INT AFTER start_date;
    ALTER TABLE users ADD stand INT NOT NULL AFTER clan;
    ALTER TABLE users ADD request BOOLEAN AFTER stand;
    ALTER TABLE users ADD FOREIGN KEY (clan) REFERENCES clans (clan_id) ON DELETE SET NULL ON UPDATE CASCADE;
    ALTER TABLE users ADD FOREIGN KEY (stand) REFERENCES stands (stand_id) ON DELETE RESTRICT ON UPDATE CASCADE;

    If that doesn't work either, create the table with phpmyadmin (but don't forget the foreign keys).
    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".
  6. #19
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    ok it accepted all the query's, but I can't see the last two columns (clan and stand), is this normal?

    ---never mind, I overlooked the fact it wasn't a new column -_-
    ---
  8. #20
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    ok almost there, but of course I ran into a problem, I don't know what to do at line 69.

    Also there are some extra thing that I want, but those aren't the problem (yet) and I might know how to do it:

    1) if the leader appoints someone else as leader then he gets an error (maybe in javascript) that this person will take his place as leader, the same for the underboss.

    2) if the leader appoints himself as a member or an other stand below the leader he gets an error that it's impossible to do this.

    3) if the underboss appoints someone as leader or if he appoints the leader as a stand below the leader, he also gets an error that it's impossible to do.

    so the real problem is that I don't really know how to change the stand for every user in the clan, I think it must be done with the 'foreach' already put in the script...



    The code:
    PHP Code:
    <?php 

        
    require("common.php"); 
         
        if(empty(
    $_SESSION['user'])) 
        { 
            
    header("Location: login.php"); 
            exit;
        } 
        
    $user_data_stmt $db->prepare('  
        SELECT 
            clan 
            , stand  
        FROM 
            users  
        WHERE 
            user_id = :user_id  
        '
    ); 
        
    $user_data_stmt->execute(array( 
            
    ':user_id' => $_SESSION['user']['user_id'
        )); 
        
    $user_data $user_data_stmt->fetch(); 
        if ( 
    $user_data    && $user_data['clan'] && ($user_data['stand'] == || $user_data['stand'] == 2) ) {             
            if(!empty(
    $_POST)) {
                
    $member_clan_stmt $db->prepare('
                    SELECT clan
                    FROM users
                    WHERE user_id = :user_id
                '
    );
                
    $member_clan_stmt->execute(array(
                    
    ':user_id' => $_POST['user_id']
                ));
                
    $member_clan $member_clan_stmt->fetchColumn();
                if(
    $member_clan == $user_data['clan']) {
                    if(
    $_POST['action'] === "Kick")
                    {
                        
    // initialise 'kickscript'                    
                        
    $user_data_stmt $db->prepare('  
                            SELECT 
                                clan 
                                , stand  
                            FROM 
                                users 
                            WHERE 
                                user_id = :user_id  
                        '
    ); 
                        
    $user_data_stmt->execute(array( 
                            
    ':user_id' => $_POST['user_id'
                        )); 
                        
    $user_data $user_data_stmt->fetch(); 
                        if ( 
    $user_data    && $user_data['clan'] && $user_data['stand'] != 1  ) {

                            
    $kick_stmt $db->prepare('
                                    UPDATE users
                                    SET clan = NULL,
                                    request = 0
                                    WHERE user_id = :user_id
                                    '
    );
                            
    $kick_stmt->execute(array(
                                
    ':user_id' => $_POST['user_id']
                            ));             
                        }

                    }
                    elseif(
    $_POST['action'] === "Change Functions")
                    {
                        
    // initialise 'function changing script'
                        
    foreach ($_POST['stand'] as $user_id) {
                           
    // ??? 
                        
    }
                    }
                }           
            }
        }
        
    $clan_members_stmt $db->prepare('
            SELECT user_id, username, stand
            FROM users
            WHERE clan = :clan
            '
    );
        
    $clan_members_stmt->execute(array(
            
    ':clan' => $user_data['clan']
        ));
        
    $clan_members $clan_members_stmt->fetchAll();
    ?>
    <html>    
        <body>       
        <?php echo $message?>
            <table> 
                <tr> 
                    <th>Username</th>
                    <th>Function</th>
                    <th>Kick</th>
                </tr> 
                <form action="clan_leader_members.php" method="post">
                    <?php foreach($clan_members as $clan_member): ?> 
                        <tr>                                                   
                            <td><a href="http://www.domain.com/profile.php?user=<?php echo $clan_member['username'];?>"><?php echo htmlentities($clan_member['username'], ENT_QUOTES'UTF-8'); ?></a></td>
                            <td>                        
                                <select name=<?php echo "stand[" $clan_member['user_id'] . "]";?>>                                                          // output will be something like "$_POST[stand][5284]"
                                    <option value="underboss" <?php if($clan_member['stand'] == 2) echo "selected";?>>Underboss</option>
                                    <option value="bankkeeper" <?php if($clan_member['stand'] == 3) echo "selected";?>>bankkeeper</option>
                                    <option value="recruiter"  <?php if($clan_member['stand'] == 4) echo "selected";?>>recruiter</option>
                                    <option value="member"  <?php if($clan_member['stand'] == 5) echo "selected";?>>member</option>                                                                                    
                                </select>
                            </td>
                            <td>
                                <input type="hidden" name="user_id" value=<?php echo $clan_member['user_id'];?>>
                                <input type="submit" name="action" value="Kick">
                            </td>                                                                                                                                        
                        </tr> 
                    <?php endforeach; ?>
                    <input type="submit" name="action" value="Change Functions">
                </form>
            </table>                        
        </body>
    </html>
    Last edited by derplumo; June 29th, 2013 at 12:59 PM.
  10. #21
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    First of all: The overall logic looks good.

    A few things, though:

    Don't use the database IDs of the stands in your code. First of all, it's cryptic: What's stand "1"? What's stand "2"? You may know now, but you'll forget if you don't touch the code for a while, and nobody else understands these numbers. Secondly, your code becomes completely dependend on how the database happens to be set up internally. If you insert the stands in a different order without explicitly setting the ID, your whole code falls apart. This is also a security risk, by the way.

    Instead, use the names of the stands like before. All you gotta do is join the users table with the stands table:

    sql Code:
    SELECT
    	users.clan
    	, stands.name AS stand
    FROM
    	users
    	JOIN stands ON users.stand = stands.stand_id
    WHERE
    	user_id = 1
    ;

    You should also avoid doing unnecessary queries. In line 26, you already fetch the member's clan. Why do you fetch it again in line 39? Make one query in line 26 to get all the necessary data (clan, stand and whatnot).

    And most importantly: You should consider modularizing your code. Currently, you have one long set of instructions. That's fine as long as the code is rather short and simple. But the more complex it gets, the less readable it will be. If you add some more features, you'll reach the point where you lose track of the overall logic -- and that's when people start making errors.

    Simply put logical blocks of code into separate functions. For example, make one function for kicking a user, one for changing the stand etc.



    Originally Posted by derplumo
    so the real problem is that I don't really know how to change the stand for every user in the clan, I think it must be done with the 'foreach' already put in the script...
    Start with changing the stand of one member.

    Write down the checks and actions as pseudo-code or draw a diagram. This way you can concentrate on the logic and get it right before you dive into the PHP syntax.

    By the way, I wouldn't do this kind of error reporting (You can't do this, you can't do that). It's user-hostile. If an underboss cannot promote somebody else a leader, simply don't let them select this option in the first place. Grey it out or don't show it at all. Note, however, that you still need to check the actions in the backend, because anybody can POST anything to your website, regardless of how your forms look like.
    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. #22
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    ok thanks, I'll make it, but what security breach is there then? I will implement an anti-CSFR token later, when I get the basics right

    and what is that sql code for?
    Last edited by derplumo; June 29th, 2013 at 01:19 PM.
  14. #23
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by derplumo
    ok thanks, I'll make it, but what security breach is there then?
    You mean regarding the database IDs?

    If you insert the "plain member" stand first without setting the ID, it will get the ID 1, and your code will allow it to do anything.



    Originally Posted by derplumo
    I will implement an anti-CSFR token later, when I get the basics right
    Do it now. It's not difficult, and you won't forget it then.

    It's simply one additional check after !empty($_POST):

    PHP Code:
    if ($_POST['anti_csrf_token'] === $_SESSION['anti_csrf_token'])
    {
        
    // go on
    }
    else
    {
        
    // display error and log possible CSRF attack



    Originally Posted by derplumo
    and what is that sql code for?
    To look up the actual name of the stand.
    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".
  16. #24
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    and where do I use that sql-code?

    I just added an anti-CSRF token for this form.
    Last edited by derplumo; June 29th, 2013 at 01:38 PM.
  18. #25
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    ok I now have:
    as you see I also gave it a shot to process all the users' functions...
    PHP Code:
    <?php 

        
    require("common.php"); 
         
        if(empty(
    $_SESSION['user'])) 
        { 
            
    header("Location: login.php"); 
            exit;
        } 
        
    $user_data_stmt $db->prepare('  
        SELECT 
            clan 
            , stand  
        FROM 
            users  
        WHERE 
            user_id = :user_id  
        '
    ); 
        
    $user_data_stmt->execute(array( 
            
    ':user_id' => $_SESSION['user']['user_id'
        )); 
        
    $user_data $user_data_stmt->fetch(); 
        if ( 
    $user_data    && $user_data['clan'] && ($user_data['stand'] == || $user_data['stand'] == 2) ) {             
            if(!empty(
    $_POST)) {
                if (isset(
    $_POST['anti_csrf_token']) && isset($_SESSION['anti_csrf_token']) && $_POST['anti_csrf_token'] === $_SESSION['anti_csrf_token']) 
                {
                    
    $member_clan_stmt $db->prepare('
                        SELECT clan
                        FROM users
                        WHERE user_id = :user_id
                    '
    );
                    
    $member_clan_stmt->execute(array(
                        
    ':user_id' => $_POST['user_id']
                    ));
                    
    $member_clan $member_clan_stmt->fetchColumn();
                    if(
    $member_clan == $user_data['clan']) {
                        if(
    $_POST['action'] === "Kick")
                        {
                            
    // initialise 'kickscript'                    
                            
    $user_data_stmt $db->prepare('  
                                SELECT 
                                    clan 
                                    , stand  
                                FROM 
                                    users 
                                WHERE 
                                    user_id = :user_id  
                            '
    ); 
                            
    $user_data_stmt->execute(array( 
                                
    ':user_id' => $_POST['user_id'
                            )); 
                            
    $user_data $user_data_stmt->fetch(); 
                            if ( 
    $user_data    && $user_data['clan'] && $user_data['stand'] != 1  ) {

                                
    $kick_stmt $db->prepare('
                                        UPDATE users
                                        SET clan = NULL,
                                        request = 0
                                        WHERE user_id = :user_id
                                        '
    );
                                
    $kick_stmt->execute(array(
                                    
    ':user_id' => $_POST['user_id']
                                ));             
                            }

                        }
                        elseif(
    $_POST['action'] === "Change Functions")
                        {
                            
    // initialise 'function changing script'
                            
                            
    foreach ($_POST['stand'] as $user_id) {
                                
                                
    $update_stand $db->prepare('
                                    UPDATE users
                                    SET stand = :stand
                                    WHERE user_id = :user_id
                                    '
    );
                                
    $update_stand->execute(array(
                                    
    ':stand' => $_POST['stand'][$user_id],
                                    
    ':user_id' => $user_id
                                
    ));
                            }
                        }
                    }           
                }
                else
                {
                    echo 
    'invalid submission'
                    
    trigger_error('possible CSRF attack'E_USER_ERROR);    // add details for logging like the user ID, the referrer (as a possible source of the attack) etc. 
                    
    exit; 
                }
            }
        }
        
    $clan_members_stmt $db->prepare('
            SELECT user_id, username, stand
            FROM users
            WHERE clan = :clan
            '
    );
        
    $clan_members_stmt->execute(array(
            
    ':clan' => $user_data['clan']
        ));
        
    $clan_members $clan_members_stmt->fetchAll();
    ?>
    <html>
        <body>
            <?php echo $message?>
            <table> 
                <tr> 
                    <th>Username</th>
                    <th>Function</th>
                    <th>Kick</th>
                </tr> 
                <form action="clan_leader_members.php" method="post">
                    <input type="hidden" name="action_token" value="<?php echo html_escape($_SESSION['action_token']) ?>"> 
                    <?php foreach($clan_members as $clan_member): ?> 
                        <tr>                                                   
                            <td><a href="http://www.domain.com/profile.php?user=<?php echo $clan_member['username'];?>"><?php echo htmlentities($clan_member['username'], ENT_QUOTES'UTF-8'); ?></a></td>
                            <td>
                                <select name=<?php echo "stand[" $clan_member['user_id'] . "]";?>>                                                          // output will be something like "$_POST[stand][5284]"
                                    <?php if($user_data['stand'] === 1)
                                    {
                                        echo 
    '<option value="leader"';
                                        if(
    $clan_member['stand'] == 1) echo "selected";
                                        echo 
    '>Leader</option>';
                                    }
    ?>
                                                        
                                    <option value="underboss" <?php if($clan_member['stand'] == 2) echo "selected";?>>Underboss</option>
                                    <option value="bankkeeper" <?php if($clan_member['stand'] == 3) echo "selected";?>>bankkeeper</option>
                                    <option value="recruiter"  <?php if($clan_member['stand'] == 4) echo "selected";?>>recruiter</option>
                                    <option value="member"  <?php if($clan_member['stand'] == 5) echo "selected";?>>member</option>                                                                                    
                                </select>
                            </td>
                            <td>
                                <input type="hidden" name="user_id" value=<?php echo $clan_member['user_id'];?>>
                                <input type="submit" name="action" value="Kick">
                            </td>                                                                                                                                        
                        </tr> 
                    <?php endforeach; ?>
                    <input type="submit" name="action" value="Change Functions">
                </form>
            </table>                         
        </body>
    </html>
  20. #26
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    ok weird, I just tried to run my whole program for testing if everything still works after changing the tables, it turns out that when someone tries to register it gives this error:

    Code:
    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`the database`.`users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`stand`) REFERENCES `stands` (`stand_id`) ON UPDATE CASCADE)' in /---mainroot---/register.php:99 Stack trace: #0 /---mainroot---/register.php(99): PDOStatement->execute(Array) #1 {main} thrown in /---still the mainroot---/register.php on line 99
    at this line in the script:

    PHP Code:
    $register_stmt $db->prepare(
                INSERT INTO users ( 
                    username, 
                    password, 
                    email, 
                    start_date
                ) VALUES ( 
                    :username, 
                    :password, 
                    :email,
                    NOW()
                ) 
            '
    ); 
            
    $hash password_hash($_POST['password'], PASSWORD_BCRYPT, array("cost" => 10));  
            
            
    $register_stmt->execute(array( 
                
    ':username' => $_POST['username'], 
                
    ':password' => $hash
                
    ':email' => $_POST['email']
            )); 
  22. #27
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    You must set a stand upon registration -- or remove the NOT NULL from the stand column (which means new users will have no stand at all).
    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".
  24. #28
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    Originally Posted by Jacques1
    You must set a stand upon registration -- or remove the NOT NULL from the stand column (which means new users will have no stand at all).
    Of course -_- but that should be possible, if a new member registers then he isn't part of a clan (yet)... Thanks again
  26. #29
  27. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    Now even more weird, I didn't touch the password.php or the login.php and it gives me this error:

    Code:
    Fatal error: Call to undefined function generate_secure_token() in Ö  on line 46
    and the code:

    PHP Code:
    $_SESSION['user'] = $row
     
    $_SESSION['action_token'] = generate_secure_token(); 
    but in the password.php it is defined but it doesn't call it in one way or the other... I didn't forget to require the file password.php (which includes the generate_secure_token())...

    I used the DO's and DON'Ts of 6 sins of php, by Jaqcues1

    !!!!never mind, I already found the problem... I've got to learn to shout later !!!!

    and Jaqcues1, didn't you forget the <?php in the "functions.inc.php" file?
    Last edited by derplumo; June 30th, 2013 at 08:26 AM.
  28. #30
  29. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    407
    Rep Power
    8
    Do I have to change

    Code:
    FOREIGN KEY (stand) REFERENCES stands (stand_id) ON DELETE 	RESTRICT ON UPDATE CASCADE
    into

    Code:
    FOREIGN KEY (stand) REFERENCES stands (stand_id) ON DELETE SET NULL ON UPDATE CASCADE
    ?

    see also #18

IMN logo majestic logo threadwatch logo seochat tools logo