MYSL error 1064, will update a record when the id is not the same.

Question

This php code is supposed to be used to update a table called contacts at a where the id= selected id. The variable $numresults checks that the id exists and sets the variable to a number, which should always be 1. Then the update statement updates the record where the id is. I get a syntax error 1064 when I run the code. When I run the code and change the ID after it has been selected it seems to work with no error. Where is my syntax going wrong?

<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>Untitled Document</title>
</head>

<body>

<?php
$hi="hi";
echo $hi;
$servername = "localhost";
$username= "root";
$dbpassword = "";
$dbname="test";
$db=mysqli_connect($servername, $username, $dbpassword, $dbname);
if(!$db){
 die("could not connect:".mysqli_connect_error()); 
} else{

  
  $id=test_input($_POST['id']);
  $firstname=test_input($_POST['firstname']);
  $lastname=test_input($_POST['lastname']);
  $pcat=test_input($_POST['pcat']);
  $congroup=test_input($_POST['congroup']);
  $cattype=test_input($_POST['cattype']);
  $company=test_input($_POST['company']);
  $position=test_input($_POST['position']);
  $email=test_input($_POST['email']);
  $website=test_input($_POST['website']);
  $phone= test_input($_POST['phone']);
  $mphone=test_input($_POST['mphone']);
  $wphone=test_input($_POST['wphone']);
  $fax=test_input($_POST['fax']);
  $add1=test_input($_POST['add1']);
  $add2=test_input($_POST['add2']);
  $city=test_input($_POST['city']);
  $state=test_input($_POST['state']);
  $zip=test_input($_POST['zip']);
  $country=test_input($_POST['country']);
  $reference=test_input($_POST['reference']);
  $entrydate=test_input($_POST['entrydate']);
  $enteredby=test_input($_POST['enteredby']);
  $notes=test_input($_POST['notes']);
   

  $rtninfo = updateContact($db, $id, $firstname, $lastname, $pcat, $congroup ,$cattype, $company, $position,$email, $website, $phone, $mphone, $wphone, $fax, $add1, $add2, $city, $state, $zip, $country, $reference, $entrydate, $enteredby, $notes);

  if ($rtninfo == "ContactNotFound")
  {
   print "<p style='color: red'>Contact Not Found - Check SSN</p>";
  } else {
   if ($rtninfo == "NotUpdated")
   {
    print "<p style='color: red'>Contact Not Updated</p>";
   } else {
    print "<p style='color: green'>Contact has been Changed";
   }
  }
 }

?> 

<?php

function updateContact($db, $id, $firstname, $lastname, $pcat, $congroup ,$cattype, $company, $position,$email, $website, $phone, $mphone, $wphone, $fax, $add1, $add2, $city, $state, $zip, $country, $reference, $entrydate, $enteredby, $notes)
{

    //First check if SSN exists

 $sql_statement  = "SELECT id, firstname, lastname, pcat, congroup, cattype, company, position, email, website, phone, mphone, wphone, fax, add1, add2, city, state, zip, country, reference, entrydate, enteredby, notes ";
 $sql_statement .= "FROM contacts ";
 $sql_statement .= "WHERE id = '".$id."' ";
 $result = mysqli_query($db, $sql_statement);  // Run SELECT

 $numresults = mysqli_num_rows($result);
 


 // If SSN exists then Update the Contact Info

 if ($numresults > 0)
 {
  $statement="UPDATE CONTACTS ";
   "SET firstname='".$firstname."'";
   /*"lastname='".$lastname."',";
   "pcat='".$pcat."',";
   "congroup='".$congroup."',";
   "cattype='".$cattype."',";
   "company='".$company."',";
   "position='".$position."',";
   "email='".$email."',";
   "website='".$website."',";
   "phone='".$phone."',";
   "mphone='".$mphone."',";
   "wphone='".$wphone."',";
   "fax='".$fax."',";
   "add1='".$add1."',";
   "add2='".$add2."',";
   "city='".$city."',";
   "state='".$state."',";
   "zip='".$zip."',";
   "country='".$country."',";
   "reference='".$reference."',";
   "entrydate='".$entrydate."',";
   "enteredby='".$enteredby."',";
   "notes='".$notes."' ";*/
  "WHERE id='".$id."' ";
  
  
 

  $result = mysqli_query($db, $statement);

  if ($result)
  {
   return $id;
  } else {
   $errno = mysqli_errno($db);

   echo("<h4>MySQL No: ".mysqli_errno($db)."</h4>");
   echo("<h4>MySQL Error: ".mysqli_error($db)."</h4>");
   echo("<h4>SQL: ".$statement."</h4>");
   echo("<h4>MySQL Affected Rows: ".mysqli_affected_rows($db)."</h4>");

   return 'NotUpdated';
  }
 } else {

  return 'ContactNotFound';
 }
 mysqli_close($db);
}
function test_input($data){
  
 $data=trim($data);
 $data=stripslashes($data);
 $data=htmlspecialchars($data);
 return $data; 
 }
?>
</body>
</html>

[Little Bobby](http://bobby-tables.com/) says **[you are at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/)**. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](https://stackoverflow.com/q/5741187)** is not safe! I recommend `PDO`, which I [wrote a function for](https://stackoverflow.com/a/45514591) to make it extremely **easy**, very **clean**, and way more **secure** than using non-parameterized queries. — GrumpyCrouton, Aug 15 '17 at 15:24
I would rethink this whole piece of code. You can narrow that down to simply using a prepared statement. — Funk Forty Niner, Aug 15 '17 at 15:33

Nagesh Katna · Answer 1 · 2017-08-15T15:48:44.200

0

You are ending you $statement line at the end with a ; and started the next line which doesnt seem to mean anything or attached to the previous line. I think the problem is in that. You should either don't end the line with a ; or try using $stament.="next line"; something like this

$statement="UPDATE CONTACTS SET firstname='".$firstname."'";
$statement.="WHERE id='".$id."' ";

And as said by @GrumpyCrouton you are open for Sql Injections too. So think about using the prepared statements like MySQLi_* OR PDO to get it fixed.

edited Aug 15 '17 at 15:48

answered Aug 15 '17 at 15:38

Nagesh Katna

679
2
7
27

Please add a warning that this is vulnerable to [SQL Injection Attacks](https://stackoverflow.com/q/60174/). – GrumpyCrouton Aug 15 '17 at 15:40
Yupp forgot about that. Edited now – Nagesh Katna Aug 15 '17 at 15:44
I took off my -1, but instead of saying `(PDO)`, it's beneficial to say `MySQLi_* OR PDO`, as there is more than just PDO. :) – GrumpyCrouton Aug 15 '17 at 15:46
Ahh, I see, I started my $statement with the update and then used a new line to set the variable without concatenating it again. Thanks. I will look into the PDO and mysqli, a little more. I thought I was using mysqli - but it isn't in a prepared statement. Thanks again – Pari Baker Aug 15 '17 at 15:55

MYSL error 1064, will update a record when the id is not the same.

1 Answers1