0

I am working on someone else's code and I am not a PHP expert. I'm trying to retrieve records that contain apostrophes in the names. The SQL table contains the apostrophes and search results is returned if I use part of the name NOT containing the apostrophe.

Example: Searching Business Name for Riley's Air Conditioning returns nothing but searching for Riley or Air Conditioning returns the expected results of Riley's Air Conditioning.

Here is the code:

<?php 
mysql_connect("localhost", "XXXXXX", "XXXXX") or die(mysql_error());
mysql_select_db("XXXXXX") or die(mysql_error());
//init vars
$category_selected="";
$terms_selected= "";
$alpha_selected="";
$searching=0;
$where="WHERE 1=1 ";
//get categories for dropdown
$query="(SELECT DISTINCT category1 FROM members) UNION (SELECT DISTINCT category2 FROM members) UNION (SELECT DISTINCT category3 FROM members) UNION (SELECT DISTINCT category4 FROM members) UNION (SELECT DISTINCT category5 FROM members )ORDER BY category1 ASC";
$categories = mysql_query($query);  
//look for query string and build where clause
if(isset($_GET['category']) && $_GET['category']!=""){
    $category_selected=$_GET['category'];
    $where .= "AND (category1='$category_selected' OR category2='$category_selected' OR category3='$category_selected' OR category4='$category_selected' OR category5='$category_selected') ";
}
if(isset($_GET['terms']) && $_GET['terms']!=""){
    $terms_selected=$_GET['terms'];
    $where .= "AND (company LIKE '%$terms_selected%' OR category1 LIKE '%$terms_selected%') ";
}
if(isset($_GET['alpha']) && $_GET['alpha']!=""){
    $alpha_selected=$_GET['alpha'];
    if($alpha_selected=="#") {
        $where .= "AND (company LIKE '1%' OR company LIKE '2%' OR company LIKE '2%' OR company LIKE '3%' OR company LIKE '4%' OR company LIKE '5%' OR company LIKE '6%' OR company LIKE '7%' OR company LIKE '8%' OR company LIKE '9%')";
    }
    else{
        $where .= "AND company LIKE '$alpha_selected%'";
    }
}
//get results if querystring is present
if ($category_selected!="" || $terms_selected!="" || $alpha_selected!=""){
    $searching=1;
    $query="SELECT * FROM members $where ORDER BY company ASC";
    $results = mysql_query($query);
    $num_rows = mysql_num_rows($results);
    $terms_selected=stripslashes($terms_selected);
}
?>
David Eckert
  • 1
  • 1
  • If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jul 29 '15 at 18:32
  • [Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Jul 29 '15 at 18:32
  • Thanks, I know its wide open but as I stated it's not my code. – David Eckert Jul 29 '15 at 18:39
  • You have to escape the single quotes in all variables you used in your sql statements: `str_replace("'","\\'",$yourvariablenamehere)` (except $where in $query). – hellcode Jul 29 '15 at 18:50

0 Answers0