MYSQL PHP inserting into table

Question

<?php
$conn= new mysqli("localhost", "my_user", "my_password", "world"); //changed for the sake of this question

$username = $_POST['Username'];
$password = sha1($_POST['Password']);
$email = $_POST['Email'];
$firstname = $_POST['FirstName'];
$lastname = $_POST['LastName'];

$insert = 'INSERT INTO User(FirstName, LastName, Email, Username, Password, Type) VALUES ("'.$firstname.'", "'.$lastname.'", "'.$email.'", "'.$username.'", "'.$password.'", 'User');';

$result = $conn->query($insert);

?>
<form method='post' action='regprocess.php'>
<fieldset class="register">
<h2>Register</h2>
<ul>
    <li><label for="FirstName">First Name: </label> <input type="text" name="FirstName" id="FirstName"></li>
    <li><label for="LastName">Last Name: </label> <input type="text" name="LastName" id="LastName"></li>
    <li><label for="Email">Email: </label><input type="email" name="Email" id="Email"></li>
    <li><label for="Username">Username: </label><input type="text" name="Username" id="Username"></li>
    <li><label for="Password">Password: </label><input type="password" name="Password" id="Password"></li>
    <li><input type="submit" value="Register"></li>
</ul>
</fieldset></form>

The form and the top sql code are in separate files.

Hello everybody, I'm trying to insert into an mysql table, and it won't insert into my table. I'm trying to get it to insert through a registration table. And I'm not quite sure why it's not working. Some insight would be great. If you need me to provide the table I will, but I don't think it's part of the reason it's not working.

There is an error in the query. Try escaping the quotes around the User part so it becomes \'User\'. — ACJ
– ACJ, Commented Aug 21, 2012 at 2:05
You mean the user bit in the values section? or the database user? — courtney
– courtney, Commented Aug 21, 2012 at 2:11
Use prepared statements or you'll be vulnerable to bobby-tables.com — Lusitanian
– Lusitanian, Commented Aug 21, 2012 at 2:50

tadman · Accepted Answer · 2012-08-21 06:36:05Z

1

It's a good thing you're using mysqli, but you're using it incorrectly and are exposing yourself to a number of very serious SQL injection bugs, the consequences of which could be severe.

This is what you should be doing to actually fix the numerous problems present in your example:

$stmt = $conn->prepare('INSERT INTO User(FirstName, LastName, Email, Username, Password, Type) VALUES (?,?,?,?,?,?)');

$stmt->bind_param($firstname, $lastname, $email, $username, $password, 'User');
$stmt->execute();

$result = $stmt->get_result();

The primary advantage of placeholders is not having to worry about how to properly quote data, it's done for you automatically. It also largely avoids having to use two different kinds of quotes within your statement.

If you do not use placeholders for ANY and ALL data being put into your SQL you may end up in serious trouble. You must be vigilant about this.

edited Aug 21, 2012 at 6:36

answered Aug 21, 2012 at 6:17

tadman

212k23 gold badges237 silver badges266 bronze badges

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

Comments

Yosi Benezra · Accepted Answer · 2012-08-21 02:29:08Z

0

Your last parameter value is in single quotes. Replace with double quotes so that it reads ..."'.$password.'", "User");';

answered Aug 21, 2012 at 2:29

Yosi Benezra

361 bronze badge

Comments

SuMu · Accepted Answer · 2012-08-21 02:30:39Z

0

I don't know PHP. I will try helping you with MySQL though.

I think there is a problem with quotation marks in the insert query.

Try removing single quotes from your values clause.

answered Aug 21, 2012 at 2:30

SuMu

11 silver badge4 bronze badges

Comments

Ionut Flavius Pogacian · Accepted Answer · 2012-08-21 06:24:20Z

0

As an advice, always use " from the start to the end of the sql query text;

$insert = 'INSERT INTO User(FirstName, LastName, Email, Username, Password, Type) VALUES ("'.$firstname.'", "'.$lastname.'", "'.$email.'", "'.$username.'", "'.$password.'", 'User');';

put it like:

$insert = "INSERT INTO User(`FirstName`, `LastName`, `Email`, `Username`, `Password`, `Type`) VALUES ('$firstname', '$lastname', '$email', '$username', '$password', 'User');";

In your query, you had 'User' and you need to escape the ' as \'

And dont forget to always sanitize your content before adding it to the database

answered Aug 21, 2012 at 6:24

Ionut Flavius Pogacian

4,81115 gold badges62 silver badges101 bronze badges

Collectives™ on Stack Overflow

MYSQL PHP inserting into table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related