Incorrect SQL Syntax Error Message

Question

How do I fix the following error? I think the issue is with the INSERT INTO part of my SQL 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

Code:

<?php

if  (isset($_POST['submitted'])){
    include('../mysqli_connect.php');


if (isset($_POST['$pn, $pc,$ps,$pd'])); 

        if(!empty($_POST['product_name'])){
        echo'<p> Successful entry.</p>';
        $pn = mysqli_real_escape_string ($dbc, trim($_POST['product_name']));
        $pn=$_POST['product_name'];
        echo "You chose:".$pn."<br>";}
        else{
            echo '<p>You forgot to enter the product name.</p>';}


        if(!empty($_POST['product_category'])){
        echo'<p> Successful entry.</p>';
        $pc = mysqli_real_escape_string ($dbc, trim($_POST['product_category']));
        $pc=$_POST['product_category'];
        echo "You chose:".$pc."<br>";}
        else{
            echo '<p>You forgot to enter the product category.</p>';}


        if(!empty($_POST['product_supplier'])){
        echo'<p> Successful entry.</p>';
        $ps = mysqli_real_escape_string ($dbc, trim($_POST['product_supplier']));
        $ps=$_POST['product_supplier'];
        echo "You chose:".$ps."<br>";}
        else{
            echo '<p>You forgot to enter the product category.</p>';}   

    if(!empty($_POST['product_description'])){
        echo'<p>Successful entry.</p>';
        $pd=mysqli_real_escape_string($dbc, trim($_POST['product_description']));
        $pd=$_POST['product_description'];
        echo"You chose:".$pd."<br>";}
        else{
            echo'<p>You forgot to enter the product_description.</p>';}

//query: Insert into database

$query = "INSERT INTO `products_catalog` (product_name, product_category, product_supplier, product_description) VALUES ('$pn', '$pc', '$ps','$pd')";

$r=@mysqli_query ($dbc,$query);
if($r){
    echo"<h2> You entered the product name:".$pn."</h2>";   
    echo"<h2>You entered the product category:".$pc."</h2>";
    echo"<h2>You entered the product supplier:".$ps."</h2>";
    echo"<h2>You entered the product description:".$pd."</h2>";}
    else{
        echo'<h1>System Error</h1>
        <p>The product name, category, supplier and description could not be entered. We apologize for any inconvenience. Please try again. If error continues, please contact tech support.</p>';

//Debugging message:
echo '<p>'.mysqli_error($dbc).'<br/><br/> Query:'.$query.'</p>';}

//End of if($r) IF.

mysqli_close($dbc); }

?>

Putting single quotes around your variables makes them the literal string "$pn", not your variable. — echolocation
– echolocation, Commented Mar 21, 2014 at 16:30
Please don't suppress errors like you do in $r = @mysqli_query ($dbc,$query); - it makes it much harder debugging — Beauvais
– Beauvais, Commented Mar 21, 2014 at 16:30
@echolocation, are you referring to '$pn' in the VALUES section? That usage is CORRECT. It's contained within a "-delimited string, so it will be substituted. — Phil Perry
– Phil Perry, Commented Mar 21, 2014 at 16:32
actually you're also double declaring $ps and $pd you're escaping it first and then re-defining it as the direct post input i'm guessing one of the inputs has a " or a ' in it on an unescaped string. Dlete the lines that look like this $pd=$_POST['product_description']; for all your vars and it'll work — Dave
– Dave, Commented Mar 21, 2014 at 16:33

Alan D. · Accepted Answer · 2014-03-21 16:39:22Z

1

$query = "INSERT INTO products_catalog (product_name, product_category, product_supplier, product_description) VALUES ('".$pn."', '".$pc."', '".$ps."','".$pd."')";

It'll probably fix the error.

:)

answered Mar 21, 2014 at 16:39

Alan D.

794 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dave Over a year ago

no it won't if his input var has a ' or a " in it because he's not escaping his input string look closely $ps = mysqli_real_escape_string ($dbc, trim($_POST['product_supplier'])); $ps=$_POST['product_supplier'];

Alan D. Over a year ago

I don't exactly how this function works,but if he put the variables he wants to escape it'll work, am I right? Something like that: $pn = mysql_real_escape_string ($_POST['product_description']);

agmezr Over a year ago

Why don't just use $query =

"INSERT INTO products_catalog (product_name, product_category, product_supplier, product_description) VALUES ('$pn', '$pc', '$ps','$pd')";

Alan D. Over a year ago

Actually I'm not sure if this is going to work, it looks like it'll store exactly the name of the values: $pn, $pc, $ps and $pd. For example: INSERT INTO hi VALUES('HELLO') is equals INSERT INTO hi VALUES('$ps') -- In the first example we store the value 'HELLO' and in the second example the value '$ps'

Dave Over a year ago

no it won't as the overall sql string is in " which means that php will process the string and replace $ps with the value of the var $ps the problem he has is that he is setting $ps (and the others) with an escaped string and then overwriting those variables with the plain $_POST var again so if the post var includes say "don't" then it'll break his sql string as it'll become "... VALUES ('don't', '', '', '')" which is an invalid sql statement as it'll have 3 single quotes in it

|

Dave · Accepted Answer · 2014-03-21 19:30:23Z

Like this

<?php

if  (isset($_POST['submitted'])){
    include('../mysqli_connect.php');


if (isset($_POST['$pn, $pc,$ps,$pd'])); 

        if(!empty($_POST['product_name'])){
        echo'<p> Successful entry.</p>';
        $pn = mysqli_real_escape_string ($dbc, trim($_POST['product_name']));
        //$pn=$_POST['product_name'];  // THIS LINE NOT REQUIRED
        echo "You chose:".$pn."<br>";}
        else{
            echo '<p>You forgot to enter the product name.</p>';}


        if(!empty($_POST['product_category'])){
        echo'<p> Successful entry.</p>';
        $pc = mysqli_real_escape_string ($dbc, trim($_POST['product_category']));
        //$pc=$_POST['product_category'];  // THIS LINE NOT REQUIRED
        echo "You chose:".$pc."<br>";}
        else{
            echo '<p>You forgot to enter the product category.</p>';}


        if(!empty($_POST['product_supplier'])){
        echo'<p> Successful entry.</p>';
        $ps = mysqli_real_escape_string ($dbc, trim($_POST['product_supplier']));
        //$ps=$_POST['product_supplier'];  // THIS LINE NOT REQUIRED
        echo "You chose:".$ps."<br>";}
        else{
            echo '<p>You forgot to enter the product category.</p>';}   

    if(!empty($_POST['product_description'])){
        echo'<p>Successful entry.</p>';
        $pd=mysqli_real_escape_string($dbc, trim($_POST['product_description']));
        //$pd=$_POST['product_description'];  // THIS LINE NOT REQUIRED
        echo"You chose:".$pd."<br>";}
        else{
            echo'<p>You forgot to enter the product_description.</p>';}

//query: Insert into database

$query = "INSERT INTO `products_catalog` (product_name, product_category, product_supplier, product_description) VALUES ('$pn', '$pc', '$ps','$pd')";

$r=@mysqli_query ($dbc,$query);
if($r){
    echo"<h2> You entered the product name:".$pn."</h2>";   
    echo"<h2>You entered the product category:".$pc."</h2>";
    echo"<h2>You entered the product supplier:".$ps."</h2>";
    echo"<h2>You entered the product description:".$pd."</h2>";}
    else{
        echo'<h1>System Error</h1>
        <p>The product name, category, supplier and description could not be entered. We apologize for any inconvenience. Please try again. If error continues, please contact tech support.</p>';

//Debugging message:
echo '<p>'.mysqli_error($dbc).'<br/><br/> Query:'.$query.'</p>';}

//End of if($r) IF.

mysqli_close($dbc); }

?>

Dave, that solved the error and the writing to the database. Thanks so much for the help.

Collectives™ on Stack Overflow

Incorrect SQL Syntax Error Message

Code:

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Code:

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related