SQL Injection error - Need to get around it for a search function

[Cep]

Hello,

Basically I have a search function which searches an Access database for a client record based on three possible user entries, the ID, the No. or the Name.

This is all fine except as you probably already guessed if a name contains '

For example, O'Brians

The problem I have is that I cannot use str_replace to remove or change this name. The reason being I would end up with a blank result being returned. So how do I keep the O'Brians entry without SQL breaking?

Here is my function code,

[code=php:0]
function search_client($clientid, $clientno, $clientname) {

  $clientname = str_replace("\\", "", $clientname);

  if ($clientid!="") {
    $sql = "SELECT * FROM `DEClient` WHERE `ID` = $clientid";
  } elseif ($clientno!="") {
    $sql = "SELECT * FROM `DEClient` WHERE `Client No` = '$clientno'";
  } elseif ($clientname!="") {
    $sql = "SELECT * FROM `DEClient` WHERE `Client Name` LIKE '$clientname'";
  }

  $result = odbc_exec(db_src(),$sql) or die("SQL Error: Function search_client - unable to select<br />ClientID = {$clientid}<br />Client No = <br />Client Name = {$clientname}<br /><br />If client name contains a ' mark please use Client ID or No. instead.");

  $num_rows = odbc_record_count($result, db_src(), $sql);

  //array(rows,id,no,name,addr,postcode,tel,fax);

  switch ($num_rows) {
    case 0:
        // No Records found return a blank result
        $search = array($num_rows, "", "", "", "", "");
        break;
    case 1:
        // Single Record found return result in array
        $row = odbc_fetch_array(odbc_exec(db_src(),$sql)) or die("SQL Error: Function search_client - unable to fetch array");

        //add client address vars together into one.
        $addr = array($row['Client Adr1'], $row['Client Adr2'], $row['Client Adr3'], $row['Client Town'], $row['Client Cnty']);
        $fulladdr = implode(",", $addr);

        $search = array($num_rows, $row['ID'], $row['Client No'], $row['Client Name'], $fulladdr, $row['Client Pcd'], $row['Client Tel'], $row['Client Fax'], $row['Client Town'], $row['Full'], $row['TotalSOLUTION'], $row['OnHold']);

        break;
    default:
        // Multiple Records found loop through each record, create data as string, add it to result array.
        $search = array();
        $search[0] = $num_rows;

        for ($i = 1; $i <= $num_rows; $i++) {
            $row = odbc_fetch_array(odbc_exec(db_src(),$sql), $i) or die("SQL Error: Function search_client - unable to fetch array");

            //add client address vars together into one.
            $addr = array($row['Client Adr1'], $row['Client Adr2'], $row['Client Adr3'], $row['Client Town'], $row['Client Cnty']);
            $fulladdr = implode(",", $addr);

            $data = array($num_rows, $row['ID'], $row['Client No'], $row['Client Name'], $fulladdr, $row['Client Pcd'], $row['Client Tel'], $row['Client Fax'], $row['Client Town'] , $row['Full'], $row['TotalSOLUTION'], $row['OnHold']);
            $datastring = implode("#", $data);
            $search[$i] = $datastring;
        }
  }
return $search;
}
[/code]

[Psycho]

You should yse mysql_real_escape_string() any time you are using user supplied text in a query.

http://us2.php.net/manual/en/function.mysql-real-escape-string.php

[Cep]

As mentioned above this is an Access database but thanks anyway ;)

[effigy]

If Access still goes by the same rules, create your own[tt] access_real_escape_string [/tt] function based on this information from the docs:

[quote]
mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.
[/quote]

[Cep]

Could you point me to the information in the docs? Which docs are we referring to by the way?

[effigy]

The docs for[tt] mysql_real_escape_string[/tt]--see the link in mjdamato's post.

[Cep]

No I don't think its possible to do this with Access as basically that function just appears to add a backslash before the problem character.

Even removing my str_replace line for the slashes and just entering O\'Brians will cause the same injection error.

[effigy]

I don't know enough about Access; I'm handing this one over to Google: http://www.google.com/search?hl=en&lr=&q=sql+injection+ms+access&btnG=Search