Page 1 of 4 123 ... Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8

    Memberslist with functions


    hi,

    I want to make a memberslist as done in the thread "a basic but secure login system". But I want something extra, I want to change some user's functions.

    I mean that a user can be a member or a member with benefits or something like that. I want to do it with the html form function "<input type='select'..." so that I can change the function of multiple users at once.

    Thanks in advance.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

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

    what exactly is your question? What have you done already, and where did you get stuck?
    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".
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    Well first some details, there are groups called 'clans' and what I want to do is that only the 'leader' or the 'underboss' can change somebody's 'stand', his role in the clan. The 'stand' is for example the function of 'leader' or 'underboss'.

    PHP Code:
    <?php 

        
    require("common.php");             // starting the connection with the database and the session
         
        
    if(empty($_SESSION['user']))     // session variable regarding the information of the user, the user is logged in from this point
        

            
    header("Location: login.php"); 
            die(
    "Redirecting to login.php"); 
        } 
        
    $clan_stmt $db->prepare('
            SELECT clan
            FROM user_information
            WHERE username = :username
        '
    );
        
    $clan_stmt->execute(array(
            
    ':username' => $_SESSION['user']['username']
        ));
        
    $user_clan $clan_stmt->fetchColumn();                
        if(
    $user_clan != "none") {        
            
    $stand_stmt $db->prepare('
                SELECT stand
                FROM user_information
                WHERE username = :username
                '
    );
            
    $stand_stmt->execute(array(
                
    ':username' => $_SESSION['user']['username']
            ));
            
    $stand $stand_stmt->fetchColumn();        
            if(
    $stand == 'leader' || $stand == 'underboss') { 
                
                if(!empty(
    $_GET['user']) && !empty($_GET['action'])) {                
                    
    $member_clan_stmt $db->prepare('
                        SELECT clan
                        FROM user_information
                        WHERE username = :username
                        '
    );
                    
    $member_clan_stmt->execute(array(
                        
    ':username' => $_GET['user']
                    ));
                    
    $member_clan $member_clan_stmt->fetchColumn();
                    if(
    $member_clan == $user_clan) {
                        if(
    $_GET['action'] == "kick") {
                            
    $members_stmt $db->prepare('
                                SELECT members
                                FROM clans
                                WHERE name = :clan_name
                                '
    );
                            
    $members_stmt->execute(array(
                                
    ':clan_name' => $user_clan
                            
    ));
                            
    $members $members_stmt->fetchColumn();
                            
    $members--;

                            
    $member_update_stmt $db->prepare('
                                UPDATE clans
                                SET members = :members
                                WHERE name = :clan_name
                                '
    );
                            
    $member_update_stmt->execute(array(
                                
    ':members' => $members,
                                
    ':clan_name' => $user_clan
                            
    ));

                            
    $leave_stmt $db->prepare('
                                UPDATE user_information
                                SET clan = "none",
                                request = 0
                                WHERE username = :username
                                '
    );
                            
    $leave_stmt->execute(array(
                                
    ':username' => $_GET['user']
                            ));

                        }                    
                    }
                    else {
                        
    header("Location: clan_list.php");    // a list of clans, just like a memberlist
                        
    die("Redirecting to clan_list.php");    
                    }   
                }
                if(!empty(
    $_POST)){
                    
    // I don't know what I have to do here, I want to set every user's function in the clan to what the 'leader' has set, or only the changes... I didn't know what to do so I tried something... 
                    
    var_dump($_POST);
                    foreach (
    $_POST as $_POST[$function]){
                        
    $user_name substr($function9);
                        
    var_dump($user_name);
                    }
                }
                
    $clan_members_stmt $db->prepare('
                    SELECT username
                    FROM user_information
                    WHERE clan = :clan
                    '
    );
                
    $clan_members_stmt->execute(array(
                    
    ':clan' => $user_clan
                
    ));
                
    $clan_members $clan_members_stmt->fetchAll();
            }
            else {
                
    header("Location: clan_list.php");
                die(
    "Redirecting to clan_list.php");    
            }
        }
        else {
            
    header("Location: clan_list.php");
            die(
    "Redirecting to clan_list.php");    
        }
    ?> 
    <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.yourdomain.com/profile.php?user=<?php echo $clan_member['username'];?>"><?php echo htmlentities($clan_member['username'], ENT_QUOTES'UTF-8'); ?></a></td>
                        <td>
                            <input type="hidden" value=<?php echo $clan_member['username'];?>>
                            <select name="function_<?php echo $clan_member['username'];?>">
                                <option value="underboss">Underboss</option>
                                   <option value="bankkeeper">bankkeeper</option>
                                <option value="recruiter">recruiter</option>
                                <option value="member" selected>member</option>                                                                                    
                            </select></td>      
                        <td><a href="http://www.yourdomain.com/clan_leader_members.php?user=<?php echo $clan_member['username'];?>&action=kick">Kick</a></td>                                                                                                                                        
                    </tr> 
                    <?php endforeach; ?>
                    <input type="submit" value="Change Functions">
                </form>
            </table>                                       
        </body>
    </html>
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    There are several issues. You should start by fixing the database scheme:

    The way you associate users with clans is wrong. Since you use the string "none" to denote the absence of a clan membership, your clan column contains a mixture of arbitrary strings and actual clan references. That's not how the relational model works. A foreign key (an attribute referencing the primary key of another table) may only contain valid references that actually point to particular rows in the other table. To denote the absence of a reference, you use the empty value (in SQL, that's NULL).

    If you use InnoDB, you can and should enforce this by using foreign key constaints. Otherwise you can easily end up with garbage data. For example, a member might have a clan that doesn't exist (maybe there's a typo, maybe it has already been deleted), you may have different versions of "no value" ("none", "no clan", "-", "n. a.") and you may have erroneous strings that simply make no sense.

    Storing the number of members for each clan is redundant and comes with all the issues redudant data has. You can easily end up with conflicting information in case one of the queries fails. And you're forced to constantly synchronize the unnecessary number. Get rid of it. If you wanna know how many members a clan has, count them.

    As to the PHP code:

    You have a lot of unnecessary queries that bloat your code. For example, why do you fetch the clan and stand of the current user in two separate queries? Why not select both at the same time? And why do you fetch the number of members, decrement it in PHP and then write it back? Why not just do SET members = members - 1? This would also prevent ugly conflicts in case two queries try to update the table at the same time -- but like I already said: You should remove this column, anyway.

    If you get rid of the unnecesary stuff, you can probably reduce your code by more than a half.

    Last but not least:

    Do not change data based on a GET request (I'm talking about the $_GET['action']). A GET request is supposed to do nothing but fetch a resource (hence the name "get"). Breaking this basic rule will lead to confusion, unintentional actions and security issues.

    Imagine an evil person preparing a "action=kick" link, obfuscating it with some short link service and sending it to one of your clan masters. The master clicks on it, and suddenly this member is gone. Actually, the master doesn't even have to physically click on the link. If the link is in the src of an image or a script container, the browser will automatically visit this page, which again kicks a member.

    Long story short: GET must not have permanent effects (except from internal stuff like logging).
    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. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    The way you associate users with clans is wrong. Since you use the string "none" to denote the absence of a clan membership, your clan column contains a mixture of arbitrary strings and actual clan references. That's not how the relational model works. A foreign key (an attribute referencing the primary key of another table) may only contain valid references that actually point to particular rows in the other table. To denote the absence of a reference, you use the empty value (in SQL, that's NULL).
    So I should do something like:

    PHP Code:
    if($clan == NULL$clan_displayed "none"// what the user gets to see is then none...
    else $clan_displayed $clan
    If you use InnoDB,
    I use PhpMyAdmin...

    Do not change data based on a GET request (I'm talking about the $_GET['action']). A GET request is supposed to do nothing but fetch a resource (hence the name "get"). Breaking this basic rule will lead to confusion, unintentional actions and security issues.

    Long story short: GET must not have permanent effects (except from internal stuff like logging).
    ok, but how do I do this then? that the master can just click on it and then kick somebody... And regarding the problem in the first place.

    And where could I learn some good and safe PHP?
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by derplumo
    So I should do something like:

    PHP Code:
    if($clan == NULL$clan_displayed "none"// what the user gets to see is then none...
    else $clan_displayed $clan
    How you display the value in your code doesn't really matter. What's important is that your database table column clan only contains actual clan names or NULL. There must not be pseudo-names like "none" -- unless you actually have a clan called "none".

    If your database table is set up correctly, you can check the clan and the stand in one query:

    PHP Code:
    <?php

    $user_data_stmt 
    $db->prepare(
        SELECT
            clan
            , stand 
        FROM
            user_information 
        WHERE
            username = :username 
    '
    );
    $user_data_stmt->execute(array(
        
    ':username' => $_SESSION['user']['username']
    ));
    $user_data $user_data_stmt->fetch();
    if ( 
    $user_data    && $user_data['clan'] && ($user_data['stand'] == 'leader' || $user_data['stand'] == 'underboss') ) {

        
    // at this point, we know that the user has a clan and is its leader or an underboss

    } else {
        
    header('Location: clan_list.php');
        exit;
    }


    Originally Posted by derplumo
    I use PhpMyAdmin...
    I'm talking about the storage engine of your tables.

    If you're using InnoDB (as opposed to MyISAM), you can and should have MySQL check if the clan names inserted into the clan column actually point to existing clans.

    The engine is specified in the CREATE TABLE statement, but it can also be changed later:

    http://dev.mysql.com/doc/refman/5.5/...e-setting.html

    PHPmyadmin is only a graphical interface for conveniently managing MySQL databases. It's not relevant to your code.



    Originally Posted by derplumo
    ok, but how do I do this then? that the master can just click on it and then kick somebody...
    First of all, you should change the GET request into a POST request.

    And then you should add an anti-CSRF token to prevent attackers from "remote-controlling" the browsers of your masters (no pun intended).



    Originally Posted by derplumo
    And where could I learn some good and safe PHP?
    Like I said last time, I'm not aware of any good PHP resource I'd recommend wholeheartedly. A PHP equivant of the Mozilla Developer Network has yet to be invented. That's why I suggested learning from good forums like stackoverflow -- or this one.

    For general security, there's the OWASP.
    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. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    ok, i'm working out the details, but I now have just a couple of tables, but the tables 'user' (login information of the user) and 'user_information' (further information of the user) should be related with the username. But how do I do this correctly, because I now think I do it wrong. I just created a trigger (for how far it's possible in PhpMyAdmin) that with a register of a new user also a new row will be created in the 'user_information'... How do I do this right?
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Why do you store the user data in two separate tables. Simply make one "users" table with all the data in it.

    On a side note: The general way of inserting a dataset and then a related dataset (like a new thread with its initial post) is to insert the main dataset, get its ID with lastInsertID and then insert the second dataset.
    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. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    ok, I now have 1 table for the users but I still don't really know how the set-up for the script would be...
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Well, what do you have? What's your database scheme? What's your code? What exactly do you wanna know?
    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".
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    I still have the same code as standing above, the table looks like this:

    Code:
    CREATE TABLE users (
      	id int(11) NOT NULL AUTO_INCREMENT,
      	username varchar(255) NOT NULL,
      	password char(60) NOT NULL,
      	email varchar(255) NOT NULL,
      	start_date datetime NOT NULL,
    	clan varchar(15),
            request boolean,
            stand varchar(30),
      PRIMARY KEY (`id`),
      UNIQUE KEY `username` (`username`),
      UNIQUE KEY `email` (`email`)
    ) ENGINE=InnoDB
    ;
    And what I want is that I can change the stand of someone, or to change the stand of multiple users in the memberlist of a clan at the same time. Also I want to make an option to kick a user, this can be a button so it can be clicked and be done with it.
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Do you have separate tables for the clan and stand? If not, create them, change the "clan" and "stand" columns to integers and add a foreign key to enforce valid references:

    sql Code:
    CREATE TABLE stands (
    	stand_id INT AUTO_INCREMENT PRIMARY KEY
    	, name VARCHAR(255) NOT NULL
    ) ENGINE InnoDB
    ;
    INSERT INTO
    	stands (name)
    VALUES
    	('leader')
    	, ('underboss')
    ;
     
     
     
    CREATE TABLE clans (
    	clan_id INT AUTO_INCREMENT PRIMARY KEY
    	, name VARCHAR(255) NOT NULL
    ) ENGINE InnoDB
    ;
    INSERT INTO
    	clans (name)
    VALUES
    	('Teh devs')
    ;
     
     
    CREATE TABLE users (
    	user_id INT AUTO_INCREMENT PRIMARY KEY
    	, username VARCHAR(255) NOT NULL UNIQUE
    	, password CHAR(60) NOT NULL
    	, email VARCHAR(255) NOT NULL UNIQUE
    	, start_date DATETIME NOT NULL
    	, clan INT
    	, stand INT NOT NULL
    	, request BOOLEAN
    	, FOREIGN KEY (clan) REFERENCES clans (clan_id) ON DELETE SET NULL ON UPDATE CASCADE
    	, FOREIGN KEY (stand) REFERENCES stands (stand_id) ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE InnoDB
    ;
    INSERT INTO
    	users (username, password, email, start_date, clan, stand)
    VALUES
    	('Jacques1', '', 'test@example.com', NOW(), 1, 1)
    ;

    Without separate tables and foreign keys, your "clans" and "stands" are just arbitrary strings without any meaning. You or some buggy application could insert some garbage string like "$%135" into the field, and MySQL would happily accept it.

    With the setup above, that's no longer possible. MySQL expects you to actually insert a valid clan and stand and will reject anything else -- which should be exactly what you want if you care about correct data.

    Regarding the script: I gave you the code for checking the authorization in #6. Use it.

    Adding the "kick user" functionality should then be pretty straightforward. In fact, it's not much different from what you already did in your previous script. The only difference is that you no longer have to decrement some counter. If the target user's clan matches the administrator's clan, you simply set the user's clan to NULL. That's it.

    But don't forget the comments on POST vs. GET and security. A persistent action must be done with POST, and it needs to be secured with an anti-CSRF token.
    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. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    ok, I get it, but how do I change the role of a couple of members at the same time with something like the <select> and <option> tags?
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Before you do that, first write the code to change the stand/role of one member. When that works, please post the code.

    Do you generally know how to have the form send an array of data?
    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".
  28. #15
  29. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    436
    Rep Power
    8
    Well I think I don't

    also I'm trying to get the tables up and working, but when I try to create the 'users' table, I get an error near line 3, but when I try to look where the flaw is and just delete line 3 and 4 ( just to see if the fault really is in that line) it keeps giving me the error:

    Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '    , username VARCHAR(255) NOT NULL UNIQUE     , password CHAR(60) NOT NUL' at line 3
Page 1 of 4 123 ... Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo