INSERT query PDO single quotation PDO Error

Question

Hi I have this query where I am inserting information into a database. Submitting the form works as intended. However, when using ' (apostrophes) within the text entered, I receive a PDOException SQLSTATE[42000] Syntax error or access violation: 1064

$result = $conn->prepare("UPDATE `news_articles` 
                            SET `postedby`=:postedby,`title`=:title,
                                `short_title`=:short_title, 
                                `article_image`=:article_image,
                                `contents`=:contents,
                                `datetime`=:datetime,
                                `event_datetime`=:event_datetime,
                                `type`=:type 
                           WHERE `articleid`=:articleid");
    $result->bindParam(':articleid', $articleid);
    $result->bindParam(':postedby', $postedby);
    $result->bindParam(':title', $title);
    $result->bindParam(':short_title', $short_title);
    $result->bindParam(':article_image', $article_image);
    $result->bindParam(':contents', $contents);
    $result->bindParam(':datetime', $datetime);
    $result->bindParam(':event_datetime', $event_datetime);
    $result->bindParam(':type', $type);
    $result->execute();

Does anybody know why this is?

Do you know which field is causing the issue? And what the data containing the apostrophe looks like — RiggsFolly
Don't think so Phil.. and yes it should be $contents as we are using TinyMCE editor so when we write an article, if it contains an apostrophe inside, it gives the error shown when submitting, — MikeXero

RiggsFolly RiggsFolly · Accepted Answer · 2016-01-29T00:39:12

I am not 100% sure of this but if you use the third parameter of the ->bindParam() to inform it of the data type it may well be all you need to correct this issue

So that would be specifically

$result->bindParam(':contents', $contents, PDO::PARAM_STR);

But you should use it on all your ->bindParam() calls

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

and Param constants http://php.net/manual/en/pdo.constants.php

INSERT query PDO single quotation PDO Error

1 Answers