I need help inserting data into MySQ database via PHP

[mexabet]

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.

[diafol]

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)?

[pritaeas]

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')"; 

[mexabet]

@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());

[pritaeas]

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

[mexabet]

@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());

[diafol]

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

[pritaeas]

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.

[mexabet]

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