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

Question

I get data into my database without any problems using mysql_real_escape_string.

Thus, a record in the database can be:

1/4" Steve lugnuts 

So great in the database.
Now I want to find the exact things. But, it will spoil either "or" (I tried several things, and it always bothers somewhere).

Here's what I have now: (user_input comes from the 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 that I cannot get anything but errors.
The search field (which should be filled with what they already inserted) has only:

 1/4\" Steve\ 

What am I doing wrong?

Answer 1

The search field (which should be filled with what they already inserted) has 1/4\" Steve\

of course it is! You have evaded your escape. mysql_real_escape_string for SQL only! but you use it for html. Although for HTML you should use a completely different way of escaping.

So do 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 it makes no sense to repeat such large chunks of HTML. Just close the 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 quotes for MySQL and for HTML, and you can probably also set magic_quotes_gpc! When quoting, you always quote text for a specific output file , for example:

• string value for mysql query
• like expression for mysql query
• html code
• Json
• mysql regex
• php regex

For each case, you need a different quote, because each use is present in a different syntax context. This also implies that quoting should not be done at the input to PHP, but at a specific output ! For this reason, functions like magic_quotes_gpc are broken ( make sure they are turned off !!! ).

So, what methods can be used for citation in these specific cases? (Feel free to correct me, there may be more modern methods, but they work for me)

• mysql_real_escape_string($str)
• mysql_real_escape_string(addcslashes($str, "%_"))
• htmlspecialchars($str)
• json_encode() - only for utf8! I use my function for iso-8859-2
• mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case, because the backslash will be reset twice!
• preg_quote()

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

Answer 3

I don’t know if this helps for sure, but shouldn't you run away for the request and again 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)."'>"; 

change

you may not want to change (exit) your login ($ user_input) every time you submit .. although if it only had an “and” it affected it, it probably wouldn't matter.

Answer 4

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

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