Not able to get results when input string contains backtick

spstieng

Hi guys. I have a problem returning DB results when input string contains backtick (´). Here is my starting point:

Input string: $name = "Church´s"
Function call: $result = $myDB->getLabelDataByName(mysql_real_escape_string($name));
DB encoding: UTF8
Table encoding: UTF8
There is only one recording named Church´s

This function returns 0 rows:
public function getLabelDataByName($labelName)

{
  $sql = "    

      SELECT *
      FROM sl_label
      WHERE name = '$labelName'";

  $this->query($sql);
  return $this->query_result;          

}

This function returns 1 row:

  public function getSearchResultForLabels($input)
  {
    $sql = "SELECT id,name
            FROM sl_label
            WHERE name LIKE '$input%'
            ORDER BY name ASC";	

  $this->query($sql);
  return $this->query_result; 
  }

The following string works if I run it from phpMyAdmin:
SELECT * FROM sl_label WHERE name = 'Church´s'

Does that mean that mysql_query() function has a bug?
Or is there an encoding problem?

Any help appreciated.

spstieng

hmm.... I wonder why I can't edit my own post.

Update: Enabling query log, I can see that the actual string that is passed, is 'ChurchÂ´s'. The Â most likely indicates a unicode problem.

My DB is using UTF8 and my tables are using utf8_danish_ci. I can even try to pass the variable using utf8_encode($labelName), but it doesn't help. How can this be solved?

spstieng

Some one tiped me that is not called a backtick, but a accent acute - will google some more

djjjozsi

replace that character into an _ , as i know that is a wildcard in the LIKE statement.

bradgrafelman

Is this string coming from an HTML form? If so, did you declare the correct charset (e.g. utf-8) in the HTML document?

Also, try adding this as soon as you open a connection to SQL and before you execute any queries:

mysql_query("SET NAMES 'utf-8'");

I've seen this fix some unicode problems several times before.

spstieng

Yes, the string is comming from an HTML form, but the problem isn't there.

The problem is that the command mysql_query() adds the Â charachter before the accent acute.

So while the label is called 'Church´s' in the database, the query is looking for 'ChurchÂ´s'. That's the reason why it returns 0 rows.

This is the query just before execution "$this->query($sql)" :
SELECT * FROM sl_label WHERE name = 'Church´s'

This is the query just afterexecution "mysql_query($sql, $conn)" :
SELECT * FROM sl_label WHERE name = 'ChurchÂ´s'

I can only see the last query by examening my query log file.

bradgrafelman

spstieng wrote:
Yes, the string is comming from an HTML form, but the problem isn't there.

How do you know? Can you show us where you set the charset on the HTML document?

spstieng wrote:
The problem is that the command mysql_query() adds the Â charachter before the accent acute.

[man]mysql_query/man neither adds nor subtracts characters to strings passed to it. It simply accepts the string and passes it along to the SQL server. Now, if there is an encoding problem either between the HTML form and the PHP script, or between the PHP script and the connection to the MySQL server, then that is no fault of mysql_query().

That's why I suggested trying the SET NAMES command as well as showing us how you're setting the correct charset on the HTML form.

spstieng

Thanks for your reply bradgrafelman.

bradgrafelman;10918469 wrote:
How do you know? Can you show us where you set the charset on the HTML document?

Because when I echo the input, it displays fine (?). I've also tried passing the value to mysql_query with utf8_encode($name) - but that doesn't help either.

[man]mysql_query/man neither adds nor subtracts characters to strings passed to it. It simply accepts the string and passes it along to the SQL server. Now, if there is an encoding problem either between the HTML form and the PHP script, or between the PHP script and the connection to the MySQL server, then that is no fault of mysql_query().

I too am sure it's an encoding problem. Byt where?

Another test case:

In another form, I add the label "Fióres Häßelberg" and submit this. In the database it's stored as written. On another page, typing 'Fi' in a text box with autosuggest, it displays "Fióres Häßelberg".

The Autosuggest uses the following code to retrieve results:

  public function getSearchResultForLabels($input)
  {
    $sql = "SELECT id,name
            FROM sl_label
            WHERE name LIKE '$input%'
            ORDER BY name ASC";	

  $this->query($sql);
  return $this->query_result; 
  }

This demonstrates that I can store and retrieve names with special characters.

That's why I suggested trying the SET NAMES command as well as showing us how you're setting the correct charset on the HTML form.

I have tried that. But that really screws up the ouput. Characters like æ, ø, å, `, is shown as completely gibberish.

spstieng

After more research and testing, I've continuing this issue in another (and more correct) forum: http://www.phpbuilder.com/board/showthread.php?p=10918509#post10918509