php - mysql query search when record value insterted with real escape string

981

So i have done inserts usingmysql_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

813

Answer

Solution:

$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 theLIKE '%word%' pattern, you should be aware that this will be very slow as your data grows. See my presentation Full Text Search Throwdown.

539

Answer

Solution:

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']; 
} 

People are also looking for solutions to the problem: php - How to change the FB login button with own image

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.