Getting an SQL syntax error on a section of my insert code

Question

Im currently making a PHP submit form that allows records to be added to a phpmyadmin database. I have created this code and have been stuck all night trying to work out whats wrong with it

When i typed the names of the fields in the form (for dummy data) i got this:

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 'Password', 'Firstname', 'Lastname', 'Address', 'Email', 'Card Number', 'CCV' )' at line 1

<!DOCTYPE html>
<html>
<head><title>Insert Users</title></head>
<body>
<h2>Insert User Confirmation</h2>

<form action="<?php $_SERVER['PHP_SELF']?>" method="post"/> <br> 
<?php
    require_once('connection.php');

    echo    "<label for='memberID' >Member ID:</label>";
    echo    "<input type='text' name='memberID' id='memberID' />";
    echo    "<br /><br />"; 

    echo    "<label for='username' >Username:</label>";
    echo    "<input type='text' name='username' id='username' />";
    echo    "<br /><br />"; 

    echo    "<label for='password' >Password:</label>";
    echo    "<input type='password' name='password' id='password' />";
    echo    "<br /><br />"; 

    echo    "<label for='fName' >Firstname:</label>";
    echo    "<input type='text' name='fName' id='fName' />";
    echo    "<br /><br />";     

    echo    "<label for='lName' >Lastname:</label>";
    echo    "<input type='text' name='lName' id='lName'  />";
    echo    "<br /><br />"; 

    echo    "<label for='address' >Address:</label>";
    echo    "<input type='text' name='address' id='address'  />";
    echo    "<br /><br />"; 

    echo    "<label for='email' >Email:</label>";
    echo    "<input type='text' name='email' id='email'  />";
    echo    "<br /><br />"; 

    echo    "<label for='cardnumber' >Card Number:</label>";
    echo    "<input type='text' name='cardnumber' id='cardnumber'  />";
    echo    "<br /><br />"; 

    echo    "<label for='ccv' >CCV:</label>";
    echo    "<input type='text' name='ccv' id='ccv'  />";
    echo    "<br /><br />"; 

    echo    "<input type='submit' name='submit' value='Submit' />";
    echo    "<input type='reset' value='Clear' />";
    echo    "<br /><br />"; 
?>
</form>


<?php
    if(!isset($_POST['submit'])) {  
        echo 'Please Register';
    }
    else {
        $memberID = $_POST['memberID'];
        $username = $_POST['username'];
        $password = $_POST['password'];
        $fName = $_POST['fName'];
        $lName = $_POST['lName'];
        $address = $_POST['address'];
        $email = $_POST['email'];
        $cardnumber = $_POST['cardnumber'];
        $ccv = $_POST['ccv'];

        $query = "INSERT INTO `members` (MemberID, Username, Password, FirstName, LastName,     StreetAddress, Email, CardNumber, CCV) VALUES ('$memberID', '$username, '$password', '$fName', '$lName', '$address', '$email', '$cardnumber', '$ccv' )";
        mysqli_query($connection, $query)
        or  die(mysqli_error($connection));

        $rc = mysqli_affected_rows($connection);
        if ($rc==1)   
                    {
                    echo '<h4>The database has been updated with the following details: </h4> ';
                    echo 'MemberID: '.$memberID.'<br />';
                    echo 'Username: '.$username.'<br />';
                    echo 'Password: '.$password.'<br />';
                    echo 'First Name: '.$firstname.'<br />';
                    echo 'Last Name: '.$lastname.'<br />';
                    echo 'Address: '.$address.'<br />';
                    echo 'Email: '.$email.'<br />';
                    echo 'Card Number: '.$cardnumber.'<br />';
                    echo 'CCV: '.$ccv.'<br />';
                    }
        else
                    {   
                    echo '<p>The data was not entered into the database this time.</p>';   
                    }
    }

?>
</body>
</html>

Make your query multiline to see better which part is at fault — B-and-P
– B-and-P, Commented Sep 23, 2014 at 14:33
If you do store store credit card numbers you must follow PCI guidelines. These guidelines are set by the payment card industry and define what you can and cannot do. — Daan
– Daan, Commented Sep 23, 2014 at 14:34
If they're teaching you these reckless habits in university, they should be barred from teaching anything to do with credit card processing. Imagine if medical school was this slapdash. "Washing your hands before surgery? Nah. Just wipe your hands on your pants." You wonder how gigantic hacks happen? It starts with this. — tadman
– tadman, Commented Sep 23, 2014 at 14:48

stackiflow111 · Accepted Answer · 2014-09-23 14:57:48Z

4

I can't add comments due too low reputation so I answer here:

Have you noticed the missing ' after '$username?

Try

$query = "INSERT INTO `members` (MemberID, Username, Password, FirstName, LastName,     StreetAddress, Email, CardNumber, CCV) VALUES ('$memberID', '$username', '$password', '$fName', '$lName', '$address', '$email', '$cardnumber', '$ccv' )";

edited Sep 23, 2014 at 14:57

answered Sep 23, 2014 at 14:34

stackiflow111

1551 silver badge9 bronze badges

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

2 Comments

Matthew Campana Over a year ago

... after all that it was just a (') I hate coding so much. Thank you

tadman Over a year ago

This is not a quoting problem. This is just insane. When using mysqli you should be using parameterized queries and bind_param to add user data to your query. That will take care of any quoting issues automatically. DO NOT use string interpolation to accomplish this because you will create severe SQL injection bugs. Any answer that uses string interpolation is wrong regardless of the form of the original question.

Carnangel · Accepted Answer · 2014-09-23 14:39:05Z

-1

Change your insert, you've done it wrong I think

INSERT INTO members(MemberID, Username, Password, FirstName, LastName, StreetAddress, Email, CardNumber, CCV) VALUES ('$memberID', '$username', '$password', '$fName', '$lName', '$address', '$email', '$cardnumber', '$ccv')

answered Sep 23, 2014 at 14:39

Carnangel

811 silver badge8 bronze badges

Collectives™ on Stack Overflow

Getting an SQL syntax error on a section of my insert code

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related