PHP PDO SQL syntax error

Question

I've looked around the internet and haven't been able to resolve this issue.

I'm trying to insert a row into a mySQL table using PDO through this function

function newItem($name, $desc, $price, $catID){
    echo $name . "\n";
    echo $price . "\n";
    echo $desc . "\n";
    echo $catID . "\n";

    $conn = self::connect();
    //INSERT Order
    $sql = "INSERT INTO catalogue (Name, Price, CatID, Desc)
    VALUES ('$name', $price, $catID, '$desc')";
    // use exec() because no results are returned
    $conn->exec($sql);

}

when i do, i get this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Desc) VALUES ('User', 0.00, 3, 'theUser')' at line 1' in C:\xampp\htdocs\classes\catalogue.php:65 Stack trace: #0 C:\xampp\htdocs\classes\catalogue.php(65): PDO->exec('INSERT INTO cat...') #1 C:\xampp\htdocs\classes\main.php(39): Catalogue->newItem('User', 'theUser', '0.00', '3') #2 {main} thrown in C:\xampp\htdocs\classes\catalogue.php on line 65

I can confirm that the self::connect(); method works, and the problem only occurs when i try to insert data into the Desc column.

I've spent a good while trying to sort this issue, however my knowledge of PDO is quite vague....

Can anyone see where I've gone wrong?

the echo's show this:

User 0.00 theUser 3

`Desc` is reserved keyword in mysql in must be in backtick https://dev.mysql.com/doc/refman/5.7/en/keywords.html and use prepare and bind statement — Saty, May 25 '16 at 10:31

score 5 · Accepted Answer · answered May 25 '16 at 10:33

5

DESC is a keyword. You have to escape the column Name using backtics or better rename the column.

$sql = "INSERT INTO catalogue (Name, Price, CatID, `Desc`)
    VALUES ('$name', $price, $catID, '$desc')";

For more Information about keywords see the official documentation.

answered May 25 '16 at 10:33

Jens

67,715
15
98
113

I'll mark this as correct in 9 minutes, thanks, and your edit came just in time, i was just about to post a comment asking if "description" is a keyword xD – Jamie McAllister May 25 '16 at 10:36
@Downvoter: please explain! – Jens May 25 '16 at 11:18
there is nothing wrong with the answer itself. It's just my humble attempt to fix the [bike shed problem](http://meta.stackexchange.com/questions/31253/the-bikeshed-problem-and-stack-exchange). Disclaimer: I will not engage in any further discussion on the matter. – Your Common Sense May 25 '16 at 11:24
@YourCommonSense Thanks for the Explanation. – Jens May 25 '16 at 11:31

Saty · Answer 2 · 2016-05-25T10:41:50.537

1

Desc is reserved keyword in mysql in must be in backtick https://dev.mysql.com/doc/refman/5.7/en/keywords.html.html and use prepare and bind statement

$sth = $conn->prepare("INSERT INTO catalogue (Name, Price, CatID, `Desc`)
    VALUES (:Name, :Price, :CatID, :Desc)");
$sth->bindParam(':Name', $name, PDO::PARAM_STR);
$sth->bindParam(':Price', $price, PDO::PARAM_STR);
$sth->bindParam(':CatID', $catID, PDO::PARAM_INT);
$sth->bindParam(':Desc', $desc, PDO::PARAM_STR);
$sth->execute();

edited May 25 '16 at 10:41

answered May 25 '16 at 10:34

Saty

22,443
7
33
51

what are the benefits of this over the method i used? – Jamie McAllister May 25 '16 at 10:37
TO prevent form sql injection. Read http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 – Saty May 25 '16 at 10:38
1

Except `:CatID` should probably be `PDO::PARAM_INT` - but yeah, it's better to go with prepared statements with bound parameters over raw SQL. – CD001 May 25 '16 at 10:41
Yaa correct @CD001 – Saty May 25 '16 at 10:42

score -3 · Answer 3 · edited May 25 '16 at 10:34

-3

Try this

$sql = "INSERT INTO catalogue (Name, Price, CatID, Desc)
    VALUES ('".$name."', $price, $catID, '".$desc."')";

edited May 25 '16 at 10:34

Jens

67,715
15
98
113

answered May 25 '16 at 10:32

Jignesh Patel

1,028
6
10

Neither solves the problem nor explains what you've done or why. – CD001 May 25 '16 at 10:47

PHP PDO SQL syntax error

3 Answers3