Access and single quotes

[munkiboy]

Hi,

I have the misfortune needing to query an Access database which has a lot of data containing singlequotes.

I'm avoiding sql errors by converting from single to double quotes, but then the search string is not the same as the data and hence no result...

Example:

One field contains the text: God's gift

If I do:

$txt = "God's gift";

$sql_str = "SELECT * FROM Texttable WHERE Dtxt = '".$txt ."'";
$sql= odbc_exec($odbc, $sql_b_str) or die (odbc_errormsg());

....I get an sql error. However, if I do:

function addslashes_mssql($str){
if (is_array($str)) {
foreach($str AS $id => $value) {
$str[$id] = addslashes_mssql($value);
}
} else {
$str = str_replace("'", "''", $str);

}

return $str;
}


$txt = addslashes_mssql("God's gift");

$sql_str = "SELECT * FROM Texttable WHERE Dtxt = '".$txt ."'";
$sql= odbc_exec($odbc, $sql_b_str) or die (odbc_errormsg());

....I get no result.

Help! 😕

[jheitz]

Not too familiar with access, but echo the $sql_str value to see what query it is trying to run and then try to run it from within Access and see what happens.

You could investigate if you could use another Query mechanism with bind variables, that way you would avoid the single quote problem

[meigwil]

I have the same problem, but with a MySQL copy of an Access database.

Is there an easy way to strip out the single quotes from a string in a field, after getting it from the db?

Thanks,

Mei

[Houdini]

Found this function on this site for MS SQL

Checks the string parameters in a SQL query for single quotes and replaces them with 2 single quotes where necessary.

This is required for MS-SQL where you may need to have an apostrophe in a string.

function checkApostrophes(&$strQuery) {
//Remove white space
$strQuery = trim($strQuery);
$params = split(",", $strQuery);
$buffer = "";
foreach($params as $param) {
$param = trim($param);
if (substr_count($param, "'") > 0) {
//replace double all quotes in data
$param = substr($param, 0, strpos($param, "'"))."'".str_replace("'", "''", substr( $param, strpos($param, "'") + 1, -1 ))."'";
}
$buffer .= $param.", ";
}
//remove final "," and "\"s
$strQuery = str_replace("\", "", substr($buffer, 0, -2));
}

[meigwil]

Thanks for that piece of code. I had searched but failed to come up with anything useful.

I've tried it but have not had any luck.

This is what i used to test:

function checkApostrophes(&$strQuery) {
  //Remove white space
  $strQuery = trim($strQuery);
  $params = split(",", $strQuery);
  $buffer = "";
    foreach($params as $param) {
    $param = trim($param);
        if (substr_count($param, "'") > 0) {
        //replace double all quotes in data
        $param = substr($param, 0, strpos($param, "'"))."'".str_replace("'", "''", substr( $param, strpos($param, "'") + 1, -1 ))."'";
        }
    $buffer .= $param.", ";
    }
  //remove final "," and "\"s
  $strQuery = str_replace("\\", "", substr($buffer, 0, -2));
}
$string1 = 'Ty\'r Ysgol';
$string2 = "Ty'r Ysgol";
$string3 = checkApostrophes($string1);
$string4 = checkApostrophes($string2);
echo $string3;
echo '<br />';
echo $string4;

This does not return anything.

All I need to do is remove single quotes. Is there an easier way?

BTW: white space in code - good or bad?

$string = 'First line';

$string = 'Third line';

Thanks,

Mei

[Blulagoon]

See if this can help you at all.

http://uk2.php.net/manual/en/function.preg-replace.php

Blu