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

Answer 2

Well, your problem is proper quoting. Your problem is that you need different quoting for MySQL and for HTML, and you probably could also have magic_quotes_gpc set! When quoting, you always quote text for some particular output, like:

1. string value for mysql query
2. like expression for mysql query
3. html code
4. json
5. mysql regular expression
6. php regular expression

For each case, you need different quoting, because each usage is present within different syntax context. This also implies that the quoting shouldn't be made at the input into PHP, but at the particular output! Which is the reason why features like magic_quotes_gpc are broken (assure it is switched off!!!).

So, what methods would one use for quoting in these particular cases? (Feel free to correct me, there might be more modern methods, but these are working for me)

1. mysql_real_escape_string($str)
2. mysql_real_escape_string(addcslashes($str, "%_"))
3. htmlspecialchars($str)
4. json_encode() - only for utf8! I use my function for iso-8859-2
5. mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case because backslash would be escaped two times!
6. preg_quote()

EDIT: Regarding your original question - if you correct your quoting, you can then of course use any characters in the strings, including the single and double quotes.

Answer 3

Print your sentece "SELECT * FROM some_table WHERE some_column LIKE '%$user_input%' " to see what it is doing (and escaping).

Not a solution but take a look to mysqli or pdo (http://stackoverflow.com/questions/548986/mysql-vs-mysqli-in-php), they have utilities for prepared statements.

Answer 4

don't know if it helps for sure, but shouldn't you escape for the query and another time for the html?

$query = sprintf("SELECT * FROM some_table WHERE some_column LIKE '%s' ", mysql_real_escape_string($user_input));

echo "<input name='user_input' type='text' size='50' value='".htmlentities($user_input)."'>";

edit

you maybe don't want to change (escape) your input ($user_input) everytime you submit ..although if its only ' and " thats affected it might not matter anyway