MySQL Showing SQL Syntax Error when run from PHP

Question

I am trying to create a dynamic page and store it in a MySQL database. It connects to the database fine, but there seems to be an error in the SQL Syntax that I can't find. I've tried reformatting the code and cannot pin point it. Here's the PHP:

<!DOCTYPE html>
<html>
    <head lang="en">
        <meta charset="UTF-8">
        <title></title>
    </head>
    <body>
        <?php
            $dbc = mysqli_connect('localhost', 'username', 'password', 'test_db')
            or die("There was an error connecting to the database. Please try again later.");

            $fullname = (string)$_POST['name'];
            $guest_email = $_POST['email'];
            $password = $_POST['password'];
            echo "<h1>Thanks for your submission!</h1>";
            echo "Your Name on File is: ". $fullname . '<br>';
            echo "Your Email on File is: ". $guest_email . '<br>';
            echo "Your Password on File is: ". $password . '<br>';

            $add_query = "INSERT INTO test_form (full_name, email, user_pass) VALUES( $fullname, $guest_email, $password)";

            $result = mysqli_query($dbc, $add_query)
            or die("<strong>There was an error processing the form. Please call your IT support!</strong>". mysqli_error($dbc));

            mysqli_close($dbc);
        ?>
    </body>
</html>

And here's the HTML:

<!DOCTYPE html>
<html>
    <head lang="en">
        <meta charset="UTF-8">
        <title>Test Form</title>
    </head>
    <body>
        <h1>Test Form</h1>
        <form action="index.php" method="post">
            <input type="text" placeholder="Name" name="name"/>
            <input type="email" name="email" id="email" placeholder="Email"/>
            <input type="password" name="password" id="password" placeholder="Enter a password"/>
            <input type="submit" value="Submit"/>
        </form>
    </body>
</html>

This is the SQL Error: 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 'Doe , [email protected] , alkisefdlkje)' at line 1 — John
– John, Commented Jan 21, 2015 at 23:38
('$fullname', '$guest_email', '$password') <= fixed ;-) but don't store passwords in plain text. You will get hacked. — Funk Forty Niner
– Funk Forty Niner, Commented Jan 21, 2015 at 23:44

Community · Accepted Answer · 2017-03-17 10:45:55Z

1

You need to quote your values when it comes to strings

('$fullname', '$guest_email', '$password')

Important note about password storage:

I noticed you may be storing passwords in plain text. If this is the case, it is highly discouraged and if used on a live site, you will get hacked.

I recommend you use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

If and when you do use one of those, make sure the column is long enough to accomodate the hash.

Plus, in regards to SQL injection which you are presently open to, use mysqli with prepared statements, or PDO with prepared statements, they're much safer.

edited Mar 17, 2017 at 10:45

CommunityBot

11 silver badge

answered Jan 21, 2015 at 23:53

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kewin Dousse · Accepted Answer · 2015-01-21 23:39:15Z

0

In $add_query, you're not concatenating the other variables correctly, like you did before. This should work :

$add_query = "INSERT INTO test_form (full_name, email, user_pass) VALUES ( '" . $fullname . "' , '" . $guest_email . "' , '" . $password . "')";

edited Jan 21, 2015 at 23:39

answered Jan 21, 2015 at 23:30

Kewin Dousse

4,0592 gold badges29 silver badges50 bronze badges

3 Comments

Funk Forty Niner Over a year ago

( " . $fullname . " , " . $guest_email . " , " . $password . ") This will also fail. We're dealing with strings.

John Over a year ago

It did fail. How would I quote post values?

John Over a year ago

It's still coming up as failed. They're all VARCHAR fields, would that happen to have anything to do with it? It runs fine when I run it through SQLPro with a plain string.

Enor · Accepted Answer · 2015-01-22 00:42:45Z

You should be aware of the fact that your code is vulnerable to SQL Injection, so it must not be used in 'real world' environments but for testing only!

To prevent SQL Injections from the beginning you can use Prepared Statements as described in the docs:

I am not familar with the mysqli driver and would recommend to use PDO, which is a more generic approach for database connection.

However, your code with mysqli prepared statements should look like this:

<?php
$dbc = mysqli_connect('localhost', 'username', 'password', 'test_db')
or die("There was an error connecting to the database. Please try again later.");

$fullname = (string)$_POST['name'];
$guest_email = $_POST['email'];
$password = $_POST['password'];
echo "<h1>Thanks for your submission!</h1>";
echo "Your Name on File is: ". $fullname . '<br>';
echo "Your Email on File is: ". $guest_email . '<br>';
//echo "Your Password on File is: ". $password . '<br>'; // never ever print any password!

/* create prepared statement */
$stmt = mysqli_prepare($dbc, "INSERT INTO test_form (full_name, email, user_pass) VALUES(?, ?, ?)");

/* bind params to prepared statement */
mysqli_stmt_bind_param($stmt, 'sss', $fullname, $guest_email, $password);

/* execute prepared statement */
mysqli_stmt_execute($stmt);

/* close statement and connection */
mysqli_stmt_close($stmt);

/* close connection */
mysqli_close($dbc);

?>

Collectives™ on Stack Overflow

MySQL Showing SQL Syntax Error when run from PHP

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related