PHP MySQLi Prepared Statements trimming strings with "non-standard" characters?

0
votes

I've run into a strange problem that I've never encountered before with prepared statements. I'm using the built in PHP MySQLi functions to insert data into the database.

The code looks like this:

$mysqli = new mysqli("localhost", "user", "pass", "test");
$mysqli->set_charset('utf8');
$stmt = $mysqli->prepare("INSERT INTO `lines` (`description`) VALUES (?)");
$stmt->bind_param("s", $_POST['description']);
$stmt->execute();

In a textarea named 'description', the following string is entered:

Alice likes Bob’s cat.

Note that the apostrophe is not the regular ' kind, but one that I got from copying the line out of an email (in OS X's Mail app).

When the textarea is submitted as part of a form, the value that gets stored in the field in the table is:

Alice likes Bob

The rest is cut off. I've debugged as much as I can think of, and I know the following to be true:

  1. The value of the $_POST['description'] variable is correct before it is placed in the bind_param function.
  2. The database tables should be using UTF8 (I have tried collations of utf8_bin and utf8_unicode_ci but neither seem to work).
  3. When I use something like phpMyAdmin to enter the same string, it works.

I'd originally thought it was an encoding problem, but point (3) seems to contradict that, so the only thing I'm left with is something wrong with the way I've set up the statement.

If anyone could shed some light it would be most helpful.

2
phpmysqliprepared-statement
There is no proof in your question that can let you blame mysqli. As long as you fail to post a reproduceable test case that can prove a bug in mysqli prepared statements, there is point in tagging your question with these tags. - Your Common Sense
I am tagging the question as MySQLi because it involves MySQLi. I never said it was a bug with MySQLi, but there are people on this site that use MySQLi and may have encountered it before. There is no proof either that my question is in the realm of "HTML" which you keep insisting on tagging it with. This is not a HTML question. - Rsaesha
Well, it seems an encoding issue. Check your source string's encoding and then change SET NAMES argument accordingly - Your Common Sense

2 Answers

0
votes

So it turns out it was encoding related as I originally suspected. The top answer in this question solved the problem, however I still cannot explain why instead of only displaying the character incorrectly (or storing it incorrectly), the string was cut off at that point. If anyone has an actual answer to why that happens, it would be most appreciated, if only for academic reasons!

-3
votes

You need to escape your string before you insert 

<?php
$str = "Is your name O'reilly?";

// Outputs: Is your name O\'reilly?
echo addslashes($str);
?>

Example taken from PHP docs.

You may also want to consider security - inserting post data into SQL without validation is never a good idea.