PDOException (syntax error)

Question

i'm trying to create a comment system using php pdo. this is my code

<?php
      include_once 'dbConfig.php';
      if(isset($_POST['user_comm']) && isset($_POST['user_name']))
     {
         $comment=$_POST['user_comm'];
         $name=$_POST['user_name'];
         $insert="insert into comments values('','$name','$comment',CURRENT_TIMESTAMP)";
          $stmt = $conn->prepare($insert);
          $stmt->execute();
         $id= $conn->lastInsertId();


        $sql = "select name,comment,post_time from comments where name='$name' and comment='$comment' and id='$id''";
        $stmt = $conn->prepare($sql);
       $stmt->execute();
       if($rows = $stmt->fetch(PDO::FETCH_ASSOC))
      {
        $name=$rows['name'];
         $comment=$rows['comment'];
        $time=$rows['post_time'];
    ?>
    <div class="comment_div">
        <p class="name">Posted By:<?php echo $name;?></p>
        <p class="comment"><?php echo $comment;?></p>
        <p class="time"><?php echo $time;?></p>
    </div>
    <?php
}
exit; ?>

this is the error am getting

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 ''42''' at line 1' in /home/u998801935/public_html/commento/post_comment.php:15 Stack trace: #0 /home/u998801935/public_html/commento/post_comment.php(15): PDOStatement->execute() #1 {main} thrown in /home/u998801935/public_html/commento/post_comment.php on line 15

am thinking maybe the error is from this part of the code ?

  $stmt->execute();
   if($rows = $stmt->fetch(PDO::FETCH_ASSOC))
  {

thanks for your help in advance

You have that error Thamilan mentioned, however you also have a big security hole! You are wide open to SQL injection! You are preparing your sql statement but not binding your parameters! Check my answer down below on how to bind your parameters so that you don't get hacked! — Webeng, May 23 '16 at 07:27

score 1 · Answer 1 · answered May 23 '16 at 07:15

1

Change

$sql = "select name,comment,post_time from comments where 
name='$name' and comment='$comment' and id='$id''"; // Notice extra single quote.

To:

$sql = "select name,comment,post_time from comments where 
name='$name' and comment='$comment' and id='$id'";

answered May 23 '16 at 07:15

Pupil

23,834
6
44
66

thanks , that was the error – baezl May 23 '16 at 07:24
@baezl, you are welcome. – Pupil May 23 '16 at 07:26

score 1 · Answer 2 · answered May 23 '16 at 07:22

You have 2 things very wrong with your code (even though you only asked about one of them).

You have an SQL Syntax error, which means that the SQL statement used in your query isn't written properly. As others have mentioned, it is due to the fact that you have an extra appostrophe. Changing id='$id'' into id='$id' would fix the error.
Your code is wide open to SQL injection. To prevent such attacks, Preparing your statements and binding your parameters should be implemented in your code. You did prepare your statement, but you did not bind your parameters, which makes the preparing completely useless against sql injection.

However, since I am in a good mood today :), this is how you would change your code to not only fix your error issue, but to make your database safe:

Change this:

$insert="insert into comments 
values('','$name','$comment',CURRENT_TIMESTAMP)";
$stmt = $conn->prepare($insert);
$stmt->execute();
$id= $conn->lastInsertId();


$sql = "select name,comment,post_time from 
comments where name='$name' and comment='$comment' and id='$id''";
$stmt = $conn->prepare($sql);
$stmt->execute();

to this:

$insert="insert into comments 
values('',:name,:comment,CURRENT_TIMESTAMP)";
$stmt = $conn->prepare($insert);
$stmt->bindParam(':name', $name );
$stmt->bindParam(':comment', $comment );
$stmt->execute();
$id= $conn->lastInsertId();


$sql = "select name,comment,post_time from 
comments where name=:name and comment=:comment and id=:id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':name', $name );
$stmt->bindParam(':comment', $comment );
$stmt->bindParam(':id', $id );
$stmt->execute();

score 0 · Answer 3 · answered May 23 '16 at 07:15

0

As the exception message says:

1064 You have an error in your SQL syntax;

You have error in SQL syntax, not PHP. Therefore you should check you queries.

As Thamilan mentioned in his comment under the question, you have an error here:

    $sql = "select name,comment,post_time from comments where name='$name' and comment='$comment' and id='$id''";

At the end to the query you have addidional single quote.

answered May 23 '16 at 07:15

Jakub Matczak

15,341
5
46
64

thanks , that was the error – baezl May 23 '16 at 07:24

score 0 · Answer 4 · edited May 23 '17 at 11:44

0

You have and extra ' at and id='$id''"

Better use prepare and bind statement as

 $sql = "select name,comment,post_time from comments where name= :name and comment= :comment and id= :id";
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':name', $name,PDO::PARAM_STR);
    $stmt->bindParam(':comment', $comment, PDO::PARAM_STR);
    $stmt->bindParam(':id', $id,PDO::PARAM_INT);
    $stmt->execute();

Read http://php.net/manual/en/pdostatement.bindparam.php

Your code in open for sql injection Read How can I prevent SQL injection in PHP? to prevent it

edited May 23 '17 at 11:44

Community

1
1

answered May 23 '16 at 07:20

Saty

22,443
7
33
51

thanks , that was the error – baezl May 23 '16 at 07:24

PDOException (syntax error)

4 Answers4