PHP SQL error help and security improvements

Question

I have some code on my website that checks if an input value of an input field exists in a database and then outputs the results as simple as possible.

Unfortunately it does not give me "No results found" if a wrong value is sent. 1 - How can I fix this? There's no error message or anything else, just a white page.

2 - Another question: is it possible for the public to get my database login credentials using this type of checkup of an input field with a separated search.php with the login credentials placed on top of the file?

3 - How can I secure this checkup with my php code against any main attacks? I think SQL injection is already prevented.

Thanks

index.php:

<form action="search.php" method="Post">
    <input type="text" name="query" />
    <input type="submit" value="Search" />
</form>

search.php:

<?php
$mydatabase=mysqli_connect("localhost", "root", "") or die("Error connecting to database: ".mysql_error());

mysqli_select_db($mydatabase, "mydb") or die(mysql_error());

$query = $_POST['query']; 
$min_length = 1;

if(strlen($query) >= $min_length){
  $query = htmlspecialchars($query); 
  $query = mysqli_real_escape_string($mydatabase, $query);

  $raw_results = mysqli_query($mydatabase, "SELECT * FROM mytable WHERE (`title` LIKE '%".$query."%')") or die("mysql_error()");

  while($results = mysqli_fetch_array($raw_results)){
    if (empty($results)) { 
      echo 'No results found'; 
    } else { 
      echo "<p><h3>".$results['title']."</h3>".$results['name']."</p>";
    }
  }
} else{
  echo "Minimum length is ".$min_length;
}
?>

Security step 1, parameterize query. Don't use escaping. Step 2, don't escape every, use `htmlspecialchars` only for outputting to the DOM. For the no results issue you won't enter the `while` with no results. Use https://www.php.net/manual/en/mysqli-result.num-rows.php to see if you have rows. (SQL injection is not prevented with this code as is) — user3783243, Apr 16 '20 at 02:36

Blizzardengle · Accepted Answer · 2020-04-16T02:53:48.837

Answer to Question 1
The manual page for mysqli_fetch_array reminds you that it will return NULL if there are no rows in the result set. If $results gets set to NULL your while loop will never run and that is why you never see your No results found message. You need to do the empty check outside the while loop or switch it to a do-while loop.

Answer to Question 2
I'm not sure I understand exactly what your asking but as long as your credentials are inside PHP tags inside a .php file no one should publicly be able to see these.*

* There are definitely exceptions to this statement. Best practice is to use environment variables for your credentials. At the very least global variables loaded from a separate file just in case the raw PHP file is exposed to users. All they would see in this case is the environment variable or global variable and not your actual credentials.

Answer to Question 3
I think your use of htmlspecialchars and mysqli_real_escape_string is a good start to protecting your database but you really should read up on the shortcomings those functions might introduce. I wound suggest looking into prepared statements. The PHP manual is a wealth of information but this SO Answer does a great job too; in case you miss it that answer I linked to links to another answer you'll also find very helpful.

PHP SQL error help and security improvements

1 Answers1