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.

 $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.

       $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());



    }

}
?>

 $category = mysql_real_escape_string(trim($_POST['category']));

That is ok.

but then doing this:


 $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:


 $category = mysql_real_escape_string(trim($_POST['category']));
 $someid = 23 ;


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

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.

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());



    }

What is the error?

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?

WHERE id=$someid"; 

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.


WHERE id = 1 ";

WHERE clause usually goes last.

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:


if(isset($_POST['form_id'])) 
{
     $form_data = mysql_real_escape_array($_POST);
     $form_data = trim_array($form_data);
     $error = "";
    
    // etc

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?

$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());



    }

}

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.


//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'] ;

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.

if ($_SESSION['userLoggedIn']) = $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) $_SESSION['userLoggedInId'] ;
        $result = mysql_query($sql) or die("An error occurred ".mysql_error());

       

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.


$_SESSION['userID'] = 14; // Or whatever the user's ID is

Then, in your update SQL you can say something like:


$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'];

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:


$_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

Hi,

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

    $_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());

If I use the following:

    $_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.

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.

Show us the output of


 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.

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.


ob_start(); session_start();
 ini_set(’session.gc_maxlifetime’, 60*60);  
	     if ($_SESSION['userLoggedIn'])

Does this help at all?

            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.