PHP SQL语法错误MYSQL UPDATE [重复]

Question

This question already has an answer here:

• When to use single quotes, double quotes, and backticks in MySQL 12 answers

So for a long time this code worked but now all of the sudden i get this error:

Error: 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 ''j_users' SET patient = '', year = '', gender = '', age = '', height = 'Select a' at line 1

HELP!

define('DB_NAME', 'DATABASE');
define('DB_USER', 'USERNAME');
define('DB_PASSWORD', 'PASSWORD');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
    die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}



$value = htmlspecialchars($_POST['patient']);
$value4 = htmlspecialchars($_POST['year']);
$value5 = htmlspecialchars($_POST['gender']);
$value6 = htmlspecialchars($_POST['age']);
$value7 = htmlspecialchars($_POST['height']) . '.' . htmlspecialchars($_POST['height_inch']);
$value8 = htmlspecialchars($_POST['weight']);
$value9 = htmlspecialchars($_POST['foot_length']);
$value10 = htmlspecialchars($_POST['sheight']) . '.' . htmlspecialchars($_POST['sheight1']);
$value11 = htmlspecialchars($_POST['Amputation']);

$value13 = htmlspecialchars($_POST['Side']);

$value16 = htmlspecialchars($_POST['Flesh']);

$value18 = htmlspecialchars($_POST['Activity']);

$value21 = htmlspecialchars($_POST['practitioner']);
$value22 = htmlspecialchars($_POST['phone']);
$value23 = htmlspecialchars($_POST['email']);

$value24 = htmlspecialchars($_POST['Account']);
$value25 = htmlspecialchars($_POST['companyname']);
$value26 = htmlspecialchars($_POST['streetaddress']);
$value27 = htmlspecialchars($_POST['city']);
$value28 = htmlspecialchars($_POST['state']);
$value29 = htmlspecialchars($_POST['zip']);
$value30 = htmlspecialchars($_POST['companyname2']);
$value31 = htmlspecialchars($_POST['streetadress2']);
$value32 = htmlspecialchars($_POST['city2']);
$value33 = htmlspecialchars($_POST['state2']);
$value34 = htmlspecialchars($_POST['zip2']);
$value35 = htmlspecialchars($_POST['foot']);

$value39 = htmlspecialchars($_POST['purchaseorder']);
$value40 = htmlspecialchars($_POST['radio']);
$value41 = htmlspecialchars($_POST['lightflesh2']);
$value42 = htmlspecialchars($_POST['darkfleah2']);
$value43 = htmlspecialchars($_POST['foamcalf']);
$value44 = htmlspecialchars($_POST['additional']);
$value45 = htmlspecialchars($_POST['Sock1']);
$value46 = htmlspecialchars($_POST['Sock2']);
$value47 = htmlspecialchars($_POST['Sock3']);
$value48 = htmlspecialchars($_POST['day']);

//$sql = "INSERT INTO order_form (patient, newamputee, yearamputee, year, gender, age, height, weight, foot_length, sheight, ak, bk, left1, right1, bilateral, light_flesh, dark_flesh, k2, k3, k4, k4_extrme, practitioner, email, Account, companyname, streetaddress, city, state, zip, companyname2, streetaddress2, city2, state2, zip2, UltraStride, ActiveStride, NaturalStride, K2_ComfortStride, purchaseorder, radio, lightflesh2, darkfleah2, foamcalf, additional, Sock1, Sock2, Sock3, ground, thirdday, twoday, nextday) VALUES ('$value', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10', '$value11', '$value12', '$value13', '$value14', '$value15', '$value16', '$value17', '$value18', '$value19', '$value20', '$value21', '$value22', '$value23', '$value24', '$value25', '$value26', '$value27', '$value28', '$value29', '$value30', '$value31', '$value32', '$value33', '$value34', '$value35', '$value36', '$value37', '$value38', '$value39', '$value40', '$value41', '$value42', '$value43', '$value44', '$value45', '$value46', '$value47', '$value48', '$value49', '$value50', '$value51')";


$update = "UPDATE 'j_users'
SET patient = '$value', year = '$value4', gender = '$value5', age = '$value6', height = '$value7', weight = '$value8', foot_length = '$value9', sheight = '$value10', Amputation = '$value11', Side = '$value13', Flesh = '$value16', Activity = '$value18', practitioner='$value21', phone='$value22', email='$value23', Account = '$value24', companyname = '$value25', streetadress='$value26', city='$value27', state='$value28', zip='$value29', companyname2='$value30', streetadress2='$value31', city2='$value32', state2='$value33', zip2='$value34', foot='$value35', purchaseorder='$value39', radio='$value40', lightflesh2='$value41', darkfleah2='$value42', foamcalf='$value43', foamcalf='$value44', Sock1='$value45', Sock2='$value45', Sock3='$value46', day='$value47'

WHERE user_login = '" . $user . "'";

if (!$update) {
    die('Invalid query: ' . mysql_error());
}

mysql_query($update, $link);

if (!mysql_query($update)) {
die('Error: ' . mysql_error()) ;
mysql_close();
}

</div>

Answer 1

The immediate cause of the error, as pointed out by Uueerdo in the comment is the incorrect symbol (single-quote instead of a backtick) in the quoting of the name of the table - which in this case does not need to be quoted at all as it is fixed and contains no special characters.

There are other issues in the code which we will leave alone for now as they do not immediately affect the issue, but I will update the answer if OP is interested in other things that would be good to fix.

UPDATE - things to fix:

• As pointed out by Drew and Uueerdo in the comments, migrate from the deprecated mysql_ interface to mysqli_ or PDO.
• The values entered by the user should be escaped with mysql_real_escape_string() (with the current interface), mysqli_escape_string() or via PDO parameter holders (?) depending on the interface, but not with htmlspecialchars(). If HTML escaping is needed, it should be done immediately before the HTML is to be displayed, not at the time it is stored in the database.
• Note that most of your input names match the database column names. Thus you might be better off fetching the fields from the database via SHOW FIELDS once into a hard-coded array, editing it to exclude the irrelevant ones (another option to fetch it dynamically and fix up the array once it is fetched), and adding some logic to deal with the exceptions like height and height_inches as you iterate through the array and generate your query in a loop. The code thus becomes more flexible and easier to maintain.
• Create some wrapper interface for your database access rather than directly accessing MySQL API. This way should a need arise to change the interface (e.g. mysql_ to mysqli) it is a matter of fixing a few calls in just one module rather than a major code change. You are also able to add things like query logging, automatic query EXPLAIN in trace mode, performance timing, and whatever else you might think of with regard to your queries, rather easy.