Php login system insert trouble

Question

I have an issue with my code. The purpose of the code is just a clear cut login system where you can login and create a user. My issue is with the creating a user. When I fill out the fields it'll say the error message error adding user since the user ID is zero. I asked my teacher and he said there was something wrong with my insert statements but i can't for the life of me figure it out. Can anyone point my in the right direction? Thank you! (the table name is called security)

PHP

$insert_sql ="INSERT INTO security SET ";
    $insert_sql .= " username = '".$username ."'";
    $insert_sql .= ", first_name = '".$first_name."'";
    $insert_sql .= ", last_name = '".$last_name."'";
    $insert_sql .= ", email = '".$email."'";
    $insert_sql .= ", password = '". $salted_password . "'";

    $result = $dbh->query($insert_sql);


    $user_id = $dbh->insert_id;

    if ($user_id > 0){

        session_start();
        $_SESSION['user_id'] =$user_id;



        echo "Login Created and user logged in<p>";
        echo "<a href=main.php>Click here to continue</a><p>";


    } else {
        $msg = 'Error adding user';
        NEW_LOGIN($dbh,$msg);
    }
}
}

[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, Apr 17 '17 at 20:53
**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). ***It is not necessary to [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, Apr 17 '17 at 20:53
@JayBlanchard Im salting the password this is only part of the function thats supposed to be giving me trouble. — JoJoSmith, Apr 17 '17 at 20:54
@JayBlanchard The error message is the Error adding user from the else statement, so that would mean that user_id is 0 which it shouldnt be. — JoJoSmith, Apr 17 '17 at 20:55

score 0 · Answer 1 · answered Apr 17 '17 at 21:12

Your insert statement incorrectly takes the form of a update statement.

$insert_sql ="INSERT INTO security SET ";
$insert_sql .= " username = '".$username ."'";
$insert_sql .= ", first_name = '".$first_name."'";
$insert_sql .= ", last_name = '".$last_name."'";
$insert_sql .= ", email = '".$email."'";
$insert_sql .= ", password = '". $salted_password . "'";

should actually be

$insert_sql ="INSERT INTO security (username_column_in_db, first_name_column_in_db,last_name_column_in_db,email_column_in_db)
values ('".$username ."','".$first_name."','".$last_name."','".$email."','". $salted_password . "')";

username_column_in_db, first_name_column_in_db, last_name_column_in_db and email_column_in_db need to be replaced with column names you have in your database.

Your user id returns as 0 because the record is not actually inserted, not because you tried to make the id as 0.

score 0 · Answer 2 · answered Apr 17 '17 at 21:15

According to the MySQL Docs, the proper INSERT Statement looks as follows:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

What you have is:

INSERT INTO security SET ...

Specifically take a look at the part after the columns a,b and c are defined in the first statement. See the difference now? Hope this helps.

Php login system insert trouble

2 Answers2