PHP CRUD Unknown column in 'where clause'

Question

I am creating a CRUD page. I've been able to insert data into my database, with the following code:

    $query="INSERT INTO user(USER_ID, PASSWORD, FIRST_NAME, LAST_NAME, CONTACT_NO, SHIPPING_ADDRESS, BILLING_ADDRESS, EMAIL) VALUES('$user_id', '$password', '$first_name', '$last_name', '$contact_no', '$shipping_address', '$billing_address', '$email')";
if(mysqli_query($con, $query))
{
echo "<center><font color=#FF0000>Record Inserted!</font></center><br>";
}
else{printf("error: %s\n", mysqli_error($con));}
    }
}

however, i am unable to delete nor edit the data as the "Unknown column in 'where clause' " error will appear. i've tried finding for solutions on google but they did not work. can someone help to see if there are any errors in my code?

The following codes are for editting data in the database:

$query="UPDATE user SET USER_ID='$user_id' , PASSWORD='$password', FIRST_NAME='$first_name', LAST_NAME='$last_name', CONTACT_NO='$contact_no', SHIPPING_ADDRESS='$shipping_address', BILLING_ADDRESS='$billing_address', EMAIL='$email' WHERE USER_ID=".$_POST['user_id'];
if(mysqli_query($con, $query))
{
    echo "<center><font color=#FF0000>Record Updated!</font></center><br>";
}
else{printf("error: %s\n", mysqli_error($con));}
}

The following codes are for deleting data in the database:

    if(isset($_GET['operation'])){
    if($_GET['operation']=="delete")
    {
        $query="DELETE FROM user WHERE USER_ID=".$_GET['user_id'];  
        if(mysqli_query($con, $query))
        {
            echo "<center><font color=#FF0000>Record Deleted!</font></center><br>";
        }
        else{printf("error: %s\n", mysqli_error($con));}
    }
}

How should we know what your problem is? The error message is quite obvious... the column name you specified isn't there. Why isn't it? Who knows such things other than yourself. Also, what you are doing is terribly insecure. You are **wide open** to SQL injection attacks, and **you will be hacked** if you haven't been already. learn to use prepared/parameterized queries with PDO or similar to avoid this problem entirely. — Brad, May 20 '13 at 03:19
Why update USER_ID when your WHERE condition is based on it? Are you changing the id? — shapeshifter, May 20 '13 at 03:19
what happens is someone posts `1;truncate table user;` as the USER_ID. http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php/60496#60496 — Orangepill, May 20 '13 at 03:21
@shapeshifter, I hardly find helping someone somehow being a dick. I'm trying to help Bob understand what his wrong with his question so that he can ask a better one in the future and get an answer that is actually useful to him. Plus, warnings about SQL injections are seemingly almost always ignored if you don't point out what the potential consequences are. Regarding my comment on php NoOb's post, I see no problem there either. What's wrong with pointing out that an answer is terribly insecure and leading folks down the wrong path? — Brad, May 20 '13 at 03:31

score 4 · Accepted Answer · answered May 20 '13 at 03:57

I guess your user_id coulmn type is varchar, so your query string throws a result like UPDATE ... WHERE user_id=id1; then Mysql assumes that id1 is a column and throws unknown column. so better to use quote in your code like this UPDATE ... WHERE user_id='".$_POST['user_id']."'

samayo · Answer 2 · 2013-05-20T03:32:27.713

First of all, this is how you should update your data when you are using mysqli

$stmt = $mysqli->prepare("UPDATE name SET firstname = ?, 
   lastname= ?,  
   WHERE id = ?");

$stmt->bind_param('id',
   $_POST['firstname'],
   $_POST['lastname']);
$stmt->execute(); 
$stmt->close();

That is how, mysqli queries should be built, for better performance and security. So, better re-learn what you know about mysqli

Now, as far as your code goes(asside from the above issue):

Problem seems the line here WHERE USER_ID=".$_POST['user_id'];

You started your query with "UPDATE user SET USER_ID='$user_id', and thus must maintain and take care your quote sequences if you start with double " quotes you must end the same, and include ;semicolon at the end.

So, that leaves us to changing the your last code to WHERE USER_ID='.$_POST['user_id'].'";

So, replace it

"UPDATE user SET USER_ID='$user_id' , PASSWORD='$password', FIRST_NAME='$first_name', LAST_NAME='$last_name', CONTACT_NO='$contact_no', SHIPPING_ADDRESS='$shipping_address', BILLING_ADDRESS='$billing_address', EMAIL='$email' WHERE USER_ID=".$_POST['user_id'];

With

"UPDATE user SET USER_ID='$user_id' , PASSWORD='$password', FIRST_NAME='$first_name', LAST_NAME='$last_name', CONTACT_NO='$contact_no', SHIPPING_ADDRESS='$shipping_address', BILLING_ADDRESS='$billing_address', EMAIL='$email' WHERE USER_ID='.$_POST['user_id'].'";

If that's what you believe, then you should probably delete your answer entirely and go read up on the subject. Why would your database layer have any bearing on how you form your query when you create your query manually? mysqli/mysql/PDO... doesn't matter if you just concatenate data into your query. You must use prepared queries to avoid SQL injection, by fundamentally separating the data from the command. — Brad, May 20 '13 at 03:21
@Brad ohh, messed up BIG time. I didn't realized about the prepared statements, I actually use PDO, so not so familiar with mysqli. — samayo, May 20 '13 at 03:24
@Brad check it out, and feel free to modify it, seems like your `mysqli` wizard, nice to meet u — samayo, May 20 '13 at 03:35

PHP CRUD Unknown column in 'where clause'

2 Answers2