I ask for help with the syntax of the UPDATE query.

There is a system_contacts table with 22 fields. I'm trying to update some of them, I get an error:

 Warning: mysql_query(): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'company_name' = 'rr', 'company_address' = '', 'company_person'' at line 2 in D:\data\contact_edit.php on line 23 Ошибка - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( 'company_name' = 'rr', 'company_address' = '', 'company_person'' at line 2

Request to contact_edit.php :

  $query = "UPDATE system_contacts SET ( 'company_name' = '".$_POST["company_name"]."', 'company_address' = '".$_POST["company_address"]."', 'company_person' = '".$_POST["company_person"]."', 'phone_1' = '".$_POST["phone_1"]."', 'phone_2' = '".$_POST["phone_2"]."', 'phone_3' = '".$_POST["phone_3"]."', 'phone_4' = '".$_POST["phone_4"]."', 'phone_5' = '".$_POST["phone_5"]."', 'work_time' = '".$_POST["work_time"]."', 'skype' = '".$_POST["skype"]."', 'site' = '".$_POST["site"]."') WHERE 'id_position' = '45' "; $usr = mysql_query($query); // строка 23 if(!$usr) exit ("Ошибка - ".mysql_error());

I rummaged everything I could. What could be the error and where to dig?

    3 answers 3

    Remove the brackets after the word SET and before the word WHERE . You have even written an error message.

    And also, when you debug SQL and you have problems, just output the SQL query on the screen, copy and try to paste it directly. Often there are extra commas, not those quotes, apostrophes instead of back quotes in field names. A bunch of syntax errors when manually forming queries.

    Upd: but you also have SQL injection on your face, since your data from the POST array is not processed in any way, read the prepared statements and start using PDO.

    • Thanks for the tips, the brackets were definitely redundant. (Although, it seems, cleaned them, but still something did not work). PDO will study. So far, I plan to protect $_POST["password"] = htmlentities( get_magic_quotes_gpc()?stripslashes($_POST["password"]):$_POST["password"]) from the injection with: $_POST["password"] = htmlentities( get_magic_quotes_gpc()?stripslashes($_POST["password"]):$_POST["password"]) Is this a normal solution? - 118_64

    That's the way you write, it is better not to do - a guaranteed path to the sql injection. Actually in this case, you seem to have her. If any of the variables in POST contain a single quote, the query will not look exactly the way you think. Most likely, the $_POST["company_address"] variable $_POST["company_address"] contains the left one.

    What to do?

    First, print out the value of the query variable, its value will clarify the situation a little. Also print var_dump($_POST) .

    Second, go to pdo and your requests will be cleaner, more beautiful and safer.

    Thirdly, if you still want to write in the old way, do not forget to escape the lines, but this is outdated, and for php it is not recommended to use this method.

    • Thank you very much! Is it normal to use the solution, at least as a temporary worker: $_POST["password"] = htmlentities( get_magic_quotes_gpc()?stripslashes($_POST["password"]):$_POST["password"]) ? - 118_64

    I show the decision, suddenly someone will come in handy. First, I reduced the request to the form:

     $query = "UPDATE `system_contacts` SET `company_name` = '1' WHERE `id_position` = '45' ";

    The request has passed. Then added the value of $ _POST

     $query = "UPDATE `system_contacts` SET `company_name` = '".$_POST["company_name"]."' WHERE `id_position` = '45' ";

    The request also passed without errors (this does not cancel what was said in the two answers above about the possibility of the injection, now I'm just talking about syntax). After that I added all the remaining values ​​of $ _POST [...], everything works.