1

I am trying to make a sign up page using php and then when I write the mysql statements below it won't accept it rather it will spit out an error which I am trying to figure out for hours now.

   $query = "INSERT INTO users(username, password,name,surname,email,role)VALUES($username, $password,$name,$lastname,$email,$role)";
   $stmt = mysqli_prepare ($dbh, $query) or die(mysqli_error($dbh));

The error that shows is:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@hotmail.com,Employee)' at line 1.

Qirel
  • 25,449
  • 7
  • 45
  • 62
sajaNgaKavaja
  • 39
  • 1
  • 2
  • 8

3 Answers3

5

Make sure you use proper quotes if you insert strings: 

$query = "INSERT INTO users(username, password,name,surname,email,role)VALUES('$username', '$password','$name','$lastname','$email','$role')";
hartwecm
  • 219
  • 1
  • 4
3

You start to use prepared statements, but then you turn around and insert variables directly into the query - don't do that, as it directly defeats the purpose of using prepare(). Bind your variables instead.

$query = "INSERT INTO users(username, password, name, surname, email, role) VALUES (?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($dbh, $query) or die(mysqli_error($dbh));
$stmt->bind_param('sssss', $username, $password, $name, $lastname, $email, $role);
$stmt->execute();
$stmt->close();

Your original error came due to lack of quotes around strings in the query, but when using prepared statements with placeholders, you shouldn't have it either.

If you've used mysqli_real_escape_string() on your variables that you insert, you should now remove that - as these variables are now parameterized in the query.

See the below links and documentation

Community
  • 1
  • 1
Qirel
  • 25,449
  • 7
  • 45
  • 62
2

For security reasons you should not use the variables inside the query.

Instead use this:

$stmt = mysqli_prepare($link, "INSERT INTO users(username, password,name,surname,email,role)VALUES(?,?,?,?,?,?)");
mysqli_stmt_bind_param($stmt, 'ssssss', $password, $name, $surname, $email, $role);
mysqli_stmt_execute($stmt);

Take a look at the php-documentation:

Roman
  • 2,530
  • 2
  • 27
  • 50