Non-standard characters in the search

[Perry Mason]

Hi everyone,

 

I'm a total newbie just finishing building my first mysql-php based site which is a medical database. Up until two weeks ago I had zero knowledge of php or mysql but so far it's coming up nicely. Just added pagination to the search results page (thanks to Crayon Violent). I'll post a link here once it's up and running, in the meantime I still have a couple of small problems with site search and would really appreciate some guidance. A push in the right direction is all I'm asking for.

 

So my first problem is non-standard characters. For example, I have Behçet disease in the database. How do I get it to come up when someone types Behcet in the search? So far the only idea I have is inserting an if statement into php for every entry which contains non-standard characters.

 

<?php

$q = $_GET['q'];

$name = $q;

if $q == 'Behcet' {$name = 'beh&#231;et'};

$query = "SELECT * FROM table WHERE name=$name";

$result = mysqli_query($connect, $query) ;

?>

 

Is there a more elegant way of doing it?

 

Thanks in advance

[Psycho]

You need to change the collation type of the field(s) that need to be accent insensitive. I'm no expert by any means of all the collation possibilities, but my tests show that using 'utf8_general_ci' collation in my local dev environment works. Most of my fields are set up with 'latin1_general_ci' which is case insensitive but not accent insensitive. When I changed to 'utf8_general_ci' and did a search for '%jose%' I was able to get the following records to display

 

Jose

josè

Jóse

[peter_anderson]

The above should work.

 

Also, your query is not secure. You need to escape the string.

[Perry Mason]

Thanks guys, will test it when I get home.

 

Peter, what do you mean by not secure?

 

$result = mysqli_query($connect, $query) or die;

-is this the escape you meant?

 

Sorry for asking stupid questions

[Psycho]

No, he is referrng to the fact that you are taking input from the user (i.e. $_GET[]) and using it directly in the query. When you do this, a user can craft their input such that they modify the query to do additional things with very bad consequences.

 

For example, if the user entered the search string: foobar'; DELETE FROM table

 

That would delete all the records in that table! You need to use mysql_real_escape_string() on ALL user input to make them safe for use in a query.

 

<?php
$name = mysql_real_escape_string($_GET['q']);
$query = "SELECT * FROM table WHERE name='$name'";
$result = mysqli_query($connect, $query) ;
?>

[Perry Mason]

Thanks, fixed it.

[Perry Mason]

One more question about about escape string.

 

On every page I have a search bar that renders a search result list. I've added mysqli_real_escape_string to the search bar script as suggested.

 

On the results page, the search results are displayed as a list of links. Once you click on one of the links, you get entry.php page with a description of that search result. I used POST method.

 

In the script for entry.php I have the following code:

 

<?php

if (isset($_GET['p']) && is_numeric ($_GET['p']))

{

$var = (int) $_GET['p'];

}else{

$var = 1;

}

$result = mysqli_query ($dbcnx, "SELECT * FROM table WHERE id LIKE '$var' ");

?>

 

(in case the user alters the 'p' variable in the URL)

 

Here's the question: do I need to add an escape string to this piece of php?

I've tried changing 'p' in the URL to something malicious and it didn't go through, but then I'm testing everything on xampp locally and maybe in the real world it will cause damage.

 

Got it figured out myself. I guess I don't need an escape string. If 'p' is changed to anything other than a number, $var will be assigned the value of 1. Correct me if I'm wrong.

[Psycho]

Correct, you are forcing the value to be an integer. So, as long as the value passed can be any integer then you don't have to use mysql_real_escape_string(). But, then again, it doesn't hurt either.