Creating a database search bar query

Question

I am trying to create a simple search bar that allows the user to search using a user id to return a specific user.

I need to include SQL injection protection.

currently the page loads a blank screen but when I refresh the page I get the "No results found" response.

I have been reading a lot of posts about search bars but nothing is helping me.

here is the code:

<html>
<head>
<title></title>
</head>
<body>
<form action="search.php" method="POST">
    <input type="text" name="search" />
    <input type="submit" value="Search" />
</form>
</body>



<?php
//search.php
  include("DbConnect.php");

  $search    = $_POST['search'];

  $safesearch = mysqli_real_escape_string($search);

  $Query = "SELECT user_id
            FROM users
            WHERE user_id = '$safesearch'";

  $Result = mysqli_query($DB,$Query);    

  $NumResults = mysqli_num_rows($Result);   

  if ($NumResults==1)
    {    
        echo "<h1>results: ".$Result."</h1>";
    }else{
        echo "<h1>No Results found</h1>";
  }
?>

@chriswatson that is incorrect usage. The `mysqli_real_escape_string` requires a connection string when used procedurally. — chris85, Feb 22 '17 at 20:01
@chris85 can you elaborate? Im familiar with the concept but I haven't spent much time with PHP. — chris watson, Feb 22 '17 at 20:02
For escaping, http://php.net/manual/en/mysqli.real-escape-string.php, for parameterizing, http://php.net/manual/en/mysqli.quickstart.prepared-statements.php. — chris85, Feb 22 '17 at 20:02

clearshot66 · Answer 1 · 2017-02-22T20:08:40.207

You should have an if(isset($_POST['submit']{ } around your code so it only fires if they search and not when the page is loaded.

If you're doing any sort of insert,select or update with sql statements that will have variables within them you should use prepared statements.

Use a prepared statement it's a lot more safe than what you're doing:

<?php
//search.php
  include("DbConnect.php");

  $search    = $_POST['search'];

  $safesearch = mysql_real_escape_string($search);

  $Query = "SELECT user_id
            FROM users
            WHERE user_id = ?";

  $stmt = $connectionVariableName->prepare($query);
  $stmt->bind_param("s",$safesearch);
  $stmt->execute();
  $stmt->bind_result($result);
  $stmt->fetch();
  $num_rows = $stmt->num_rows;
  $stmt->close();   

  if ($num_rows > 0)
    {    
        echo "<h1>results: ".$result."</h1>";
    }else{
        echo "<h1>No Results found</h1>";
  }
?>

http://php.net/manual/en/mysqli.prepare.php

You should also sanitize the search variable by testing it with regex to make sure it only contains the characters you allow in the userid and not / * ^ etc

thanks - I have had a read of this, it seems very complicated for my current level. I will try to understand this and use it if possible. — chris watson, Feb 22 '17 at 20:10
Go to the php.net site I sent and it will show you exactly what you need to do. If you want security you need to learn prepared statements eventually anyway. I have faith in you :) But to stop the refresh error put your php code within the isset like I stated at first. That says that only run this code if submit (search) is clicked. — clearshot66, Feb 22 '17 at 20:13

score 0 · Answer 2 · answered Feb 22 '17 at 21:54

You want partial phrase search, I presume.

Then your query must look like this:

$Query = "SELECT user_id
            FROM users
            WHERE user_id LIKE '%$safesearch%'";

Not very familiar with php, but % symbol seem not being a special character in the language (correct me if I'm wrong), if it by chance is - escape it.

To make it case insensitive -

$safesearch = strtolower($safesearch);

$Query = "SELECT user_id
                FROM users
                WHERE lowercase(user_id) LIKE '%$safesearch%'";

Creating a database search bar query

2 Answers2