SQL syntax error: can't submit form correctly

Question

I get this error once I submit my form. As I have not used PDO too much yet, my code may have obvious mistakes. It is intended to update the table with form data and read it. How can I fix it?

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'UPDATE edit SET content=':bodyBackgroundColour' WHERE id='bodyBackgroundColour';' at line 2

try
{
    $database = new PDO ('mysql:host=localhost;dbname=youdonotneedtoknow;charset=utf8', 'youdonotneedtoknow', 'youdonotneedtoknow');
    $database -> setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $database -> setAttribute (PDO::ATTR_EMULATE_PREPARES, false);

    //Update
    if (!empty ($_POST))
    {
    $statement = $database -> prepare ("
    UPDATE edit SET content=':windowTitle' WHERE id='windowTitle';
    UPDATE edit SET content=':bodyBackgroundColour' WHERE id='bodyBackgroundColour';
    UPDATE edit SET content=':divisionBackgroundColour' WHERE id='divisionBackgroundColour';
    ");
    $statement -> execute (array (
    ':windowTitle' => $_POST ['windowTitle'],
    ':bodyBackgroundColour' => $_POST ['bodyBackgroundColour'],
    ':divisionBackgroundColour' => $_POST ['divisionBackgroundColour'],
    ));
    }
}
catch (PDOException $exception)
{
    die ('Error: ' . $exception -> getMessage () . '<br><br>' . 'Por favor diríjase a "index.php" en este mismo directorio y contacte a su administrador.');
}

Remove the quotes around your placeholders. Placeholders get parsed internally and quotes are added where necessary for strings etc. — scrowler, Aug 28 '14 at 02:01
Are you sure the value of `:bodyBackgroundColour` is not empty? — Prabowo Murti, Aug 28 '14 at 02:01
You turned off `PDO::ATTR_EMULATE_PREPARES` but attempt to prepare multiple queries. See [this](http://stackoverflow.com/a/6461110/188331). — Raptor, Aug 28 '14 at 02:03

score 1 · Accepted Answer · answered Aug 28 '14 at 02:07

There are 2 errors in the codes.

#1

Placeholders are not required to be surrounded by quotes. Therefore the statement should be:

UPDATE edit SET content=:windowTitle WHERE id='windowTitle';
UPDATE edit SET content=:bodyBackgroundColour WHERE id='bodyBackgroundColour';
UPDATE edit SET content=:divisionBackgroundColour WHERE id='divisionBackgroundColour';

#2

executing statement requires PDO::ATTR_EMULATE_PREPARES to set to 1 or true. Also, it requires PHP 5.3+ and mysqlnd driver.

Advice: Use transactions. Execute each statement one by one, and at last commit / rollback depends on your needs.

SQL syntax error: can't submit form correctly

1 Answers1