Error Message!

[NiallM1974]

Hi Guys,

I have been practicing running a test page called new-article.php. How ever when I click the [Add button I get the message:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'clock', '','')' at line 2

Here is the code for new-article.php:

Code: Select all

<?php

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

    require 'includes/database.php';

    $sql = "INSERT INTO article (title, content, published_at)
            VALUES ('" . mysqli_escape_string($conn, $_POST['title']) . "','"
                       . mysqli_escape_string($conn, $_POST['content']) . "','"
                       . mysqli_escape_string($conn, $_POST['published_at']) . "')";

    $results = mysqli_query($conn, $sql);

    if ($results === false) {

        echo mysqli_error($conn);

    } else {

        var_dump($sql);

        $id = mysqli_insert_id($conn);
        echo "Inserted record with ID: $id";

    }
}

?>
<?php require 'includes/header.php'; ?>

<h2>New article</h2>

<form method="post">

    <div>
        <label for="title">Title</label>
        <input name="title" id="title" placeholder="Article title">
    </div>

    <div>
        <label for="content">Content</label>
        <textarea name="content" rows="4" cols="40" id="content" placeholder="Article content"></textarea>
    </div>

    <div>
        <label for="published_at">Publication date and time</label>
        <input type="datetime-local" name="published_at" id="published_at">
    </div>

    <button>Add</button>

</form>

<?php require 'includes/footer.php'; ?>

[Benjamin]

It would appear the values in the query aren't actually being escaped. Can you echo the database query and post it here please?

[NiallM1974]

Is this what you mean Benjamin:

Code: Select all

<?php

$db_host = "localhost";
$db_name = "cms";
$db_user = "cms_www";
$db_pass = "";

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

if (mysqli_connect_error()) {
    echo mysqli_connect_error();
    exit;
}

[Benjamin]

No, move

Code: Select all

var_dump($sql);

up so it is below the

Code: Select all

echo mysqli_error($conn);

line.

[Benjamin]

I think you have Magic Quotes enabled. This is causing the $_POST data to be escaped twice. You can verify this by running:

Code: Select all

echo get_magic_quotes_gpc();

If the output is "1", find your php.ini file, turn it off, and then restart apache.

[NiallM1974]

Is this what you mean Ben:

Code: Select all

<?php

require 'includes/database.php';

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

    $conn = getDB();

    $sql = "INSERT INTO article (title, content, published_at)
            VALUES ('" . $_POST['title'] . "','"
                       . $_POST['content'] . "','"
                       . $_POST['published_at'] . "')";

    $stmt = mysqli_prepare($conn, $sql);

    if ($stmt === false) {

        echo mysqli_error($conn);
        var_dump($sql);

    } else {

        if (mysqli_stmt_execute($stmt)) {

            $id = mysqli_insert_id($conn);
            echo "Inserted record with ID: $id";

        } else {

            echo mysqli_stmt_error($stmt);

        }
    }
}

?>

What are magic quotes?

[Benjamin]

Yes that is what I meant.

Here is some information on magic quotes: https://en.wikipedia.org/wiki/Magic_quotes

You may be using an outdated version of PHP. Do you know what version you are using?

[NiallM1974]

I am using version 7.3

[Benjamin]

So if Magic Quotes are enabled, your query would look like:

Code: Select all

INSERT INTO article (title, content, published_at) VALUES ('Ten O\\'Clock', '...')     

Instead of:

Code: Select all

INSERT INTO article (title, content, published_at) VALUES ('Ten O\'Clock', '...')

You should ensure Magic Quotes are turned off, and learn the proper practices for escaping data for queries. The code in your original post looked fine.

[NiallM1974]

How do I turn off the magic quotes?

[Benjamin]

Create a file containing the following:

Code: Select all

<?php
phpinfo();

When you run this in a browser, it will tell you where your php.ini file is. Inside of that file, locate the directive for magic_quotes, and set it to 0 or false, then restart apache.

[NiallM1974]

The magic quotes were already turned off:

magic_quotes_gpc=Off
magic_quotes_runtime=Off
magic_quotes_sybase=Off

[Benjamin]

Ok, that brings us back to where we started.

I see you rewrote the code a bit. Can you please post all your code, both files, so I can run it locally and test it. Please don't included the password.

[NiallM1974]

new-article.php

Code: Select all

<?php

require 'includes/database.php';

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

    $conn = getDB();

    $sql = "INSERT INTO article (title, content, published_at)
            VALUES ('" . $_POST['title'] . "','"
                       . $_POST['content'] . "','"
                       . $_POST['published_at'] . "')";

    $stmt = mysqli_prepare($conn, $sql);

    if ($stmt === false) {

        echo mysqli_error($conn);
        var_dump($sql);

    } else {

        if (mysqli_stmt_execute($stmt)) {

            $id = mysqli_insert_id($conn);
            echo "Inserted record with ID: $id";

        } else {

            echo mysqli_stmt_error($stmt);

        }
    }
}

?>
<?php require 'includes/header.php'; ?>

<h2>New article</h2>

<form method="post">

    <div>
        <label for="title">Title</label>
        <input name="title" id="title" placeholder="Article title">
    </div>

    <div>
        <label for="content">Content</label>
        <textarea name="content" rows="4" cols="40" id="content" placeholder="Article content"></textarea>
    </div>

    <div>
        <label for="published_at">Publication date and time</label>
        <input type="datetime-local" name="published_at" id="published_at">
    </div>

    <button>Add</button>

</form>

<?php require 'includes/footer.php'; ?>

database.php

Code: Select all

<?php

$db_host = "localhost";
$db_name = "cms";
$db_user = "cms_www";
$db_pass = "";

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

if (mysqli_connect_error()) {
    echo mysqli_connect_error();
    exit;
}

[Benjamin]

This code works for me, please try it:

Code: Select all

<?php

ini_set('display_errors', true);
error_reporting(E_ALL);

require 'database.php';

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

    //$conn = getDB();

    $sql = "INSERT INTO article (title, content, published_at)
            VALUES ('" . mysqli_real_escape_string($conn, $_POST['title']) . "','"
                       . mysqli_real_escape_string($conn, $_POST['content']) . "','"
                       . mysqli_real_escape_string($conn, $_POST['published_at']) . "')";

    $stmt = mysqli_prepare($conn, $sql);

    if ($stmt === false) {

        echo mysqli_error($conn);
        var_dump($sql);

    } else {

        if (mysqli_stmt_execute($stmt)) {

            $id = mysqli_insert_id($conn);
            echo "Inserted record with ID: $id";

        } else {

            echo mysqli_stmt_error($stmt);

        }
    }
}

?>

<h2>New article</h2>

<form method="post">

    <div>
        <label for="title">Title</label>
        <input name="title" id="title" placeholder="Article title">
    </div>

    <div>
        <label for="content">Content</label>
        <textarea name="content" rows="4" cols="40" id="content" placeholder="Article content"></textarea>
    </div>

    <div>
        <label for="published_at">Publication date and time</label>
        <input type="datetime-local" name="published_at" id="published_at">
    </div>

    <button>Add</button>

</form>