Updating a row of data with MySQL

Question

I want to all users to be able to update their about page. Here is the string I am using in my php to query the MySQL update. I am getting an error. What am I doing wrong?

$insert_query= "UPDATE user_info SET bio= $bio_ans, residence= $residence_ans, work= $work_ans WHERE user_id= $user_id"; mysqli_query($connect, $insert_query) or die('error with query1');

Ryan · Accepted Answer · 2012-04-11 22:15:40Z

2

If the update value is not an integer then you need to use quotes around the value.

 $insert_query= "UPDATE user_info SET bio= '$bio_ans', residence= '$residence_ans', work = '$work_ans' WHERE user_id= '$user_id'"; mysqli_query($connect, $insert_query) or die('error with query1');

answered Apr 11, 2012 at 22:15

Ryan

14.7k9 gold badges68 silver badges104 bronze badges

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

2 Comments

Paul Sullivan Over a year ago

that will just insert the string $bio_ans etc I think whereas I think the user is ooking to put ht evalue of the variable by concatenation

Javier Over a year ago

@PaulSullivan: no, the single quotes are ignored by PHP since they're within the double quotes. the whole string is a double-quote one, so variable interpolation will occur. quotes don't nest.

Nathaniel Ford · Accepted Answer · 2012-04-11 22:17:29Z

$insert_query= "UPDATE user_info SET bio= $bio_ans, residence= $residence_ans, work=$work_ans WHERE user_id= $user_id"; mysqli_query($connect, $insert_query) or die('error: $mysqli->error');

The $mysqli->error will let you know specifically what went wrong.

Paul Sullivan · Accepted Answer · 2012-04-11 22:18:06Z

$insert_query= "UPDATE user_info SET bio='" . $bio_ans. "', residence='" . $residence_ans."', work=
'".$work_ans."' WHERE user_id= '$user_id'"; mysqli_query($connect, $insert_query) or die('error with query1');

Note that string concatenation of this type is horrendously insecure and you should be calling parameterised procedures

Community · Accepted Answer · 2017-05-23 12:11:27Z

Besides the lack of quotes (as shown by RPM), the really wrong thing you're doing is to use variable interpolation to create an SQL query.

This is only acceptable for numeric variables, and then only if you have forcibly cast it to a numeric type just before use. String escaping is supposedly safe, but very error-prone (doing it twice, forgetting to add when you add a new value, etc). The slightest slip will open a huge hole for SQL injection, which is the absolute easiest way to crack a web app.

Use prepared statements with parameter bindings and you'll be safe.

Collectives™ on Stack Overflow

Updating a row of data with MySQL

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Related