-1

I can't seem to figure out for the life of me why this CREATE EVENT statement isn't working. I can put its equivalent directly into MariaDB from the CLI but for some reason this one doesn't work.

echo "unspaced, nexttime, ident, ttime<br />";

var_dump ($unspaced, $nexttime, $ident, $ttime);
echo "<br />";

$neweventstatement = $loginconn->prepare("CREATE EVENT :eventname ON SCHEDULE AT CURRENT_TIMESTAMP + :nexttime SECOND DO BEGIN UPDATE `tasks` SET ended = NULL WHERE id = :id AND created = :tasktime; END");
$neweventstatement->bindParam(':eventname', $unspaced, PDO::PARAM_STR);
$neweventstatement->bindParam(':nexttime', $nexttime, PDO::PARAM_INT);
$neweventstatement->bindParam(':id', $ident, PDO::PARAM_INT);
$neweventstatement->bindParam(':tasktime', $ttime, PDO::PARAM_STR);
$neweventstatement->execute();

which produces the following output:

unspaced, nexttime, ident, ttime
string(31) "t682020101400334320201014235504" int(300) int(68) string(19) "2020-10-14 00:33:43"

Error: 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 '? ON SCHEDULE AT CURRENT_TIMESTAMP + ? SECOND DO BEGIN UPDATE tasks SET ended = NULL' at line 1

I've also tried obvious variants like

CREATE EVENT :eventname ON SCHEDULE AT CURRENT_TIMESTAMP + :nexttime SECONDS DO BEGIN UPDATE `tasks` SET ended = NULL WHERE id = :id AND created = :tasktime; END
CREATE EVENT :eventname ON SCHEDULE AT CURRENT_TIMESTAMP + :nexttime SECOND DO UPDATE `tasks` SET ended = NULL WHERE id = :id AND created = :tasktime;
CREATE EVENT :eventname ON SCHEDULE AT CURRENT_TIMESTAMP + :nexttime SECOND DO BEGIN UPDATE `tasks` SET ended = NULL WHERE id = :id AND created = :tasktime; END
DELIMITER ^^ CREATE EVENT :eventname ON SCHEDULE AT CURRENT_TIMESTAMP + :nexttime SECOND DO BEGIN UPDATE `tasks` SET ended = NULL WHERE id = :id AND created = :tasktime; END ^^

but nothing seems to be working.

I feel like I'm missing something obvious but I can't see it.

EDIT: for some reason a scurrulous person has marked this as a duplicate of "WHY OH WHY WON'T MY PREPARED STATEMENT TAKE A TABLE NAME." Which it's not. I've edited it to make it clear what the question is. The issue, as identified below, is that CREATE EVENT cannot be a prepared statement.

sudononymous
  • 46
  • 4
  • 1
    Have you tried this in the cli yet? This is the message I get in mysql 5.7.30. `#1295 - This command is not supported in the prepared statement protocol yet` https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_unsupported_ps – bassxzero Oct 15 '20 at 00:34

1 Answers1

1

The section on prepared statements in mysql manual contains the list of SQL statements that you use in a prepared statement. CREATE EVENT statement is not included in this list, so you cannot use it as a prepared statement.

You have to create the sql statement via string concatenation and execute it as such. You must add further checks to ensure that this will not cause an sql injection vulnerability.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Well... crap. That's what I was afraid of. There are a couple other questions on StackOverflow about prepared statements & CREATE EVENT but none of the answers mention the fact that it's not compatible so I'm like "If it weren't compatible someone would have mentioned it... right?" Anyway this is actually super helpful even though I'm disappointed. Thanks! – sudononymous Oct 15 '20 at 01:07