stuck on updating the data in maria db

Question

i want to update the databse by using this code

$personnel_sql = "UPDATE user_detail SET qualification='$qualification', community='$community', pwd='$pwd', gender='$gender', nationality='$nation', religion='$relegion', ex_serviceman='$ex_service', date_joining='$doj', date_of_retirement='$dor', service_field='$serving_area', j&k_factor='$jkfactor', body_mark='$mark_body', aadhar_no='$aadhar_no' WHERE username='$user'";
//  echo $personnel_sql;

  if ($conn->query($personnel_sql) === TRUE) {
    // echo "Record updated successfully";
    $message = 'Your basic data is successfully updated.';
 echo $message;
    // echo "<SCRIPT type='text/javascript'>
      //   alert('$message');
        // window.location.replace(\"index.php?get=update/&&username=$user\");
    // </SCRIPT>";
 } else {
     echo "Error updating record: " . $conn->error;
 }

and i got this error again and agai why

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '&k_factor='No', body_mark='dharmendra', aadhar_no='2147483647' WHERE username='D' at line 1

what is the reason of this error

after doing this

echo $personnel_sql;

it gives me this data

UPDATE user_detail SET qualification='10', community='OBC', pwd='Speak Problem, Both ear hearing problem, Left ear hearing problem , right ear hearing problem, There is no hands, Paralised both hands, Only Left hand, Paralised lrft hand, Less finger in left hand, Only Right Hand, Less finger in right hand, No legs, ', gender='MALE', nationality='INDIA', religion='HINDU', ex_serviceman='', date_joining='Apr 16, 2019', date_of_retirement='Apr 23, 2019', service_field='Army', j&k_factor='No', body_mark='dharmendra', aadhar_no='2147483647' WHERE username='Dharmendra_Soni_1554114509'

You should also prepare your query instead of injecting variables directly into the query- — Qirel, Apr 06 '19 at 10:22
try to echo `$personnel_sql` before submitting so you can see the exact query after the variables where altered and make sure you don't have a `char escape` issue — , Apr 06 '19 at 10:29
A side note: this looks like you inject client provided data right into your sql query. Note that this makes your code vulnerable to sql injection attacks or simple crashes due to unexpected characters breaking the syntax of the resulting queries. Please read about the benefits of using the combination of "prepared statements" and "parameter binding". — arkascha, Apr 06 '19 at 10:57

Tom Udding · Answer 1 · 2019-04-06T11:00:36.993

Your column j&k_factor uses a special character, namely the & (ampersand) (see MySQL documentation and this answer for more information). You are required to quote such identifiers using a back-tick `. It will look like `j&kfactor`='$jkfactor'. You can even quote all identifiers in your query.

I would also highly recommend that you implement some form of prepared statements. See comment by @arkascha why you should use it.

stuck on updating the data in maria db

1 Answers1