Passing special characters to a mysql database with php

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.

Exactly what the error says. For mysqli_real_escape_string() you need to pass the connection as a variable to the function like `mysqli_real_escape_string($conn, $_POST['firstname']))`. However your code is wide open to sql injection attacks. You should be using prepared statements — Dimitris Filippou, May 10 '18 at 16:32

score 0 · Answer 1 · answered May 10 '18 at 16:29

0

Relatively simple, you have to add $conn to your mysqli_real_escape_string(). For example;

$firstname = mysqli_real_escape_string($conn, $_POST['firstname']);

answered May 10 '18 at 16:29

Julian Koster

262
1
10

1

Actually, I don't believe this will work. The OP is heavily mixing object oriented and procedural style commands. `$conn` in this case is a `mysqli()` object not a connection `link` as described in the [docs](http://php.net/manual/en/mysqli.real-escape-string.php)... – War10ck May 10 '18 at 16:35

score 0 · Answer 2 · answered May 10 '18 at 16:32

0

The mysqli_real_escape_string function has to include your connection to the database.

http://php.net/manual/en/mysqli.real-escape-string.php

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given... what i do wrong?

answered May 10 '18 at 16:32

Lee

135
1
1
11

War10ck · Accepted Answer · 2018-05-10T17:50:28.113

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.

Passing special characters to a mysql database with php

3 Answers3