I'm getting the following error message, while inserting data into a MySQL database table:

Sorry, an error occurred while inserting data into the database. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?), admin, $1$2W/.hV3.$3iwUzDrlU4BvNPp80vy8J0, ' at line 1

The following is my PHP code:

<?php


// include configuration file
require ("../library/config.php");
require_once '../library/functions.php';


// if form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST")
{

    // validate submission
    if (empty($_POST["username"]))
    {
        echo nl2br ("Provide a username. \n");
    }
    else if (empty($_POST["password"]))
    {
        echo nl2br ("Enter a password. \n");
    }
    else if (empty($_POST["confirmation"]))
    {
        echo nl2br ("Confirm your password. \n");
    }
    else if ($_POST["password"] != $_POST["confirmation"])
    {
        echo ("Password and confirmation do not match. \n");
    }
    if (empty($_POST["email"]))
    {
        echo ("Provide your email address. \n");
    }

    if (!empty($_POST["username"]))
    {

        //This gets all the other information from the form
        $username = $_POST["username"];
        $password = crypt($_POST["password"]);
        $email = $_POST["email"];

        // validate username
        $username = ($_POST["username"]);
        if (!preg_match("/^[a-zA-Z0-9]*$/", $username))
        {
            echo "Username must contain only letters and numbers.";
        }
        if (strlen($username) < 4 || strlen($username) > 10)
        {
            echo "Username must be from 4 to 10 characters.";
        }
        // validate email address
        $email = ($_POST["email"]);
        if (!preg_match("/([\w\-]+\@[\w\-]+\.[\w\-]+)/", $email))
        {
            echo "Invalid email address.";
        }
        if ($_POST["email"] === false)
        {
            echo "The email has already been taken.";
        }

        //$query = "INSERT INTO admin (username, hash, email) VALUES ($username, $password, $email)";
        $query = "INSERT INTO admin (username, hash, email) VALUES (?, ?, ?),
        $username,
        $password,
        $email";  

        // if username is in database
        mysql_query($query) or die('Sorry, an error occurred while inserting data into the database. ' . mysql_error());                    


        // redirect to admin homepage            
        echo "<script>window.location.href='index.php';</script>";
        exit;
    }
}
?>

Any help would be highly appreciated.

Re: I need help inserting data into MySQ database via PHP 80 80
Member Avatar
  1. Do not use mysql_* functions they are deprecated - use prepared statements (PDO/mysqli) - see my tute: DW Tutorial: Common Issues with MySQL and PHP

You seem to be having this issue because you have written a prepared statement but are using mysql_query - which doesn't support them. Also you do not bind the parameters/values for the statement. How is MySQL to know which values you are trying to bind (substitute)?

commented: Thanks +3
Re: I need help inserting data into MySQ database via PHP 80 80

Assuming your not going to take diafol's advice, here's the quick fix:

$query = "INSERT INTO admin (username, hash, email) 
    VALUES ('$username', '$password', '$email')"; 
Re: I need help inserting data into MySQ database via PHP 80 80

@diafol and @pritaeas, a million thanks.

Please, what's the MySQLi's equivalent of this code:

mysql_query($query) or die ('Sorry, an error occurred while inserting data into the database. ' . mysql_error());
Re: I need help inserting data into MySQ database via PHP 80 80

Have a look at a code snippet I made a while back.

Re: I need help inserting data into MySQ database via PHP 80 80

@pritaeas, thanks for your continued support.

I took a look at the code snippet you linked to, but was confused by the fact that it mixed connection with the query in the same file.

Please, what the best way to write the following using MySQLi and also preventing SQL injection?

$query = "INSERT INTO admin (username, hash, email) VALUES (?, ?, ?),
            $username,
            $password,
            $email";  

            // if username is in database
            mysql_query($query) or die('Sorry, an error occurred while inserting data into the database. ' . mysql_error());
Re: I need help inserting data into MySQ database via PHP 80 80
Member Avatar

Your are still using mysql_* functions. pritaeas' link is using mysqli.

Re: I need help inserting data into MySQ database via PHP 80 80

confused by the fact that it mixed connection with the query in the same file

My snippet shows connection and query in the same file because it is an example snippet, and not fully functional production code.

what the best way to write the following using MySQLi and also preventing SQL injection?

This code snippet shows you how to use MySQLi with binding of parameters.

Re: I need help inserting data into MySQ database via PHP 80 80

Thanks, I'll go through the snippet to see if I can figure it out.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.