Error Message!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
NiallM1974
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Error Message!

Post by 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'; ?>
Attachments
new-article02.jpg
new-article01.jpg
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Re: Error Message!

Post by 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;
}
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by Benjamin »

No, move

Code: Select all

var_dump($sql);
up so it is below the

Code: Select all

echo mysqli_error($conn);
line.
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Re: Error Message!

Post by 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?
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Re: Error Message!

Post by NiallM1974 »

I am using version 7.3
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Re: Error Message!

Post by NiallM1974 »

How do I turn off the magic quotes?
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Re: Error Message!

Post by NiallM1974 »

The magic quotes were already turned off:

magic_quotes_gpc=Off
magic_quotes_runtime=Off
magic_quotes_sybase=Off
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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
Forum Newbie
Posts: 7
Joined: Mon Jan 18, 2021 5:53 am

Re: Error Message!

Post by 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;
}
User avatar
Benjamin
Site Administrator
Posts: 6905
Joined: Sun May 19, 2002 10:24 pm

Re: Error Message!

Post by 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>

Post Reply