I have a PHP mysqli prepared statement that looks like this:

$editSQL = "UPDATE tblClientReminders
              SET Header = ?, Notes = ?, DepartmentID = ?,
                  TierID = ?, RemindType = ?, RemindDate = ?, DateAdded = ?
              WHERE ReminderID = ?";
     $stmt = $conn->prepare($editSQL);
     $stmt->bind_param('sbiiissi', $input[1], $input[2], $input[3],
             $input[4], $input[5], $input[6],
             $DateAdded, $_SESSION[$thisTracker]);
     $DateAdded = date("Y-m-d h:i:s");

When I run this, it executes without throwing any errors and runs everything else as expected; however, the "Notes" field is cleared even though $input[2] has content. But when I do this using concatenation:

$editSQL = "UPDATE tblClientReminders
         SET Header = '".$input[1]."', Notes = '".$input[2]."',
             DepartmentID = '".$input[3]."', TierID = '".$input[4]."',
             RemindType = '".$input[5]."', RemindDate = '".$input[6]."',
             DateAdded = '".date("Y-m-d h:i:s")."'
         WHERE ReminderID = '".$_SESSION[$thisTracker]."'";
     $stmt = $conn->prepare($editSQL);       

$input[2] is properly saved along with everything else. I have verified that the column "Notes" is a blob in the mySQL database, and that $input[2] actually contains the intended data when I try the prepared statement. So, why is $input[2] not saving in the first example, and how would I fix that.

The only other factor here I can think of is that $input[2] is passing an AES string, but I can't think of why that would be a problem since other queries elsewhere in my code are saving AES strings just fine, but I think this is the only one that does it to a blob.

An example of inputs:

$input[1] = 'test';
$input[2] = '8L2G1urlEQEjZdyMrSQeow==::88f32b3584167b1d7286a57f861d4fd6';
$input[3] = 1;
$input[4] = 3;
$input[5] = 0;
$input[6] = '2018-11-20';
$_SESSION[$thisTracker] = 15;
  • Please provide a sample value, including punctuation, for $input[2]. Off hand, I would expect the second to fail, not the first. – Rick James Nov 20 '18 at 18:06
  • Added an example AES string, as far as raw input goes, even simple numbers and strings like 123 and test are failing. – Nosajimiki Nov 20 '18 at 18:16
  • That is, the value is plain ascii, no specific need for BLOB. And there are no quotes or apostrophes, so no need (aside from SQL Injection prevention) to need "prepare". Can you provide a failing test case? Just a one-column table with one input value to insert? No mention of $_SESSION. Variables set to constant values, etc. – Rick James Nov 20 '18 at 18:21
  • A simplification: DateAdded = '".date("Y-m-d h:i:s")."' --> DateAdded = NOW() – Rick James Nov 20 '18 at 18:23
  • Input[1] is a non-encrypted string; so, it does need to be prepared to prevent injection. I might be able to simply concatenate just input[2] since it is encoded, but I'd rather fix it than work around it if at all possible. Some of the data handled by this system may need to meet various security compliance standards; so, I'd rather not have to explain it in a security audit. – Nosajimiki Nov 20 '18 at 18:34

The bind_param page mentions (emphasis mine) for the types parameter that

corresponding variable is a blob and will be sent in packets

which is done in this manner:

$null = NULL;
$stmt->bind_param('sbiiissi', $input[1], $null, $input[3],
         $input[4], $input[5], $input[6],
         $DateAdded, $_SESSION[$thisTracker]);
$stmt->send_long_data(1, $input[2]);

If your BLOB data exceeds the max_allowed_packet size you will need to call send_long_data multiple times, sending a smaller fragment of it each time.

More info in this Oracle blog.

  • THIS WORKED!!! but $stmt->send_long_data(0, $input[2]); should be $stmt->send_long_data(1, $input[2]); The first input is the array location of your inputs starting with zero; so, while it should be zero your example, in my case it is 1. Just an important distinction for people following up on this. – Nosajimiki Nov 20 '18 at 18:50
  • How about turning on the general log so we can see this in action. – Rick James Nov 20 '18 at 18:50
  • @Nosajimiki sorry, that was a typo. – mustaccio Nov 20 '18 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.