Apostrophe error in sql php

[dejawho]

When search is submitted with a apostrophe ' it returns error:
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 '%'))) ORDER BY ID ASC LIMIT 0, 10' at line 1

Used Dreamweaver and Dynamic Search Extention

Ideas?

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}


$tfmsqlstr = "";
if (isset($_POST["search"]) || isset($_GET["search"])){
  $tfm_searchField = (isset($_POST["search"]))?$_POST["search"]:$_GET["search"];
  if($tfm_searchField != "") {
    $tfm_andor = "AND";
    $tfm_exact = "false";
    $bellChar = chr(7);
    //if any words option
    //not implemented
    //if exact phrase option
    //not implemented
    $tfmsqlstr = " WHERE ((";
    $tfm_databaseFields = explode(",","Long_Desc");    

    if ((strstr($tfm_searchField,'"')) || ($tfm_exact == "true")){ 
       $tfm_searchField = str_replace('"','',$tfm_searchField); 
       $tfm_andor = "OR";
    }else 
    if (stristr($tfm_searchField," or ")){ 
      $tfm_searchField = preg_replace('/\s+or\s+/i',$bellChar,$tfm_searchField);
      $tfm_andor = "OR";
    }else  

    if (strstr($tfm_searchField,',') || strstr($tfm_searchField,' ') || stristr(strtolower($tfm_searchField),' and ')) { 
      $tfm_searchField = preg_replace("/\s+and\s+/i",$bellChar,$tfm_searchField);
      $tfm_searchField = str_replace(",",$bellChar,$tfm_searchField);
      $tfm_searchField = str_replace(" ",$bellChar,$tfm_searchField);
    }
    $splitField = explode($bellChar,$tfm_searchField);
    for ($i = 0; $i < sizeof($splitField) ;$i++){
      for ($j = 0; $j < sizeof($tfm_databaseFields); $j++){
        $tfmsqlstr = $tfmsqlstr."(".$tfm_databaseFields[$j]." LIKE '%".str_replace("'","''",$splitField[$i])."%')"; 
        if ($j < sizeof($tfm_databaseFields)-1) $tfmsqlstr = $tfmsqlstr." OR "; 
      }
      if ($i < sizeof($splitField) -1) $tfmsqlstr = $tfmsqlstr.") ".$tfm_andor." (";
    }
    $tfmsqlstr = $tfmsqlstr."))";
  }else{
    $tfmsqlstr = " WHERE 1=1 ";
  }
}else{
  $tfmsqlstr = " WHERE 1=1 ";
}

$maxRows_allfood = 20;
$pageNum_allfood = 0;
if (isset($_GET['pageNum_allfood'])) {
  $pageNum_allfood = $_GET['pageNum_allfood'];
}
$startRow_allfood = $pageNum_allfood * $maxRows_allfood;
$search = $_GET['search'];
//TOMLR Special List Recordset
$maxRows_allfood = 10;
$pageNum_allfood = 0;
if (isset($_GET['pageNum_allfood'])) {
  $pageNum_allfood = $_GET['pageNum_allfood'];
}
$startRow_allfood = $pageNum_allfood * $maxRows_allfood;

// Defining List Recordset variable
$tfmsqlstr_allfood = " WHERE 1=1 ";
if (isset($tfmsqlstr)) {
  $tfmsqlstr_allfood = $tfmsqlstr;
}
mysql_select_db($database_myCon, $myCon);

$query_allfood = "SELECT * FROM nutrition {$tfmsqlstr_allfood}  ORDER BY ID ASC";
$query_limit_allfood = sprintf("%s LIMIT %d, %d", $query_allfood, $startRow_allfood, $maxRows_allfood);
$allfood = mysql_query($query_limit_allfood, $myCon) or die(mysql_error());
$row_allfood = mysql_fetch_assoc($allfood);

if (isset($_GET['totalRows_allfood'])) {
  $totalRows_allfood = $_GET['totalRows_allfood'];
} else {
  $all_allfood = mysql_query($query_allfood);
  $totalRows_allfood = mysql_num_rows($all_allfood);
}
$totalPages_allfood = ceil($totalRows_allfood/$maxRows_allfood)-1;
//End TOMLR Special List Recordset

$queryString_allfood = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_allfood") == false && 
        stristr($param, "totalRows_allfood") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_allfood = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_allfood = sprintf("&totalRows_allfood=%d%s", $totalRows_allfood, $queryString_allfood);
?>

[halojoy]

dejawho;10940524 wrote:

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 '%'))) ORDER BY ID ASC LIMIT 0, 10' at line 1

$tfmsqlstr = $tfmsqlstr."))";
corresponds to
syntax to use near '%'))) ORDER BY

I must say you use very complicated things to build your query.
No wonder it can be some error 😉

[dejawho]

Yea I think scrapping it and starting fresh would solve it. Other than that error though its working fine.

[halojoy]

Maybe only rewrite some parts.
And keep the good stuff.

[bradgrafelman]

Why scrap the query when you haven't even tried to debug it?

For example, you haven't even printed out the query that was used (or at least, if you did, you didn't show us).

[dejawho]

I think my problem is the automation. Normally I use a little of Dreamweavers functions then change what I need manually to make it work. This time I'm using standard dreamweaver code and a extension that writes a bunch of code. Its done so much that its getting too confusing for my limited abilities:

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}


$tfmsqlstr = "";
if (isset($_POST["search"]) || isset($_GET["search"])){
  $tfm_searchField = (isset($_POST["search"]))?$_POST["search"]:$_GET["search"];
  if($tfm_searchField != "") {
    $tfm_andor = "AND";
    $tfm_exact = "false";
    $bellChar = chr(7);
    //if any words option
    //not implemented
    //if exact phrase option
    //not implemented
    $tfmsqlstr = " WHERE ((";
    $tfm_databaseFields = explode(",","Long_Desc");    

    if ((strstr($tfm_searchField,'"')) || ($tfm_exact == "true")){ 
       $tfm_searchField = str_replace('"','',$tfm_searchField); 
       $tfm_andor = "OR";
    }else 
    if (stristr($tfm_searchField," or ")){ 
      $tfm_searchField = preg_replace('/\s+or\s+/i',$bellChar,$tfm_searchField);
      $tfm_andor = "OR";
    }else  

    if (strstr($tfm_searchField,',') || strstr($tfm_searchField,' ') || stristr(strtolower($tfm_searchField),' and ')) { 
      $tfm_searchField = preg_replace("/\s+and\s+/i",$bellChar,$tfm_searchField);
      $tfm_searchField = str_replace(",",$bellChar,$tfm_searchField);
      $tfm_searchField = str_replace(" ",$bellChar,$tfm_searchField);
    }
    $splitField = explode($bellChar,$tfm_searchField);
    for ($i = 0; $i < sizeof($splitField) ;$i++){
      for ($j = 0; $j < sizeof($tfm_databaseFields); $j++){
        $tfmsqlstr = $tfmsqlstr."(".$tfm_databaseFields[$j]." LIKE '%".str_replace("'","''",$splitField[$i])."%')"; 
        if ($j < sizeof($tfm_databaseFields)-1) $tfmsqlstr = $tfmsqlstr." OR "; 
      }
      if ($i < sizeof($splitField) -1) $tfmsqlstr = $tfmsqlstr.") ".$tfm_andor." (";
    }
    $tfmsqlstr = $tfmsqlstr."))";
  }else{
    $tfmsqlstr = " WHERE 1=1 ";
  }
}else{
  $tfmsqlstr = " WHERE 1=1 ";
}

$maxRows_allfood = 20;
$pageNum_allfood = 0;
if (isset($_GET['pageNum_allfood'])) {
  $pageNum_allfood = $_GET['pageNum_allfood'];
}
$startRow_allfood = $pageNum_allfood * $maxRows_allfood;
$search = $_GET['search'];
//TOMLR Special List Recordset
$maxRows_allfood = 10;
$pageNum_allfood = 0;
if (isset($_GET['pageNum_allfood'])) {
  $pageNum_allfood = $_GET['pageNum_allfood'];
}
$startRow_allfood = $pageNum_allfood * $maxRows_allfood;

// Defining List Recordset variable
$tfmsqlstr_allfood = " WHERE 1=1 ";
if (isset($tfmsqlstr)) {
  $tfmsqlstr_allfood = $tfmsqlstr;
}
mysql_select_db($database_myCon, $myCon);

$query_allfood = "SELECT * FROM nutrition {$tfmsqlstr_allfood}  ORDER BY ID ASC";
$query_limit_allfood = sprintf("%s LIMIT %d, %d", $query_allfood, $startRow_allfood, $maxRows_allfood);
$allfood = mysql_query($query_limit_allfood, $myCon) or die(mysql_error());
$row_allfood = mysql_fetch_assoc($allfood);

if (isset($_GET['totalRows_allfood'])) {
  $totalRows_allfood = $_GET['totalRows_allfood'];
} else {
  $all_allfood = mysql_query($query_allfood);
  $totalRows_allfood = mysql_num_rows($all_allfood);
}
$totalPages_allfood = ceil($totalRows_allfood/$maxRows_allfood)-1;
//End TOMLR Special List Recordset

$queryString_allfood = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_allfood") == false && 
        stristr($param, "totalRows_allfood") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_allfood = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_allfood = sprintf("&totalRows_allfood=%d%s", $totalRows_allfood, $queryString_allfood);
?>

All I'm trying to do is get a query to get the most accurate results. The extension that I'm using does it perfectly(code above) but I get an error anytime somebody uses a ' in the search.

I wrote a query on my own without automation but the results are really poor.

Thanks for any help, its the first time I've ever posted on a forum. Normally blundering around for a few days solves the problem.

[johanafm]

dejawho;10940524 wrote:

When search is submitted with a apostrophe ' it returns error:

Without looking at the code, I'd bet you don't escape it. In standard SQL, you escape ' with ', so it should look like

WHERE field = 'stuff '' morestuff'

and in mysql the (default) way of doing it is with [code]
WHERE field = 'stuff \' morestuff'[/code]

With mysql(i), you can use $escaped_string = real_escape_string($unescaped_str) once you have your db connection and it will be escaped according to the actual db settings.

When in doubt, log/print the error, error number and full query.