PHP/SQL Dealing with special characters (PHP Beginner)

Question

I'm trying to build a simple search function in php that searches values in a SQL database.

My code seems to work ok so far, however I have some issues with special characters. For example I don't get any search results, as far as I can tell, the search is executed as the die command is not called.

$query = mysql_query("SELECT * FROM table WHERE attribute LIKE '%$input%';") 
or die("Could not search");

I think the issue is that $input is a string which can contain things like a&b. How do i deal with this?

Additionally the database contains some French words, so I have to deal with all the é,ç,ô etc. How do I make sure the outputs from the database that contain special characters, for example hôtel, are correctly displayed through php?

UPDATE

After spending hours trying to figure out what the issue is, I've found some indications where things might go wrong. Initially I had thought that escaping caused the problems, this does not seem to be the case...

My code creates a string $query_in which is then passed using

mysql_query($query_in);

I echoed the $query_in string for debugging:

SELECT * FROM table WHERE attribute1 LIKE 
"%a & b%" OR attribute2 LIKE "%a & b%" OR
attribute3 LIKE "%a & b%" OR attribute4
LIKE "%Ta & b%" OR attribute5 LIKE "%a & b%"
OR attribute6 LIKE "%a & b%"

If I manually run (copy&paste) this query in the phpMyadmin interface it works just fine and I get results. However, when I check for empty return values from the database using:

$count = mysql_num_rows($query);

echo $count returns 0

How can that be? Also, rest assured I'll switch things over to msqli after this is fixed ;)

Apply these two things. 1- You need to define the collation of your table to be utf8_general_ci. 2- You need to define the 'mysql_set_charset('utf8');' in the file where you made connection with the database and right after the selection of database like 'mysql_select_db' use this 'mysql_set_charset' this will allow you to add and retrieve data properly in what ever the language it is. After that just simply perform the search by using mysql_real_escape_string and you will have what is required. Cheers — Haisum Usman, Apr 21 '16 at 07:58
guys, guys! No one there to warn our young friend [not to use mysql_* functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php)? — giorgio, Apr 24 '16 at 21:40

score 0 · Answer 1 · answered Apr 21 '16 at 07:57

Try with utf8_encode

$query = mysql_query("SELECT * FROM table WHERE attribute LIKE '%".utf8_encode($input)."%';") or die("Could not search");

Or by adding this line with database connection,

mysql_set_charset("utf8", $database_name);

Suggestion : don't use mysql it's deprecated now.

score 0 · Answer 2 · answered Apr 21 '16 at 08:38

to deal with special character use addslash or mysql escape function

$query = mysql_query("SELECT * FROM table WHERE attribute LIKE '%" . addslashes($input) . "%';") or die("Could not search");

OR

$query = mysql_query("SELECT * FROM table WHERE attribute LIKE '%" . mysql_real_escape_string($input) . "%';") or die("Could not search");

PHP/SQL Dealing with special characters (PHP Beginner)

2 Answers2