SitePoint Sponsor

User Tag List

Results 1 to 22 of 22

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Sercure Correct Update Syntax?

    Hi,

    I am trying to implement the correct update code. I have a registration script which enables someone to create an account but I am now trying to create a page which allows someone to add details to their account. Do I simply change "INSERT INTO" to "UPDATE".

    What Im confused about is how I mix Post with string update to help make it secure.

    Code:
     $category = mysql_real_escape_string(trim($_POST['category']));
    I found this on the http://www.w3schools.com/php/php_mysql_update.asp W3 Schools Site but it doesn't include an safety measures.


    Code:
           $sql = "
            UPDATE
                user
            SET
                category = '".$_POST['category']."',
    			linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."'";
    
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
    
           
    
        }
    
    }
    ?>

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
     $category mysql_real_escape_string(trim($_POST['category'])); 
    That is ok.


    but then doing this:
    PHP Code:
     $category mysql_real_escape_string(trim($_POST['category']));

    $sql "UPDATE users 
    SET category='
    $category'"
    Will wipe out all your existing category records - you are likely missing a WHERE clause.

    It usually looks more like:
    PHP Code:
     $category mysql_real_escape_string(trim($_POST['category']));
     
    $someid 23 ;


    $sql "UPDATE users 
    SET category='
    $category'
    WHERE id=
    $someid"

  3. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Many thanks,

    The current code I have does update all the accounts so now I am trying to update each account matching it with the id (the field is id) however when I try to add this id function it creates an error.

    Code:
    function mysql_real_escape_array($t)
    {
        return array_map("mysql_real_escape_string",$t);
    }
    
    function trim_array($ar)
    {
        return array_map("trim",$ar);
    }
    
    if(isset($_POST['form_id']))
    {
        $_POST = mysql_real_escape_array($_POST);
        $_POST = trim_array($_POST);
        $error = "";
    
        if(!isset($_POST['category']) || empty($_POST['category'])) {
            $error = "Please select a category.";
    
        }
    
        if(!isset($_POST['website']) || empty($_POST['website'])) {
            $error.= " Please enter a Website Domain.";
        }
    
        if(!isset($_POST['company']) || empty($_POST['company'])) {
            $error.= " Please enter a Company Name.";
        }
    
        if(!isset($_POST['building']) || empty($_POST['building'])) {
            $error.= " Please enter a Building Name or Number.";
        }
    
        if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
            $error.= " Please enter a Street Name.";
        }
    	
    	    if(!isset($_POST['town']) || empty($_POST['town'])) {
            $error.= " Please enter your Town.";
        }
    
        if(!isset($_POST['state']) || empty($_POST['state'])) {
            $error.= " Please enter a State.";
        }
    
        if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
            $error.= " Please enter a Zip Code/Post Code.";
        }
    
        if(!isset($_POST['country']) || empty($_POST['country'])) {
            $error.= " Please select your country.";
        }
    
        if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
            $error.= " Please enter details about your company.";
        }
    
        if($error == "")
        {
            $sql = "
            UPDATE
                users
            SET
                category = '".$_POST['category']."',
    		linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
                firstname = '".$_POST['firstname']."',
                surname = '".$_POST['surname']."',
                email = '".$_POST['email']."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."'";
    
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
    
           
    
        }

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    What is the error?

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    When I enter the following code anywhere in the code it creates the following error: Parse error: syntax error, unexpected T_STRING

    Im not sure where to place this code or how to apply the id of the member to it. How do I say: $someid = id of user?

    Code:
    WHERE id=$someid";

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I just made $someid up as en example of how you would it. It is up to you to substitute the id number of the record which needs updating.

    For example you could hard code a value 1 and that UPDATE would only update a record with the id 1, if you even had one of course.
    Code:
    WHERE id = 1 ";
    WHERE clause usually goes last.

  7. #7
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It's normally considered bad form to modify the $_POST/$_GET arrays directly. I would suggest moving the values to a new array.

    Maybe something like:
    PHP Code:
    if(isset($_POST['form_id'])) 
    {
         
    $form_data mysql_real_escape_array($_POST);
         
    $form_data trim_array($form_data);
         
    $error "";
        
        
    // etc 
    Keith
    Freelance web developer
    http://www.duvalltech.com/

  8. #8
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Im confused but what you mean. I am using if ($_SESSION['userLoggedIn']) to check if someone is logged in. How do I detect the user number of the member?

    Code:
    $id = (id) $_POST['form_id'];
    function mysql_real_escape_array($t)
    {
        return array_map("mysql_real_escape_string",$t);
    }
    
    function trim_array($ar)
    {
        return array_map("trim",$ar);
    }
    
    if(isset($_POST['form_id']))
    {
        $_POST = mysql_real_escape_array($_POST);
        $_POST = trim_array($_POST);
        $error = "";
    
        if(!isset($_POST['category']) || empty($_POST['category'])) {
            $error = "Please select a category.";
    
        }
    
        if(!isset($_POST['website']) || empty($_POST['website'])) {
            $error.= " Please enter a Website Domain.";
        }
    
        if(!isset($_POST['company']) || empty($_POST['company'])) {
            $error.= " Please enter a Company Name.";
        }
    
        if(!isset($_POST['building']) || empty($_POST['building'])) {
            $error.= " Please enter a Building Name or Number.";
        }
    
        if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
            $error.= " Please enter a Street Name.";
        }
    	
    	    if(!isset($_POST['town']) || empty($_POST['town'])) {
            $error.= " Please enter your Town.";
        }
    
        if(!isset($_POST['state']) || empty($_POST['state'])) {
            $error.= " Please enter a State.";
        }
    
        if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
            $error.= " Please enter a Zip Code/Post Code.";
        }
    
        if(!isset($_POST['country']) || empty($_POST['country'])) {
            $error.= " Please select your country.";
        }
    
        if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
            $error.= " Please enter details about your company.";
        }
    
        if($error == "")
        {
            $sql = "
            UPDATE
                users
            SET
                category = '".$_POST['category']."',
    		linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
                firstname = '".$_POST['firstname']."',
                surname = '".$_POST['surname']."',
                email = '".$_POST['email']."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."',
    			WHERE id=$id";
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
    
           
    
        }
    
    }

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Well I used $id as an example, because most developers use a unique `id` for each row of a table -- you do not have to.

    How else is the database supposed to know which row is to be updated?

    If you do have an id in this table, then you could be setting that id number to the SESSION variable when someone successfully logged in.

    PHP Code:
    //user logs in succesfully;
    $_SESSION['userLoggedIn'] = true// you must be doing similar to this elsewhere
    // so now add this
    $_SESSION['userLoggedInId'] = 23// or whatever unique identifier you have

    // .. later ...

    // retrieved and inserted into the sql string
    // (int) is used to typecast the incoming variable to an integer
    // just in case someone it trying on an SQL injection attack

    // rest of sql statement, plus
    WHERE id " . (int) $_SESSION['userLoggedInId'] ; 

  10. #10
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Im getting a bit confused by this. I am using 'id' as a column name.

    Could you please strip it back to the start.


    Code:
    if ($_SESSION['userLoggedIn']) = $id
    Code:
    $_POST['form_id'];
    function mysql_real_escape_array($t)
    {
        return array_map("mysql_real_escape_string",$t);
    }
    
    function trim_array($ar)
    {
        return array_map("trim",$ar);
    }
    
    if(isset($_POST['form_id']))
    {
        $_POST = mysql_real_escape_array($_POST);
        $_POST = trim_array($_POST);
        $error = "";
    
        if(!isset($_POST['category']) || empty($_POST['category'])) {
            $error = "Please select a category.";
    
        }
    
        if(!isset($_POST['website']) || empty($_POST['website'])) {
            $error.= " Please enter a Website Domain.";
        }
    
        if(!isset($_POST['company']) || empty($_POST['company'])) {
            $error.= " Please enter a Company Name.";
        }
    
        if(!isset($_POST['building']) || empty($_POST['building'])) {
            $error.= " Please enter a Building Name or Number.";
        }
    
        if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
            $error.= " Please enter a Street Name.";
        }
    	
    	    if(!isset($_POST['town']) || empty($_POST['town'])) {
            $error.= " Please enter your Town.";
        }
    
        if(!isset($_POST['state']) || empty($_POST['state'])) {
            $error.= " Please enter a State.";
        }
    
        if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
            $error.= " Please enter a Zip Code/Post Code.";
        }
    
        if(!isset($_POST['country']) || empty($_POST['country'])) {
            $error.= " Please select your country.";
        }
    
        if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
            $error.= " Please enter details about your company.";
        }
    
        if($error == "")
        {
            $sql = "
            UPDATE
                users
            SET
                category = '".$_POST['category']."',
    		linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
                firstname = '".$_POST['firstname']."',
                surname = '".$_POST['surname']."',
                email = '".$_POST['email']."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."';
    WHERE id = " . (id) $_SESSION['userLoggedInId'] ;  
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());

  11. #11
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    When the user logs in, you set $_SESSION['userLoggedIn'] = 1; correct? At the same time, you can add the user's ID to the session variable.
    PHP Code:
    $_SESSION['userID'] = 14// Or whatever the user's ID is 
    Then, in your update SQL you can say something like:
    PHP Code:
    $sql "
             UPDATE
                 users
             SET
                 category     = '"
    .$_POST['category']."',
                 linkcategory = '"
    .str_replace(' ''-',strtolower($_POST['category']))."',
                 firstname    = '"
    .$_POST['firstname']."',
                 surname      = '"
    .$_POST['surname']."',
                 email        = '"
    .$_POST['email']."',
                 website      = '"
    .$_POST['website']."', 
                 company      = '"
    .$_POST['company']."',
                 building     = '"
    .$_POST['building']."',
                 streetname   = '"
    .$_POST['streetname']."',
                 town         = '"
    .$_POST['town']."', 
                 state        = '"
    .$_POST['state']."',
                 postcode     = '"
    .$_POST['postcode']."',
                 aboutcompany = '"
    .$_POST['aboutcompany']."',
                 country      = '"
    .$_POST['country']."'
             WHERE 
                id = " 
    $_SESSION['userID']; 
    Keith
    Freelance web developer
    http://www.duvalltech.com/

  12. #12
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks, its coming together.

    But what do I replace with 14 to read the actual 'id' number from the database?

    If member 1012 is logged in how is that allocated to $_SESSION['userID']?

    Would I use this $_SESSION['userID'] = ($_SESSION['userLoggedIn']) or am I completely wrong?

  13. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by justlukeyou View Post
    Thanks, its coming together.

    But what do I replace with 14 to read the actual 'id' number from the database?

    If member 1012 is logged in how is that allocated to $_SESSION['userID']?

    Would I use this $_SESSION['userID'] = ($_SESSION['userLoggedIn']) or am I completely wrong?
    When the user successfully logs in don't just analyse "logged in" == true or false but grab that users ID.

    Then go to where you create the session.

    As you are starting the session assign the variable containing the user ID to a named session variable, to re-use @kduv ; example:
    PHP Code:
    $_SESSION['userID'] = the variable containing id number of the user
    ....

    then later, when the user updates, your sql can contain that SESSION variable, which will contain the users ID if you have done this properly.

    HINT: use var_dump($_SESSION) to see what these vars contain when things go wrong. Its very easy to incorrectly assume what SESSION holds -- in particular whether it is a string or an integer or boolean etc

  14. #14
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The column is 'id'. Im still completely stuck on how to apply the id to anything, even $_SESSION['userID'] =

    Code:
        $_SESSION['userID'] = 'id';
    
    $id = (int) $_POST['form_id'];
    function mysql_real_escape_array($t)
    {
        return array_map("mysql_real_escape_string",$t);
    }
    
    function trim_array($ar)
    {
        return array_map("trim",$ar);
    }
    
    if(isset($_POST['form_id']))
    {
        $_POST = mysql_real_escape_array($_POST);
        $_POST = trim_array($_POST);
        $error = "";
    
        if(!isset($_POST['category']) || empty($_POST['category'])) {
            $error = "Please select a category.";
    
        }
    
        if(!isset($_POST['website']) || empty($_POST['website'])) {
            $error.= " Please enter a Website Domain.";
        }
    
        if(!isset($_POST['company']) || empty($_POST['company'])) {
            $error.= " Please enter a Company Name.";
        }
    
        if(!isset($_POST['building']) || empty($_POST['building'])) {
            $error.= " Please enter a Building Name or Number.";
        }
    
        if(!isset($_POST['streetname']) || empty($_POST['streetname'])) {
            $error.= " Please enter a Street Name.";
        }
    	
    	    if(!isset($_POST['town']) || empty($_POST['town'])) {
            $error.= " Please enter your Town.";
        }
    
        if(!isset($_POST['state']) || empty($_POST['state'])) {
            $error.= " Please enter a State.";
        }
    
        if(!isset($_POST['postcode']) || empty($_POST['postcode'])) {
            $error.= " Please enter a Zip Code/Post Code.";
        }
    
        if(!isset($_POST['country']) || empty($_POST['country'])) {
            $error.= " Please select your country.";
        }
    
        if(!isset($_POST['aboutcompany']) || empty($_POST['aboutcompany'])) {
            $error.= " Please enter details about your company.";
        }
    
        if($error == "")
        {
            $sql = "
            UPDATE
                users
            SET
                category = '".$_POST['category']."',
    		linkcategory = '".str_replace(' ', '-',strtolower($_POST['category']))."',
                firstname = '".$_POST['firstname']."',
                surname = '".$_POST['surname']."',
                email = '".$_POST['email']."',
                website = '".$_POST['website']."',
                company = '".$_POST['company']."',
                building = '".$_POST['building']."',
                streetname = '".$_POST['streetname']."',
    			town = '".$_POST['town']."',
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."',
    			         WHERE 
              id = " . $_SESSION['userID'];  
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());

  15. #15
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If I use the following:

    Code:
        $_SESSION['userID'] = 'bob';
    I get the following message:

    An error occurred 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 'WHERE id = bob' at line 18

    But I dont know how to turn 'bob' into the actually id number of the member.

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    How would you get the id number of bob out of the database?

  17. #17
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    How would you get the id number of bob out of the database?
    Hi,

    'bob' is just an example. When I try id or $id this doesn't work either.

    I know I am logged in because I have else that reads "You are logged in as".

    But I dont know how to apply the id.

  18. #18
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Show us the output of
    PHP Code:
     var_dump($_SESSION) ; 
    When a user has successfully logged in.

    Then show us the exact SQL statement that checks the login, and then show the exact code you use to start the session.

  19. #19
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    This is the code which is echoed when I use the vardump. I take it its completely wrong.


    array(1) { ["userID"]=> string(3) "bob" }


    This is the code I am using to check if someone is logged in.

    Code:
       
    ob_start(); session_start();
     ini_set(’session.gc_maxlifetime’, 60*60);
    Code:
    	     if ($_SESSION['userLoggedIn'])
    Does this help at all?

  20. #20
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
                state = '".$_POST['state']."',
                postcode = '".$_POST['postcode']."',
                aboutcompany = '".$_POST['aboutcompany']."',
    			country = '".$_POST['country']."',
    			         WHERE 
              id = " . $_SESSION['userID'];  
            $result = mysql_query($sql) or die("An error occurred ".mysql_error());
    
           
    
        }
    I also think I have the commas wrong but what ever I try brings the same result.

  21. #21
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Can anyone advise what the vardump should show?

  22. #22
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    731
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys for your help. Making some progress with this now. When I enter '70' as the id number the page updates only id 70.

    However when I change '70' to 'id' it updates all the profiles in the database and not just 70.


    Code:
       $_SESSION['userID'] = '70';
    
              id = " . $_SESSION['userID'];
    Any suggestions please on how I can replace '70' with the id from the database of the member who is logged in?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •