0

I'm doing a school project writing a simple website (in this case, the theme is Harry Potter) One page is supposed to display infomation from MySQL database about the different houses.

<?php
    $dbname = 'hogwarts';
    $dbuser = 'Albus';
    $dbpass = 'SherbertLemon';
    $dbhost = 'localhost';

    $link = mysqli_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
    mysqli_select_db($link, $dbname) or die("Could not open the db '$dbname'");
      $housename = '';
      if (isset($_GET['house'])) { 
        $housename = $_GET['house'];
        $sql = "SELECT info FROM houses WHERE name='$housename'";
        $result = mysqli_query($link,$sql);

        if (!$result) { echo "Could not get data."; }
        else {
            $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
            $info = "{$row['info']}";
        }
      }
?>

Now, as it were, the line $sql = "SELECT info FROM houses WHERE name='$housename'"; allows for SQL-injections and my question is - how do I sanitize and/or turn this into prepared statements to prevent and better secure my site?

Kindest regards

EDIT I tried query parameters as suggested and this is what I came up with. It works and SQLmap finds no injection points :)

<?php
    $dbname = 'hogwarts';
    $dbuser = 'Albus';
    $dbpass = 'SherbertLemon';
    $dbhost = 'localhost';
    $info = "Not found";
    $link = mysqli_connect($dbhost, $dbuser, $dbpass) or die("Unable to Connect to '$dbhost'");
    mysqli_select_db($link, $dbname) or die("Could not open the db '$dbname'");
      $housename = '';
      if (isset($_GET['house'])) { 
        $housename = $_GET['house'];


        
        $stmt = $link->prepare("SELECT info FROM houses WHERE name=?");
        $stmt->bind_param("s",$housename);
        $stmt->execute();
        $result = $stmt->get_result();


        //$sql = "SELECT info FROM houses WHERE name='$housename'";
        //$result = mysqli_query($link,$sql);

        if (!$result) { echo "Could not get data."; }
        else {
            $row = mysqli_fetch_array($result,MYSQLI_ASSOC);
 
         if    ($row)
            $info = "{$row['info']}";
        }
      }
?>
Jan Andersen
  • 11
  • 1
  • 1
    Please do not sanitize anything. Do not use such word at all. Use prepared statements and forget about any sanitizing. – Dharman Dec 12 '22 at 17:13
  • It's more to the point to say use _query parameters_. Using prepared statements but continuing to combine PHP variables into queries by concatenation or variable expansion is missing the point. – Bill Karwin Dec 12 '22 at 17:31
  • 1
    There are plenty of examples of using query parameters in the documentation or in the linked question. If you're in over your head, keep studying. This stuff isn't easy, it takes some effort to learn. But it's not impossible. With some reading and practice, you can do it. – Bill Karwin Dec 12 '22 at 17:34
  • By the way, `if (!$result) { echo "Could not get data."; }` is useless and `or die("Unable to Connect to '$dbhost'");` is harmful. Please see correct examples here: https://phpdelusions.net/mysqli – Your Common Sense Dec 14 '22 at 13:27

0 Answers0