Inserting variable into my mysql table with php

Question

I am trying to insert a value into my mysql table by using a variable but i can´t get it to work as it takes the variable as an empty variable, what have i done wrong?

<?php
$conn=new mysqli("domain","user","password","database");
if($conn->connect_error)
{
    die("Connection failed: " . $conn->connect_error);
}
$name ="test2";
$email="1234";
$password ="1234";
$sql="INSERT INTO android(name,email,password) values('$name','$email','$password')";

echo $sql;
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} 
else {
echo "Error: " . $sql . "<br>" . $conn->error;
} 

$conn->close();
?>

which gives me the response:

Error: insert into android(name,email,password) values('','','') Duplicate entry '' for key 'PRIMARY'

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Dec 05 '16 at 14:05
In your given table structure, which column is associated with PRIMARY KEY. — Nana Partykar, Dec 05 '16 at 14:09
the email one, and there are none with the same name as above — Ella, Dec 05 '16 at 14:11
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure you ***[don't escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Dec 05 '16 at 14:12
Looks like you're sending blank values to the query according to your error — Jay Blanchard, Dec 05 '16 at 14:14

Masivuye Cokile · Answer 1 · 2016-12-05T14:31:13.697

I believe you are still in the process of learning php MySQL.. My suggestion to you is to stop this tutorial(s) that you are currently using, and start learning prepared statements with mysqli or pdo, and never store passwords in plain text even if you are practicing on your local machine, its better to start learning the correct way of doing thing from the word go..

php have amazing functions to hash and secure your passwords, password_hash() and password_verify()

Your code with mysqli prepared should look like

<?php
$servername = "localhost";
$username = "user";
$password = "password";
$hash = password_hash($password,PASSWORD_DEFAULT);
$dbname = "database";

$name ="test2";
$email="1234";
$password ="1234";
$hash=password_hash($password,PASSWORD_DEFAULT);


$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO android (name,email,password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $name, $email, $hash);
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Your error is on the duplicate on email that u made a primary key on your table, so therefore you need to check if that email does not exists before you can insert it.

Your proper code should be something like:

<?

$sql = $con->prepare("SELECT email from android WHERE email = ? LIMIT 1");
$sql->bind_param('s', $email);
$sql->execute();

$sql->bind_result($email);
$sql->store_result();
if ($sql->num_rows == 1) //row exists
    {
    if ($sql->fetch()) //contents of the row
        {

        echo $email . "already registered";
    }
} else {
    //email does not exist lets insert

    // prepare and bind
    $sql = $conn->prepare("INSERT INTO android (name,email,password) VALUES (?, ?, ?)");
    $sql->bind_param("sss", $name, $email, $password);


    $sql->execute();

    echo "New records created successfully";

    $sql->close();
    $conn->close();

}
?>

Inserting variable into my mysql table with php

1 Answers1