用php将特殊字符传递给mysql数据库

Question

I seem to be having a small issue with my database here. I'm using mySQL and posting using php, however the problem I face is that I can't post special characters to the database because they're using in php and causes an error. So I believe the best option is using mysqli_real_escape_string(). So I set up my vairaible that I'm using as the values to post to my database with the mysqli_real_escape_string() and I'm getting the following error:

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given

Which makes be think I haven't set it up correctly as I thought I only needed to pass the one parameter not two. I have my php set up like this if anyone can correct me that would be great:

<?php

  $servername = "localhost";

  $username = "root";

  $password = "";

  $dbname = "dbname";

  $conn = new mysqli($servername, $username, $password, $dbname);

  if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    $name = mysqli_real_escape_string(strtolower($_POST['name']));

    $header = strtolower($_POST['header']);

    $address = strtolower($_POST['address']);

    $city = strtolower($_POST['city']);

    $county = strtolower($_POST['county']);

    $post = strtolower($_POST['post']);

    $tele = strtolower($_POST['tele']);

    $mob = strtolower($_POST['mob']);

    $email = strtolower($_POST['email']);

    $web = strtolower($_POST['web']);


    $sql1 = mysqli_query($conn, "SELECT * FROM business_dir WHERE `name` = '$name'");

    $matchFound = mysqli_num_rows($sql1) > 0 ? 'true' : 'false';

    if ($matchFound == 'false') {

      $sql2 = "INSERT INTO business_dir (`name`, `header`, `address`, `city`, `county`, `post`, `tele`, `mob`, `email`, `web`) VALUES ('$name', '$header', '$address', '$city','$county','$post', '$tele', '$mob', '$email', '$web')";

      if ($conn->query($sql2) === TRUE) {

          echo '<div class="alert alert-success text-center" style="margin:20px;" role="alert">Business Succesfully Added!</div>';              

      }

      else {

        echo '<div class="alert alert-danger text-center" style="margin:20px;" role="alert">Error: ' . $sql2 . "<br>" . $conn->error.'</div>';

      }

    }

    else {

      echo '<div class="alert alert-danger text-center" style="margin:20px;" role="alert">Business Failed To Be Added, An Entry With The Same Name Already Exists!</div>';

    }

  }

?>

Thanks guys.

Answer 1

You're heavily mixing object oriented functions and procedural functions which will not work. I've converted your full example to an object oriented approach below:

<?php    
    $servername = "localhost";
    $username = "root";
    $password = "";  
    $dbname = "dbname";

    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        $name = $conn->real_escape_string(strtolower($_POST['name'])); 
        $header = strtolower($_POST['header']);
        $address = strtolower($_POST['address']);
        $city = strtolower($_POST['city']);    
        $county = strtolower($_POST['county']);
        $post = strtolower($_POST['post']);
        $tele = strtolower($_POST['tele']);    
        $mob = strtolower($_POST['mob']);    
        $email = strtolower($_POST['email']);    
        $web = strtolower($_POST['web']);

        $stmt = $conn->prepare("SELECT * FROM business_dir WHERE `name` = ?");
        $stmt->bind_param("s", $name);
        $stmt->execute();
        $stmt->store_result();

        $matchFound = $stmt->num_rows > 0 ? TRUE : FALSE;

        // Close the prepared statement
        $stmt->close();

        if ($matchFound === FALSE) {  
            $stmt = $conn->prepare("INSERT INTO business_dir (`name`, `header`, `address`, `city`, `county`, `post`, `tele`, `mob`, `email`, `web`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->bind_param("ssssssssss", $name, $header, $address, $city, $county, $post, $tele, $mob, $email, $web);

            if ($stmt->execute() == TRUE) {
                echo '<div class="alert alert-success text-center" style="margin:20px;" role="alert">Business Succesfully Added!</div>';              
            } else {
                echo '<div class="alert alert-danger text-center" style="margin:20px;" role="alert">Error: ' . $sql2 . "<br>" . $conn->error.'</div>';
            }

            $stmt->close();
        } else {
            echo '<div class="alert alert-danger text-center" style="margin:20px;" role="alert">Business Failed To Be Added, An Entry With The Same Name Already Exists!</div>';
        }
    }

    // Close the mysqli connection
    $conn->close();
?>

In addition, mysqli_real_escape_string() offers little protection against SQL Injection Attacks. As such, I've modified your example to use prepared statements for added security.