Error performing update: SQLSTATE[42000]

PHP
1

I am getting this error in my PHP (using PDO) code:

Error performing update: 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 ‘:size_rob, size_premium = :size_premium, brand = :brand, name’ at line 2

Can anyone give me a push in the right direction to solve this?

if (isset($_REQUEST['name']))
{

$data_adv_id = $_REQUEST['data_adv_id'];
$data_adv_id = htmlspecialchars($data_adv_id, ENT_QUOTES, 'UTF-8');

$size_rob = $_REQUEST['size_rob'];
$size_rob = htmlspecialchars($size_rob, ENT_QUOTES, 'UTF-8');
	
$size_premium = $_REQUEST['size_premium'];
$size_premium = htmlspecialchars($size_premium, ENT_QUOTES, 'UTF-8');

$brand = $_REQUEST['brand'];
$brand = htmlspecialchars($brand, ENT_QUOTES, 'UTF-8');

	if ($brand == "TA1") { $adv_i = "ta";}

$name = $_REQUEST['name'];
$name = htmlspecialchars($name, ENT_QUOTES, 'UTF-8');

$month = $_REQUEST['month'];
$month = htmlspecialchars($month, ENT_QUOTES, 'UTF-8');
	
$year = $_REQUEST['year'];
$year = htmlspecialchars($year, ENT_QUOTES, 'UTF-8');

try
{
$sql = "UPDATE adv_" . $adv_i . " SET
[B]size_rob = :size_rob,
size_premium = :size_premium,
brand = :brand,
name = :name,[/B]
month = :month,
year = :year
WHERE id = '$data_adv_id'
LIMIT 1
";
$affectedRows = $pdo->exec($sql);

// bindValue prevents injection attacks
 $s = $pdo->prepare($sql);
 $s->bindValue(':size_rob', $_POST['size_rob']); // copied/pasted from insert
 $s->bindValue(':size_premium', $_POST['size_premium']);
 $s->bindValue(':brand', $_POST['brand']);
 $s->bindValue(':name', $_POST['name']);
 $s->bindValue(':month', $_POST['month']);
 $s->bindValue(':year', $_POST['year']);
 $s->execute();
}
2

This may not be the issue, but an issue is that you’re executing the SQL before you do any preparing or binding.

3

I removed that line and it executed without errors. However, it did not actually update the table! Strange…

4

If I understand the error, it is supposed to be a problem before the notice, not after, right?

5

To figure out the answer to this one, it would be useful if you coul echo or var_dump the value of $sql as well as all the values that you’re binding.

6

Correct. It was the colon-placeholder that was causing the syntax error, because without preparing the statement, the server doesn’t know that it’s supposed to be a placeholder. Instead, it was trying to interpret the colon-placeholder as literal syntax.

7

This colon?

size_rob = :size_rob,

I thought that was correct syntax.

8

When it’s run through prepare(), then it is. But if you skip prepare and try to execute it as is, then it isn’t.

9

The OP shows it running before prepare, so that’s not the issue then.

10

It was the cause of the syntax error issue. As I recall, you said the syntax error went away when you removed the exec() line.

11

I need to update the post because of new errors. I’ll do that later with a different post since it is now a different issue.

Thanks for helping me out!

12

Oh, brother … the problem was one too many commas in the UPDATE query, which was added later when I was adding and removing lines.