Using MySQL variable in PHP Prepared Statement-CodePudding

When using a SET clause in a MySQL statement, mysqli->prepare returns false.

This MySQL statement will "prepare" okay:

$query = "  INSERT INTO log (channel, message, context, datetime)
              VALUES (?, 'testmsg', '{}', CURRENT_TIMESTAMP); ";
  
  $stmt = $mysqli->prepare($query); // $stmt will be true

This statement will cause "prepare" to return false.

$query = " SET @Channel = ?;
           INSERT INTO log (channel, message, context, datetime)
           VALUES (@Channel, '', '{}', CURRENT_TIMESTAMP); "
$stmt = $mysqli->prepare($query); // $stmt will be false

The error is:

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 'INSERT INTO log (channel, message, context, datetime) ' at line 2

Why can't prepare handle the SET operator in MySQL in this manner? I want to list all my variables (this example only has one but imagine one with 10 ) at the top of my statement for readability instead of sprinkling them inside the mysql code. That's not very unreadable and difficult to debug.

CodePudding user response：

You can only prepare one SQL statement at a time. But your $query contains two statements, separated by a semicolon. Split them apart, or just use this.

$query = " INSERT INTO log (channel, message, context, datetime)
           VALUES (?, '', '{}', CURRENT_TIMESTAMP); "
$stmt = $mysqli->prepare($query);

CodePudding user response：

If you need to use SET for some reason, you can SET user-defined variables in a prepared statement. Then you can use those variables in a subsequent call. You just can't do both in one call.

// first use SET
$query = "SET @Channel = ?";
$stmt = $mysqli->prepare($query);
// ...bind parameters...
$stmt->execute();

// Then do a second call:
$query = "INSERT INTO log (channel, message, context, datetime)
           VALUES (@Channel, '', '{}', CURRENT_TIMESTAMP); "
$mysqli->query($query); // no need for prepare()/execute() in this case

As long as you use the same connection for both calls, the user-defined variables will still have their value when you INSERT. There's no need to SET and then use the variables in the same call.