UPDATE .. SET .. WHERE SQL Query Failing

Question

I am trying to sort out a query on a web app that I'm making, which keeps failing and I have no idea why! The code is below:

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

Any idea's, help or tips? Note that further down my web app I have SELECT * FROM users WHERE username='$username' which works fine so there is no issue with the database connection.

What do you mean by failing? Do you get an SQL error? Please also note, that you did not escape your username, making SQL Injection possible. — Chris, Jul 04 '13 at 06:21
Among other SQL injection-related things, you should quote that `username=$_POST['username']` field at the end. — Jimmy Sawczuk, Jul 04 '13 at 06:22
@Chris I know about the security issues, I plan to fix them next. What happens is that $updatemsg returns the second value (The error message). — Benedict Lewis, Jul 04 '13 at 06:23
if(mysql_query($setDetails)){ is not the right way to check if update was successfull; u shud use mysql_affected_rows(). But while using it there are some precautions required. Check here for that: http://stackoverflow.com/questions/9397291/mysql-affected-rows-returns-0-for-update-statement-even-when-an-update-actuall/9871672#9871672 — Mrigesh Raj Shrestha, Jul 04 '13 at 06:24
@JimmySawczuk That fixed it, thanks. If you want to post an answer I would be more than happy to mark it as accepted. — Benedict Lewis, Jul 04 '13 at 06:24
@BenedictLewis Irfan DANISH covered it pretty well, he can keep the accept. :) — Jimmy Sawczuk, Jul 04 '13 at 17:26
Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — mickmackusa, Dec 25 '17 at 12:17

score 3 · Accepted Answer · answered Jul 04 '13 at 06:22

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

Details: Your code:

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

Correct Code:

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

Missing ' around username string.

I cannot accept the answer for another 5 minutes, you must wait 10 minutes after asking your question to mark an answer as correct. — Benedict Lewis, Jul 04 '13 at 06:30

score 1 · Answer 2 · answered Jul 04 '13 at 06:23

1

Try like this.Put quotes for the username

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

And try to avoid mysql_* statements due to they are deprecated.Instead use mysqli_* statements or PDO statements

answered Jul 04 '13 at 06:23

GautamD31

28,552
10
64
85

This suggested solution is not safe from injection attacks. – mickmackusa Dec 25 '17 at 12:11

UPDATE .. SET .. WHERE SQL Query Failing

2 Answers2