特殊字符（即“...”字符）导致查询失败

Question

I am not sure what character set this character '...' belongs to. However, in some cases, users copy and paste data in to the note field of an application I am working on. The note contents contains this special characters and causes the insert / update query to fail.

I was wondering if there is a string function to detect and remove this special character (along with other unwanted characters from this character set), while maintaining all other special characters ?

Example:

$query = 'INSERT INTO notes (note) VALUES ("… hello … world!")';
mysqli_query($conn, $query); //nothing is inserted

Thanks in advance!

Answer 1

If I were you I would consider accepting a wider set of characters. (You just need to remember to escape the characters properly.) To strip out certain characters just isn't user friendly, and using UTF-8 just seems limited.

If you really want to strip out UTF-8 characters, you can use

$string = preg_replace('/[^(\x19-\x7F)]*/','', $string);

as described here.

---

Here's an example:

<?php
$string = "a bc…de f";
echo preg_replace('/[^(\x19-\x7F)]*/','', $string); 
?>

Output:

a bcde f

---

Finally, you need to escape your input:

$string = preg_replace('/[^(\x19-\x7F)]*/','', $string);
$string = mysqli_escape_string($string);
$query = 'INSERT INTO notes (note) VALUES (' . $string . ')';
mysqli_query($conn, $query);