An error in SQL syntax

Question

The purpose of my code is extract data from an CSV file and upload it into my DB.

I can extract all attributes from each line from the file but it keeps showing an error on the sql query.

Here is my PHP file:

<?
$row = 1;
$server="XXXXX";
$user="XXXX";
$password="XXXX";
$db="XXXX";
mysql_connect($server,$user,$password) or die('erreur au serveur');
mysql_select_db($db) or die('erreur db');
if (($handle = fopen('XXXX/articles.csv','r+')) !== FALSE) {
while (($data = fgetcsv($handle,";")) !== FALSE) {
    $num = count($data);
    //echo "<p> $num champs à la ligne $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
        $produit =$data[$c];
        $att = explode(";", $produit);
 $prod = $att[0]; echo 'id = '.$prod .'<br/>';
 $code = $att[1];  echo 'code = '.$code.'<br/>';
 $nom = $att[2]; echo 'nom = '.$nom.'<br/>';
 $cat = $att[3]; echo 'categorie = '.$cat.'<br/>';
 $prix = $att[4]; echo 'prix = '.$prix.'<br/>';
 $cond = $att[5]; echo 'cond = '.$cond.'<br/>';
 $date = $att[6]; echo 'date = '.$date.'<br/>';
 $qtes = $att[7]; echo 'qtes = '.$qtes.'<br/>';
 $photo = $att[8]; echo 'photo = '.$photo.'<br/>';
 $qte = $att[9];  echo 'qte = '.$qte.'<br/>';
 $cam = $att[10]; echo 'camion = '.$cam;
 $sql = 'UPDATE produit SET code_barre ='.$code.',nom_prod ='.$nom.', photo ='.$photo.',categorie='.$cat.',condition ='.$cond.',prix_uniraire ='.$prix.', date_exp='.$date.' ,qte ='.$qte.',qte_stock ='.$qtes.', id_camion= '.$cam.' WHERE id_prod ='.$prod.'';
 $res = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);     
    $row++;
    }
}
fclose($handle);
}
?>

this is what I get :

Notice: 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 'condition =emballés,prix_uniraire =3450, date_exp=04/02/2016 ,qte =200,qte_stock' at line 1 in UPDATE produit SET code_barre =345123,nom_prod =Nutella, photo =www.google.com,categorie=Chocolat,condition =emballés,prix_uniraire =3450, date_exp=04/02/2016 ,qte =200,qte_stock =2100, id_camion= 2 WHERE id_prod =3 in /home/a2258793/public_html/seekarticles.php on line 34

Do not use the deprecated `mysql_*` API. Use `mysqli_*`or `PDO` with prepared statement. — Jens, May 15 '15 at 10:44
@Strawberry wondering why all the upvoted answers got upvoted in the first place, missing that all important problem. Indeed, `condition` is a MySQL reserved word and OP is ignoring the error message `for the right syntax to use near 'condition` or even bothered Googling it. Wondering if this should be closed based on that. — Funk Forty Niner, May 15 '15 at 11:41
@Fred-ii- Understanding the logic of voting on SO: That way madness lies. — Strawberry, May 15 '15 at 11:42
@Strawberry Seems like everyone's ignoring the comments section, which even though this is a comments section, can be just as important as the answers section ;-) *Let them eat cake*. — Funk Forty Niner, May 15 '15 at 11:44

score 2 · Accepted Answer · edited May 23 '17 at 12:29

Pay attention to the start of your problem, MySQL is telling you where the problem starts:
(Sidenote: and to the comments section under your question, they're just as important).

for the right syntax to use near 'condition  
                                 ^ the problem starts here

"condition" is a MySQL reserved word and requires special attention.

https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Either you wrap the column name in ticks, or rename it to another word, say "conditions" in plural form. It isn't a MySQL reserved word.

`condition` ='.$cond.'

Should the above fail, it will be because of your quoting method. Consult my suggestion below.

You could/should also rewrite that line to: (and using a different quoting method), since your values contains strings. The rest, MySQL will take care of the integers.

$sql = "UPDATE produit SET code_barre ='".$code."', 
nom_prod ='".$nom."', photo ='".$photo."',categorie='".$cat."', 
`condition` ='".$cond."',prix_uniraire ='".$prix."', 
date_exp='".$date."' ,qte ='".$qte."', 
qte_stock ='".$qtes."', id_camion= '".$cam."' 
WHERE id_prod ='".$prod."' ";

Nota: Only string values need to be quoted. You can modify as needed.

I.e.: WHERE id_prod = $prod"; if $prod is an integer including your column(s).

Sidenote:

Your present code is open to SQL injection. Use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

Footnotes:

If you should encounter any other errors, you will need to escape your values using mysql_real_escape_string().
MySQL will complain about apostrophes etc. I.e.: Nutella's the best! and would interpret that as 'Nutella's the best!' in the VALUES causing a syntax error. Whereas escaping the data would interpret that as 'Nutella\'s the best!' rendering it valid, since it was escaped.

@Strawberry I agree. However, I have seen in some cases where using `'$var'` would cause problems, as opposed to `'".$var."'` strangely enough. OP may need to escape all values in case of apostrophes etc. — Funk Forty Niner, May 15 '15 at 11:59
@Strawberry I've made an additional edit concerning probable issues the OP may encounter, under **Footnotes**. Let's hope this matter gets resolved without a hitch ;-) — Funk Forty Niner, May 15 '15 at 12:02

FrancoisBaveye · Answer 2 · 2015-05-15T11:09:30.697

1

You need to escape your values, try this one :

$sql = 'UPDATE `produit` SET `code_barre` = "'.$code.'", `nom_prod` = "'.$nom.'", `photo` = "'.$photo.'", `categorie` = "'.$cat.'", `condition` = "'.$cond.'", `prix_uniraire` = "'.$prix.'", `date_exp` = "'.$date.'", `qte` = "'.$qte.'", `qte_stock` = "'.$qtes.'", `id_camion` = "'.$cam.'" WHERE `id_prod` = "'.$prod.'"';

Using PDO (PDO::prepare) would help you a lot : it automatically manages to escape values.

edited May 15 '15 at 11:09

answered May 15 '15 at 10:45

FrancoisBaveye

1,902
1
18
25

still having the same error but i will try it using PDO – Nour91 May 15 '15 at 10:49
Put it just before `$res = mysql_query($sql) or trigger_error(mysql_error()." in ".$sql);`. Still false ? – FrancoisBaveye May 15 '15 at 10:55
same error and then this one which start with string(239) – Nour91 May 15 '15 at 10:57
What is after `string(239)` ? I need the whole query to help you solve this problem. – FrancoisBaveye May 15 '15 at 11:00
string(239) "UPDATE produit SET code_barre = "345123",nom_prod ="Nutella", photo ="www.google.com",categorie="Chocolat",condition ="emballés",prix_uniraire ="3450", date_exp="04/02/2016"" ,qte ="200",qte_stock ="2100", id_camion= "2" WHERE id_prod ="3"" – Nour91 May 15 '15 at 11:05
I updated the query in my answer. Feel free to try it. – FrancoisBaveye May 15 '15 at 11:10

Narendrasingh Sisodia · Answer 3 · 2015-05-15T12:31:05.210

1

Just update your query into. Its all because of escape values and condition is reserved keyword in mysql

$sql = "UPDATE produit SET code_barre = '$code',nom_prod ='$nom', photo ='$photo',categorie='$cat',condition ='$cond',prix_uniraire ='$prix', date_exp='$date' ,qte ='$qte',qte_stock ='$qtes', id_camion= '$cam' WHERE id_prod ='$prod'";

edited May 15 '15 at 12:31

answered May 15 '15 at 10:45

Narendrasingh Sisodia

21,247
6
47
54

score 0 · Answer 4 · answered May 15 '15 at 10:46

If you print your query you will find the text are not surrounded by cotes so put cots like this.

$sql = 'UPDATE produit SET code_barre ="'.$code.'",nom_prod ="'.$nom.'", photo ="'.$photo.'",categorie="'.$cat.'",condition ="'.$cond.'",prix_uniraire ="'.$prix.'", date_exp="'.$date.'" ,qte ="'.$qte.'",qte_stock ="'.$qtes.'", id_camion= "'.$cam.'" WHERE id_prod ="'.$prod.'"';

Hope it will solve your problem

An error in SQL syntax

4 Answers4