Entering single quotes in search field results in mysqli_num_rows error

Question

I've been messing around with my search page. I added both mysqli_real_escape_string and strip_tags and as far as I can tell everything works fine and should be safe.

I was just trying to enter random symbols in the search form, to see whether all my database entries could still be found with these safety measures in place. All good, except when I search for a single quotation mark (').

This gives me the "mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given" error. There's a lot of information about that error and I know how to solve it (I thought so at least). However, in this particular case I have no clue :/

The code:

ini_set('display_errors', 1);
$search = $_GET ['q'];
$conn = mysqli_connect("localhost", "root", "","release");
$search = mysqli_real_escape_string($conn, "$search");
$search = stripslashes(strip_tags(trim("$search")));

$query = mysqli_query($conn,"SELECT * FROM game WHERE game_name LIKE '%". $search ."%' ORDER BY game_release");
$count = mysqli_num_rows($query);

You have to use bind parameter mechanism. It is shown here: http://stackoverflow.com/questions/15748254/how-to-run-the-bind-param-statement-in-php — Kancho Iliev, Apr 18 '15 at 07:46

score 1 · Accepted Answer · answered Apr 18 '15 at 07:56

You should use mysqli_real_escape_string at the very end, right before you inject the variable in your database query although prepared statements are preferred. And easier as far as I am concerned.

Your current problem is not that you cannot do it with just mysqli_real_escape_string, but the combination and order of functions you use:

$search = mysqli_real_escape_string($conn, "$search");
$search = stripslashes(strip_tags(trim("$search")));

You are escaping the special characters for mysql values in the first line. That is done by adding slashes.

And in the second line you remove the slashes, undoing your escaping with mysqli_real_escape_string and making your query vulnerable to sql injection.

As far as the security for the sql statement is concerned, mysqli_real_escape_string is enough, so for that you would only need a prepared statement or:

$search = mysqli_real_escape_string($conn, $search);

The rest is unnecessary but if you want to do that any way (if tags are not allowed for example), you should move that line to above the final escaping line:

$search = stripslashes(strip_tags(trim($search)));
$search = mysqli_real_escape_string($conn, $search);

// and now run your query without further manipulation:
$query = mysqli_query(...);

Thank you for your clear explanation Jeroen. Receiving constructed answers such as this one really helps me a lot! — Michael, Apr 18 '15 at 08:18

Entering single quotes in search field results in mysqli_num_rows error

1 Answers1