PHP SQL Update Query not working

Question

I have been trying to create a simple form to update details on a database, the PHP code is below:

// UPDATE THE DATABASE RECORDS //
$update = $_GET['update'];
if($update == "true"){
    $setDetails="UPDATE users SET email='{$_POST['email']}', api_key='{$_POST['api_key']}', api_secret='{$_POST['api_secret']}' WHERE username='{$_POST['username']}'";
    if(mysql_query($setDetails)){
        $updatemsg = '<div class="alert alert-success"><a href="#" class="close" data-dismiss="alert">×</a><strong>Success!</strong> Your details have been updated in our database.</div>';
    }else{
        $updatemsg = '<div class="alert alert-error"><a href="#" class="close" data-dismiss="alert">×</a><strong>Failure!</strong> Your details could not be updated in our database. Please try again later or contact us if this keeps happening.</div>';
    }
}else if($update == "false"){
    $updatemsg = '<div class="alert alert-success"><a href="#" class="close" data-dismiss="alert">×</a><strong>Success!</strong> Your changed were discarded.</div>';
}
// UPDATE THE DATABASE RECORDS //

// GET THE DATABASE RECORDS //
$getDetails="SELECT * FROM users WHERE username='$username'";
$details=mysql_query($getDetails);
$num=mysql_numrows($details);
if($num != 0){
    $new_user = false;
    $username=mysql_result($details,0,"username");
    $email=mysql_result($details,0,"email");
    $subscription_type=mysql_result($details,0,"subscription_type");
    $subscription_date=mysql_result($details,0,"subscription_date");
    $api_key=mysql_result($details,0,"api_key");
    $api_secret=mysql_result($details,0,"api_secret");
    setcookie("api_key", $api_key, time()+50000);
    setcookie("api_secret", $api_secret, time()+50000);
}else{
    $new_user = true;
}
// GET THE DATABASE RECORDS //

The variables defined when the database records are fetched are then used to populate a HTML form:

<form action="?update=true" method="POST">
<h2>Your Details</h2>
<input id="username" name="username" type="text" placeholder="" disabled="true" class="input-xlarge" value="<?=$username?>">
<input id="email" name="email" type="text" placeholder="" class="input-xlarge" value="<?=$email?>">
<input id="subscription_type" name="subscription_type" type="text" placeholder="" disabled="true" class="input-xlarge" value="<?=$subscription_type?>">
<input id="subscription_date" name="subscription_date" type="text" placeholder="" disabled="true" class="input-xlarge" value="<?=$subscription_date?>">
<input id="api_key" name="api_key" type="text" placeholder="" class="input-xlarge" value="<?=$api_key?>">
<input id="api_secret" name="api_secret" type="text" placeholder="" class="input-xlarge" value="<?=$api_secret?>">
<button type="submit" class="btn btn-success" id="saveChanges"><i class="icon-ok icon-white"></i> Save Changes</button> <a href="?update=false" class="btn btn-danger" id="discardChanges"><i class="icon-remove icon-white"></i> Discard Changes</a>
</form>

When the page is loaded first time, the form is populated with no problems, but when it is edited and submitted $updatemsg is the 2nd one (Success) but there are no changes to the data in the database. Any ideas?

You will probably (rightfully) get several comments telling you that you are vulnerable to an injection attack because you are putting your $_POST variables directly into your query. Lookup mysql injection, you need to be aware of this if you are doing database programming. — JPR
– JPR, Commented Jul 4, 2013 at 6:49
@JPR I do know that I am vulnerable to injection, and I intent to sort that out before/if this code ever goes live - at the moment it is just me practising. — Benedict Lewis
– Benedict Lewis, Commented Jul 4, 2013 at 6:50
why your form fields are disabled??? this is the one reason that values are not posted. — Muhammad
– Muhammad, Commented Jul 4, 2013 at 6:51
@Nomi The fields that are disabled are values that cannot be edited, they are just displayed. As you can see, only the inputs that are not disabled are posted. — Benedict Lewis
– Benedict Lewis, Commented Jul 4, 2013 at 6:55

JPR · Accepted Answer · 2013-07-04 06:53:42Z

2

It looks like it's failing because the quotes and brackets are incorrectly used. But what you need to do is something like:

$email = mysql_real_escape_string($_POST['email']);

....

"UPDATE users SET email='$email'.....

And so forth, and so on. If not that, in the else part call mysql_error() and it will tell you if the query is failing.

answered Jul 4, 2013 at 6:53

JPR

8694 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benedict Lewis Over a year ago

That fixed it, thanks! Just out of interest, does that partly fix the SQL injection issue?

JPR Over a year ago

Short answer, yes, but I believe there are even ways to get around that, so in one more word no. Best practice would be using PDO bound parameters - ditch the mysql_() functions and read up on PDO. As far as I know it eliminates the issue of injection completely. I think it is safe to say that using PDO / prepared statements is the "right" way to properly use SQL with PHP.

Conrad Lotz · Accepted Answer · 2013-07-04 06:55:23Z

0

Firstly I would declare the api secrets in a constant file instead of through a http post on a page. Try the following to get the update to work assuming the post values are present

$setDetails="UPDATE users SET email='".$_POST['email']."', api_key='".$_POST['api_key']."', api_secret='".$_POST['api_secret']."' WHERE username='".$_POST['username']."'";

answered Jul 4, 2013 at 6:55

Conrad Lotz

8,9533 gold badges25 silver badges28 bronze badges

1 Comment

Benedict Lewis Over a year ago

The API secrets are stored in a database and are only entered/changed through the HTTP Post.

Collectives™ on Stack Overflow

PHP SQL Update Query not working

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related