In PHP, how do I make a mySQL select query that contains both quotation marks and apostrophes?

Question

I'm getting data into my database without any problem using mysql_real_escape_string.

So an entry in the database might be:

1/4" Steve's lugnuts

So that's perfectly in the database.
Now I want to search for that exact thing. But, it will mess up at either the " or the ' (I've tried a number of things, and it always messes up somewhere).

Here's what I have now: (user_input comes from a form on the previous page)

$user_input=mysql_real_escape_string($_REQUEST['user_input']);
$search_row=mysql_query("SELECT * FROM some_table WHERE some_column LIKE '%$user_input%' ");
while($search = mysql_fetch_array($search_row))
            {stuff happens}

echo "<form action='search_results.php' method='post'>";
echo "<input name='user_input' type='text' size='50' value='" . $user_input. "'>";
echo "<input type='submit' value='Lookup Parts' />";
echo "</form>";

But the problem is, I can't seem to get anything but errors.
The search field (which is supposed to populate with what they already put in) just has:

1/4\" Steve\

What am I doing wrong?

Answer 1

The search field (which is supposed to populate with what they already put in) just has 1/4\" Steve\

of course it has!
You misplaced your escaping. mysql_real_escape_string is for SQL only! but you're using it's result for the html. While for the HTML you have to use completely different way of escaping.

So, make it

$user_input=mysql_real_escape_string($_REQUEST['user_input']);
$search_row=mysql_query("SELECT * FROM some_table WHERE some_column LIKE '%$user_input%' ");
while($search = mysql_fetch_array($search_row))
            {stuff happens}


$user_input =htmlspecialchars($_REQUEST['user_input'],ENT_QUOTES); // here it goes

echo "<form action='search_results.php' method='post'>";
echo "<input name='user_input' type='text' size='50' value='$user_input'>";
echo "<input type='submit' value='Lookup Parts' />";
echo "</form>";

also note that there is no use in echoing such large chunks of HTML. Just close PHP tag and then write pure HTML:

?>
<form action='search_results.php' method='post'>
<input name='user_input' type='text' size='50' value='<?=$user_input?>'>
<input type='submit' value='Lookup Parts' />
</form>

Looks WAY more clear, readable and convenient