0

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.

3
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by bob on whidbey
1

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

Edited by cuonic: n/a

Votes + Comments
Answered my question.
0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.