build query for MySQL name field Like O'Brien

[bob on whidbey]

PHP and MySQL problems with ' and " have been discussed ad nausea on the web. I still don't get it. For example my db has an Author's name field with someone called O'Brien. If I create the DB with phpMyAdmin by reading in a CSV file, that field will show as O'Brien. (not O\'Brien)

In a search form the user can type in the Author field: O'Brien. In the searchResults.php program, I don't get a match, even though I've tried using mysqli_real_escape_string or addslashes or stripslashes on the POST .field. I must be building the PHP query wrong. Here's what I have:

$Author = stripslashes($_POST);
$SQL = "select * from Catalog where Authors Like '%".$Author."%'"; // can't find in file
// before stripslashes $Author looks like O\'Brien
// after strip looks like O'Brien

the resulting SQL statement looks like:
select * from Catalog where Authors Like '%O'Brien%'

This looks bad, with the unbalanced apostrophes.

Is there a need to build the DB so that the MySQL fields contain \' instead of ' If so, how can I do that?

Alternatively, how can I build my query string without these problems?

It would seem that the DB and my query should both have O\'Brien, in order to get a match and be selected.

[iamthwee]

Save the apostrophe as a html entity?

[cuonic]

When inserting the entry into the database, you need to correctly sanitize it :

<?php

include("includes/mysql_connect.php"); // You need to connect to DB Server First !

$author = mysql_real_escape_string($_POST['author']);
$query = mysql_query("INSERT INTO catalog (author) VALUES ('$author')");

?>

The Original authors name is O'Brien, that is what is Posted to the script
The PHP script will then add a \ before the '. O'Brien becomes O\'Brien

If you use this same method on all pages, like the search page, it will search the database for O\'Brien once the sanitizing is done, even though the user put in O'Brien

Understood ? :D

[bob on whidbey]

Thx cuonic. I think my primary problem was trying to build a database by reading the CSV file with phpMyAdmin. Unless you get those \' into the DB when it's being build, you are SOL.