CODEWITHSUNDEEP
phputf-8character-encodingascii
AnchovyLegend
AnchovyLegend

Reputation: 12538

Special characters (i.e. '...' character) causing query to fail

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!

Upvotes: 0

Views: 2407

Answers (2)

aioobe
aioobe

Reputation: 421180

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);

Upvotes: 1

Solrac
Solrac

Reputation: 931

Before solving your problem, you shouldnt insert raw input into your database.

What you should do is:

error_reporting(E_ALL);
$string = mysqli_escape_string($conn, "… hello … world!");
mysqli_query($conn, $query);

error_reporting will show you raw errors on screen and escaping the string will:

a) Avoid SQL injections.

b) Insert the record even if it has special chars.

Now, if you need to remove those special chars use something like: 

$string = preg_replace($pattern, "", $string);

before escaping it.

Upvotes: 0

Related Questions