Query Failed!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Question

I am trying to put this into the database. And I am getting an unexpected error, however, saying:

Query Failed! 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 'order(Order_Date,Order_Time,Delivery_Charge,Delivery_Fname,Delivery_Lname,Delive' at line 1.

Here's my PHP:

<?php
//error_reporting(E_ERROR | E_PARSE);  
include("includes/db.php");
include("includes/functions.php");

if($_REQUEST['command']=='update')
{
$date     = date('Y-m-d');
$time     = time('H:i:s');
$charge   = $_REQUEST['ocharge'];
$fname    = $_REQUEST['ofname'];
$lname    = $_REQUEST['olname'];
$mobile   = $_REQUEST['omobile'];
$add1     = $_REQUEST['oadd1'];
$add2     = $_REQUEST['oadd2'];
$postcode = $_REQUEST['opostcode'];
$state    = $_REQUEST['ostate'];
$country  = $_REQUEST['ocountry'];
$weight   = $_REQUEST['oweight'];
$credit   = $_REQUEST['ocredit'];
$pin      = $_REQUEST['opin'];
$city     = $_REQUEST['ocity'];



    $result=mysql_query("insert into order(Order_Date,Order_Time,Delivery_Charge,Delivery_Fname,Delivery_Lname,Delivery_HP,Delivery_Street1,Delivery_Street2,Delivery_Postcode,Delivery_State,Delivery_Country,Total_Weight,Credit_No,Pin_No,Delivery_City) values ('$date',$time,$charge,'$fname','$lname',$mobile,'$add1','$add2',$postcode,'$state','$country',$weight,$credit,$pin,'$city')");

    if($result === FALSE)
    {
            die("Query Failed!".mysql_error().$result);
    }

    $orderid=mysql_insert_id();   


    $max=count($_SESSION['cart']);
    for($i=0;$i<$max;$i++)
    {
        $pid=$_SESSION['cart'][$i]['productid'];
        $q=$_SESSION['cart'][$i]['qty'];
        $price=get_price($pid);

        mysql_query("insert into order_detail (Order_ID,Product_ID,Order_Quantity,Sub_Total) values ('$orderid','$pid','$q','$price')");    
    }

    die('Thank You! your order has been placed!');

  }
  ?>

What is wrong with the query?

When reporting a problem of this kind, it helps to also `echo` out the SQL and supply it in your question (as well as the PHP code) so we can see where the error is. However, it is good that you have supplied the PHP, that reveals you have several SQL injection vulnerabilities in the query. Escaping your user inputs would be a good start, but parameterisation (with a better database library such as PDO) would be even better. — halfer, Aug 31 '13 at 08:47
For each use of `$_REQUEST` (or any user input), run each one through [`mysql_real_escape_string`](http://www.php.net/manual/en/function.mysql-real-escape-string.php). Ensure you are connected to the database when you use this function, since it needs a database connection to work fully. Read up on "SQL injection" too - it is a vulnerability that permits malicious users to execute unauthorised SQL on your database (e.g. deleting rows). — halfer, Aug 31 '13 at 08:54
possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — michaelb958--GoFundMonica, May 07 '14 at 00:15

Amal Murali · Accepted Answer · 2013-08-31T08:21:59.447

6

ORDER is a reserved keyword. So, you'll need to escape it in backticks, like so:

INSERT INTO `order` ...

Not using reserved keywords in your query would be the better solution, but escaping them with backticks works, too.

edited Aug 31 '13 at 08:21

answered Aug 31 '13 at 08:16

Amal Murali

75,622
18
128
150

I'm try to escape it in backticks already,but still have same problem. – Jess Tan Aug 31 '13 at 08:31
Query Failed!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 ''order' (Order_Date,Order_Time,Delivery_Charge,Delivery_Fname,Delivery_Lname,Del' at line 1 – Jess Tan Aug 31 '13 at 08:45
@JessTan he said backtick not single quote. this one `\`` not that one `'` – Prix Aug 31 '13 at 08:56
@JessTan: you're probably confusing backticks with single quotes. See the [**difference**](http://jsbin.com/upuwaGi/1). – Amal Murali Aug 31 '13 at 09:14
Opps..sorry for the confuse,and finally i get it,thanks a lot,really thanks a lot for your all help. – Jess Tan Aug 31 '13 at 09:23

halfer · Answer 2 · 2013-09-04T22:04:44.033

Here's a few debugging tips. Rather than this:

$result=mysql_query("insert into order(Order_Date,Order_Time,Delivery_Charge,Delivery_Fname,Delivery_Lname,Delivery_HP,Delivery_Street1,Delivery_Street2,Delivery_Postcode,Delivery_State,Delivery_Country,Total_Weight,Credit_No,Pin_No,Delivery_City) values ('$date',$time,$charge,'$fname','$lname',$mobile,'$add1','$add2',$postcode,'$state','$country',$weight,$credit,$pin,'$city')");

Always do this:

$sql ="insert into order(Order_Date,Order_Time,Delivery_Charge,Delivery_Fname,Delivery_Lname,Delivery_HP,Delivery_Street1,Delivery_Street2,Delivery_Postcode,Delivery_State,Delivery_Country,Total_Weight,Credit_No,Pin_No,Delivery_City) values ('$date',$time,$charge,'$fname','$lname',$mobile,'$add1','$add2',$postcode,'$state','$country',$weight,$credit,$pin,'$city')";
$result = mysql_query($sql);

This makes it trivial, when working on your code, to also do this:

echo htmlentities($sql);

That will show you the query you are working with (and not the PHP code that builds the query, which may hide awkward characters inside your values).

Finally, consider writing your code like this:

$sql = "
    INSERT INTO order (
        Order_Date, Order_Time, Delivery_Charge,
        Delivery_Fname, Delivery_Lname, Delivery_HP,
        Delivery_Street1, Delivery_Street2, Delivery_Postcode,
        Delivery_State,  Delivery_Country, Total_Weight,
        Credit_No, Pin_No, Delivery_City
    )
    VALUES (
        '$date', $time, $charge,
        '$fname', '$lname', $mobile,
        '$add1', '$add2', $postcode,
        '$state', '$country', $weight,
        $credit, $pin, '$city'
    )
";
$result = mysql_query($sql);

I've upper-cased the SQL and formatted the query to make it readable, so you can be sure you are supplying the right value for the right column. No horizontal scrolling (in your editor or on our screens) is now necessary.

As indicated in the comments, if you take this approach to database inserts, you need to ensure that all of your values are correctly escaped, especially if they come from user input. However, parameterisation is a better way to do this, and note that the "mysql" library is now deprecated.

Addendum: looking at the query, I would say that you need apostrophes around $time, $mobile and $postcode (assuming they are all strings). I presume $charge and $weight are numeric and so therefore do not need quoting.

Query Failed!You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

2 Answers2

Linked