Server error MySQL Error - 1064

Question

Well not being the best programmer in the land i thought i would ask this question in the hope someone would spot the error or just tell me I am going the wrong way about it.

I have a Flash Builder web site that am trying to create a Search function to search my MYSQL database with and then return the results.

I have included the not null part just in case the user does not type any data in the relevant variable.

But I am getting this ERROR Reason: Server error MySQL Error - 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wells AND sex LIKE female' at line 1 #0

HERE IS THE CODE

public function searchClients($fname, $lname, $country, $town, $sex) {

$SQLStatment = "SELECT id, fname, lname, sex, country, town, dateofbirth, monthofbirth, yearofbirth FROM $this->tablename";
$WhereClause = "";
$where =" Where ";

if($fname != "NULL") {
            $WhereClause.= 'Fname LIKE '.$fname;
}         

if($lname != "NULL") {
 if($WhereClause != "") 
 {  
            $WhereClause.= ' AND lname LIKE ' .$lname;
 }
 else
 {
        $WhereClause = 'lname LIKE ' .$lname;
 }
}

if($country != "NULL") {     
if($WhereClause != "") 
 {  
            $WhereClause.= ' AND country LIKE ' .$country;
 }
 else 
 {
        $WhereClause = 'country LIKE ' .$country;
 }
}

if($town != "NULL") {
 if($WhereClause != "") 
 {  
            $WhereClause.= ' AND town LIKE ' .$town;
 }
 else
 {
        $WhereClause = 'town LIKE ' .$town;
 }
}    

if($sex != "NULL") {
 if($WhereClause != "")
 {  
            $WhereClause.= ' AND sex LIKE ' .$sex;
 }
 else
 {
        $WhereClause = 'sex LIKE ' .$sex;
 }
}    
$SQLStatment.= $where;
$SQLStatment.= $WhereClause;

    $stmt = mysqli_prepare($this->connection, $SQLStatment);
    $this->throwExceptionOnError();

     mysqli_stmt_execute($stmt);
    $this->throwExceptionOnError();

    $rows = array();

    mysqli_stmt_bind_result($stmt, $row->ID, $row->fname, $row->lname,  $row->sex, $row->country, $row->town, $row->dateofbirth, $row->monthofbirth, $row->yearofbirth);

    while (mysqli_stmt_fetch($stmt)) {
      $row->fname = ucfirst(substr($row->fname,0,1));
      $row->lname = ucfirst($row->lname);
       $row->town = ucfirst($row->town);
      $row->lname = (($row->fname) . " " . ($row->lname));
      $row->yearofbirth = GetAge($row->dateofbirth. '-' .$row->monthofbirth. '-' .$row->yearofbirth);
      $row->Pic_loc = "";
      $row->Pic_loc= "IMAGES/".($row->ID)."/image01.jpg";
      $rows[] = $row;
      $row = new stdClass();
      mysqli_stmt_bind_result($stmt, $row->ID, $row->fname, $row->lname,  $row->sex, $row->country, $row->town, $row->dateofbirth, $row->monthofbirth, $row->yearofbirth);
    }

    mysqli_stmt_free_result($stmt);
    mysqli_close($this->connection);

    return $rows;
}

It looks good to me but does not work, any suggestions? to pin point nearer in the code that its moaning is

if($town != "NULL") {
 if($WhereClause != "") 
 {  
            $WhereClause.= ' AND town LIKE ' .$town;
 }
 else
 {
        $WhereClause = 'town LIKE ' .$town;
 }
}    

if($sex != "NULL") {
 if($WhereClause != "")
 {  
            $WhereClause.= ' AND sex LIKE ' .$sex;
 }
 else
 {
        $WhereClause = 'sex LIKE ' .$sex;
 }
}    

just find it really odd that it does not moan about the other three if clauses but it does about the last two.

Answer 1

the where clauses should look like:

$WhereClause .= ' AND sex LIKE "' . mysql_escape_string($sex).'"';

You can use prepared statements / PDO to both simplify the code and protect the application from SQL injection

Answer 2

Your strings should be quoted. Do below changes in where clause :

if($town != "NULL") {
     if($WhereClause != "") 
     {  
                $WhereClause.= ' AND town LIKE "' .mysql_escape_string($town).'"';
     }
     else
     {
            $WhereClause = 'town LIKE "' .mysql_escape_string($town).'"';
     }
    }    

    if($sex != "NULL") {
     if($WhereClause != "")
     {  
                $WhereClause.= ' AND sex LIKE "' .$sex.'"';
     }
     else
     {
            $WhereClause = ' sex LIKE "' .$sex.'"';
     }
    }  

BTW you shoud you need to use percent signs around your values with Like

Answer 3

First off, a debugging tip—when you're building statements on the fly, and get a SQL error you can't immediately figure out, the first thing to do is print out the built statement (and please include that in your future SO posts). Partially, it'd look like:

SELECT … lname LIKE wells AND sex LIKE female …

From that, you can see that the quotes around your values are missing—it should be:

SELECT … lname LIKE 'wells' AND sex LIKE 'female' … -- quoting non-numeric is NOT optional in SQL

So, to get this to run, you could just add in the single quotes. But there are still two problems, and a potential third.

1. This still won't work. I assume from using LIKE instead of = that you want to do a substring match. So you need to add in percent signs around your value: lname LIKE '%wells%'.
2. You are already using prepared statements, you really ought to just build your query using placeholders (e.g., lname LIKE ?) and then fill in that way. This will handle escaping for you, preventing SQL injection.

The potential third problem is that the performance of substring matches using LIKE is terrible. That type of match can not use an index. You are doing a full table scan. Using a full-blown search solution (Sphinx, fulltext, etc.) can solve this one (but may add much more work).