Real_escape_string prevents INSERT statement from working MYSQL PHP

Question

So I'm making my own blog scripts using MYSQL and PHP. I had the whole 'writing the blog to a database' thing working perfectly, until I realised that if you tried to write a blog with speech marks, this would prevent the INSERT statement from working (obviously - the speechmarks were ending the SQL statement).

So I tried to use real_escape_string, and now the INSERT doesn't work even if you exclude quotes. I tried using:

sqlstate

in order to find out the issue, and it returned "42000" - which, after googling for a little bit, refers to a syntax error, which doesn't make much sense as there is no syntax error before the use of real_escape_string.

Also, I'm now getting this error:

 Call to a member function close() on a non-object in /postarticle.php on line 37

Which refers to the close() call in the ELSE statement.

Please may you help? Been going round in circles for a while. Here is my code:

<?php
$host = 'CENSORED';
$user = 'CENSORED';
$pass = 'CENSORED';
$db = 'CENSORED';

$connection = new mysqli($host,$user,$pass,$db);

$_SESSION["article"] = $_POST["article"];
$date_of_blog = getdate(); 

$article = ($_SESSION["article"]);

$sql1 = "SELECT * FROM `Blogs`";
$res1 = $connection->query($sql1);
$newrows = $res1->num_rows + 1;

$sql2 = "INSERT INTO Blogs(BlogID, Blog_Contents, D_O_B) VALUES ('$newrows','$article','$date_of_blog')";
$sql2 = $connection->real_escape_string($sql2);
$res2 = $connection->query($sql2);

if ($res2->num_rows == $newrows)
{
    $res->close();
    $connection->close();
    header( 'Location: adminpanel.php' );

}
else
{

echo ($connection->sqlstate);
$connection->close();
$res->close();
}

exit();


?>

Also, on a side note, the getdate() call that I've got has never worked. In the database every blog post comes up as: 0000:00:00 00:00:00

EDIT: Issue is now solved. Find the functional code below:

<?php
$host = 'CENSORED';
$user = 'CENSORED';
$pass = 'CENSORED';
$db = 'CENSORED';


$connection = new mysqli($host,$user,$pass,$db);

$_SESSION["article"] = $_POST["article"];

$article = ($_SESSION["article"]);

$article = $connection->real_escape_string($article);

$sql1 = "SELECT * FROM `Blogs`";
$res1 = $connection->query($sql1);
$newrows = $res1->num_rows + 1;

$sql2 = "INSERT INTO Blogs(BlogID, Blog_Contents, D_O_B) VALUES (\"$newrows\",\"$article\",CURDATE())";
$res2 = $connection->query($sql2);

if ($res2 != false)
{
    header( 'Location: adminpanel.php' );
}
else
{   
    echo ($connection->sqlstate);
}
$connection->close();
$res->close();


exit();


?>

I'm very sorry if these questions are basic and annoy the professionals around here; I've tried to follow the guidelines and I've googled for a while etc. I just haven't found any solutions that match my issue(s). Thankyou for your time.

Answer 1

There are a number issues with the code as originally posted. Chiefly, the cause of the two issues you initially identified is a misuse of mysqli::real_escape_string(). It needs to be called on each variable individually which appears in the code. So instead of calling it on the whole statement, it must be called multiple times for multiple variables like:

$article = $connection->real_escape_string($connection);

The failure of the query due to incorrect quoting (due to real_escape_string()) is the reason for the error message calling close().

As ascertained in the comments, you are using num_rows + 1 to validate that one new row has been inserted based on the previous number of rows returned. This is problematic for a few reasons. Mainly, it exposes a race condition wherein a row may be inserted from two sessions at once and one or both will fail because the expected value for $newrows doesn't match. Really BlogID should be an auto_increment column in your database. That eliminates the need for any logic around it whatsoever. You don't even need to include it in the INSERT because it will be automatically incremented.

That also completely eliminates the need for the first SELECT statement.

Substituting MySQL's native NOW() function for the date value, you can simplify the statement to:

INSERT INTO Blogs (Blog_Contents, D_O_B) VALUES ('$article', NOW())

To test success or failure of the insert, you just need to verify that its variable is not false.

Putting this together, your code can be reduced as:

if (!isset($_POST['article'])) {
   // exit or handle an empty post somehow...
}
$connection = new mysqli($host,$user,$pass,$db);
$_SESSION["article"] = $_POST["article"];

// Escape $article for later use
$article = $connection->real_escape_string($_SESSION["article"]);

// Only an INSERT is needed. $article is already escaped
$sql = "INSERT INTO Blogs (Blog_Contents, D_O_B) VALUES ('$article', NOW())";
// Run the query
$res = $connection->query($sql);

// Test for failure by checking for a false value
if ($res) {
  // The connection & resource closure can be omitted
  // PHP will handle that automatically and implicitly.
  header( 'Location: adminpanel.php' );
  // Explictly exit as good practice after redirection
  exit();
}
else {
  // The INSERT failed. Check the error message
  echo $connection->error;
}

This should bring your current code into working order. However, since you're learning this it is an excellent time to begin learning to use prepared statements via prepare()/bind_param()/execute() in MySQLi. This is a recommended best practice to prevent SQL injection, although using real_escape_string() works as long as you use it correctly and never forget.

See How can I prevent SQL injection in PHP for examples.

But it would look like:

// connection already established, etc...
// Prepare the statement using a ? placeholder for article
$stmt = $connection->prepare("INSERT INTO Blogs (Blog_Contents, D_O_B) VALUES (?, NOW())");
if ($stmt) {
  // bind in the variable and execute
  // Note that real_escape_string() is not needed when using
  // the ? placeholder for article
  $stmt->bind_param('s', $_SESSION['article']);
  $stmt->execute();

  // Redirect
  header( 'Location: adminpanel.php' );
  exit();
}
else {
  echo $connection->error;
}

Answer 2

You need to apply the real_escape_string function to the variables not the entire SQL string.

$sql2 = "INSERT INTO Blogs(BlogID, Blog_Contents, D_O_B) VALUES ('".$connection->real_escape_string($newrows)."','".$connection->real_escape_string($article)."','".$connection->real_escape_string($date_of_blog)."')";

The purpose is to remove anything that might be misinterpreted as query functions by MySQL, but there are parts of the query that you obviously want to be interpreted as such.