Perry Mason Posted December 30, 2009 Share Posted December 30, 2009 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ç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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2009 Share Posted December 30, 2009 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 Quote Link to comment Share on other sites More sharing options...
peter_anderson Posted December 30, 2009 Share Posted December 30, 2009 The above should work. Also, your query is not secure. You need to escape the string. Quote Link to comment Share on other sites More sharing options...
Perry Mason Posted December 30, 2009 Author Share Posted December 30, 2009 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 30, 2009 Share Posted December 30, 2009 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) ; ?> Quote Link to comment Share on other sites More sharing options...
Perry Mason Posted December 31, 2009 Author Share Posted December 31, 2009 Thanks, fixed it. Quote Link to comment Share on other sites More sharing options...
Perry Mason Posted January 1, 2010 Author Share Posted January 1, 2010 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. Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 2, 2010 Share Posted January 2, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.