Jump to content

Non-standard characters in the search


Perry Mason

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.