mysql query search when record value insterted with real escape string

Question

So i have done inserts using mysql_real_escape_string but now i cant seem to figure out how to do a "like" search for those records...

any help would be appreciated. maybe there is a better way to do this?

so simple example would be:

in the DB record there is foo' bar.

now i want to search for foo bar or foo' bar as a like but i keep getting a mysql error.

$term = "foo' bar";
$sql = mysql_query("SELECT * FROM artists WHERE artist_name LIKE '%$term%'") or die ( mysql_error() );

Also would like to know if the record in the table is set to" foo' bar" how can I search for just "foo bar" in a LIKE

please check this link first - http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-function-in-php — Pragnesh Chauhan, Nov 10 '12 at 04:12

Bill Karwin · Answer 1 · 2012-11-11T05:28:09.110

$term = "foo' bar";
$term_escaped = mysql_real_escape_string($term);
$sql = mysql_query("SELECT * FROM artists WHERE artist_name LIKE '%$term_escaped%'") or die ( mysql_error() );

But you should start using PDO instead of the deprecated mysql extension in PHP. You don't need to escape values when you use query parameters with PDO:

$term = "foo' bar";
$stmt = $pdo->prepare("SELECT * FROM artists WHERE artist_name LIKE CONCAT('%',?,'%')");
if ($stmt === false) {
    die(print_r($pdo->errorInfo(), true));
}
if ($stmt->execute(array($term)) === false) {
    die(print_r($stmt->errorInfo(), true));
}

Re your updated question:

It sounds like you are asking about the opposite of your original question, that is some of you data contains a literal single-quote character, and you want to search for matches that don't have the quote character.

Just don't use the quote character in the pattern you search for with LIKE.

WHERE artist_name LIKE '%foo bar%'

You should understand that the SQL LIKE predicate is not full-text search. It only searches for the sequence of characters in your pattern string, including white space and punctuation and so on. If you don't use those extra characters, data that contains those characters doesn't match.

PS: since you are using the LIKE '%word%' pattern, you should be aware that this will be very slow as your data grows. See my presentation Full Text Search Throwdown.

i am so deep into this project not sure how long it would take me to convert the whole thing. BUt one more question. do you konw how i can get it to return results if the search term is "foo bar" how could i return the "foo' bar" as a like? — rchiriboga, Nov 10 '12 at 04:30
I can't tell what you mean. Please edit your original question and add an example of what you want to search for and what data you want to match it. — Bill Karwin, Nov 11 '12 at 00:20
I have added it. Hope it helps. Let me know. I am typing this from my phone. Thanks in advance. — rchiriboga, Nov 11 '12 at 03:19

Jared Drake · Accepted Answer · 2012-11-10T04:47:27.040

Nooooooooo! don't use mysql. Use pdo.

require_once('../php_custom_classes/database.php');

$testObj = new Database();
$newObj = new PDO("mysql:host=".$this->hostName.";dbname=".$this->dbName, $this->username, $this->password);


$term = 'foo bar';
$sql = "SELECT * FROM artists WHERE artist_name LIKE '%$term%'";
$stmt = $newObj->prepare($sql);
$stmt->execute();
while($row = $stmt->fetch()) { 
    echo $row['article_id']; 
}

mysql query search when record value insterted with real escape string

2 Answers2