Problem inserting data with MySQLi

Question

Problem inserting data with MySQLi

Navigation

#1 by (8 votes)

4

I have this database created in phpMyAdmin:

The problem I have is that when I insert the data of the form where I create a user manages to connect to the database, the data sends them but does not save them, concluding with the redirection to another page.

This is my code:

<?php
$servername ="mysql.hostinger.es";
$username ="user";
$password = "pass";
$dbname = "db";
$con=mysqli_connect($servername,$username,$password,$dbname );
//check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

//add details in database
if (isset($_POST['usuario']) and isset($_POST['pass'])) {
    $user = $_POST['usuario'];
    $pass = $_POST['pass'];
    mysqli_query($con,"INSERT INTO cuentas (usuarios, clave)
VALUES ($user, $pass)");
}
header("Location: resultado.html");

?>

What is wrong? What should change? I hope you can help me with this problem.

mysql sql php mysqli phpmyadmin

asked by Alejandro Patricio 26.12.2015 в 07:20

source

1 answer

Select dependent on Django? Apply CSS styles to Light DOM (shadow DOM) in custom element HTML5

score 8 · Answer 1

The error is in how you are inserting the data into the database: some quotes are missing.

In your cuentas table, the usuarios and clave fields are alphanumeric ( varchar(30) both), and then you should go in single quotes in INSERT , but go without quotes of any kind.

For example, the current code generates:

INSERT INTO cuentas (usuarios, clave) VALUES (Alvaro Montoro, Contraseña)

That will fail because both Alvaro Montoro and Contraseña are not names of columns or known variables.

The code should look something like this:

mysqli_query($con,"INSERT INTO cuentas (usuarios, clave) VALUES ('$user', '$pass')");

That would then generate the following:

INSERT INTO cuentas (usuarios, clave) VALUES ('Alvaro Montoro', 'Contraseña')

And it would work without problems.

Apart from that (and as I mentioned in the comments) you should use parameterized queries because the current code (and the solution here based on that code) can suffer SQL injection attacks.

The change is very simple and you would gain a lot in security (to avoid cases like Bobby Tables , in English). It would be something like this:

$sql = "INSERT INTO cuentas (usuarios, clave) VALUES (?, ?)";
if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("ss", $user, $pass);
    $stmt->execute();
    ...
}

Note: Note that now the quotes are no longer needed, because as you specify that the parameters are going to be string (the "ss"), then mysqli adds them without needing you to do anything else.