Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0

    Need help please


    I have 2 tables:
    PHP Code:
    CREATE TABLE tops(
      
    id INT NOT NULL AUTO_INCREMENT,
      
    users_id INTEGER NOT NULL,
      
    title varchar(80),
      
    details varchar(250),
      
    type varchar(30NOT NULL,
      
    site varchar(50),
      
    votes INT NOT NULL,
      
    facebook varchar(80),
      
    image varchar(100NOT NULL,
      
    CONSTRAINT tops_pk PRIMARY KEY(id),
      
    CONSTRAINT user_fk FOREIGN KEY(users_idREFERENCES users(id)); 
    PHP Code:
    CREATE TABLE users(
     
    id int AUTO_INCREMENT NOT NULL,
     
    username varchar(60),
     
    password varchar(60),
     
    email varchar(80),
     
    premium enum('0','1'NOT NULL,
     
    timestamp int NOT NULL,
     
    CONSTRAINT users_pk PRIMARY KEY(id); 
    When I register the data goes to the users table [working fine]
    Then when I add a server the data goes to tops table [working fine]

    However, users_id field in tops table doesn't update to the id of the user that added the server, it just remains 0. Could anyone help me find a problem here please
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

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

    Originally Posted by cripin
    Could anyone help me find a problem here please
    Not without seeing your actual code. Maybe the user ID is already wrong in the application code, maybe there's a foreign key violation. We don't know, we're not psychic.

    We need your code, and you'll need to do some basic debugging yourself (like outputting the UPDATE query).
    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. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,



    Not without seeing your actual code. Maybe the user ID is already wrong in the application code, maybe there's a foreign key violation. We don't know, we're not psychic.

    We need your code, and you'll need to do some basic debugging yourself (like outputting the UPDATE query).
    What do you mean by, maybe the user ID is already wrong in the application code? What code do you need, I can paste in the registration and add_your_server code
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    Registration code:
    PHP Code:
    <?php
    require_once 'connect.php';
    require_once 
    'PasswordHash.php';
    session_start();

    if(isset(
    $_SESSION['logged_in'])){
        
    header('location: index.php');
    }else{
        
    $debug TRUE;
        function 
    fail($pub$pvt =''){
            global 
    $debug;
            
    $msg $pub;
            if(
    $debug && $pvt !=='')
                
    $msg .= ": $pvt";
            
    header('Content-Type: text/plain');
            exit(
    "An error occured ($msg).\n");
        }
        
    $hash_cost_log2 8;
        
    $hash_portableFALSE;
        if(
    $_SERVER['REQUEST_METHOD']=='POST'){
            
    $username $_POST['username'];
            
    $password $_POST['password'];
            
    $password2 $_POST['password2'];
            
    $email $_POST['email'];
            
    $email2 $_POST['email2'];
            
            
    $hasher = new PasswordHash($hash_cost_log2$hash_portable);
            
    $hash $hasher->HashPassword($password);
            if(
    strlen($hash)<20){
                
    fail('Failed to hash new password');
            unset(
    $hasher);
        }else{
            if(!empty(
    $username)&&!empty($password)&&!empty($password2)&&!empty($email)&&!empty($email2)){
                if(
    $password == $password2){
                    if(
    $email == $email2){
                        if(
    ctype_alnum($username)&&strlen($username)<60){
                            if(
    ctype_alnum($password)&&strlen($password)<72){
                                if(
    filter_var($email,FILTER_VALIDATE_EMAIL)&&strlen($email)<80){
                                    
    $check_username $conn->query("SELECT (username) FROM users WHERE username ='$username'");
                                    
    $check_username->fetch();
                                    
    $check_username->execute();    
                                    
    $check_username->closeCursor();
                                                                
                                    if(
    $check_username->rowCount()==1){
                                        
    $status 'Username is already in use';
                                        
                                    }else{
                                        
    $check_pass $conn->query("SELECT (email) FROM users WHERE email ='$email'");
                                        
    $check_pass->fetch();
                                        
    $check_pass->execute();
                                            
    $check_pass->closeCursor();
                                        if(
    $check_pass->rowCount()==1){
                                        
                                            
                                            
    $status 'Email is already in use';
                                        }else{
                                            
    $register $conn->prepare("INSERT INTO users(username,password,email) VALUES(?,?,?)");
                                            
    $register->bindParam(1,$usernamePDO::PARAM_STR);
                                            
    $register->bindParam(2,$hashPDO::PARAM_STR);
                                            
    $register->bindParam(3,$email);
                                            
    $register->execute();
                                            
    $register->closeCursor();
                                            
                                            echo 
    'Registered';
                                        }
                                    }
                                    
                                }else{
                                    
    $status 'Invalid email address';
                                }
                            }else{
                                
    $status 'Invalid or too long password';
                            }
                        }else{
                            
    $status 'Invalid or too long username';
                        }
                    }else{
                        
    $status 'Your email addresses do not match';
                    }
                }else{
                    
    $status 'Your passwords do not match';
                }
            }else{
                
    $status 'Please fill in all fields';
            }
            
    }
        }    
         
    ?>

        <form class="register_form" action="" method="POST">
            <ul>
                <li>
                    <label for="username">Username:</label>
                    <input type="text" name="username" id="username" maxlength="20">
                </li>
                <li>
                    <label for="password">Password:</label>
                    <input type="password" name="password" id="password" maxlength="20">
                </li>
                <li>
                    <label for="password2">Repeat Password:</label>
                    <input type="password" name="password2" id="password2" maxlength="20">
                </li>
                <li>
                    <label for="email">Email:</label>
                    <input type="text" name="email" id="email" maxlength="80">
                </li>
                <li>
                    <label for="email2">Repeat Email:</label>
                    <input type="text" name="email2" id="email2" maxlength="80">
                </li>
                <li>
                    <input type="submit" value="Register">
                </li>
            </ul>
            <p class="status"><?php echo $status?></p>
        </form>
    Add server code:
    PHP Code:
    <?php
    require_once 'connect.php';

    if(
    $_SERVER['REQUEST_METHOD']=='POST'){
        
    $title $_POST['title'];
        
    $description $_POST['description'];
        
    $site $_POST['site'];
        
    $facebook $_POST['facebook'];
        
    $type $_POST['type'];
        
        
    $valid_url "/\b(?:(?:https?|ftp):\/\/|www\.)[-a-z0-9+&@#\/%?=~_|!:,.;]*[-a-z0-9+&@#\/%=~_|]/i";
        

        if(!empty(
    $title)&&!empty($description)&&!empty($site)&&!empty($type)){
            if(
    strlen($title)<80){
                if(
    strlen($description)<250&&strlen($description)>99){
                    if(
    preg_match($valid_url$site)&&strlen($site)<50){
                        if(
    preg_match($valid_url$facebook)&&strlen($facebook)<80){
                            if(
    ctype_alpha($type)&&strlen($type)<25){
                                
    $add $conn->prepare("INSERT INTO tops(title,details,site,facebook,type) VALUES(?,?,?,?,?)");
                                
    $add->bindParam(1,$title,PDO::PARAM_STR);
                                
    $add->bindParam(2,$description,PDO::PARAM_STR);
                                
    $add->bindParam(3,$site,PDO::PARAM_STR);
                                
    $add->bindParam(4,$facebook,PDO::PARAM_STR);
                                
    $add->bindParam(5,$type,PDO::PARAM_STR);
                                
    $add->execute();
                            }else{
                                
    $status 'invalid selection';
                            }
                        }else{
                            
    $status 'Invalid facebook page name';
                        }
                    
                    }else{
                        
    $status 'Invalid website url';
                    }
                }else{
                    
    $status 'Description contains invalid characters or is too short/long';
                }
            }else{
                
    $status 'Title contains invalid characters or is too long';
            }
        }else{
            
    $status 'Please fill in all required fields';
        }
    }
    ?>
    <form class="add_form" action="" method="POST">
            <ul>
                <li>
                    <label for="title">Title:</label>
                    <input type="text" name="title" id="title" placeholder="Max 80 characters">
                </li>
                <li>
                    <label for="description">Description:</label>
                    <textarea name="description" id="description" maxlength="250" placeholder="Max 250 characters"></textarea>
                </li>
                <li>
                    <label for="site">Website url:</label>
                    <input type="text" name="site" id="site" placeholder="Must include: www" maxlength="50">
                </li>
                <li>
                    <label for="facebook">Facebook page:</label>
                    <input type="text" name="facebook" id="facebook" placeholder="Optional" maxlength="80">
                </li>
                <li>
                    <label for="type">Select server type:</label>
                    <select name="type" id="type">
                        <option></option>
                        <option value="Scions of Destiny">Scions of Destiny</option>
                        <option value="Oath of Blood">Oath of Blood</option>
                        <option value="Interlude">Interlude</option>
                        <option value="Gracia">Gracia</option>
                        <option value="Freya">Freya</option>
                        <option value="High Five">High Five</option>
                        <option value="Goddess of Destruction">Goddess of Destruction</option>
                    </select>
                </li>
                <li>
                    <input type="submit" value="Add">
                </li>
            </ul>
            <p class="status"><?php echo $status?></p>
        </form>
    PS. I been only learning php for about 2 weeks didn't know any programming before so it could have security issues, but functions properly
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Well, I don't see you setting the users_id of the tops table anywhere:

    sql Code:
    INSERT INTO tops(title,details,site,facebook,TYPE) VALUES(?,?,?,?,?)

    So it's not too surprising that it's not set.



    Originally Posted by cripin
    PS. I been only learning php for about 2 weeks didn't know any programming before so it could have security issues, but functions properly
    In line 39 and 48 of the registration code, you inject $username and $email directly into the query string. If you pass them as parameters instead like you do with the INSERT queries, your code is pretty secure from what I can tell right now. After 2 weeks, you're already doing better than most PHP programmers after 2 years.

    I know that you validate $username and $email, but it's not a good idea to rely on that. Prepared statements are always the better solution.
    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".
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    thanks a lot for putting your time in to read through the code, so what should I set the uid to ? maybe something like $_SESSION['id']?
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Yes.

    Shouldn't you have a check for $_SESSION, anyway? Currently, it looks like anybody could add a server.
    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".
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    I did it on purpose until I finish adding all features that I want to add, later I will add the check , thank you
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    After I added $_SESSION['id'] I get an error: Column 'users_id' cannot be null
    This is if I don't set session_start(); at the beginning however, when I do set the session_start(); the add_your_server.php still submits but it doesn't insert any data into mysql table at all.

    It works just fine if I don't add the $_SESSION['id'] though, where is the problem here?

    Could it be because on login.php I only set $_SESSION['logged_in'] = True; ?
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    Fixed it, by setting the $_SESSION['username'] = $username;

    then, created this query
    PHP Code:
    $query $conn->prepare("SELECT id FROM users WHERE username = ?");
    $query->bindParam(1,$_SESSION['username']);
    $query->execute();
    return 
    $yes $query->fetchColumn(); 
    And inside the add_server file I added $yes for the user_id
    PHP Code:
        $add $conn->prepare("INSERT INTO tops(title,details,site,facebook,type,users_id) VALUES(?,?,?,?,?,?)");
                                
    $add->bindParam(1,$title,PDO::PARAM_STR);
                                
    $add->bindParam(2,$description,PDO::PARAM_STR);
                                
    $add->bindParam(3,$site,PDO::PARAM_STR);
                                
    $add->bindParam(4,$facebook,PDO::PARAM_STR);
                                
    $add->bindParam(5,$type,PDO::PARAM_STR);
                                
    $add->bindParam(6,$yes,PDO::PARAM_INT);
                                
    $add->execute(); 
  20. #11
  21. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Not sure why you make it so complicated.

    Get rid of all the "logged_in", "username" etc. in the session. You only need one session value: The user ID.

    Set it upon login: $_SESSION['user_id'] = .... In the other scripts, you check the login status with !empty($_SESSION['user_id']). And you can then simply read the user ID directly from the session:

    PHP Code:
    $add->bindParam(6$_SESSION['user_id'], PDO::PARAM_INT); 
    By the way, you should be using named parameters instead of numeric ones.
    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".
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    thanks for the tip, also then what do i make the $_SESSION['user_id'] = to? And how would this work?
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    When somebody logs in, you look up the user account for those credentials, right? At this point, you need to store the user ID in the session.

    Let's say I've registered with the email address "jacques@example.com" and the password "secret". My ID is 42. When I log in, I send you those credentials. You fetch the row with the email "jacques@example" (it has the ID 42), you check if the password is correct, and then you let me through. At this point, you need to store the ID 42 in my session: $_SESSION['user_id'] = $row['id'].

    How else would you identify the owner of a session? Of course you could store the email address or the user name (assuming it's unique), but this means that you have to look up the user ID every time you need it.
    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".
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    22
    Rep Power
    0
    thanks for explanation, however I get an error after putting the $_SESSION['user_id'] = $row['id'] inside the add_server file. This is the main code for login

    Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'users_id' cannot be null'

    PHP Code:
    if(!empty($username)&&!empty($password)){
                if(
    ctype_alnum($username)&&strlen($username)<60){
                    if(
    ctype_alnum($password)&&strlen($password)<72){
                        
    $login $conn->prepare("SELECT username FROM users WHERE username = ?");
                        
    $login->bindParam(1,$usernamePDO::PARAM_STR);
                        
    $login->execute();
                        if(
    $login->rowCount()==1){
                            
    $log $conn->prepare("SELECT password FROM users WHERE username = ?");
                            
    $log->bindParam(1,$usernamePDO::PARAM_STR);
                            
    $log->execute();
                            
    $row $log->fetch(PDO::FETCH_ASSOC);
                            
    $hash $row['password'];
                            
    $password $_POST['password'];
                            
                            if(
    $hasher->CheckPassword($password,$hash)){
                                
    $_SESSION['user_id'] = $row['id'];
                                
    header('location: index.php'); 
    What could the problem be?
  28. #15
  29. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Your row has no "id" column. You didn't select it.

    You need to select both the id and the password.
    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".
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo