使用PDO将textarea $ _POST导入MySQL

Question

I'm trying to post a textarea value to a mysql table... alongside a few other fields & datatypes, using PHP's PDO. All other fields insert appropriately, when I remove the textarea field ('desc') from the insert or update. When I include the textarea ('desc') in my prepared statement... the query fails. I've tried a few different data types for the 'desc' field in the table, to include text, bigtext, smalltext, varchar. So then I turned back to looking at the type of field and whether or not I had to escape the content or anything.. but have read that I do not, when using PDO.

$ItemID = $_POST['ItemID'];
$Name = $_POST['Name'];
$Desc = $_POST['Desc'];
$ItemValue = $_POST['ItemValue'];
$GiveawayDate = date('y-m-d 17:00:00',strtotime($_POST['GiveawayDate']));
$NewItemData = array('name'=>$Name, 'desc'=>$Desc, 'itemvalue'=>$ItemValue, 'giveawaydate'=>$GiveawayDate, 'creator'=>$_SESSION['userid'], 'itemid'=>$ItemID);

$ReturnData['Fail']=0;

try {
    $DBH = new PDO("mysql:host=$hostname;dbname=$db_name", $db_username, $db_password);
    $STH = $DBH->prepare("UPDATE giveawayitem SET name=:name, desc=:desc, itemvalue=:itemvalue, giveawaydate=:giveawaydate, creator=:creator WHERE iditem=:itemid");
    $STH->execute($NewItemData);
    if($STH->rowCount()==0){
        $ReturnData['Fail']=1;
        $ReturnData['Error']='Update Failure';
        $ReturnData['Status']='Item Creation Error. 0 rows affect.';
    }else{
        $ReturnData['Status']='Item Updated :)';
    }
}
catch(PDOException $e) {
    $ReturnData['Fail']=1;
    $ReturnData['Error']=$e->getMessage();
}       
$DBH = null;

die(json_encode($ReturnData));

The logic behind the array returning via JSON is that this is being executed via AJAX.

Each POST does make it through.

The PDOException $e->getMessage() doesn't return anything... I'm just getting a rowCount of 0.

Answer 1

You're using desc for your column, being a MySQL reserved word without escaping it with ticks.

Either rename it to something else like "description", or wrap it in ticks:

UPDATE giveawayitem SET name=:name, `desc`=:desc ...

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

Had you used setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION) it would have signaled the syntax error such as:

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 'desc

• Add $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); right after the connection is opened.

See also:

• http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

---

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.